SQL TIps

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 …

Different way to find 2nd minimum Salary TIP #36

  Mostly people faced this question in interview find highest salary so I am just trying to explain different ways let us understand this by following example suppose we have following table of employees with salary Query 1:-  by  aggregate function SELECT MAX (fltBasicSalary)FROM @tblEmployeeSalaryWHERE fltBasicSalary IN (SELECT DISTINCT TOP 2 fltBasicSalaryFROM @tblEmployeeSalaryORDER BY fltBasicSalary …

Row_Number() function for providing sequence number as per your wish TIP #35

  Sometimes we require sequence column or we can a row number column so we have ROW_NUMBER() function in SQL SERVER. Lets understand this by following example In this example we have  person table which have firstname, last name, middlename  columns. Now we want to fetch records  with a extra column ROW_Number() so we write …

Get different records or random order at each time when you fetch record by newId() TIP#33

Hello friends, Sometimes your project require whenever a page load or user search then each time you need to show a random order or we can say different records or order. To achieve this we can use following command SELECT * FROM yourtableName order by NewID() in below snap I am fetching person table of …

Easy way to find result set of a stored procedure without running it- TIP #32

  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 …