SQL Server

Specifying file groups in Select Into.

2018-06-08T17:18:00.000+05:30

IN Select into we can not specify file group and hence always table was created primary file group.

Then we have to create clustered index on other filegroup to change filegroup of created tabled.

This is time and resource consuming as it needs to move data from one filegroup to other filegroup.

But now in MS SQL 2017 we can specify file group while using select into.

Lets see how to do this.

We have two filegroups in database.

[PRIMARY] and [SAMPLE]

So I want to create table in SAMPLE file group.

Select * into newtable on [SAMPLE] from sys.tables

This will create table in SAMPLE file group instead of PRIMARY.

How to set shortcuts in query editor

2017-03-16T18:52:00.001+05:30

If we set short cuts for commands like sp_lock, sp_helptext , sp_who2 it can be handy as its
frequently required for any developers.
Lets see how to set short cut for this in query editor.

Go to Tools-> Option->Environemnt->Keyboard->Query Shortcuts

Set shortcuts here for your favorite and frequently used commands.

Dm_db_stats_properties - to get statistics properties

2017-03-16T18:49:00.000+05:30

Managing statistics is important part for developers. Many times we have seen
improper statistics screwed up the queries and queries which completes in seconds
takes hours to complete.
Monitoring statistics and updating it regularly is important for this aspect.
For this we can use sys.dm_db_stats_properties which give better insight about
each stats for a table.

This DMV has two arguments

Object_id
Stats_id

Better we should user cross apply it with sys.stats and it will give up
in depth details about the stats.

SELECT s.object_id,

Object_name (s.object_id),

s.stats_id,

s.auto_created,

s.user_created,

s.no_recompute,

s.has_filter,

s.is_temporary,

s.is_incremental,

d.last_updated,

d.rows,

d.rows_sampled,

d.steps,

d.unfiltered_rows,

d.modification_counter

FROM sys.stats s

JOIN sys.tables t

ON s.object_id = t.object_id

CROSS apply sys.Dm_db_stats_properties(s.object_id, s.stats_id) d

WHERE t.type = 'u'

How to audit login

2017-03-15T20:23:00.000+05:30

Many times we need to check users logged in our system for various reasons.
This reasons can be many but main requirement we need to know who logged in out system.
There are many ways in SQL Server for this and with introduction of newer versions
many new methods are available for this .

Lets see some of this.1
1.Using SSMS
Connect to server , go to properties.
Go to Security -> Login Auditing.
Select "Both failed and successful logins"

Now restart the server.
You can see the details in errorlog.

2.Using Profiler
Connect to profiler.
Select events
"Audit Login"
"Audit Login Failed"
Start the trace.
You can see the detail in profiler trace.

Error 8623 and 8632 in SQL Server 2016.

2017-03-15T20:22:00.001+05:30

Recently after migrating to SQL Server 2016 one of our app start to failing.
While debugging I found the query which was failing.
The query is like this.

This was working fine SQL Server 2014 but now failing in SQL Server 2016.
I tried it on various serves where SQL Server 2016 is installed.
Getting either 8623 or 8632 error.

BOL give description for this as below.

Error 8623:
The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

Error 8632:
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

It says we need to change the query to avoid such large number of values in IN clause.
We need to use join in this case.

Workaround
As its third party app and we can not modify the query immediately
We changed compatibility level to 120 (SQL Server 2014) and the query worked !!

This gives us time to contact app developer and get the app modified with new
query.

PWDCOMPARE - to check predictable password

2017-03-10T20:04:00.003+05:30

How to check week passwords?
As a strong security policy we also need that password are not predictable.
Many time we need to test this but checking all such predictable password with all logins
is very tedious task and any one will try to avoid.

Recently I had a issue where I need to check that certain predictable passwords are not used by
any login.

Here we can use pwdcompare function , which solves this purpose very easily.

It checks password with stored password hash from sys.sql_logins.

I want to check which logins are using password like qwedsa or password

SELECT *

FROM sys.sql_logins

WHERE PWDCOMPARE('qwedsa',password_hash) = 1

OR PWDCOMPARE('password',password_hash) = 1

Once we know this logins we can ask the user to change password with strong combinations.

New data compression - Columnstore Archive

2016-04-13T16:15:00.001+05:30

All who have used columnstore index must know that columnstore index also compress data.
Many cases after some time this data become historical and use of this data reduces over time.
In this case we can further compress data sung columnsore_archive compression option. Retrieving data takes time after this compression but its acceptable as it reduces storage and its usage frequency is low compare to current data

