Tag Archives: #SQLServer

All about Microsoft SQL Server

#0350 – SQL Server – CREATE INDEX – DROP_EXISTING option and Msg 7999

1 Reply

Normally, when we need to perform maintenance operations on any database object as part of a database deployment, we would first check for it’s existence. If the object exists, the query would be required to first drop the object and then recreate it (or simply modify it).

For indexes, this would result in a script that is similar to the one shown below:

--Creating Indexes (the traditional/common way)
IF EXISTS (SELECT * 
           FROM sys.indexes AS si 
           WHERE si.name = 'ncidx_ProductName')
    DROP INDEX ncidx_ProductName ON dbo.Product;
GO

CREATE UNIQUE NONCLUSTERED INDEX ncidx_ProductName
       ON dbo.Product (ProductName);
GO

However, as I was reading the Books On Line the other day, I noticed an interesting option in the CREATE INDEX statement – the DROP_EXISTING option. Here’s an example demonstrating the usage of this option:

--Creating Indexes using the DROP_EXISTING option
CREATE UNIQUE NONCLUSTERED INDEX ncidx_ProductName 
    ON dbo.Product (ProductName) WITH (DROP_EXISTING = ON);
GO

DROP_EXISTING allows us to request the database engine to drop and rebuild the index with the new definition. However, the index must exist before the DROP_EXISTING can be used. If DROP_EXISTING is used with the CREATE INDEX statement for an index that does not exist, the following error is seen.

Msg 7999, Level 16, State 9, Line 2
Could not find any index named ‘ncidx_ProductName’ for table ‘dbo.Product’.

Summary

Although I am yet to use the DROP_EXISTING option in my maintenance operations, it does appear to be lucrative due to the reduced number of lines involved in recreating the index. A couple of points need to be kept in mind, though:

  • The index must be existing when the DROP_EXISTING option is used
  • We can use DROP_EXISTING when changing the definition of the index
  • Index type (clustered to non-clustered or vice-versa) cannot be changed when DROP_EXISTING is used

Further Reading

Until we meet next time,

Be courteous. Drive responsibly.

#0349 – SQL Server – SSMS Usability – Unable to Show XML, Unexpected End of File when parsing

Leave a reply

Today’s post comes from a recent experience. I was trying to extract an XML from one of the logs that we maintain in the application when exchanging data over an interface. The XML was coming from a table and I had written a simple SELECT statement similar to the one shown below:

SELECT xt.XMLTest
FROM dbo.XMLSSMSTest AS xt;
GO

While SSMS displayed the XML in the results pane, clicking on the XML attempts to open the XML. With a very large XML, it results into the following error:

image

——————————
TITLE: Microsoft SQL Server Management Studio
——————————

Unable to show XML. The following error happened:
Unexpected end of file while parsing Name has occurred. Line 1, position 2097154.

One solution is to increase the number of characters retrieved from the server for XML data. To change this setting, on the Tools menu, click Options.

——————————
BUTTONS: OK
——————————

The solution

The solution is actually indicated in the error message itself – increase the number of characters being retrieved from the server for XML data. To do this:

  1. Go to Tools –> Options
  2. Expand the “Query Results” node
  3. Expand SQL Server –> Results to Grid options
  4. Under “Maximum Characters Retrieved”, select the appropriate value for “XML Data”
  5. Click “OK” and close out of the Options window
  6. Attempt to open the XML again

image

A note of caution

Changing the Maximum Characters Retrieved value may impact the SSMS performance depending upon the size of the XML being opened.

Until we meet next time,

Be courteous. Drive responsibly.

#0348 – SQL Server – Msg 1946 – The index entry exceeds maximum length of 900 bytes

Leave a reply

Having indexes on character columns is fairly common in OLTP systems that deal with multiple look-up values. However, indexes on character columns come with a small catch.

