SQL TIps

A hidden feature sp_MSforeachtable–run on entire tables of a Database TIP# 51

Problem:- Sometimes it happened that you need to run a single statement on entire tables which exists in database.  so most of the time we think of cursor which run for each sys.objects (table)  and we execute the dynamic statement by replacing table name. Solution: Although the solution we are thinking is correct there is …

A Interview question How to determine what value you have updated of a column ? A output clause example #TIP 48

  Problem:- Sometimes it may require that whatever you have updated you want to know what was the previous value of that column? It was asked by many interviewer in the interview how to avoid trigger or how to know what was previous value before update ? Solution:- Now to achieve this the basic step …

Performance tips – How to determine last statistics update in table ? TIP #46

Hi, For maintenance purpose we need to determine when the stats last update and analyze the data and if data is out dated then we need to update the stats. To determine this we need to run the following scripts SELECT OBJECT_NAME(s.object_id) AS [ObjectName]      ,s.name AS [StatisticName]      ,STATS_DATE(s.object_id, [stats_id]) AS [StatisticUpdateDate]FROM sys.stats sINNER JOIN sys.objects …

3 different way to find row count of a table ? TIP #45

  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 …

First_value & Last_Value according to group set is that easy ? tip #44

  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 …

Interview Question- How conditionally sort the records ? TIP#43

  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 …