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
declare @sql varchar(max) = 'alter table some_db_table ' + ' drop constraint ' + @fc_name
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)', 'varchar(6)')
This is SQL server way of saying you need to create a function
create function fnGetAccountIdFromRequestXML(@request xml)
declare @accountId varchar(6)
select @accountId = @request.value('(/Order/accountId)', 'varchar(6)')
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.
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
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
group by order_ref_prefix
order by occurrence desc
where order_ref_prefix = 'MOB' and <other predicates>