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, …
Problem:- One of the pain point in any SQL engineer is “Performance”. There are various reasons due to which your SQL Server database is slow. One of the possible reason is your maintenance. You don’t know when statistics last updated and take further step if those are not updated Solution:- Here we have simple …
Problem:- Suppose you have a source table and one destination table and you want to synch destination table from source table. means if record is already exists then you need to update and the record with latest value and insert the record if record is not exists. Solution:- Although there are various ways exist …
Friends, In last post post #54 we understood what is a Heap table. Now the challenge is what if this table is highly fragmented so. Now our task is to run maintenance of this highly fragmented table. So you have to run following command “ALTER TABLE TABLENAME REBUILD; For example I run the Rebuild …
Dear Friends, Although , When we have designed our database he/she knows what type of constraints is require to make data perfect and the Database designer applies those constraints but it might be possible that sometime we have to disable those constraints make some tweak with the data in the table and then again …
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:– How to count large number of rows for specific condition ? Solution Suppose you have a large table which having more than millions of records and you want to count no of rows for specific condition which itself contain more than 100 millions records. Now in this case when we use COUNT function …
Sometimes , We need to know what is the structure of stored procedure ? , How many columns will be return ? So to determine this we have easy stored procedure provided by SQL Server which is sp_describe_First_Result_Set. Below is the template to run it. You can provide your stored procedure name as variable …
Dear Friends, One of the really important part of any database is performance. Everyone complaining your system is slow , your database performance is slow but why it is slow ? Most of the time we are not aware what is the source? Which SQL statement creating problem? Lets understand this thing why our SQL …
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 …