In continuation of previous blog:
- 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.
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:
- ORDER BY is mandatory to use OFFSET and FETCH clause
- OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.
In continuation of previous blog..
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.
A. Creating a sequence that increases by 1
create sequence dbo.empby1
start with 5
increment by 1
How to check sequence through t-sql:
select next value for dbo.empby1
B. Creating a sequence that decreases by 1
create sequence dbo.empneg1
start with 0
Increment by -1;
C. Creating a sequence using default values
create sequence dbo.mirzaseq ;
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.
- Column store Indexes:
A 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:
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:-
- Column store indexes
- Contained Database
- Sequencing Objects
- Error Handling
- User defined roles
- Windows server core support
SQL Server 2014 Features:-
- New In-Memory OLTP Engine
- Enhanced Windows Server 2012 Integration
- Enhancements to AlwaysOn Availability Groups
- Enhancements to Backups
- SQL Server Data tools for Business Intelligence
- Updateable Column store Indexes
SQL Server 2016 Features:-
- Always Encrypted
- Stretch Database
- Real Time Operational Analytics
- Poly Base into SQL Server
- Native JSON Support
- Enhancements to Always On
- Enhanced In-Memory OLTP
- 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.
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.
Kindly share if you like it.
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.
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:
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.
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.
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:
As we know that each replication consists of a publisher and subscribers. I executed the below query at the subscriber end and issue resolved.
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
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.
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.
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 the Status of Notes
Run the following command to view the status of all nodes
View the status of cluster group
Run the following command to view the status of the cluster groups
View the status of cluster Networks
Run the following command to view the status of cluster networks
How to view properties of all network interface devices
Run the following command to view the properties of all network interface devices
View the Status of Cluster Resources
Run the following command to view the status of Cluster Resources
Failover Service to a new node
Run the following command to initiate a Failover
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.