January 31, 2016

SQL Server: Return Newly Inserted Row


Whenever you insert a new row in a SQL Server table that contains default values, you may want to return the newly inserted row to the user, along with the default value generated. However while doing a plain INSERT statement, all you get is the total number of rows affected.

Here’s an example:


Here we are using the NEWID() to generate a unique customer number in each row. However as you can see, the user does not get to see the default ID that got generated for the newly inserted rows. In order to get back the row values that were inserted, use the OUTPUT clause of the INSERT statement as shown here:

INSERT INTO #TT (Name, AreaCode)
SELECT 'Suprotim', 2355 UNION ALL
SELECT 'Anush', 2388
and this time you get to see the newly inserted rows:


As you can see, we have added the OUTPUT clause right after the INSERT statement. The rows inserted into the table are captured in the virtual table INSERTED and returned back as a result set.

In case you are wondering, yes it is possible to capture the result set in a table or table variable. Assuming there is a Table variable called @TempTbl, just use the following:

INSERT INTO #TT (Name, AreaCode)
INTO @TempTbl
SELECT 'Suprotim', 2355 UNION ALL
SELECT 'Anush', 2388
and now you can do further processing on this data using @TempTbl.


January 25, 2016

SQL Server 2012 Query Pagination


This article was authored by Praveen Dabade.

In SQL Server 2012, Microsoft introduced a couple of T-SQL Enhancements. One of them is Query Pagination which we will explore today.

For this demonstration, I am using the Northwind database to demonstrate this new feature. Now in most of the applications, a common requirement is how to fetch the data from the database servers, page wise.

In earlier versions of SQL Server like SQL Server 2005/2008/R2, we can implement Pagination by using different techniques. For example, we implement pagination using ROW_NUMBER() function or CTE - Common Table Expression.

In SQL Server 2012, Microsoft has introduced Pagination as a part of Select query in a Order By clause. Now you will have to use OFFSET and FETCH NEXT with the order by clause.

Let's take a look at a few examples. I am using Northwind database for this demonstration. I have created a Stored Procedure which takes two parameters. First parameter takes the page number and the second parameter ask you to fetch the no. of records for that page. The stored procedure code is as below -


If you execute the above stored procedure

EXEC FetchPagedRecords 2,10

you will get the following results -


The OFFSET specifies the number of rows to skip before it starts returning the rows and FETCH NEXT specifies the number of rows to be returned.

Microsoft has introduced an easy way of implementing Data Paging in SQL Server 2012 by adding OFFSET and FETCH NEXT in an Order By clause. I hope you will use it in your applications.


January 18, 2016

SQL Server 2012 - Binding Sequence to a Column‏


Continuing my series on SQL Server 2012, today we will learn about Sequence which is an object in SQL Server 2012 and can be used to generate customized sequence numbers. Although it is independent of objects, however an object can bind it. In this post, we will see how to use that as default value for a column

Create a sequence named my_seq

create sequence my_seq
    as int
    start with 1
    increment by 1


Create a table in which one of the columns has a default value of my_seq

create table testing (col1 int, col2 int default next value for my_seq)

Now add some data to the table

insert into testing (col1)
select 34 union all
select 6

Select data from the table and see what col2 returns

select * from testing 


Col2 returns unique numbers. It should be noted that if the sequence is used by many objects, the value may not be sequential i.e. some values may be used somewhere else.

This way we can use sequence object to generate unique numbers like an identity column


January 15, 2016

OFFSET FETCH in SQL Server 2012


I have been exploring the new OFFSET-FETCH filter introduced in SQL Server 2012 and find it quite useful. In simple words, the OFFSET and FETCH clauses give you the capability to implement a paging solution by specifying the starting and ending set of rows to return.

Madhivanan has written a nice article on OFFSET-FETCH introducing this new feature over here: Pagination with OFFSET and FETCH NEXT in SQL Server 2012

In this article, I will list down some important points to consider while working with the OFFSET-FETCH clause in SQL Server 2012
  • OFFSET indicates the number of rows to skip, FETCH indicates the rows to return after the skipped rows
  • The TOP clause in SQL Server is similar to the FETCH clause except that it does not have skipping capability.
  • However TOP supports PERCENT and WITH TIES, but OFFSET-FETCH does not.
  • Any query that uses OFFSET-FETCH must also have an ORDER BY clause
  • You can use OFFSET without FETCH, but not the opposite. With every FETCH clause, you need OFFSET clause
  • The Filter has a singular and plural support. For example to fetch one row, you can use FETCH 1 ROW. Similarly to FETCH more than 1, you can specify the plural ROWS
Check out an OFFSET-FETCH example


January 11, 2016

Parsename to Extract Precision and Scale values‏ From Decimal Numbers


The numeric datatype stores numbers with precision and scale. Suppose you want to extract only a precision or a scale, you can do it via many ways. One of the ways is to make use of the PARSENAME function.

Consider the following example

declare @amount decimal(12,2)
set @amount=87234.50
select parsename(@amount,2) as precision, parsename(@amount,1) as scale

The result is

precision            scale
---------            --------
87234                  50

Parsename is used to extract specified part of a name. In general, it is used to extract names from four part object names separated by a dot. Argument number 1 extracts the last part of a string, and the 2nd argument extracts the next last part.

This way we can effectively make use of parsename function to extract precision and scale values from the decimal numbers.


January 02, 2016

Remove Non-Alphabet Characters from a String–SQL Server


Suppose you have a string that contains numbers and other special characters and you want to keep only alphabets and remove all the other characters. One way to do this is to use a while loop that parses each character

Here is the code for the same

