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 …
Problem:- My query is slow what are the basic things I can do to get good performance without going for indexes. Solution:- May be this solution help you which I am describing here or it is possible you already aware of it. (a) Avoid function in column :-The most basic tip is avoid …
Problem :- Sometimes it may require to determine all the available instance of SQL Server in network. Solution:- One of the easy way to determine the instance with SQLCMD. Lets understand this by following steps 1) Open dos prompt 2) write SQLCMD – L OR SQLCMD/L Above command with return instance on your machine …
Problem :- Most of the time we have one master table and one child table which content reference of master table. We have to fetch all the child column as comma separated for master table column. for example I have a product category table and product table. We want all the product as a …
As discussed in last tip #37 Dense rank having exactly same syntax like Rank the only difference between rank & dense rank is that “dense_Rank fill the gap or avoid gap in ranking means if two row set have same ranking then just after record will have just next ranking. I am taking same …