SQL Server 2012 Features:3: Pagination

In continuation of previous blog:

  1. Pagination: This feature focuses on the result set of T-sql. It divides the result set into different parts to view easily. We can do pagination by using ‘OFFSET’ and ‘FETCH’ commands. We can pull only the required number of records instead of all.

Example:

There is an emp table and we are applying pagination on the same.

Here I have applied pagination for 0 to 5 records.

Few points to ponder:

  1. ORDER BY is mandatory to use OFFSET and FETCH clause
  2. OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.

 

 

Posted in Database Administration, Sql Server New Feature, T-SQL | Leave a comment

SQL Server 2012 Features:2:Sequencing

In continuation of previous blog..

Sequencing

A sequence is a user-defined schema bound object that generates a sequence of numeric values according to the specification with which the sequence was created. This sequence is generated in an order of ascending or descending at a defined interval. Sequence is not attached to a table.

Example:

A. Creating a sequence that increases by 1

create sequence dbo.empby1

start with 5

increment by 1

GO

How to check sequence through t-sql:

select next value for dbo.empby1
Result:

Sequence part1

In SSMS:

Sequence part2 ssms

B.  Creating a sequence that decreases by 1

create sequence dbo.empneg1

start with 0

Increment by -1;

GO

C. Creating a sequence using default values

create sequence dbo.mirzaseq ;

Posted in Database Administration, Sql Server New Feature | Leave a comment

SQL Server 2012 Features:1:Column store Indexes

As promised to provide little glimpse of each feature from the definition point of view, I am ready to deliver each feature each week for your kind notice.

  1. Column store Indexes:

columnstore index is a technology for storing, retrieving and managing data by using a columnar data format, called a columnstore. A columnstore is data that is logically organized as a table with rows and columns, and physically stored in a column-wise data format.

For high performance and high compression rates, the columnstore index slices the table into groups of rows, called rowgroups, and then compresses each rowgroup in a column-wise manner.

They are the preferred data storage format for data warehousing and analytics workloads.

Use a clustered columnstore index to store fact tables and large dimension tables for data warehousing workloads. This improves query performance and data compression by up to 10x.

Use a nonclustered columnstore index to perform analysis in real-time on an OLTP workload.

How to create index through SSMS:

Column store Index

 

Posted in Database Administration, Sql Server New Feature | Tagged , | Leave a comment

Interview Question:Important New Features of SQL Server 2012, 2014 & 2016

Questions may be asked from the DBA prospective so I tried to collect few important features for SQL Server 2012,2014 and 2016 . Next blog will be published with explanation for each feature.

SQL Server 2012 Features:-

  1. Column store indexes
  2. Contained Database
  3. Sequencing Objects
  4. Pagination
  5. Error Handling
  6. User defined roles
  7. Windows server core support

SQL Server 2014 Features:-

  1. New In-Memory OLTP Engine
  2. Enhanced Windows Server 2012 Integration
  3. Enhancements to AlwaysOn Availability Groups
  4. Enhancements to Backups
  5. SQL Server Data tools for Business Intelligence
  6. Updateable Column store Indexes

SQL Server 2016 Features:-

  1. Always Encrypted
  2. Stretch Database
  3. Real Time Operational Analytics
  4. Poly Base into SQL Server
  5. Native JSON Support
  6. Enhancements to Always On
  7. Enhanced In-Memory OLTP
  8. Revamped SQL Server Data Tools

Note:  A brief description will be published for each feature in coming post.

Please like or comment if it is  beneficial for you.

Thank You!!!

Posted in Sql Server, Sql Server New Feature | Leave a comment

Microsoft + LinkedIn

960-microsoft-acquire-linkedin-a-196pershare-deal

REDMOND, Wash., and MOUNTAIN VIEW, Calif. — June 13, 2016 — Microsoft Corp. (Nasdaq: MSFT) and LinkedIn Corporation (NYSE: LNKD)  announced they have entered into a definitive agreement under which Microsoft will acquire LinkedIn for $196 per share in an all-cash transaction valued at $26.2 billion, inclusive of LinkedIn’s net cash. LinkedIn will retain its distinct brand, culture and independence. Jeff Weiner will remain CEO of LinkedIn, reporting to Satya Nadella, CEO of Microsoft.

LinkedIn is the world’s largest and most valuable professional network and continues to build a strong and growing business.

Nadella’s Statement about LinkedIn:

“The LinkedIn team has grown a fantastic business centered on connecting the world’s professionals. Together we can accelerate the growth of LinkedIn, as well as Microsoft Office 365 and Dynamics as we seek to empower every person and organization on the planet.”

Note: Please refer the below link from Microsoft to get the complete news.

Microsoft to acquire LinkedIn

Kindly share if you like it.

Posted in Microsoft News, Uncategorized | Leave a comment

Blocking caused by -1/Negative SPID

I received a request from my development team for a query which is stuck and not giving any result and no error throwing.

I thought for a moment and start troubleshooting by checking the blocking. I caught the issue as blocking was there. The major blocking spid was -1.

I prepared a solution of this problem for everyone by collecting the information from different sources.

Solution:

What is -1 spid?

