Sql server 2012 OFFSET use

Hello,

Today i want to share sql server 2012 new features OFFSET Fetch next example.

It is working fast more than our row_number function. It will show you difference when data in lacs and your query is complex like use inner join more than two tables and also use some sub querys.

Use below query to get more idea for fetch next function.

DECLARE @tblTemp TABLE   
(   
    ID INT,  
    firstName NVARCHAR(100),
    lastName NVARCHAR(100)
);
  
INSERT INTO @tblTemp VALUES(1,'Shikhar', 'Dhawan')  
INSERT INTO @tblTemp VALUES(2,'Rohit', 'Sharma')  
INSERT INTO @tblTemp VALUES(3,'Kumar', 'Sangakara')  
INSERT INTO @tblTemp VALUES(4,'Jonathan', 'Trott')  
INSERT INTO @tblTemp VALUES(5,'Virat', 'Kohli')
INSERT INTO @tblTemp VALUES(6,'Ms', 'Dhoni')
INSERT INTO @tblTemp VALUES(7,'Ravindra', 'Jadeja')
INSERT INTO @tblTemp VALUES(8,'Ravichandran', 'Ashiwn')
INSERT INTO @tblTemp VALUES(9,'Mitchell', 'Mccleanaghan')
INSERT INTO @tblTemp VALUES(10,'James', 'Anderson')
INSERT INTO @tblTemp VALUES(11,'Ishant', 'Sharma')
INSERT INTO @tblTemp VALUES(12,'Joe', 'Root')

DECLARE @pageIndex INT = 0,
	@pageSize INT = 5,
	@sortField NVARCHAR(50) = 'firstName',
	@order BIT = CAST(1 AS BIT)

SELECT * FROM @tblTemp
ORDER BY 
	CASE WHEN (@sortField ='firstName' AND  @order=CAST(1 AS BIT)) THEN firstName END ASC,
	CASE WHEN (@sortField ='firstName' AND @order=CAST(0 AS BIT)) THEN firstName END DESC,
	CASE WHEN (@sortField ='lastName' AND @order=CAST(1 AS BIT)) THEN lastName END ASC,
	CASE WHEN (@sortField ='lastName' AND @order=CAST(0 AS BIT)) THEN lastName END DESC
OFFSET (@pageIndex * @pageSize) ROWS
FETCH NEXT @pageSize ROWS ONLY

Remember Fetch next will only work with order by clause. You have to set order by to work with offset and fetch next.

Here in this query i cover multiple order issue of offset fetch next. If you want to use dynamic sortfield in offset than use case statement in order by clause.

enjoy new feature of sql server 2012.

Identity seed value check

Hello All,

As part of Database Migration we need to clear Data from table then need to SET OFF Is IDENTITY => No

Then import Data from one database or table to Another Database or Table then Primary Key need to again Set Identity On at that time We need to Set IDENTITY SEED value as Last Inserted or max value of Primary Key so next record will generate Id from this value , some nos of records in table may be different as compare to identity so at that time you need to get Max value of identity field and check IDENTITY SEED if both are different then need to set it.

Here as i mentioned will help you to check Identity_Seed and Max(Identity Field/ Pk)

SELECT MAX(Identity_Column_Name)
FROM tbl_Name
SELECT IDENT_SEED('tbl_Name') AS Identity_Seed;

NText Variable in Stored Procedure

Once I used the following statement in a stored procedure

declare @somelongtext ntext

but the SP gave me the error

Msg 2739, Level 16, State 1, Line 1

The text, ntext, and image data types are invalid for local variables.

Which makes me understand that text, ntext and image data types variables cannot be used with ‘declare’

So I solved the problem by using the following statement

declare @somelongtext nvarchar(max)

Use of Rank() over Partition By in SQL.

Use of Rank() over Partition By in SQL.
 
Suppose a table SelectDuplicate has rows as under
 
ID         Fname             Lname             City
1          Ritesh               Shah                 Ahmedabad
2          Avi                    Sagiv                Edison
3          Dharmesh         Kalaria              Parsipenny
4          Ritesh               Shah                 WestField
5          Dharmesh         Kalaria              Ahmedabad
 
And we need to remove duplicate rows from this table
 
