LEN and DATALENGTH

LEN function is very commonly used function in t-sql. One thing to note in this function that is ignores the trailing blanks;

Declare @v VarChar(5)

Set @v = 'ati '

Select Len(@v)

Same goes for CHAR and NVARCHAR.

To overcome this, use and character at the end and minus 1 from the length J

Declare @v VarChar(5)

Set @v = 'ati '

Set @v = @v + '?'

Select Len(@v)-1

Some people might use DATALENGTH function;

Declare @v VarChar(5)

Set @v = 'ati '

Select  DATALENGTH(@v)

But be alert as DATALENGTH function counts the bytes, not the length. If you change the data type of @v from varchar to nvarchar, the result will change;

Declare @v NVarChar(5)

Set @v = 'ati '

Select  DATALENGTH(@v)

Thanks for reading.

XACT_ABORT V/S TRY .. CATCH

Few days back we witnessed an issue that the SQL Server was running slow. We investigated and soon found out that there were uncommitted transactions on the server. We re-confirmed those transactions from the developers and resolved the issue. Someone tossed the idea to apply SET  XACT_ABORT ON in all SPs so that this situation may not occur again. That made me to write this article so that I can explain the pros and cons of SET XACT_ABORT ON option.

SET XACT_ABORT ON

As many of you already know that this options just takes the whole batch a one transaction. If any of the statement in the batch fails, the transaction of the whole batch is rolled back. Commits in case of success. Simple. But not that simple. You cannot control the flow of transaction with if XACT_ABORT is ON. TRY .. CATCH will not work. Even if you have the error log is implemented in CATCH block, it will not be saved as it will also get rolled backed. So, DB end error log is gone. In some cases you may need to continue with the rest of your code in case of an error, for example return a dataset. You cannot do this as well. The plus point you will never have an open transaction on your server. All transactions will either commit or rollback.

Another way to handle an open transaction issue is to apply TRY .. CATCH in all SPs. Ok, I know. There are few limitations of TRY .. CATCH. TRY .. CATCH do not catch the schema related errors. It will return an error without hitting CATCH block where you have rolled back opened transaction in case of a schema error (table does not exists, etc ). Check the code below;

       BEGIN TRY 

              -- Table does not exist; object name resolution 

              -- error not caught. 

              begin Tran

              SELECT * FROM NonexistingTable;

              commit 

       END TRY 

       BEGIN CATCH 

              rollback

              SELECT  

                     ERROR_NUMBER() AS ErrorNumber 

                 ,ERROR_MESSAGE() AS ErrorMessage; 

       END CATCH 

      

       GO    

       Select @@TRANCOUNT

      

Execute the above statements. You will end up with an opened transaction.

You can also try this in a stored procedure. You will still end up in an open transaction.

Bow add SET XACT_ABORT ON;

       SET XACT_ABORT ON

       BEGIN TRY 

              -- Table does not exist; object name resolution 

              -- error not caught. 

              begin Tran

              SELECT * FROM NonexistingTable;

              commit 

       END TRY 

       BEGIN CATCH 

              rollback

              SELECT  

                     ERROR_NUMBER() AS ErrorNumber 

                 ,ERROR_MESSAGE() AS ErrorMessage; 

       END CATCH 

      

       GO    

       Select @@TRANCOUNT

Your transaction will be rolled back and you will find no open transaction.

CONCLUSION

XACT_ABORT ON solves the open transaction issue but at the cost of logging implemented in CATCH block (if you have any).

But “NonexistingTable” type of issues should not be in the production environment. Like why will I deploy a SP using a table that does not exists? Strange thing for me. You should have QA in place and your deployment scripts should be verified before running in the production environment.

 You decide.

SET DATEFIRST and SET LANGUAGE

This is a tricky part. SQL Server have language settings at the following levels;

1.       Server

2.       User Login

3.       Session

You need to be very clear for the language settings of the user / login as it may impact the date functions;

DECLARE @Today DATETIME; 

SET @Today = getdate(); 

 

SET LANGUAGE Italian; 

SELECT DATENAME(month, @Today) AS 'Month Name'; 

Select @@DATEFIRST

 

SET LANGUAGE us_English;

