Recently, One of my friends asked How to rename a column by SQL command frankly speaking I am not used too of using this command I prefer UI interface. Lets understand this by an example. Suppose I have a users table in which there is a column with name status which should be statusId …
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 …
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 …
As discussed in previous tip which is TIP #33 we can get random result set by using NEWID function. Now same can be achieve by using one more option which is called TABLESAMPLE. We have following syntax for it SELECT * FROM tableName TABLESAME (Number ROWS/PERCENT) For example I am using same Adventureworks database …
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 …
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 …
Dear Friends, One of the really important part of any database is performance. Everyone complaining your system is slow , your database performance is slow but why it is slow ? Most of the time we are not aware what is the source? Which SQL statement creating problem? Lets understand this thing why our SQL …
Dear Friends, Sometimes we get task like, we need to import data in a target table from a source table and the condition is that the data which we are going to import should not exist in target table . To achieve this we have two ways 1) Left outer join 2) Except clause …
As the name suggested PARSENAME is the SQL Server function which provide help in parsing. Lets understand this by an example Suppose , I have an IPAddress variable which I need to parse DECLARE @IPAddress AS VARCHAR(100)SET @IPAddress=’412.312.800.713′ SELECT PARSENAME (@IPAddress,1) The syntax of the parsename is simple PARSENAME(String,stringPartIndex) Only few things which I have …
Dear Friends, To test the performance of a stored procedure we run the stored procedure several time. You have observed when you run the stored procedure first time it took time but when you run next time it is faster. It just because the stored procedure /data is cached. Now , due to this …