The maximum size of an index is 900 bytes. With character data forming part of the index columns, 900 bytes can fill up very fast depending upon the overall size of the character columns involved. While an index on the character columns can still be created if the existing data in the columns does not exceed 900 bytes, the system may reject an insert/update because the total size of the columns being inserted/updated exceed 900 bytes.

While I have never actually experienced this situation, I have seen SQL Server warning about index length exceeding 900 bytes. This prompted me to do a small test, which I am sharing via this post.

The scenario

To simulate the scenario, I will create a test table (for representational purposes) with a couple of columns which will be used on an index.

USE tempdb ;
GO

--(C) SQLTwins, nakulvachhrajani.com

--Safety Check
IF OBJECT_ID('dbo.IndexLengthTest', 'U') IS NOT NULL 
    DROP TABLE dbo.IndexLengthTest ;
GO

--Create the test table
--NOTE: For representational purposes only!
CREATE TABLE dbo.IndexLengthTest
    (
      KeyValue1 NVARCHAR(250),
      KeyValue2 NVARCHAR(250),
      KeyValue3 NVARCHAR(250),
      KeyValue4 UNIQUEIDENTIFIER
    )
GO

--Create the test Index
--NOTE: For representational purposes only!
CREATE UNIQUE NONCLUSTERED INDEX uncidx_IndexLengthTest 
    ON dbo.IndexLengthTest ( KeyValue1, KeyValue2, KeyValue3, KeyValue4 ) ;
GO

NOTE that when the index is created, the following warning is seen in the “Messages” tab of the SQL Server Management Studio (SSMS).

Warning! The maximum key length is 900 bytes. The index ‘uncidx_IndexLengthTest’ has maximum length of 1516 bytes. For some combination of large values, the insert/update operation will fail.

The warning clearly indicates that  the maximum possible size of the key in the proposed index is 1516 byes [(2*250)*3 + 16 = 1516 bytes]. Because the existing data in the table is less than 900 bytes, SQL Server will go ahead and create an index on the selected columns.

The Test

I will now  attempt to insert a record in the table whose total size exceeds 900 bytes.

INSERT  INTO dbo.IndexLengthTest
        (
          KeyValue1,
          KeyValue2,
          KeyValue3,
          KeyValue4
        )
VALUES  (
          REPLICATE(N'a', 250),
          REPLICATE(N'b', 250),
          REPLICATE(N'c', 250),
          NEWID()
        ) ;
GO

SQL Server immediately returns the following error:

Msg 1946, Level 16, State 3, Line 1
Operation failed. The index entry of length 1516 bytes for the index ‘uncidx_IndexLengthTest’ exceeds the maximum length of 900 bytes.

To summarize, SQL Server prevents the user from inserting records that violate the index key length which is why caution needs to be exercised when designing indexes on character columns.

Further Reading

Until we meet next time,

Be courteous. Drive responsibly.

#0347 – SQL Server – Basics – A SELECT that does not fetch anything also does not SET anything

2 Replies

In today’s post, I go all the way back to the basics because of a logical bug I saw in production code recently.

It is always said to be a good practice to initialize variables within procedures so that you know what the default value is and what are valid and invalid values for a variable. For example, if the procedure does not have to deal with NULLs, then it is a good idea to initialize the variables as NULL so that a simple = NULL check is sufficient to tell whether we still have a default, invalid value or not.

The code below attempts to fetch the vacation hours for a given employee. The variables have default values defined, so it follows the variable initialization best practice mentioned above. In all probability, static code analysis/review would have never caught a logical bug hidden in the code below which raises it’s head when the code is run with an incorrect employee Id.

USE AdventureWorks2012;
GO

DECLARE @employeeVacationHours INT = -1;
DECLARE @employeeId INT;

--To be filled in by user
SELECT @employeeId = 777;

SELECT @employeeVacationHours = VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = @employeeId;

IF (@employeeVacationHours < 0)
PRINT 'Employee has invalid vacation hours.';

SELECT @employeeVacationHours AS VacationHours;