declare @str varchar(20)
set @str='ab12#89L(h12k'
Select @str as 'Original String'
declare @temp_str varchar(20), @i int
select @temp_str ='',@i=1
while @i<=len(@str)
    set @temp_str=@temp_str+case when substring(@str,@i,1) like '[a-zA-Z]' then substring(@str,@i,1) else '' end
    set @i=@i+1
select @temp_str as 'String with Alphabets'

The code inside the While loop takes each character and check if it is in the range a-z or A-Z and appends that character with another variable. Other characters will not get appended. So at the end of while loop, the second variable will have only alphabets.



December 13, 2015

SQL Server: Create Objects Automatically in New Databases


Suppose whenever a new database is created, you also want to create a stored procedure automatically (by default) in this new database. Let us see the simplest way to do this.

The Model database is a system database which SQL Server uses as a model to create new databases. So if you create any object in the model database, that object will be created automatically in all newly-created databases. Let us see this with the following examples

Create this test procedure in model database

use model
create procedure test
select 100 as number

Now execute this procedure

EXEC test

It displays the value 100.
Now create another database named test

CREATE database test

Now execute the same procedure in test database

use test
EXEC test


As you can see, executing the above statement displays the value 100 since this value was taken from the stored procedure in the model database and is now available with test database too.

Feel free to share other approaches to create objects automatically in a database.


November 23, 2015

Computed Columns in SQL Server


Computed columns are derived columns that are bound to values of other columns. However the datatype of these computed columns depends on the nature of the end result. They may be bound to columns of either the same datatype or they could be bound to columns of different datatypes.

Consider the following set of data

declare @t table(id int, computed_id as id, computed_date as dateadd(day,id,getdate()))
insert into @t (id)
select 5
select * from @t

The result is

SQL Computed Column
As you can see, the datatype of computed_id will be same as that of the ID as ID is directly used in the computed column definition. However the datatype of the column computed_date will be datetime because the expression dateadd(day,id,getdate()) will do an implicit conversion to datetime datatype as getdate() is used in the definition.

Let us consider another set of data

declare @t table(id int, computed_id as id/2.0, computed_date as id*300000000000)
insert into @t (id)
select 5
select * from @t

The output is

SQL Computed Column

As you can see, the datatype of computed_id will be of decimal type because of the expression id/2.0 which results to decimal number. The datatype of the column computed_numberf will be BIGINT because the expression id*300000000000 will do an implicit conversion to the BIGINT datatype as the result won't fit into a INT datatype

So the datatpye of computed column differs based on the expression and if you want to update the value returned by a computed column to another table, you need to make sure that the datatypes match each other.

Keep these points in mind while using Computed columns in SQL Server


October 26, 2015

SQL Server 2014: How Old is your Build?


The @@Version is a very handy T-SQL function which gives you the Major SQL Server version, the Edition (Standard, Business Intelligence or Enterprise), Build Number (RTM, Service Packs, Cumulative Update Level), Build Release Date and Windows Version.

Let’s run this query along with @@ServerName which gives us the SQL Server Instance Name too.

@@VERSION as [SQL Version]

Running this query on my SQL Server brings up the following:


When you expand the SQL Version column, you get the following.

Microsoft SQL Server 2014 - 12.0.2254.0 (X64) Jul 25 2014 18:52:51  Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

where :

Major SQL Server Version = Microsoft SQL Server 2014
Build Number = 12.0.2254.0 (X64)
Build Release Date = Jul 25 2014 18:52:51
Edition = Enterprise Edition (64-bit)
Windows Version = Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Some SQL Server 2014 RTM Branch Builds are as follows (ordered by Release Date):

Build Short Description Release Date (mm/dd)
11.00.9120 SQL Server 2014 CTP1 6/25/2013
12.00.1524 SQL Server 2014 CTP2 10/15/2013
12.00.2000 SQL Server 2014 RTM 4/1/2014
12.00.2342 Cumulative Update Package 1 4/21/2014
12.00.2370 Cumulative Update Package 2 6/27/2014
12.00.2381 KB 2977316 Security Update (QFE) 8/12/2014
12.00.2254 KB 2977315 Security Update (GDR) 8/12/2014
12.00.2402 Cumulative Update Package 3 8/18/2014
12.00.2405 KB 2999809 FIX 9/25/2014
12.00.2430 Cumulative Update Package 4 10/21/2014
12.00.2423 KB 3007050 FIX 10/22/2014
12.00.2436 KB 3014867 FIX 11/27/2014
12.00.2456 Cumulative Update Package 5 12/18/2014
12.00.2464 KB 3024815 FIX 1/5/2015
12.00.2472 KB 3032087 FIX 1/28/2015
The one in bold is installed on my PC and using this table with my Build number gives me an idea of how old is my build. There have been about 8 different builds released after I last installed SQL Server 2014 and I should update my latest build for better performance and security.


October 13, 2015

Does my SQL Server Database Support Compression?


Some time back, Praveen Dabade had written a nice article on SQL Server Compressed Tables and Indexes in SQL Server where he explained how compression is now supported on ‘ROW and PAGE’ for tables and indexes. However did you know that compression is an enterprise-level feature?

How do determine what Enterprise Edition features are enabled on your database? Well you can use the sys.dm_persisted_sku_features DMV to find what Enterprise Edition features are
enabled on your database.

Learn more about Dynamic Management Views (DMV’s) here

Here’s the query for the same

SELECT feature_name,feature_id

Running this query will list all edition-specific features that are enabled in the current database. Some of the database changing features restricted to the SQL Server Enterprise or Developer editions are Compression, Partitioning, ChangeCapture etc.

This DMV is also useful in situations where you are planning to move a database from a higher to a lower edition. Eg: From Enterprise to Standard edition. Running the query will tell you if there are any Enterprise Edition features enabled that may not work when you move to a lower edition.

The DMV will return no rows if no features restricted to a particular edition are used by the database.


Copyright © 2009-2016 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions