Tag: t-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!!

Posted on

Dropping unknown default constraints in SQL server

For releases, we had to provide SQL scripts to install database changes. This had to be done outside of SQL server management studio and run on multiple environments. Occasionally, I had to drop unnamed constraints in the script. (Most developers wrote their install scripts with named constraints to avoid the difficulty). Every time I encountered this problem, I googled and followed this excellent blog post by Rob Farley. It gave the SQL commands to query for the name of an unnamed constraint, given the table and column name. It stopped short of giving you the SQL code to actually drop the constraint.

So here is my take:

declare @df_name varchar(max)
select @df_name = d.name from sys.tables t
    join sys.default_constraints d
        on d.parent_object_id = t.object_id
    join sys.columns c
        on c.object_id = t.object_id
        and c.column_id = d.parent_column_id
    where t.name = 'some_db_table'
    and c.name = 'some_column_in_table'

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

SQL Server stored procedures continue execution on error

This week I came across a bug in a stored procedure caused by this baffling default behaviour in SQL Server. By default, the execution of a stored procedure continues onto the next statement after an error is raised. The stored procedure in question calls another stored procedure, which has been modified in the previous release to take an extra parameter.

DECLARE @tmp TABLE (value VARCHAR(100))
INSERT INTO @tmp 
	EXEC sp_anotherStoredProcedure
		@orderId = @orderId,
		@petType = 'cat'

	IF NOT EXISTS(
		SELECT * 
		FROM @tmp l
		INNER JOIN Awesome_Event_Buffer r ON l.value = r.Description
		WHERE r.customer = @customer
	)
		print 'do some important things'

Because of the addition of the new parameter, the nested store procedure call now always fail with an error. However, the calling stored procedure is not halted, but continues with the next statement. The if not exists check now always return true, and the important things are done regardless.

To abort a stored procedure whenever a runtime error occurs, set When SET XACT_ABORT to ON, like

ALTER PROCEDURE [dbo].[sp_storedProcedure]
	@parameter1 INT,
        @parameter2 INT
AS
	set xact_abort on
        ...

Alternatively, you can check for errors explicitly using the error number @@error

if (@@error <> 0)
    return (@@error)
Posted on

SQL union does not guarantee the order of the result set

I introduced a bug in a SQL script I wrote recently because I was unaware that SQL union did not guarantee the order of the returned result set.

The SQL stored procedure finds the primary and secondary network nodes that match a set of criteria. It looks something like:

select bd.name, n.nodeId, r.realmName
from BuildDefaults bd
join NetworkNodes n on bd.nodeId = n.nodeId
join NetworkRealms r on cn.realmId = r.realmId
where bd.typeId = @type and bd.locationId = @location
union
select bd.name, n.nodeId, r.realmName
from BuildDefaults bd
join NetworkNodes n on bd.nodeId = n.nodeId
join NetworkRealms r on n.realmId = r.realmId
where bd.typeId = @type and bd.locationId = @secLocation

I assumed the result returned by the first select would always be the first row of the final result set. (And the result from the second select would be the second row). In retrospect, it should have been obvious that this assumption was wrong. SQL never guarantees order unless the keyword order by is used!

To guarantee the order in a SQL union, an extra order column has to be used:

select bd.name, n.nodeId, r.realmName, 1 as nodeOrder
from BuildDefaults bd
join NetworkNodes n on bd.nodeId = n.nodeId
join NetworkRealms r on cn.realmId = r.realmId
where bd.typeId = @type and bd.locationId = @location
union
select bd.name, n.nodeId, r.realmName, 2 as nodeOrder
from BuildDefaults bd
join NetworkNodes n on bd.nodeId = n.nodeId
join NetworkRealms r on n.realmId = r.realmId
where bd.typeId = @type and bd.locationId = @secLocation
order by nodeOrder
Posted on

Running SQLCMD in SQL Server Management Studio 2012

I normally run sqlcmd commands on the command line. However, there are others in my team who run them inside SQL Server Management Studio (SSMS). Therefore I have to make sure my scripts run within SSMS as well.

By default, SSMS doesn’t understand sqlcmd commands.

Not in SQLCMD mode

To use the Database Engine Query Editor to write or edit SQLCMD scripts, you must enable the SQLCMD scripting mode. By default, SQLCMD mode is not enabled in the Query Editor. You can enable scripting mode by clicking the SQLCMD Mode icon in the toolbar or by selecting SQLCMD Mode from the Query menu.

To use SSMS to write or edit sqlcmd scripts, sqlcmd scripting mode must be enabled before script execution.

SQLCMD mode button

However, the sqlcmd mode icon is not on the toolbar in SSMS 2012 by default. To drop the icon onto the toolbar,

  1. Open the menu Tools -> Customize -> Command, choose the SQL editor toolbar.
  2. Then under ‘Query Options’, click the ‘Add Command…’ button.
  3. Select ‘Query’ in Categories, and choose ‘SQLCMD Mode’ in the commands.

Now when you have the query window highlighted, you should be able to toggle SQLCMD mode, before running execute.

Posted on

The mysterious “the statement did not return a result set” SQL Server Exception

Last week I worked on a SQL server stored procedure that looked something like this

create procedure sp_Awesome_Proc
(
  @numbers XML,
  @customerId int
)
AS
BEGIN
declare @output table(......)
insert into @output(....) select .... from ...
... some more updates and joins ...
select * from @output
END

This stored procedure was called from Java using Spring’s JdbcTemplate

query("exec sp_Awesome_Proc @numbers=?, @customerId=?", new Object[] {a, b}, new RowMapper {....});

When I called the stored procedure via the above Java code, I kept getting the exception SQLServerException: The statement did not return a result set. However, if I used the same parameters and called the stored procedure within SQL Server Studio, it returned a table.

It turned out that if the stored procedure performed any inserts or updates prior to the final select, this baffling statement did not return a result set exception will be thrown. In my case, a series of queries were executed to populate a table variable which is returned at the end of the stored procedure.

A simple solution to this problem is to add SET NOCOUNT ON at the start of the stored procedure.

create procedure  (
  @numbers XML,
  @customerId int
)
AS
BEGIN
SET NOCOUNT ON 
...
END

In fact, all the stored procedure within our code base had this statement. I have been copy and pasting this into all the previous stored procedures I created, without knowing its significance. Only now I learned the why behind it.

Posted on

Linked Servers : Creating a local development database in MS SQL Part 3

One of the tables I wanted to copy to my local SQL server has nearly 200 million entries. It would take far too long to copy if I generate scrips for the data using the SQL server scripting tool. Besides, I didn’t need all 200 million entries for development anyway.

The Linked Servers feature in SQL server management studio makes it simple to copy a selection of data from table to another. Once two database instances are linked, you can use SQL select and insert commands to copy data.

Create a linked server

Open the local database in SQL server management studio. Select Server Objects -> Linked Servers. Then right click and select New Linked Server. The following dialogue will appear on screen.

null

Add an appropriate name in the Linked Server text box (for example, lotsofdata-server). Under server type, select the SQL server radio button. Choose Security on the left navigation pane. Select the radio button Be made using this security context. Enter the correct username and password for the remote server.

To copy the first 10000 rows from the remote server lotsofdata-server into a table that doesn’t exist in the local database

select TOP 10000 * into dbo.[awesomeTable]  from [lotsofdata-server].[awesomeDatabase].dbo.[awesomeTable]

If the table already exist in the local database,

insert into dbo.[awesomeTable] select TOP 10000 * from [lotsofdata-server].[awesomeDatabase].dbo.[awesomeTable]
Copying an identity column

If the table you wanted to copy contains an identity column, then you need to turn on identity insert

SET IDENTITY_INSERT awesomeTable ON

and explicitly specify all the columns you are inserting into the table, like

insert into dbo.[awesomeTable] (col1, col2) select TOP 10000 (col1, col2) from [lotsofdata-server].[a-database-name].dbo.[awesomeTable]

Otherwise, SQL server will complain along the lines of cannot insert explicit value for identity column in table awesomeTable.

Posted on

Creating a test user : Creating a local development database in MS SQL Part 2

When I was given my work laptop, it already had MS SQL server and the management studio installed. It was set up to use windows authentication. On the other hand, our populated test/development database used a SQL server authentication (ie with username/password). I needed to create a login for a test user on my local MS SQL server to achieve compatibility.

Create a test user login

First, in SQL server management studio, open a new query window by right clicking on the server name and select New Query. (This will create a query window for the master database). Create a login for user ${db.username} with ${db.password}.

USE master;
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'${db.username}')
CREATE LOGIN [${db.username}] WITH PASSWORD = '${db.password}';

Then to add the new user to the test database ${db.name}.

USE [${db.name}];
CREATE USER [${db.username}] FOR LOGIN [${db.username}] WITH DEFAULT_SCHEMA=[dbo];
ALTER ROLE [db_owner] ADD MEMBER [${db.username}];
ALTER ROLE [db_datareader] ADD MEMBER [${db.username}];
ALTER ROLE [db_datawriter] ADD MEMBER [${db.username}];
Mixed authentication

My SQL server was originally set to only allow windows authentication. I needed the SQL server instance to accept mixed authentication instead. (Mixed authentication allows both windows and sql server style authentications). In SQL server management studio, right click on the server name, then choose Properties. On the Security page, under Server authentication, select SQL Server and Windows Authentication mode.

You need to restart the SQL server instance to activate this feature. You can restart the server by right clicking on the server name again, and choose Restart. However, you might want to activate TCP/IP authentication before restarting.

Enable TCP/IP Protocol

Lastly, open the SQL server configuration manager (via the windows start menu). Under SQL Server Network Configuration -> Protocols for MSSQLSERVER, toggle the status for TCP/IP to Enabled. You can restart the server now by going to SQL Server Services (in the left navigation pane), right click on SQL Server (MSSQLSERVER) and choose Restart.