Tag: transact-sql

Posted on

Dropping unknown foreign key constraints in SQL server

A few years ago, I wrote a blog post about scripting the drop of unknown default constraints in SQL server. Today, I came across a similar problem but with unnamed foreign key constraint. Below is the SQL command needed for find the name of a foreign key constraint given the table and column names.

declare @fc_name varchar(max)
select @fc_name = o.name from sys.tables t
    join sys.foreign_key_columns fk
		on fk.parent_object_id = t.object_id
    join sys.columns c
	    on c.object_id = t.object_id
		and c.column_id = fk.parent_column_id
	join sys.objects o
		on o.object_id = fk.constraint_object_id
	 where t.name = 'some_db_table'
    and c.name = 'some_column_in_table'

if @fc_name is not null
begin
   declare @sql varchar(max) = 'alter table some_db_table ' + ' drop constraint ' + @fc_name 
   exec (@sql)
end
Posted on

Indexed computed column for frequenty used XML values

If you query an XML element or attribute value frequently, you can promote the value into a column and create an index on the promoted column. This makes the code easier to read and queries faster.

As an example, you have a XML column called requestXML in the table Orders. Within the requestXML is a XPath element /Order/accountId which your application queries frequently. If you try to create a computed column for the element value as follow, SQL server will return the error message ‘Xml data type methods are not supported in computed column definitions. Create a scalar user-defined function to wrap the method invocation’.

ALTER TABLE dbo.Orders
  ADD accountId AS requestXML.value('(/Order/accountId)[1]', 'varchar(6)')

This is SQL server way of saying you need to create a function 

create function fnGetAccountIdFromRequestXML(@request xml)
 returns varchar(6)
 with schemabinding
 begin
     declare @accountId varchar(6)
     select @accountId = @request.value('(/Order/accountId)[1]', 'varchar(6)')
     return @accountId 
 end

Don’t forget the ‘with schemabinding’ part if you want to create an index on the computed column. Now you are ready to create the computed column and then the index

ALTER TABLE dbo.Orders
ADD accountId AS dbo.fnGetAccountIdFromRequestXML(requestXML);

CREATE NONCLUSTERED INDEX IX_Orders_accountId  ON  dbo.Orders(accountId)

The accountId element in requestXML has now been promoted to an indexed computed column. It can now be used in queries just like any normal SQL database table columns.

Posted on

Computing the prefix from a alphanumeric varchar column in SQL Server

Recently, I needed to do some data analysis for a project I was working on. The order references are alphanumeric strings, with a variable length prefix that indicates the origin system. For example, order references AB123456789 and MOB98765 have prefixes AB and MOB.

I have pulled the data into a SQL database table for analysis. I wanted to use the prefix in SQL group by and aggregrate functions. To accomplish this, I created a computed column for the order prefix.

ALTER TABLE datatable
ADD order_ref_prefix 
AS (stuff(order_ref, patindex('%[0-9]%', order_ref), 10000, ''))

This computed column make use of two SQL server functions patindex and stuff. Patindex returns the starting position of the first occurrence of a pattern in a string. In this example, it returns the first occurence of a number in the order reference. Stuff replaces part of a string with another. Here, we replace the substring between the start of the first number in the order reference to position 10000 with an empty string. This effectively truncates the order reference from the first number found.

Once I have the order prefix in its own column, I can use them in queries like these:

select order_ref_prefix, count(*) as occurrence
from datatable
group by order_ref_prefix
order by occurrence desc

select *
from database
where order_ref_prefix = 'MOB' and <other predicates>

Super handy!!