SELECT DATENAME(month, @Today) AS 'Month Name' ; 

Select @@DATEFIRST

SET LANGUAGE British;

SELECT DATENAME(month, @Today) AS 'Month Name' ; 

Select @@DATEFIRST

SET LANGUAGE us_English;

GO 

As you can see from the above code, I have set the language back to US English (English in SQL Server language drop downs) as I like to have it that way. You also see that @@DATEFIRST function returns different value for different languages. This value is set on the basis of;

1.       Language selected

2.       SET DATEFIRST option

You can change the language and still keep the First date of week same as in US English;

SET LANGUAGE us_English;

Select @@DATEFIRST

SET LANGUAGE British;

Select @@DATEFIRST

SET DATEFIRST 7

Select @@DATEFIRST

As you can see, US_English sets the @@DateFirst to 7 and British sets it to 1. But we have used SET DATEFIRST to change the language to British but kept @@DATEFIRST  to 7 using SET DATEFIRST option.

This is very important when you are working multilingual databases. Where ever you are using day of week values (in DatePart, etc), you should keep this change in mind as well. Either use one language throughout your database for Server, users and session. But if there might be any change to get the benefit of SQL Server multilingual support, you should use SET DATEFIRST into account when programming to keep all the calculations and checks aligned.

xp_fileexist And its Alternate

xp_fileexists is a very useful undocumented stored procedure of SQL Server.

The usage of xp_fileexist is as follow;

Exec xp_fileexist “E:\abc.txt”

The values returned are

You can also use OUTPUT parameter to get the value of “File Exists” column as below;

Declare @vFileExists int

exec master.dbo.xp_fileexist 'E:\abc.txt', @vFileExists OUTPUT

Select @vFileExists

If you want to save all three returned values, then you will have to go throu Temp Table approach;

Declare @vFileExists Table (FileExists int, FileDir int, ParentDirExists int)

insert into @vFileExists

       exec master.dbo.xp_fileexist 'E:\abc.txt'

Select * from @vFileExists

Sometimes xp_fileexist behaves abnormally. Your file is there and it does not check the file and returns 0 in FileExists column. This is an extended SP so its behavior may also change with different versions of SQL Server.

If you are facing the same problem, change you code with xp_cmdshell “dir” approach;

Declare @vExistsPath nvarchar(100)

Declare @files Table ([FileName] nvarchar(100))

Set @vExistsPath = ''

Set @vExistsPath = 'E:\abc.txt'

Set @vExistsPath = 'dir ' + @vExistsPath + ' /b'

Insert into @files EXEC xp_cmdshell @vExistsPath

Select * from @files

if Exists(Select 1 from @files where [FileName] = 'abc.txt' And [FileName] is Not Null)

begin

       Select 1

end

else

begin

       Select 0

end

The “/b” switch returns only filename with extension as a result of “dir” command

Error Handling in SQL Server 2012

As we all know that from SQL Server 2005 and onward, we had TRY CATCH to handle the exceptions / errors in t-sql code along with @@ERROR methodology.

@@ERROR

Returns an error number if the previous statement encountered an error. If the error was one of the errors in the sys.messages catalog view, then @@ERROR contains the value from the sys.messages.message_id column for that error. You can view the text associated with an @@ERROR error number in sys.messages.

Because @@ERROR is cleared and reset on each statement executed, check it immediately following the statement being verified, or save it to a local variable that can be checked later.

Simple Example;

CREATE TABLE [dbo].[tblTest](

       [NAME] [varchar](100) NOT NULL,

 CONSTRAINT [PK_tblTest_1] PRIMARY KEY CLUSTERED

(

       [NAME] ASC

)

)

GO

INSERT INTO tblTest 

       Select 'Atif'

       Union

       Select 'Sheikh'

Update tblTest

Set Name = 'Atif'

Where Name = 'Sheikh'

if @@ERROR <> 0

       print 'Error Generated'

else

       Print 'No Error.'

TRY CATCH

We can also make use of TRY CATCH block in order to catch the error;

BEGIN TRY

       Update tblTest

       Set Name = 'Atif'

       Where Name = 'Sheikh'

END TRY

