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 …
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 , It may required that you want a query who can provide you result which have phonetic similarity. Lets understand this by example Suppose you want to search a record whose pronunciation or phonetic sound are similar or close to then in that case you can use SOUNDEX property of SQL SERVER which is …
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 …
This is one the new feature in SQL Server 2012 by which you can concatenate values. The USP of the function is that it handle the NULL values also. means if you are concatenating values in which null also exists then it handle those null value also means it concatenates only those values which …
Problem: Sometimes, it might be possible that we need not null value only from particular columns and if all column have null value then we provide a default value. Lets understand this by a general and very interesting example suppose a friend come to your house and you want to give him a treat …
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 …
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 …