Lets see with example

CREATE TABLE test

(

id INT,

data VARCHAR(max)

)

DECLARE @i INT

SET @i = 1

WHILE @i <= 100000

BEGIN

INSERT INTO test

VALUES (@i,

'The value of i is '

+ CONVERT(VARCHAR(10), @i))

SET @I = @i + 1

END

SP_SPACEUSED test

Normal Table

Now we will create clustered columnstore index here.

It will compress data

CREATE CLUSTERED COLUMNSTORE INDEX c1 ON test

Table with clustered columnstore index

Now we will compress more using columnstore_archive option

This will compress data even more.

ALTER INDEX c1 ON test REBUILD WITH (data_compression = columnstore_archive)

Table with columnstore_archive compression option

Here we can see size reduces from 8848 KB to 2704 KB and then 1232 KB.

Inline indexes

2016-04-13T13:43:00.001+05:30

SQL 2014 has many silent features one of them is inline indexes.
Whats inline indexes?
Inline indexes are which can be created while creating table.
We can specify index in Create Table statement itself.

Here is an example

CREATE TABLE test_table

(

id INT,

data VARCHAR(max),

INDEX idx1 (id)

)

Limitations
1.We can create filtered index
2.We can not create columnstore index.

Good news is that both this issues resolved in SQL Server 2014.

Optimized select into in SQL Server 2014

2016-04-13T12:22:00.002+05:30

Lets have quick glance of this feature.
As we all know select .... into clause and how to use it.
Now in SQL 2014 its more optimized and will operate in parallel also for better performance.

Lets see with example

I have one table test_table with 200 Million records in SQL Server 2014 and 2012 both.

We will run Select .... into query and check execution plan

SQL Server 2012

SELECT *

INTO new_table

FROM xyz

SQL Server 2014

SELECT *

INTO new_table

FROM xyz

As we can see in execution plan optimizer is using parallelism operator to dump data.

SQLPS Powershell in SQL Server -4

2014-10-09T21:13:00.001+05:30

Lets see some more cmdlets

1.Backup-Sqldatabase
first we need to check examples how to use Backup_Sqldatabase

Run this command
Get-Help Backup-Sqldatabase -Examples

It will give us examples
Similarly for all cmdlets we can get examples how to use it.

Now we will run a sample how take backup

Backup-Sqldatabase -Database Test

It will take backup of Database Test.

How to redirect result to HTML page?

Run this command

Invoke-Sqlcmd "select * from sys.tables" | ConvertTo-Html > c:\test.html

Lets see the result in browser by opening test.html in browser.

Managed lock priority -SQL 2014

2014-10-03T17:14:00.007+05:30

Managed lock priority

Its available in SQL 2014 and used for online operation s
Mainly used for switching partition and rebuilding indexes online.

Till now its issue to switch partition while tables are in use.
Its because that requires schema modification lock SCH-M lock.
As its not possible to get this lock until any operation is running on table and hence switching partition is a nightmare for any developers.
This request for SCH-M lock was creating a chain of locks and hence affecting performance of OLTP operations.

Now in 2014 we have option that we can define priority for switching or online reindexing with other operations.
Lets see how to use this feature

If we see at syntax of alter table or alter index we will find this option

WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ], ABORT_AFTER_WAIT =
{ NONE | SELF | BLOCKERS } )

MAX_DURATION is time for which we want to wait before completing the operation

ABORT_AFTER_WAIT :- What action we want to take after waiting time completes
NONE :- Will take no action and continue waiting
SELF :- It will kill self session
BLOCKERS:- It will kill session which are creating blocking and will do switching or
reindexing.

But this killing session has one drawback it will lead transaction to rollback and if we dont know which operations are in rollback it may impact OLTP operations.
So we need to be careful while doing this and should be done when there is low activity or switching/reindexing has higher priority compare to other tasks.

Lets create a table and check this behavior

CREATE PARTITION FUNCTION part_test_func( datetime)

AS RANGE LEFT FOR VALUES( '2014-09-10' , '2014-09-20' , '2014-09-30');

CREATE PARTITION SCHEME part_test_scheme

AS PARTITION part_test_func TO( [primary],[primary],[primary],[primary]);

CREATE TABLE part_test( id int IDENTITY( 1 , 1) ,

data varchar( 100) ,

date datetime)

ON part_test_scheme( date);

CREATE TABLE part_test1( id int IDENTITY( 1 , 1) ,

data varchar( 100) ,

date datetime)

ON part_test_scheme( date);

INSERT INTO part_test

VALUES( 'a' ,

'2014-09-05'

);

INSERT INTO part_test

VALUES( 'a' ,

'2014-09-15'

);

INSERT INTO part_test

VALUES( 'a' ,

'2014-09-25'

);

INSERT INTO part_test

VALUES( 'a' ,

'2014-10-05'

);

Now in other window I will run this query

BEGIN TRAN

SELECT *

FROM part_test WITH(updlock)

Now we will go for partition switching in first window

We will run this query





ALTER TABLE part_test switch PARTITION 1 TO part_test1 PARTITION 1

WITH (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES,

ABORT_AFTER_WAIT = BLOCKERS))

It will wait for 1 minute and then kill the blocking and statement and complete

switching.

But we also need to know which session were killed.

We can find this details in errorlog

Computed Columns 2

2014-09-30T12:51:00.001+05:30

Sometimes we are using some scalar function to generate computed column values

This values also not persisted until the function is marked as schema bound

Lets see example here

Create table test

(

Id int,

Name varchar(100) ,

qualification as dbo.Get_qualification(Name) )

CREATE FUNCTION dbo.Get_qualification (@str VARCHAR(100))

returns VARCHAR(100)

BEGIN

DECLARE @str1 VARCHAR(100)

IF Charindex (' ', @str, 0) > 0

SET @str1 = LEFT(@str, Charindex(' ', @str, 0) - 1)

ELSE

SET @str1 = ''

RETURN @str1

END

Now lets insert value

SELECT *

FROM test

INSERT INTO test

VALUES (1,

'Er Amish')

INSERT INTO test

VALUES (2,

'CA John')

INSERT INTO test

VALUES (3,

'Dr Rahul')

SELECT *

FROM test

Now we will try to create index on qualification

first we will check either this columns is indexable?

SELECT Columnproperty(Object_id('test'), 'qualification', 'isindexable')

This column is not indexable.

Reason is that the function we are using is not schema bounded.

Now we have to create function with schema binding

DROP TABLE test

Alter FUNCTION dbo.Get_qualification (@str VARCHAR(100))

returns VARCHAR(100)

with schemabinding

BEGIN

DECLARE @str1 VARCHAR(100)

IF Charindex (' ', @str, 0) > 0

SET @str1 = LEFT(@str, Charindex(' ', @str, 0) - 1)

ELSE

SET @str1 = ''

RETURN @str1

END

Create table test

(

Id int,

Name varchar(100) ,

qualification as dbo.Get_qualification(Name) )

INSERT INTO test

VALUES (1,

'Er Amish')

INSERT INTO test

VALUES (2,

'CA John')

INSERT INTO test

VALUES (3,

'Dr Rahul')

SELECT *

FROM test

SELECT Columnproperty(Object_id('test'), 'qualification', 'isindexable')

ALTER TABLE test

ALTER COLUMN qualification

ADD persisted

Computed columns 1

2014-09-29T21:56:00.003+05:30

computed columns

use test

We used computed columns many times , but we can get most benefit of computed columns when we can mark them

as persisted.

For this columns must be deterministic.

Lets see how can we get it

CREATE TABLE test

(

first_name VARCHAR(50),

last_name VARCHAR(50),

full_name AS first_name + ' ' + last_name,

entry_date AS CONVERT(DATE, Getdate()),

hour_id AS Datepart(HH, Getdate()),

minute_id AS Datepart(MI, Getdate())

)

INSERT INTO test

(first_name,

last_name)

VALUES ('amish',

'shah')

INSERT INTO test

(first_name,

last_name)

VALUES ('anil',

'shah')

SELECT *

FROM test

Now here we can see that full_name is combination of first_name and last_name but what if table has million of rows?

Querying full_name will get benefit of indexing?

Is it good if we get this values at runtime?

Getting date and minute and hour at runtime efficient?

So ultimate solution is we need this values to be stored in table

We need to check either this value is deterministic at first?

SELECT Columnproperty(Object_id('test'), 'full_name', 'isCOMPUTED'),

Columnproperty(Object_id('test'), 'full_name', 'isDETERMINISTIC'),

Columnproperty(Object_id('test'), 'full_name', 'isINDEXABLE'),

Columnproperty(Object_id('test'), 'full_name', 'isPRECISE')

SELECT Columnproperty(Object_id('test'), 'ENTRY_DATE', 'isCOMPUTED'),