BEGIN CATCH

       print 'Error Generated.'

END CATCH

We can make use of transactions and roll back transactipon in the CATCH block. The TRY...CATCH construct also supports additional system functions (ERROR_LINE, ERROR_MESSAGE, ERROR_PROCEDURE, ERROR_SEVERITY, and ERROR_STATE) that return more error information than @@ERROR. TRY...CATCH also supports an ERROR_NUMBER function that is not limited to returning the error number in the statement immediately after the statement that generated an error.

BEGIN TRY

       Update tblTest

       Set Name = 'Atif'

       Where Name = 'Sheikh'

END TRY

BEGIN CATCH

       print 'Error Generated.'

      

       SELECT

    ERROR_NUMBER() AS ErrorNumber

    ,ERROR_SEVERITY() AS ErrorSeverity

    ,ERROR_STATE() AS ErrorState

    ,ERROR_PROCEDURE() AS ErrorProcedure

    ,ERROR_LINE() AS ErrorLine

    ,ERROR_MESSAGE() AS ErrorMessage;

END CATCH

THROW

Now, in SQL Server 2012, we can also use THROW to throw the exception to the application. You can say it works like RAISERROR;

BEGIN TRY

       Update tblTest

       Set Name = 'Atif'

       Where Name = 'Sheikh'

END TRY

BEGIN CATCH

    print 'Error Generated.';     

       THROW;

END CATCH

You can use THROW statement like RAISERROR as well;

BEGIN TRY

       Update tblTest

       Set Name = 'Atif'

       Where Name = 'Sheikh'

END TRY

BEGIN CATCH

    print 'Error Generated.';     

       THROW 51000, 'Primary Key Violates.', 1;

END CATCH

Limitatoin of Sequence Objects in SQL Server 2012

Sequence Object introduced in SQL Server 2012 is a good features added by MS SQL Server team. I have already discussed this in my post "Sequence Object in SQL Server 2012" but there are many limitations. I will try to explain the prominent limitations.

Let us create a sequence object in our test database.

CREATE SEQUENCE LimitSequence AS INT
 START WITH 1
 INCREMENT BY 1
GO

Also, create a test table with ID generated by using the sequence object;

CREATE TABLE dbo.TestLimit(ID INT,Name VARCHAR(100))
GO
INSERT INTO dbo.TestLimit VALUES
 (NEXT VALUE FOR LimitSequence,'Atif'),
 (NEXT VALUE FOR LimitSequence,'Sheikh'),
 (NEXT VALUE FOR LimitSequence,'Asif')
GO

Cannot Use DISTINCT, UNION, UNION ALL, EXCEPT or INTERSECT 

Now, if you try to run the query using sequence object with any of these clauses, you will get an error message;

Select Distinct NEXT VALUE FOR LimitSequence,* from dbo.TestLimit

On executing the above query, you get an eror message as ;

Msg 11721, Level 15, State 1, Line 1
NEXT VALUE FOR function cannot be used directly in a statement that uses a DISTINCT, UNION, UNION ALL, EXCEPT or INTERSECT operator.

This applies to all operators.

Using simple ORDER BY clause

If you try the query using the sequence object with simple ORDER by clause, it will generate an error;

Select NEXT VALUE FOR LimitSequence,* from dbo.TestLimit

Order by ID

Error message is;

Msg 11723, Level 15, State 1, Line 1

NEXT VALUE FOR function cannot be used directly in a statement that contains an ORDER BY clause unless the OVER clause is specified.

Good part for this is to user OVER (ORDER BY ). 

Select NEXT VALUE FOR LimitSequence,*, ROW_NUMBER() Over (Order by ID) as RNO from dbo.TestLimit

The above query will execute without any error.

TOP 

Cannot use with TOP.

Select Top(10) NEXT VALUE FOR LimitSequence,* from dbo.TestLimit

This query will generate error message as;

Msg 11739, Level 15, State 1, Line 1

NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set, or the query contains TOP or OFFSET. 

As stated in the error message, if ROWCOUNT is set or TOP and OFFSET is used, the query will generate an error message.

CASE, CHOOSE, COALESCE, IIF, ISNULL, or NULLIF

