Grouping sets is one of the cool feature came in SQL SERVER 2008. Lets understand here with problem and solution. Problem:- Suppose , We want an aggregation result in a query with different groups. Firstly we want aggregated result on first column then combination of First & second column then other column combination. So, …
Heap table:- A table without cluster index is called Heap table. Now you are thinking why we are talking about this. So as SQL developer we should aware and In next post I will explain which is related to maintenance how to Rebuild a heap table. Now our next question is how to determine …
As we discussed yesterday a hidden feature tip #51 which is sp_MSForeachtable . sp_MsForeachtable is useful to run command on each table of selected database. Now what if we want to run command on each database also ? then in that case we need to take help of sp_MsForeachDB. Sp_MsForEachDB by the name it …
Problem:- Many times you want to know how many rows exists in the particular tables. So let me share 3 different way to know this thing Solution:- Let me share 3 different options Option 1:- You all aware of this term which is Count function SELECT COUNT(1) As Rows FROM [Person].[Person] Option 2:- Sp_Space …
Problem:- Most of the time we require data in which we require first value and last value from different group of rows. Now how easy we can get result this is one of the challenge for us. Solution:- Lets understand this by an example. Suppose you have a sales table in which you maintain …
Problem:- Can we sort records according to particular condition ? Solution:- Most of the time developer faces this challenge of sorting records conditionally. I know many of us faces this question in interview. Lets understand this by an example. Suppose I have a person table in database and I want to sort the records …
As the name suggested PARSENAME is the SQL Server function which provide help in parsing. Lets understand this by an example Suppose , I have an IPAddress variable which I need to parse DECLARE @IPAddress AS VARCHAR(100)SET @IPAddress=’412.312.800.713′ SELECT PARSENAME (@IPAddress,1) The syntax of the parsename is simple PARSENAME(String,stringPartIndex) Only few things which I have …
It is very interesting how to insert multiple values with a single statement. Lets understand this with the help of below example. Suppose you have a Country table and you want to insert seed (initial values) then you can write those statement like below Now same values can be inserted with another one …
To open SQL Profiler (right now I am talking about SQL Server 2012) you need to write Profiler.exe on run command as shown in below figure for earlier version you can use profiler90.exe. You can find various parameters option using Profiler.exe –? command When you run this command you will find below screen. Although …
Sometimes, we need to understand what exactly going on with particular statement, stored procedure why it is slow? To check performance of statement what exactly going on we also interested how many logical reads, physical reads a particular statement taking. To achieve this we need to write SET STATISTICS IO ON For exampleGOSET STATISTICS IO …