/* RESULT:

VacationHours
-------------
-1

*/

In this case, the SELECT statement which is supposed to fetch the vacation hours for the employee will not return any records. Because of this, the variable will continue to preserve it’s value and will not reset to the default value or NULL.

A SELECT statement that does not fetch anything also does not SET anything.

The scenario mentioned here is a simplified one – in the production code, this logical bug caused the procedure to go down a completely different code path causing various other “random” issues.

The solution to this problem? In this example, it’s quite simple – change the initialization value for the @employeeVacationHours variable to NULL and then check for NULL after the SELECT operation is complete.

Until we meet next time,

Be courteous. Drive responsibly.

#0346 – SQL Server – Using CASE with a PRINT statement

2 Replies

Informational and alert messages during execution of a T-SQL script are the most preferred method to update the user on the progress of the task being executed. These messages are generated by the various PRINT statements embedded in the script.

Often there may be a requirement to display a particular message when a given set of conditions is met and another message otherwise. In such cases, I used to write a  standard IF…ELSE block and embed the PRINT statements within.

So, for example if the task at hand was to determine whether two numbers are equal or not, the traditional code would have been:

USE tempdb;
GO

--Traditional method
IF OBJECT_ID('dbo.proc_TraditionalNumberCompare','P') IS NOT NULL
DROP PROCEDURE dbo.proc_TraditionalNumberCompare;
GO

CREATE PROCEDURE dbo.proc_TraditionalNumberCompare
@intA INT,
@intB INT
AS
BEGIN
SET NOCOUNT ON;

IF (@intA - @intB ) = 0
PRINT 'Traditional Method - Both integers are the same.';
ELSE IF (@intA - @intB) > 0
PRINT 'Traditional Method - Integer A is greater than Integer B';
ELSE IF (@intA - @intB) < 0
PRINT 'Traditional Method - Integer B is greater than Integer A';
ELSE
PRINT 'Unreachable code!';
END
GO

--Tests
EXEC dbo.proc_TraditionalNumberCompare @intA = 5, @intB = 5;
GO
/* RESULT
Traditional Method - Both integers are the same.
*/

EXEC dbo.proc_TraditionalNumberCompare @intA = 2, @intB = 5;
GO
/* RESULT
Traditional Method - Integer B is greater than Integer A
*/

EXEC dbo.proc_TraditionalNumberCompare @intA = 5, @intB = 3;
GO
/* RESULT
Traditional Method - Integer A is greater than Integer B
*/

However, I recently realized that if the expression returns a string value, it can directly be used in a single PRINT statement, i.e. I can rewrite the logic to compare two integers shown above into a single statement as demonstrated below:

USE tempdb;
GO

IF OBJECT_ID('dbo.proc_NumberCompare','P') IS NOT NULL
DROP PROCEDURE dbo.proc_NumberCompare;
GO

CREATE PROCEDURE dbo.proc_NumberCompare
@intA INT,
@intB INT
AS
BEGIN
SET NOCOUNT ON;

PRINT CASE
WHEN (@intA - @intB) = 0
THEN 'Both integers are the same.'
WHEN (@intA - @intB) > 0
THEN 'Integer A is greater than Integer B'
WHEN (@intA - @intB) < 0
THEN 'Integer B is greater than Integer A'
ELSE 'Unreachable code!'
END;
END
GO

--Tests
EXEC dbo.proc_NumberCompare @intA = 5, @intB = 5;
GO
/*RESULT
Both integers are the same.
*/

EXEC dbo.proc_NumberCompare @intA = 2, @intB = 5;
GO
/*RESULT
Integer B is greater than Integer A
*/

EXEC dbo.proc_NumberCompare @intA = 5, @intB = 3;
GO
/*RESULT
Integer A is greater than Integer B
*/

I’m sure you will also find this flavour of implementing the PRINT statement useful. Do let me know your thoughts before you go.

Further Reading

Until we meet next time,

Be courteous. Drive responsibly.