NONCLUSTERED Index on Table Variable in SQL Server 2014

There are always advantages of using table variable when working with small dataset. But the only flaw with Table Variable was its lack of ability to create NON Clustered index on it but  SQL Server 2014  has given support to create NONCLUSTERED index on table variable.

In SQL Server 2008 R2

Lets try to create NC on @table variable column.

declare @tab_variable table

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

     f_name varchar(10) index idx_f_name,

     l_name varchar(10))

Msg 1018, Level 15, State 1, Line 4

Incorrect syntax near 'index'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

We saw SQL Server 2008 R2 has no support for NC on table variable.

In SQL Server 2014

Now let’s see how we can create non clustered index on table variable on SQL Server 2014

declare @tab_variable table

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

       f_name varchar(10) index idx_f_name,

       l_name varchar(10))

insert into @tab_variable(f_name,l_name)

select top 1000000 SUBSTRING(name,0,9), SUBSTRING(name,0,9) from  sys.columns

select f_name, l_name from @tab_variable

where f_name like 'TSQL'

f_name     l_name

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

TSQL       TSQL

(1 row(s) affected)

So here we say that Non Clustered index, we can create on @table variable column, it is an awesome feature added to SQL Server 2014, the gap of temp variable and table variable reduced to just scope difference J

Your code has potential to bow every head.  

 

Post Reference: Vikram Aristocratic Elfin Share

A new way to create Non Clustered composite index in SQL Server 2014

In Earlier version of SQL Serve i.e SQL Server 2008, SQL Server 2012, we were unable to create Composite NONCLUSTERED index on table level, i.e while creating table.

Creating NONCLUSTERED composite Index on SQL Server 2008R2

create table test1

(col1 int primary key clustered,

col2 int index idx_nc(col2, col2),

col3 int

)

Msg 1018, Level 15, State 1, Line 3

Incorrect syntax near 'index'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

Earlier version of SQL Server does not allow us to create Composite NONCLUSTERED index on table level. So to create Composite index, we were writing separate statement of Create INDEX, see below

First create table

create table test1

(col1 int primary key clustered,

col2 int,

col3 int

)

Command(s) completed successfully.

Then once the table is ready, create composite index on columns.

create nonclustered index idx_test1_col2_col3

on test1(col2,col3)

Command(s) completed successfully.

But with the advancement of SQL Server 2014, it allow us to create Composite NonClustered Index on table level, lets see how we can create Composite index in SQL Server 2014 at table level.

Creating NONCLUSTERED Composite Index at Table Level in SQL Server 2014

create table test1

(col1 int primary key clustered,

col2 int index idx_nc(col2, col3),

col3 int

)

Command(s) completed successfully.

Querying Sys.Indexes to check the indexes created.

select name, type_desc from sys.indexes

where object_id = OBJECT_ID('test1')

name                         type_desc

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

PK__test1__357D0D3EAC78055D  CLUSTERED

idx_nc                       NONCLUSTERED

Your code is so flexible, u can twist it, blend it and fit it to ur client wants, it has the potential of likeliness to patrons needs.

Post Reference: Vikram Aristocratic Elfin Share

Reusability, create a global default value and bind it to various tables

Here in this article we will see, how we can create default value and bind it to various table fields.

So First we will be creating a User defined datatype if int basic type.

sp_addtype 'pin_code_type', int, null

Command(s) completed successfully.

Now lets create a Default and assign a default value.

create default pin_default as 326595

Command(s) completed successfully.

Now lets bind the Default value to the User defined Data type

sp_bindefault 'pin_default', 'pin_code_type'

Command(s) completed successfully.

Default bound to data type.

The new default has been bound to columns(s) of the specified user data type.

Lets create table and bound the  column to user defined data type ‘pin_code_type’

create table temp

(id int,

zip pin_code_type)

Command(s) completed successfully.

insert into temp(id) values(1)

select * from temp

id          zip

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

1           326595

1           326595

Lets create on more Table an assign the default variable to  the table column.

create table temp2

(id int identity(1,1),

c_address varchar(10),

zip pin_code_type)

insert into temp2(c_address) values('delhi')

select * from temp2

id          c_address  zip

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

1           delhi      326595

Conclusion: Here we can see, we created only one Default Value and bind it to more than one table. This is an example of Re-Usability.

Code makes the coder dance in the Rain; Let the life get easier, let’s welcome the coder to your life

Post Reference: Vikram Aristocratic Elfin Share