Columnproperty(Object_id('test'), 'ENTRY_DATE', 'isDETERMINISTIC'),

Columnproperty(Object_id('test'), 'ENTRY_DATE', 'isINDEXABLE'),

Columnproperty(Object_id('test'), 'ENTRY_DATE', 'isPRECISE')

SELECT Columnproperty(Object_id('test'), 'hour_id', 'isCOMPUTED'),

Columnproperty(Object_id('test'), 'hour_id', 'isDETERMINISTIC'),

Columnproperty(Object_id('test'), 'hour_id', 'isINDEXABLE'),

Columnproperty(Object_id('test'), 'hour_id', 'isPRECISE')

Here we can see values derived from getdate() is not deterministic, so we can not create index over it

So first we will mark full_name as persisted

alter table test

alter column full_name

add persisted

Now this full_name is marked persisted and its value will be stored in table.

Now we will go for entry_date column

alter table test

alter column entry_date

add persisted

we are getting error

Msg 4936, Level 16, State 1, Line 58

Computed column 'ENTRY_DATE' in table 'test' cannot be persisted because the column is non-deterministic.

So whats solution for this?

We need to redesign table here

DROP TABLE test

CREATE TABLE test

(

first_name VARCHAR(50),

last_name VARCHAR(50),

full_name AS first_name + ' ' + last_name,

entry_datetime DATETIME DEFAULT Getdate(),

entry_date AS CONVERT(DATE, entry_datetime),

hour_id AS Datepart(HH, entry_datetime),

minute_id AS Datepart(MI, entry_datetime)

)

INSERT INTO test

(first_name,

last_name)

VALUES ('amish',

'shah')

INSERT INTO test

(first_name,

last_name)

VALUES ('anil',

'shah')

SELECT *

FROM test

SELECT Columnproperty(Object_id('test'), 'full_name', 'isCOMPUTED'),

Columnproperty(Object_id('test'), 'full_name', 'isDETERMINISTIC'),

Columnproperty(Object_id('test'), 'full_name', 'isINDEXABLE'),

Columnproperty(Object_id('test'), 'full_name', 'isPRECISE')

SELECT Columnproperty(Object_id('test'), 'ENTRY_DATE', 'isCOMPUTED'),

Columnproperty(Object_id('test'), 'ENTRY_DATE', 'isDETERMINISTIC'),

Columnproperty(Object_id('test'), 'ENTRY_DATE', 'isINDEXABLE'),

Columnproperty(Object_id('test'), 'ENTRY_DATE', 'isPRECISE')

SELECT Columnproperty(Object_id('test'), 'hour_id', 'isCOMPUTED'),

Columnproperty(Object_id('test'), 'hour_id', 'isDETERMINISTIC'),

Columnproperty(Object_id('test'), 'hour_id', 'isINDEXABLE'),

Columnproperty(Object_id('test'), 'hour_id', 'isPRECISE')

Now we can see we are getting 1 for all properties.

All this columns are deterministic.

We can mark them persisted

ALTER TABLE test

ALTER COLUMN entry_date

ADD persisted

ALTER TABLE test

ALTER COLUMN minute_id

ADD persisted

ALTER TABLE test

ALTER COLUMN hour_id

ADD persisted

How to get list of servers running in network using PowerShell

2014-09-25T12:31:00.001+05:30

As a DBA its your repsonsibility to get list of SQL Servers running in network and manage them. As infrastructure grows its getting tough to get list of SQL Servers running in network along with their versions
It easy in Powershell
Let see how to use it

Run this command

