Database

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 …

Boost query performance with avoiding basic mistake with Select statement. Performance Tip – #42

  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 …

Determine all SQL SERVER instance in entire network or at least your machine :) TIP #41

  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 …