Max Key length warning in SQL Server 2016 for NC Index

There are certain times when your index gets created with warning message

“Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'test_index' has maximum length of 1705 bytes. For some combination of large values, the insert/update operation will fail.”

Remember SQL Server 2016 increase maximum key size for Non Cluster Index from 900 byte to 1700 byte. That seems interesting correct!  You can now have more key column in your NC index Wow!!

Well it is not so wow! The larger index comes with lot of cons, which you need to consider before designing larger NC index, I will write a separate note on this.

Let reciprocate the above error to understand more on it.

CREATE SCHEMA indexDemo GO

CREATE TABLE indexDemo.IndexByteSQL2016 ( MyCol1 varchar(1705) );

Here we created table with a single column of length 1705, just do remember SQL Server 2016 allow only 1700 byte key column length.  

Now lets create NC index

CREATE INDEX test_index ON indexDemo.IndexByteSQL2016 (MyCol1);

Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'test_index' has maximum length of 1705 bytes. For some combination of large values, the insert/update operation will fail.

NC index created with warning, this warning we cannot ignore since it will affect your insertion. Let try to fire some insert statement to test this warning

INSERT INTO indexDemo.IndexByteSQL2016

SELECT cast(replicate('a', 700) AS varchar(1000)); -- Success

(1 row(s) affected)

INSERT INTO indexDemo.IndexByteSQL2016

SELECT cast(replicate('b', 901) AS varchar(1000)); -- Success

(1 row(s) affected)

INSERT INTO indexDemo.IndexByteSQL2016

SELECT cast(replicate('c', 1703) AS varchar(2000)); -- Fail

Msg 1946, Level 16, State 3, Line 20

Operation failed. The index entry of length 1703 bytes for the index 'test_index' exceeds the maximum length of 1700 bytes for nonclustered indexes.

The above insertion fails, why? Though you are trying to insert record of permissible length defined by your datatype i.e. varchar(1705) but this column is participating in NC index key creation and in SQL Server 2016 the permissible length of NC IndexKey columns is 1700.

So this particular value cast(replicate('c', 1703) AS varchar(2000) won’t get space in index creation thus insertion fails

Enjy coding…SQL J

Post Reference: Vikram Aristocratic Elfin Shar

Querying employee salary along with maximum salary of his department

I found a very interesting question from one of the user of SQL group, his problem statement was

“I need to find each employee salary along with the max salary of his department with a single line query”

It is very easy to find using SQL ‘Over by with Partition By clause’

Lets demonstrate it with an example. Here I am creating Emp and Dept table and inserting few records in it.

create table Dept1

(dept_id int identity(1,1) primary key,

dname varchar(20))

Command(s) completed successfully.

create table Emp2

(emp_id int identity(1,1) primary key,

emp_name varchar(20),

dept_id int references Dept1(dept_id),

salary float)

Command(s) completed successfully.

insert into Dept1

select 'HR' union all

select 'IT' union all

select 'Account'

insert into Emp2

select 'Sudeepta', 1, 1000 union all

select 'Susmita', 1, 3000 union all

select 'Smruti', 3, 10000 union all

select 'Richa', 3, 8000 union all

select 'Sanjana', 1, 4000 union all

select 'Sweta', 2,4000 union all

select 'Deepa', 2,7000

Now since the data are there in the table, lets write query to find each employee salary along with the maximum salary for his department.

select e.emp_name, d.dname,e.salary, max(e.salary) over (partition by d.dept_id) MaxSalary from

Emp2 e inner join Dept1 d

on e.dept_id = d.dept_id

emp_name             dname                salary                 MaxSalary

-------------------- -------------------- ---------------------- ----------

Sudeepta             HR                   1000                   4000

Susmita              HR                   3000                   4000

Sanjana              HR                   4000                   4000

Sweta                IT                   4000                   7000

Deepa                IT                   7000                   7000

Smruti               Account              10000                  10000

Richa                Account              8000                   10000

(7 row(s) affected)

So here we can from the output we have salary for each employee along with the maximum salary of his department. We have simply used aggregated function max along with ‘over with partition by clause’ which is taking d.dept_id to partition the record to find the max salary of his department.

I can’t sleep …until I die…My passion SQL :)

Post Reference: Vikram Aristocratic Elfin Share

Generating series, now it’s very simple using recursive CTE

A interesting question asked by one of my Community friend to generate series of 4,8,16…. Without using any kind of looping structure.

This is very simple with the recursive CTE, lets see how we are implementing the solution

; with sam_series (myrow) as (

select 4 as myrow

union all

select myrow+ myrow from sam_series

where myrow<1000

)

select * from sam_series;

 myrow

-----------

4

8

16

32

64

128

256

512

1024

If we dissect the above CTE we can see

The anchor member is:  select 4

Recursive invocation member is: select myrow + myrow which is tied with Union All.

Termination Point is: myRow < 1000.

Conclusion: CTE provides the significant advantage of being able to reference itself, thereby creating a recursive CTE.

Get your cup filled with Tea … Enjoy coding  :)

Post Reference: Vikram Aristocratic Elfin Share

First insertion will always takes the seed value, all other insertion following first will take seed/reseed value and add increment factor to form the identity value

Lets demonstrate it with the help of a temporary table, here we are creating a table with identity column and set the seed value to 1 and increment factor to 1

create table #tempReseed

(id int identity(1,1),

col1 varchar(10));

Command(s) completed successfully.

Now before we execute any insert statement on the table we are Reseed the Identity value to 15

dbcc CHECKIDENT('#tempReseed',reseed,15)

Checking identity information: current identity value 'NULL'.

DBCC execution completed.

Now lets insert a records and check what value will get inserted to id column.

insert into #tempReseed(col1) values('ABC')

(1 row(s) affected)

Querying the table after insertion.

select * from #tempReseed

id          col1

----------- ----------

15          ABC

Here we found the id is set with reseed value i.e. 15

Lets again reseed the identity value, this time to 25

dbcc CHECKIDENT('#tempReseed',reseed,25)

Checking identity information: current identity value '15'.

DBCC execution completed.

Lets fire an insertion after reseeding identity value to 25

insert into #tempReseed(col1) values('DEF')

(1 row(s) affected)

Lets query the table to find what gets inserted this time.

select * from #tempReseed 

id          col1

----------- ----------

15          ABC

26          DEF

Cool!!! This time Reseed value was added with the incremented factor i.e. 1 an new value formed was 25+1 =26 which gets inserted.

Conclusion: First insertion will always takes the seed value, all other insertion following first will take seed/reseed value and add increment factor to form the identity value.

Code and enjoy … very sync :)

Post Reference: Vikram Aristocratic Elfin Share