[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

Here it gives list of all servers where SQL Server is running

Now if want to filter this result
Lets see how to use it

$a = "MDVLAMISHS"
[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() | ? { $_.servername -eq $a}

It will filter resuts as show below

SQLPS Powershell in SQL Server -3

2014-09-19T18:03:00.003+05:30

Now we will go for some cmdlets and see how to use them.
One question comes in your mind is how to run query from powershell?

Lets see first it
For this there is cmdlet -Invoke-Sqlcmd

1. Running SQL query by specifying Database name,
Here we are connected to server so no need to specify it.

So command is
1.Invoke-Sqlcmd -Database test -Query "Select * from sys.databases"

It will give result for this query .

2.Now we will also set database , so we dont need to give db name here.
In previous article I show how to use database.
Lets see again

CD Databases
CD Test

It will connect to database test.
Run this query

Invoke-Sqlcmd -Query "Select * from sys.databases"

3. So you are aware how to use Invoke-Sqlcmd
If we want to get syntax on how to use it.

Get-Help Invoke-Sqlcmd -full
It will not only give syntax but also give example how to use it.

4.Is Invoke-Sqlcmd is long for typing ?
We can set alias of it.

New-Alias tsql Invoke-Sqlcmd

Not tsql alias is set
So run query with this alias

tsql -Query "Select * from sys.tables"

5.Is result not good looking as we want?
Its due to command prompt console which has limitation to display result

so use Out-GridView

So the command is

tsql -Query "select * from sys.tables" | Out-GridView

SQLPS Powershell in SQL Server -2

2014-09-19T15:04:00.000+05:30

Ok , so now we are connected to SQLPS
We will first navigate to different folders of SQL Server

Run this Command
Dir
It will show list of all available folders

Now run this command

CD SQL

It will give hostname
In my case its
CD <hostname>
I have masked hostname of my server

Then CD TEST
here TEST is instance name of SQL Server

So now we are connected to current instance of SQL Server
Now Run DIR command
It will give list of objects which we can see in SSMS while connecting to server

We want to get list of all databases
So run this command

CD DATABASES
Then DIR

It will give list of all databases

Now get out of DATABASEs
CD..
Then CD LOGINS-> DIR

It will give list of All Logins

SQLPS - Powershell in SQL Server

2014-09-18T17:44:00.005+05:30

Those who are using SQL Server must have idea of Powershell eventhough they dont have used it.
Its a nice utility and sometimes makes work more easy then routine coding.
Here today first we weill get basic idea on how to use it.
Before SQL Server 2012 it was a sepereate exe SQLPS.exe, but 2012 onwards MS has added it as a seperate module for powershell. So eventhough SQLPS is available, it will be deprecated in future.

Now lets see how to use Powershell and add SQLPS modules in it.

1. Open Powerhsell
Open windows poewrshell from start menu.

2. Now we need to check either SQLPS module is loaded or not.
Run this command

Get-Module -ListAvailable

As we can see SQLPS is already loaded.
I am using SQL 2014 and it by default loaded in windows powershell.

3.Now we will connect to SQLPS
Run this command
SQLPS

4.Now we will get list of cmdlets available for SQL Server
Run this command
Get-Command -Commandtype cmdlet -Module SQLPS
It will list all available cmdlet for SQL Server

Online reindexing of partition.

2014-09-17T17:18:00.000+05:30

Till now SQL 2012 its not possible to reindex a partition online.
We had two option

1. Either reindex entire table online
2. Reindex a single partition offline.

As data growing over time and tables are now of TBs size, is normal situation partition is a required strategy for DBAs and Developers. But in OLTP system reindexing or defraging index is regular requirement and doing this activity on large table without down time is really a challenge and trouble for us.
But now in SQL Server 2014 we can reindex a partition online.
So we dont need to take table offline to reindex a partition or we dont need to reindex entire large table everytime we need reindexing.
So we can reindex current partition regularly and even dont need downtime for this.

Lets see with example for this.

create partition function part_test_func(datetime) as range
left for values ('2008-09-10','2008-09-20','2008-09-30')

CREATE PARTITION FUNCTION part_test_func( datetime)

AS RANGE LEFT FOR VALUES( '2014-09-10' , '2014-09-20' , '2014-09-30');

CREATE PARTITION SCHEME part_test_scheme

AS PARTITION part_test_func TO( [primary],[primary],[primary],[primary]);

CREATE TABLE part_test( id int IDENTITY( 1 , 1) ,

data varchar( 100) ,

date datetime)

ON part_test_scheme( date);

INSERT INTO part_test

VALUES( 'a' ,

'2014-09-05'

);

INSERT INTO part_test

VALUES( 'a' ,

'2014-09-15'

);

INSERT INTO part_test

VALUES( 'a' ,

'2014-09-25'

);

INSERT INTO part_test

VALUES( 'a' ,

'2014-10-05'

);

CREATE CLUSTERED INDEX idx ON part_test( id );

CREATE INDEX idx_data ON part_test( data);

CREATE INDEX idx_date ON part_test( date);

Alter index all on part_test

rebuild partition = 1

with (online = on)

So now we are rebuilding only 1st partition that also online. :-)

Nonclustered index got corrupted.

2014-09-17T15:29:00.003+05:30

Recently we had issue of nonclustered index corruption on table. We faced this issue multiple times and we also run DBCC CHECKTABLE to get the details of index corruption. Every time we drop index and recreate index , but immediately it got corrupted. Our system was running fine since last 3 yrs and suddenly we got this issue.
Due to this our all update and insert started to fail, and since its main summary table we are helpless till we resolve this issue.
We checked errorlog also to get issue.
Below is image of the error.

I will also describe the scenario of our system
Ours is very large system where tables have data in billions.
The table in which we were facing issue had more than 1.5 Billion records.
We had very large volume of data and this summary table is updated every 5 minutes.
Also this table is queried entire day.
So we need nolock or readuncommitted hint to query table as this table is regularly updated every 5 minutes.

While searching on net and checking hotfix we got solution for this issue.
Its a bug in MS and has affected MSSQL 2008 to 2014 version,
The reason is also same scenario which we are using.

"This issue occurs because the NOLOCK hint causes the query to incorrectly read values in the table when the query reads the same values multiple times."

It was creating wrong update plan and ultimately corrupting non clustered index.

Here is link of hotfix for this issue.

http://support.microsoft.com/kb/2878968

After applying hotfix the issue resolved successfully.
Hope this will help others also who might face this issue.

How get physical location of row.

2014-07-31T17:34:00.004+05:30

Its undocumented command and gives physical location of the row.

This helps while doing advanced trouble shooting.

Its fun to get internals of MSSQL Server doing regular tasks.

First we will look at %%physloc%%

Lets see with example

CREATE TABLE test

(

id INT,

data VARCHAR(10)

)

DECLARE @i INT

SET @I = 1

WHILE @i <= 100

BEGIN

INSERT INTO test

VALUES (@i, Replicate ('a', @i % 10))

SET @i +=1

END

SELECT *

FROM test

Now we will look into physical location of row.

SELECT %%physloc%% , *

FROM test;

This gives hexadecimal value for row location

Its not easy to decode we need function sys.fn_PhysLocFormatter(%%physloc%%) for this

so new query

SELECT sys.fn_PhysLocFormatter( %%physloc%%) , %%physloc%% , *

FROM test;

Here we can see its showing location in more readable format.

Now we want to interpret this values.

We have one more function for this sys.fn_PhysLocCracker(%%physloc%%)

So new query

SELECT sys.fn_PhysLocFormatter( %%physloc%%) , %%physloc%% , b.* , t.*

FROM test t

CROSS APPLY

sys.fn_PhysLocCracker( %%physloc%%)b;

So now we can read it more easily

It shows file_id , page_id and slot_id where row resides in database.

Do we want to get more details ?

Thanks to this function we details of file/page/slot.

Now We can use DBCC Page here.

DBCC PAGE

First we will see arguments for DBCC PAGE

1. Database name or ID

2. File ID

3.Page ID

4.Level

0 header

1 header and hex dump

2 header and page dump

3 header and detailed information

DBCC TRACEON(3604)

DBCC PAGE (master,1,479,3)

DBCC TRACEOFF(3604)

Table Valued Parameters

2014-07-02T22:30:00.002+05:30

Table Valued parameters are user defined table types and used to pass multiple rows of data as input to procedure or function.

Benefits

* we dont need many parameters

* we dont need temporary tables

* We dont need XML to pass multiple values

* pass multiple values of different data types

* Enable client to specify sort order and unique keys

* SQL 2012 onwards table valued parameters are cached for all queries.

Limits

*Have no statistics

* Must be passed as READONLY.

* Ideal for less than 1000 rows due to Initialization cost

Lets see how to use it

TRUNCATE TABLE int_data

TRUNCATE TABLE test_data

CREATE TABLE int_data

(

id INT

)

CREATE TABLE test_data

(

id INT,

data VARCHAR(100)

)

DECLARE @i INT

SET @i = 1

WHILE @i <= 256

BEGIN

INSERT INTO int_data

VALUES (@i)

SET @i = @i + 1

END

DECLARE @i INT

SET @i = 1

WHILE @i <= 256

BEGIN

INSERT INTO test_data

VALUES (@i,

Char(@i) )

SELECT @i,

Char(@i)

SET @i = @i + 1

END

SELECT *

FROM int_data

SELECT *

FROM test_data

CREATE type test_table_type AS TABLE (id int)

CREATE PROC Test_proc @test_table TEST_TABLE_TYPE readonly

BEGIN

SELECT t.*

FROM test_data t

JOIN @test_table t1

ON t.id = t1.id

END

DECLARE @t TEST_TABLE_TYPE

DECLARE @i INT

SET @i = 97

WHILE @i <= 122

BEGIN

INSERT INTO @t

VALUES (@i )

SET @i = @i + 1

END

EXEC Test_proc @t

Books online for SQL Server 2014

2014-07-02T18:45:00.005+05:30

Books online for SQL 2014 is now available
Lets see how to install it.

First go to help -> Manage Help Setting

In Manage Help Setting select "choose online or local help"

Select "I want to use local help"

In Manage Help Setting select "Install content from online"
I have already added so it shows Local
You have to click on add for selected content and finally update at bottom

Go to SQL Server 2014

Select component you want

Click on update.

Your SQL Server 2014 BOL is now installed on your server

SQL Variant

2014-06-12T17:48:00.001+05:30

SQL Variant

Its data type that stores values of various SQL Server-supported data types.

In certain conditions where we are not sure about datatype we can define it as sql_variant for testing purpose.

Its supporting int,char and binary value but will not support char(max)/varchar(max)/nvarchar(max) .

CREATE TABLE test( id int ,

word varchar(100) ,

data sql_variant

);

DECLARE

@i int;

SET @I = 1;

WHILE @i <= 10

BEGIN

if @i %2 = 1

INSERT INTO test

SELECT @i ,

'word' + CONVERT( varchar(10) , @i) ,

else

INSERT INTO test

SELECT @i ,

'word' + CONVERT( varchar(10) , @i) ,

'word' + CONVERT( varchar(10) , @i)

SET @i+=1;

END;

SELECT *,

Sql_variant_property(DATA, 'BaseType') BaseType

FROM test

We would like to know datatype of data stored in SQL_VARIANT.

We can get this information from Sql_variant_property by using basetype argument.

Limits

According to BOL

sql_variant can have a maximum length of 8016 bytes. This includes both the base type information and the base type value. The maximum length of the actual base type value is 8,000 bytes.

sql_variant cannot be used in CONTAINSTABLE and FREETEXTTABLE.

sql_variant cannot be used in like search

SELECT *

FROM test

WHERE data LIKE '%wo%'

This will fail.

We have to cast it to its basetype before doing such operation

SELECT *

FROM test

WHERE CONVERT(VARCHAR(10), data) LIKE '%wo%'

SELECT *

FROM test

WHERE CONVERT(INT, data) > 3

AND Isnumeric(data) = 1

sp_sequence_get_range

2014-06-12T13:04:00.003+05:30

This proc assigns range of sequence values to current application along with metadata.

sp_sequence_get_range [ @sequence_name = ] N'<sequence>'

, [ @range_size = ] range_size

, [ @range_first_value = ] range_first_value OUTPUT

[, [ @range_last_value = ] range_last_value OUTPUT ]

[, [ @range_cycle_count = ] range_cycle_count OUTPUT ]

[, [ @sequence_increment = ] sequence_increment OUTPUT ]

[, [ @sequence_min_value = ] sequence_min_value OUTPUT ]

[, [ @sequence_max_value = ] sequence_max_value OUTPUT ]

[ ; ]

@sequence_name = Name of sequence and schema is optional. sequence_name is nvarchar(776).

@range_size = The number of values to fetch from the sequence. @range_size is bigint.

@range_first_value = Returns the first value of the sequence object . @range_first_value is sql_variant with.

@range_last_value =Returns the last value of the requested range. @range_last_value is sql_variant .

@range_cycle_count = Returns the number of times that the sequence object cycled . @range_cycle_count is int.

@sequence_increment =Returns the increment of the sequence object used to calculate the requested range. @sequence_increment is sql_variant.

@sequence_min_value = Returns the minimum value of the sequence object. @sequence_min_value is sql_variant with.

@sequence_max_value = Returns the maximum value of the sequence object. @sequence_max_value is sql_variant.

First we will create a sequence and use this proc to get result values

CREATE SEQUENCE TestSeq

AS int

START WITH 1

INCREMENT BY 5

MINVALUE 1

MAXVALUE 500

CYCLE

CACHE 10;

DECLARE

@range_last_value sql_variant ,

@range_first_value sql_variant ,

@range_cycle_count int ,

@sequence_increment sql_variant ,

@sequence_min_value sql_variant ,

@sequence_max_value sql_variant;

EXEC Sp_sequence_get_range

@sequence_name = N'TestSeq',

@range_size = 10,

@range_first_value = @range_first_value OUTPUT,

@range_last_value = @range_last_value OUTPUT,

@range_cycle_count = @range_cycle_count OUTPUT,

@sequence_increment = @sequence_increment OUTPUT,

@sequence_min_value = @sequence_min_value OUTPUT,

@sequence_max_value = @sequence_min_value OUTPUT;

SELECT @range_first_value AS firstNumber,

@range_last_value AS lastnumber,

@range_cycle_count AS cycle_count,

@sequence_increment AS increment_value,

@sequence_min_value AS min_value,

@sequence_max_value AS max_value

Here we can see that 1 is first number and 46 is last numbers.

so number upto 46 are assigned by this proc and it can be used current session

for whatever task.

Any query that will call this sequence will now get value above 46

Now we will change range_size to 1000

so it will have to cycle sequence multiple times

DECLARE

@range_last_value sql_variant ,

@range_first_value sql_variant ,

@range_cycle_count int ,

@sequence_increment sql_variant ,

@sequence_min_value sql_variant ,

@sequence_max_value sql_variant;

EXEC Sp_sequence_get_range

@sequence_name = N'TestSeq',

@range_size = 1000,

@range_first_value = @range_first_value OUTPUT,

@range_last_value = @range_last_value OUTPUT,

@range_cycle_count = @range_cycle_count OUTPUT,

@sequence_increment = @sequence_increment OUTPUT,

@sequence_min_value = @sequence_min_value OUTPUT,

@sequence_max_value = @sequence_min_value OUTPUT;

SELECT @range_first_value AS firstNumber,

@range_last_value AS lastnumber,

@range_cycle_count AS cycle_count,

@sequence_increment AS increment_value,

@sequence_min_value AS min_value,

@sequence_max_value AS max_value

We can see that its cycled for 10 times.

Now what if cycle is not allowed

ALTER SEQUENCE testseq

NO CYCLE;

DECLARE

@range_last_value sql_variant ,

@range_first_value sql_variant ,

@range_cycle_count int ,

@sequence_increment sql_variant ,

@sequence_min_value sql_variant ,

@sequence_max_value sql_variant;

EXEC Sp_sequence_get_range

@sequence_name = N'TestSeq',

@range_size = 1000,

@range_first_value = @range_first_value OUTPUT,

@range_last_value = @range_last_value OUTPUT,

@range_cycle_count = @range_cycle_count OUTPUT,

@sequence_increment = @sequence_increment OUTPUT,

@sequence_min_value = @sequence_min_value OUTPUT,

@sequence_max_value = @sequence_min_value OUTPUT;

SELECT @range_first_value AS firstNumber,

@range_last_value AS lastnumber,

@range_cycle_count AS cycle_count,

@sequence_increment AS increment_value,

@sequence_min_value AS min_value,

@sequence_max_value AS max_value

We will get error here.

As cycle is not allowed and range_size 1000 requires it to cycle multiple times.

Functions to get identity value

2014-06-05T12:54:00.003+05:30

There are 3 functions to get current identity value

@@identity

Returns last identity value generated by last statement in current session for all scopes

If statement fires trigger and identity value generated by that trigger than @@identity

will give value generated by that trigger

Even if transaction rolled back identity value is not reverted , so @@identity value also will not reverted.

IDENT_CURRENT (<table name>)

Gives last identity value generated for specific table

Its independent of se

SCOPE_IDENTITY()

Returns identity value generated in current session current scope.

So if there is identity value generated by trigger it's not reflected here as it's not in current scope.

CREATE TABLE ident_test( id int IDENTITY( 1 , 1 ) ,

data varchar( 10)

);

CREATE TABLE trigger_test( id int IDENTITY( 5 , 10 )

);

CREATE TRIGGER ins_ident_test ON ident_test

FOR INSERT

BEGIN

INSERT INTO INTO trigger_test

DEFAULT VALUES;

END;

INSERT INTO INTO ident_test

VALUES( 'a');

INSERT INTO INTO ident_test

VALUES( 'b');

INSERT INTO INTO ident_test

VALUES( 'c');

SELECT *

FROM ident_test;

SELECT *

FROM trigger_test;

SELECT @@IDENTITY;

Return last generated identity value for statement, its from trigger

SELECT IDENT_CURRENT( 'ident_test') ,

IDENT_CURRENT( 'trigger_test');

Returns last generated identity value for table

SELECT SCOPE_IDENTITY();

Returns last generated identity value for current scope.