Then the query for this would be as under

SELECT * FROM
  (SELECT ID,FNAME,LNAME,CITY, RANK() OVER 
  (PARTITION BY FNAME,LNAME ORDER BY ID) AS RANK
  FROM  SELECTDUPLICATE) TMP WHERE TMP.RANK = 1
 

Suppose a table BlogCount has the following rows
 
BloggerName              Topic               Year                 Total
Ritesh                           SQL                  2005                 10
Ritesh                           SQL                  2006                 17
Alpesh                          SQL                  2007                 124
Nikunj                           SQL                  2008                 124
Ritesh                           .NET                 2008                 24
Nisha                            SQL                  2007                 14
nikunj                            .NET                 2007                 18
Alpesh                          SQL                  2008                 14

SELECT * FROM
  (SELECT BLOGGERNAME,TOPIC,YEAR,TOTAL,RANK() OVER 
  (PARTITION BY BLOGGERNAME ORDER BY TOTAL DESC) AS RANK
  FROM  BLOGCOUNT) TMP WHERE TMP.RANK = 1
 

This query will return following rows i.e the topic, year and maximum total for each Blogger.
 
BloggerName              Topic               Year     Total                rank
Alpesh                          SQL                  2007     124                   1
Nikunj                           SQL                  2008     124                   1
Nisha                            SQL                  2007     14                     1
Ritesh                           .NET                 2008     24                     1

Features of SQL 2008, that makes it different from SQL 2005

  • SQL Server 2008 has built-in compression that allows you to compress the database files and the transaction log files associated with the compressed database.ROW compression means storing fixed length data in variable length storage format and PAGE compression means minimizing the data redundancy.
  • Then there’s the Resource Governor. This new feature lets you define the amounts of resources that individual or groupings of workloads are allowed to use during execution. With Resource Governor, you can create an environment in which many different workloads coexist on one server without the fear of one or more of those workloads overwhelming the server and reducing the performance of the other workloads.
  • SQL Server 2008 works with the table partitioning mechanism (which was introduced in SQL Server 2005) to allow the SQL Server engine to escalate locks to the partition level before the table level. This intermediary level of locking can dramatically reduce the effects of lock escalation on systems that have to process hundreds and thousands of transactions per second.
  • With SQL Server 2005, many administrators started implementing database mirroring to achieve high availability. SQL Server 2008 offers many improvements for the practice of database mirroring. For instance, in the past, database mirroring occasionally had performance issues related to moving transaction log data from the principal to the mirrored databases. In response, SQL Server 2008 now reduces the amount of information that is moved across the network from the principal’s transaction log to the mirror’s transaction log by compressing the information before sending it to the mirror’s transaction log for hardening.
  • Extensible Key Management allows for an enhanced structure to safely store the keys used in the encryption infrastructure—not only in the database itself but also outside the database in third-party software modules or with a Hardware Security Module.
  • One example is the new MERGE statement, which allows the developer to check for the existence of data before trying to insert the data. This check prior to performing the INSERT statement allows the data to be updated. No longer is it necessary to create complex joins in order to update data that exists and to insert data that does not already exist, all during a single statement.
  • SQL Server 2008 introduces two separate data types to handle date and time data. Different data types will translate to improved performance for many queries since there will no longer be a need to perform an operation on the data before it can be used in the query.
  • When creating newer database structures, database developers often find themselves stretching the structure of databases in order to implement mapping applications. SQL Server 2008 helps to address this issue with new spatial data types. The two spatial data types, GEOGRAPHY and GEOMETRY, allow developers to store location-specific data directly into the database without having to break those data elements down into formats that fit other standard data types.
  • SQL Server 2008 has the new FILESTREAM data type. With this data type, files can still be stored outside of the database, but the data is considered part of the database for transactional consistency. This allows for the use of common file operations while still maintaining the performance and security benefits of the database.
  • SQL Server 2008 introduces sparse columns, which allows NULL values to be stored without taking up any physical space on the disk. Because sparse columns do not consume actual space, tables that contain sparse columns can actually exceed the 1,024 column limit.
  • SQL Server 2008 also introduces a new mechanism, Change Data Capture, for managing incremental changes that need to be loaded into the data warehouse. This captures and places changed data into a set of change tables. Capturing updated, deleted, and inserted data in an easily consumed storage schema allows for the incremental loading of data warehouses from those tables—as opposed to having to build custom insert statements that try to figure out the changes made to existing rows of data before updating the data warehouse

 
Out of all these features developers/programmers use Resource Governor, MERGE statements and Datatypes more frequently.

Query to fetch the value of EXEC() into a variable

Generally its not possible to directly assign the value of EXEC() into a variable.
But it can be done very easily with the help of a temporary table as shown under.

DECLARE @Database NVARCHAR(50)
DECLARE @cmd NVARCHAR(200)
DECLARE @lastrec int

--  Assign some valid database and table name to variable @Database.

create table #temp(pid int)
set @cmd = 'select max(PK_UniqueID) lastrec from '+ @Database
insert into #temp exec(@cmd)
select @lastrec = pid from #temp
drop table #temp

Inserting multiple records using single insert statement

Hello All,

When we want to insert multiple records into table, we do it using multiple INSERT INTO clauses as follow.

CREATE TABLE tblTest(FirstName VARCHAR(50),LastName VARCHAR(50))
INSERT INTO tblTest (FirstName,LastName) VALUES ('first1', 'last1')
INSERT INTO tblTest (FirstName,LastName) VALUES ('first2', 'last2')
INSERT INTO tblTest (FirstName,LastName) VALUES ('first3', 'last3')
INSERT INTO tblTest (FirstName,LastName) VALUES ('first4', 'last4')

We can insert multiple records using single INSERT INTO clause. For this we need to use UNION ALL clause with INSERT INTO clause as follow.

INSERT INTO tblTest (FirstName,LastName)
SELECT 'first1', 'last1'
UNION ALL
SELECT 'first2', 'last2'
UNION ALL
SELECT 'first3', 'last3'
UNION ALL
SELECT 'first4', 'last4'

Difference between DELETE & TRUNCATE statement

Hello All, Today we discuss some DELETE & TRUNCATE differences.

Delete and Truncate Statement are used to remove data from table.

We can specify condition using where clause in delete statement. We can not specify condition in truncate statement. Truncate statement will remove all data from table.

Delete removes one row at a time and records entry in transaction log for each deleted row. TRUNCATE removes the data by De-allocating the data pages used to store the table’s data, and only the page De-allocations are recorded in the transaction log. So truncate is faster then delete.

We can rollback delete command. We can not rollback truncate command.

Delete does not reset identity of table. Truncate resets identity of table.

Delete is a DML command. Truncate is a DDL command.

Delete activates trigger. Truncate does not activate trigger.

Selecting a Comma Seperated List

Hello All,

Sometimes we required a comma separated value instead of table. So generally people make User define functions or Cursors or loop but there is a really very simple way to do this in a simple select statement.

See the below query

DECLARE @Users varchar(MAX)
SET @Users = ''
SELECT @Users = @Users + CONVERT(NVARCHAR(50),firstname) + ','
FROM UserDetails
SET @Users = LEFT(@Users,LEN(@Users)-1)
SELECT @Users as users

How to delete duplicate records ?

Hello Everyone,

Sometimes we encounter a problem in sql table to delete duplicate records (eg there are 3 similar records, only 2 have to be deleted) and we need a SQL query for this.
This is a common problem so I thought I’d provide some solutions for this problem.

If table contains primary key then you can delete duplicate records by using below sql query.

DELETE FROM TableName WHERE PrimaryColumn NOT IN
(SELECT MIN(PrimaryColumn) FROM TABLENAME GROUP BY DuplicateColumnName)

If table doesn’t contains primary key then you can delete it easily by using following method.
The simplest way to delete the duplicate records is to SELECT DISTINCT into a temporary table, truncate the original table and SELECT the records back into the original table.

You can also delete it by using below query.

With temptable as
(
SELECT ROW_NUMBER() over (PARTITION BY DuplicateColumnName,DuplicateColumnName ORDER BY DuplicateColumnName,DuplicateColumnName) AS rownumber,* FROM TableName
)
DELETE FROM temptable WHERE rownumber > 1