SPIDs with a value of -1 are orphaned distributed transactions. It is a distributed transaction for which the transactional state is unknown. A distributed transaction is a database transaction that involves more than one database system (usually) located on different servers.

These negative values may be -1, -2 etc.

The process responsible for coordinating these transactions is the Microsoft Distributed Transaction Coordinator, or MSDTC.

How to Kill Negative SPId:

Open a new query window and execute the script:

select  * from sys.syslockinfo

 You will get the result as below:

UOW (Unit of work)

 

Here, need to check the UOW (Unit of work), it would be a 24 character long id, Copy the UOW id and then kill it.

Kill ‘CD947343-36F5-4515-B4CB-3A42402C8207’

Thank You!

Posted in Database Administration, Sql Server, Uncategorized | Leave a comment

SQL Server on Linux

SQL-Loves-Linux_2_Twitter-002-640x358

A glimpse of this announcement from Microsoft as below:

SQL Server on Linux will provide customers with even more flexibility in their data solution. One with mission-critical performance, industry-leading TCO, best-in-class security, and hybrid cloud innovations – like Stretch Database which lets customers access their data on-premises and in the cloud whenever they want at low cost – all built in.

“SQL Server’s proven enterprise experience and capabilities offer a valuable asset to enterprise Linux customers around the world,” said Paul Cormier, President, Products and Technologies, Red Hat. “We believe our customers will welcome this news and are happy to see Microsoft further increasing its investment in Linux. As we build upon our deep hybrid cloud partnership, spanning not only Linux, but also middleware, and PaaS, we’re excited to now extend that collaboration to SQL Server on Red Hat Enterprise Linux, bringing enterprise customers increased database choice.”

 

Note: To read the complete news please refers Microsoft blog and news.

https://blogs.microsoft.com/blog/2016/03/07/announcing-sql-server-on-linux/

Thank You!

Posted in Microsoft News | Leave a comment

SQL SERVER ERROR- Cannot insert explicit value for identity column in table ‘table’ when IDENTITY_INSERT is set to OFF

SQL SERVER ERROR- Cannot insert explicit value for identity column in table ‘tablename’ when IDENTITY_INSERT is set to OFF

Yesterday I got this error while monitoring the replication status of a server. The actual error is in snapshot as below:

SQL SERVER ERROR- Cannot insert explicit value for identity column in table 'SchemeToLocation' when IDENTITY_INSERT is set to OFF

Resolution:

As we know that each replication consists of a publisher and subscribers. I executed the below query at the subscriber end and issue resolved.

Script:

Go to Subscriber end and execute the below query by changing the table name:

Declare @TableID Int = (

Select  Top 1 sys.sysobjects.id As Table_ID

From    sys.sysobjects

Join    sys.syscolumns

On  syscolumns.id = sysobjects.id

Where   sys.sysobjects.name = ‘Tablename’

And (sys.syscolumns.status & 0x88) = 0x80 — 0x80 = Identity, 0x08 = Not For Replication

Order By 1   )

IF @TableID IS NOT NULL

EXEC    sys.sp_identitycolumnforreplication @TableID, 1

Note: After running this script at the subscriber end, go to publisher and right click on the database which we need to monitor and launch replication monitor and check the subscriber status.

Your likes and comments would be highly appreciated.

Thank You

Posted in Database Administration, Replication, Uncategorized | Leave a comment

How to logoff another user from sql server

How to logoff a user from SQL  Server

It is obvious that a single server may be accessed by several people at a time. We can logoff active/inactive users from the SQL server as per requirement.

It is simple and very easy task need to perform as steps given below:

Open the task manager –> Go to users option –> Select the user –> Logoff

 

 

Note:  Take precautions while logging off the user.

  • You must have full control permission in case of logoff another user from a session.
  • You should send a message to the users before taking this action because logging off a user without warning can result in loss of data on the user’s session.

 

Posted in Database Administration, Security, Uncategorized | Leave a comment

SQL Server Clustering from Command Line

Introduction

In this tutorial I will show you few command line options to manage a SQL Cluster status as well as for falling over.

Need of Command Line

The graphical tool works great, but what can you do when your Failover Cluster Manager freezes and you are not be able to start the program.

Here I will show you few command line that includes status checks and how to failover

How to view SQL Cluster

Run the following command to view all SQL Cluster on the network

view sql cluster
cluster list

View the Status of Notes

Run the following command to view the status of all nodes

view status of nodes
cluster node

View the status of cluster group

Run the following command to view the status of the cluster groups

view status of cluster group
cluster group

View the status of cluster Networks

Run the following command to view the status of cluster networks

view status of cluster networks
cluster network

How to view properties of all network interface devices

Run the following command to view the properties of all network interface devices

view properties of all network interface devices
cluster network interface

View the Status of Cluster Resources

Run the following command to view the status of Cluster Resources

view status of cluster resources
cluster resource

Failover Service to a new node

Run the following command to initiate a Failover

failover service to a new node
initiate failover

Conclusion

This article describes how to manage a SQL Cluster form the command line when graphical tool gets frozen. These command lines are easy to execute and give you all the information which you can get from graphical tool. You can also use these cluster command lines to configure, create, administer server cluster form form the command prompt.

Posted in Clustering, Database Administration | 2 Comments