All these are not allowed. I will give the example if ISNULL function;

Select Isnull( NEXT VALUE FOR LimitSequence,0),* from dbo.TestLimit

Erro rmessage is;

Msg 11741, Level 15, State 1, Line 1

NEXT VALUE FOR function cannot be used within CASE, CHOOSE, COALESCE, IIF, ISNULL and NULLIF.

You can try the rest of the functions yourself.

WHERE Clause

You cannot use it in WHERE clasuse;

Select * from dbo.TestLimit

where NEXT VALUE FOR LimitSequence = 6

Error message will be;

Msg 11720, Level 15, State 1, Line 2

NEXT VALUE FOR function is not allowed in the TOP, OVER, OUTPUT, ON, WHERE, GROUP BY, HAVING, or ORDER BY clauses.

So, these are few prominent limitation for the sequence objects. 

Contained Database

What is it?

A contained database is a database within a sql server instance that is not dependent upon the instance itself in terms of users and metadata. All users and metadata is stored within the contained database.

What is the purpose?

These database are easy to port from instance / server to instance / server. You don't have to worry about the user logins associated with the database. This is very handy when you are dealing with large number of database users.

Any Important Note / drawback?

Important note is that in SQLServer 2012, only Partial containment type is supported. It means that your database can be a None contained database or partially contained database. A partially contained database is a contained database that can allow some features that cross the database boundary. SQL Server includes the ability to determine when the containment boundary is crossed. Fully contained user entities (those that never cross the database boundary), for example sys.indexes. Any code that uses these features or any object that references only these entities is also fully contained.

As stated above, SQL Server 2012 is supported with only partially contained state. A partially contained database is a contained database that allows the use of uncontained features.

According to BOL, use the sys.dm_db_uncontained_entities and sys.sql_modules view to return information about uncontained objects or features. By determining the containment status of the elements of your database, you can discover what objects or features must be replaced or altered to promote containment.

The behavior of partially contained databases differs most distinctly from that of non-contained databases with regard to collation. 

How to create?

Ok, enough theory. there are three steps to create a contained database;

1. First, we need to enable the "contained database authentication". We can do this using T-SQL;

sp_configure 'contained database authentication', 1;

GO

RECONFIGURE

GO

2. Then we create a new database as contained database;

USE master

GO

CREATE  DATABASE TestDB

CONTAINMENT=PARTIAL
GO

--Create table with records

USE TestDB
GO

CREATE TABLE tblTest(

id int ,
Name varchar (250)
)

GO

INSERT INTO tblTest

VALUES

(10,'Atif'),
(20,'Imran'),
(30,'Asif')
GO 

3. Then, at last, we will be creating the user(s) for the our partially contained database;

USE TestDB

GO

CREATE USER TestUser WITH PASSWORD=N'testCUser1$',DEFAULT_SCHEMA=dbo

GO

EXEC sp_addrolemember'db_owner', 'TestUser'

GO

In order to test our exercise, we have to log into a new session using the above created user. Check you server Server authentication settings. As I am using sql server user and not a Domain user, the instance should be configured as "SQL Server and Windows authentication mode". In the figure I have specified the user name and password of the user that we have created in the contained database;

Next, we have to specify the name of the contained database. You will have to type it as the user you have specified does not exists in the sql server instance.

Once the login name is authenticated, you will have you object explorer listing only the contained database;

That's it. We have successfully created a contained database.
Ok, what about the old databases that i need to convert to partial contained database?

We can convert an uncontained database to partial contained database by using the following 3 steps;

1. Enable the "contained database authentication" as above.
2. Alter the database as;

ALTER DATABASE [MyDatabase]

SET CONTAINMENT=PARTIAL

GO

3. In the last step, we move the user(s) to the database;

sp_migrate_user_to_contained

@username = N'MyDBLogin',

@rename = N'keep_name',

@disablelogin = N'disable_login'

GO

In the above statement, we have un-authorized the 'MyDBLogin' to log in the instance. We did not changed the login name here by specifying the value 'keep_name' for @rename.

Now, if you log in using this user as in our last example, you will only see the 'MyDatabase' in teh object explorer.

Hope this will help you.