SELECT REPLACE(STR(ISNULL(MAX(1),1),9), SPACE(1), '0')
Following expression will add the text before passed digit or text
SELECT REPLACE(STR(ISNULL(MAX(1),1),9), SPACE(1), '0')
In This Article I Will explain how to get the days of month by specifing the date.
Declare @test datetime
set @test = '2/1/2012'
Select DateDiff(dd, dateadd(dd, 1-day(@test),@test), dateadd(m,1,dateadd(dd, 1-day(@test), @test)))
1.Tip 1: Always use WHERE Clause in SELECT Queries while we don’t need all the rows to be returned. This will help to narrow the return rows else it will perform a whole table scan and waste the Sql server resources with increasing the network traffic. While scanning the whole it will lock the Table which may prevent other users to access the table.
Tip 2: It is seen many times developers use codes like
SELECT * FROM OrderTable WHERE LOWER(UserName)='telsa'
Instead of writing it like the below
SELECT * FROM OrderTable WHERE UserName='telsa'
Infact both the queries does the same work but the 2nd one is better and retrieves rows more speedly than the first query. Because Sql Server is not case sensitive
Tip 3: While running a query, the operators used with the WHERE clause directly affect the performance. The operators shown below are in their decreasing order of their performance.
2. >,>=,<, <=
Tip 4 : When we are writing queries containing NOT IN, then this is going to offer poor performance as the optimizer need to use nested table scan to perform this activity. This can be avoided by using
EXISTS or NOT EXISTS.
When there is a choice to use IN or EXIST, we should go with EXIST clause for better performance.
Tip 5: It is always best practice to use the Index seek while the columns are covered by an index, this will force the Query Optimizer to use the index while using IN or OR clauses as a part of our WHERE clause.
SELECT * FROM OrderTable WHERE Status = 1 AND OrderID IN (406,530,956)
Takes more time than
SELECT * FROM OrderTable (INDEX=IX_OrderID) WHERE Status = 1 AND OrderID IN (406,530,956)
Tip 6: While we use IN, in the sql query it better to use one or more leading characters in the clause instead of using the wildcard character at the starting.
SELECT * FROM CustomerTable WHERE CustomerName LIKE 'm%'
SELECT * FROM CustomerTable WHERE CustomerName LIKE '%m'
In the first query the Query optimizer is having the ability to use an index to perform the query and there by reducing the load on sql server. But in the second query, no suitable index can be created while running the query.
Tip 7: While there is case to use IN or BETWEEN clause in the query, it is always advisable to use BETWEEN for better result.
SELECT * FROM CustomerTable WHERE CustomerID BETWEEN (5000 AND 5005)
Performs better than
SELECT * FROM CustomerTable WHERE CustomerID IN (5000,5001,5002,5003,5004,5005)
Tip 8: Always avoid the use of SUBSTRING function in the query.
SELECT * FROM CustomerTable WHERE CustomerName LIKE 'n%'
Is much better than writing
SELECT * FROM CustomerTable WHERE SUBSTRING(CustomerName,1,1)='n'
Tip 9 : The queries having WHERE clause connected by AND operators are evaluated from left to right in the order they are written. So certain things should be taken care of like
Provide the least likely true expressions first in the AND. By doing this if the AND expression is false at the initial stage the clause will end immediately. So it will save execution time
If all the parts of the AND expression are equally like being false then better to put the Complex expression first. So if the complex works are false then less works to be done.Tip 10: Its sometimes better to combine queries using UNION ALL instead of using many OR clauses.
SELECT CustomerID, FirstName, LastName FROM CustomerTable
WHERE City = 'Wichita' or ZIP = '67201' or State= 'Kansas'
The above query to use and index, it is required to have indexes on all the 3 columns.
The same query can be written as
SELECT CustomerID, FirstName, LastName FROM CustomerTable WHERE City = 'Wichita'
SELECT CustomerID, FirstName, LastName FROM CustomerTable WHERE ZIP = '67201'
SELECT CustomerID, FirstName, LastName FROM CustomerTable WHERE State= 'Kansas'
Both the queries will provide same results but if there is only an index on City and no indexes on the zip or state, then the first query will not use the index and a table scan is performed. But the 2nd one will use the index as the part of the query.
Tip 11: While the select statement contains a HAVING clause, its better to make the WHERE clause to do most of the works (removing the undesired rows) for the Query instead of letting the HAVING clause to do the works.
e.g. in a SELECT statement with GROUP BY and HAVING clause, things happens like first WHERE clause will select appropriate rows then GROUP BY divide them to group of rows and finally the HAVING clause have less works to perform, which will boost the performance.
Tip 12: Let’s take 2 situations
A query that takes 30 seconds to run, and then displays all of the required results.
A query that takes 60 seconds to run, but displays the first screen full of records in less than 1 second.
By looking at the above 2 situations a developer may choose to follow the 1st option, as it uses less resources and faster in performance. But actually the 2nd one is more acceptable by a DBA. An application may provide immediate feedback to the user, but actually this may not be happening at the background.
We can use a hint like
SELECT * FROM CustomerTable WHERE City = 'Wichita' OPTION(FAST n)
where n = number of rows that we want to display as fast as possible. This hint helps to return the specified number of rows as fast as possible without bothering about the time taken by the overall query.
UPDATE TableName SET ColumnName1 = CAST(columnname1 AS varchar(10)) + '' + CAST(columnname2 AS varchar(10))
select CAST(columnname1 AS varchar(10)) + '' + CAST(columnname2 AS varchar(10)) from TableName
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'
I have a table in which i have three columns :
tablename : table1
class, gender, house
now i want to output like following
class male female house
A 3 2 rabbit
A 5 6 Bull
B 6 3 rabbit
B 0 7 bull
we want to count of students according to class and house names. so following query will be solve our problem:
Select class, SUM(case when gender = 'Male' then 1 else 0 end) as Male , SUM(case when gender = 'Female' then 1 else 0 end) as Female , house from table1 group by class, house
The following query is for getting 6th highest salary from Employee table,
SELECT TOP 1 salary FROM ( SELECT DISTINCT TOP 6 salary FROM employee
ORDER BY salary DESC) a ORDER BY salary
we can also get nth number of highest salary from table
SELECT TOP 1 salary FROM ( SELECT DISTINCT TOP n salary FROM employee
ORDER BY salary DESC) a ORDER BY salary
DBCC CHECKIDENT('YouTableName', RESEED, 0)
1. SELECT * FROM Orders LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID
WHERE Orders.ID = 12345
2. SELECT * FROM Orders LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID AND
The first will return an order and it's lines (if any) for order number
12345. The second will return all orders, but only order 12345 will have any
lines associated with it.
1) Select * from tblTest (Returns all columns/Rows)
2) Select * from tblTest Where TestID=2 (Returns the row/s which TestID has
3) Select * from tblTest where TestID Between 10 and 20 (Return all rows
between 10 and 20, this result includes 10 and 20)
4) Select * from tblTest Where TestCity in ('New
York','Washington','California') (Returns all rows which city is NewYork,
5) Select * from tblTest Where TestName Like 'A%' (Return all rows where the
name starts letter A)
6) Select * from tblTest Where TestName Like '%A' (Return all rows where the
name ends letter A)
7) Select * from tblTest Where TestName Like '[ABC]%' (Return all rows of
name start with A / B / C)
8) Select * from tblTest Where TestName Like '[^ABC]%' (Return all rows of
name not start with A and B and C)
9) Select (TestName+space(1)+TestCity) as Address from tblTest (Returns
single column address, name and city added together with a space)
10) Select * from tblTest Where TestName IS NULL (Return all rows which
TestNane has null values)
11) Select * from tblTest Where TestName IS NOT NULL (Return all rows which
TestNane has not null values)
12) Select * from tblTest Order By TestID
Desc (Sort the result set descending order, Asc or not using any sort Ascending
13) Select 'Visual Studio' as IDE, '2010' as Version (Creating memory
resident result set with two columns[IDE and Version])
14) Select Distinct TestID from tblTest (Returns unique rows based on
15) Select Top 10 * from tblTest (Return 10 customers randomly)
16) Select getdate() (Shows the current date)
17) Select db_name() (shows the database name which you are working on)
18) Select @@Servername (Shows name of the server)
19) Select serverproperty ('Edition') (You can pass following ServerName,
Edition, EngineEdition, ProductLevel to get current information about the
20) Select user_name() (Get current user)
21) Select * into #test from tblTest (Create temporary table #test and
insert all records from tblTest)
22) Select Max(TestID) from tblTest (Returns Maximum TestID from
23) Select * from tblTest Compute Max(TestID) (Returns two result sets -
getting all rows and maximum value of TestID)
24) Select FirstName, LastName, Salary, DOB,
When 'M' Then 'Male'
When 'F' Then 'Female'
(This Change Gender fields as if M then prints Male and if F then
Link for This Article: