SQL is an essential language for you if you are developing data-driven applications. This post will bring you Microsoft SQL tricks that many of you might not have thought were possible.
1. Limit length of text:
Use this to cut short a longer text and limit the result to specified number of characters (‘Description’ is your column name):
SELECT CASE WHEN LEN(DESCRIPTION)>100 THEN LEFT(DESCRIPTION, 97) + ‘…’ ELSE DESCRIPTION END AS DESCRIPTION
FROM COUNTRIES
| Result: |
| Republic of India is a country in South Asia. It is the seventh-largest country by area, the seco… |
2. Round off a number to required decimal places:
You can use Round or Cast to achieve that:
DECLARE @NUMBER FLOAT
SET @NUMBER = 3.66666
SELECT ROUND(@NUMBER,2)
SELECT CAST(@NUMBER AS NUMERIC(36,4))
| Result: |
| 3.67 |
| 3.6667 |
3. Get the Identity Value:
Get the identity value after inserting a record in a table:
DECLARE @COUNTRY_ID INT
INSERT INTO COUNTRIES (COUNTRY_NAME)
SET @COUNTRY_ID = SCOPE_IDENTITY()
4. Using CASE Statement:
You can use SQL Case statement SELECT query as follows:
SELECT CASE WHEN RESULT >= 90 THEN ‘Excellent’
WHEN RESULT >= 80 THEN ‘Great’
WHEN RESULT >= 60 THEN ‘Average’
ELSE ‘Poor’ END
AS GRADE FROM RESULTS
You can use Case statement in WHERE clause too:
SELECT * FROM CONTACTS
WHERE GENDER = CASE WHEN @GENDER=’Female’ THEN ‘F’
WHEN @GENDER=’Male’ THEN ‘M’
ELSE ” END
5. Copy a Table:
Use this query to create an exact copy (backup) of a table. This will create a new table:
SELECT * INTO COUNTRIES_BACKUP
FROM COUNTRIES
You can also copy a table from one database to another:
SELECT *
INTO DATABASE2..COUNTRIES
FROM DATABASE1..COUNTRIES
Here’s how you can create a view from another database:
CREATE VIEW EUROPE_COUNTRIES AS
SELECT * FROM DATABASE2..COUNTRIES
WHERE DATABASE2..COUNTRIES.CONTINENT = ‘EUROPE’
6. Copy records:
Here’s how you can copy records from one table to another table which is already existing:
INSERT INTO COUNTRIES_BACKUP (Name, Continent)
SELECT Name, Continent FROM COUNTRIES
WHERE Continent = ‘ASIA’
For ease, you can also select all columns if both your tables are having same structure, columns, etc.:
INSERT INTO COUNTRIES_BACKUP
SELECT * FROM COUNTRIES
WHERE Continent = ‘ASIA’
But if your table is having an identity column, you will need to turn on ‘identity insert’ and also specify all the columns. ‘SELECT *’ will not work:
SET IDENTITY_INSERT COUNTRIES_BACKUP ON
INSERT INTO COUNTRIES_BACKUP (ID, Name, Continent)
SELECT (ID, Name, Continent) FROM COUNTRIES
WHERE Continent = ‘ASIA’
SET IDENTITY_INSERT COUNTRIES_BACKUP OFF
7. Update a Table with Join:
You can update a table with conditions based on a join with another table(s):
UPDATE STATES
SET STATES.CONTINENT=COUNTRIES.CONTINENT
FROM STATES
INNER JOIN COUNTRIES ON COUNTRIES.ID=STATES.COUNTRY_ID
WHERE STATES.TYPE=’A’
8. Find Duplicate Rows:
Everybody knows how to find distinct records from a table. But do you know how to find duplicate records? Here you go:
SELECT CITY_NAME, COUNT(CITY_NAME) AS DUPLICATES FROM CITIES
GROUP BY CITY_NAME
HAVING (COUNT(CITY_NAME) > 1)
You can also use this query to find rows that occur exactly once or twice or thrice and so on:
SELECT CITY_NAME, COUNT(CITY_NAME) AS DUPLICATES FROM CITIES
GROUP BY CITY_NAME
HAVING (COUNT(CITY_NAME) = 2)
9. Search a Text in All Tables in the Entire Database:
You can use this stored procedure to search for a text. Create this stored procedure in your database and use the following statement:
EXEC SearchAllTables ‘YourSearchText’
This will list of all tables, their columns and their values those are having your search text.
10. Aggregate Functions:
| SELECT COUNT(Description) FROM … | Counts number of records where Description is not NULL |
| SELECT COUNT(*) FROM … | Counts number of records a table |
| SELECT MIN(Price) FROM … | Returns the smallest value in a given column |
| SELECT MAX(Price) FROM … | Returns the largest value in a given column |
| SELECT SUM(Price) FROM … | Returns the sum of the numeric values in a given column |
| SELECT AVG(Price) FROM … | Returns the average value of a given column |
11. List of Databases:
Use following query to get a list of all databases in the SQL server:
SELECT * FROM SYSDATABASES
Or you can use a stored procedure:
EXEC sp_databases
12. List All Tables:
Use this to get a list of all tables within the database:
SELECT * FROM SYSOBJECTS WHERE XTYPE=’U’
13. Get Table Information:
To easily get information about a table, select table name typed in the query window and press Alt+F1. This will show list of all the columns in the table, their datatypes, lengths, etc.
You can also use following query to get column information of a table:
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘COUNTRIES’
14. List All Stored Procedures and functions:
This will list all stored procedures and functions:
SELECT *
FROM [Database_Name].INFORMATION_SCHEMA.ROUTINES
You can also get it from SysObjects:
SELECT NAME, TYPE
FROM DBO.SYSOBJECTS WHERE (TYPE = ‘P’)
15. Search Within Stored Procedures:
You can search a text in stored procedures or functions:
SELECT OBJECT_NAME(id)
FROM syscomments
WHERE [text] LIKE ‘%text-to-search%’
AND OBJECTPROPERTY(id, ‘IsProcedure’) = 1
GROUP BY OBJECT_NAME(id)
You can also get stored procedure (or function) text by its name:
SELECT SYSOBJECTS.NAME, TEXT FROM SYSCOMMENTS
INNER JOIN SYSOBJECTS ON SYSOBJECTS.ID=SYSCOMMENTS.ID
WHERE SYSOBJECTS.NAME = ‘(STORED PROCEDURE NAME)’
You can get entire stored procedure text using this:
SP_HELPTEXT
16. Generate Comma Separated Text:
You can use STUFF keyword to generate a single comma separate string from a column:
SELECT STUFF((SELECT ‘, ‘ + COUNTRY_NAME FROM COUNTRIES ORDER BY COUNTRY_NAME
FOR XML PATH (”)), 1, 2, ”) AS COUNTRY_NAMES
| Result: |
| Algeria, Belguim, Canada, China, Denmark, Egypt |
17. Execute a dynamically built statement as a query
DECLARE @Query NVARCHAR(MAX)
SET @Query = ‘SELECT * FROM STATES WHERE 1=1’
IF @COUNTRY != ” BEGIN
SET @Query+= ‘ AND COUNTRY = ”’ + @COUNTRY + ””
END
EXEC sp_executesql @Query
18. Raise an error from SQL
Often you may want to raise an error and throw it in your .Net application for many reasons:
IF (NOT EXISTS(SELECT COUNTRY_ID FROM COUNTRIES WHERE COUNTRY_ID=@COUNTRY_ID)) BEGIN
SET @ERROR = ‘Country ‘ + CAST(@COUNTRY_ID AS VARCHAR) + ‘ doesn”t exist.’
RAISERROR(@ERROR,16,1)
RETURN
END
19. Grant Permissions to an SQL User:
Below are the examples on how to grant different types of permissions on different types of objects in SQL:
GRANT SELECT ON dbo.YourTable TO YourUserName
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE ON dbo.YourTable TO YourUserName
GRANT ALL ON dbo.YourTable TO YourUserName
GRANT EXECUTE ON YourStoredProcedureName TO YourUserName
GRANT ALL ON YourStoredProcedureName TO YourUserName
You can also provide all permissions on all objects of a database by making the user a DB owner:
exec sp_addrolemember ‘db_owner’, ‘UserName’
20. Shut Down SQL Server:
if you need to stop SQL server for some reason, you can use SHUTDOWN command.
SHUTDOWN WITH NOWAIT
You can also start or stop SQL server using SQL server configuration manager:
You can also do this by starting/stopping the SQL server Windows Service:
21. Generate Random Number:
You can use RAND function to generate random numbers.
SELECT RAND()
| Result: |
| 0.79105909788182 |
SELECT CAST(RAND() * 100000 AS INT)
| Result: |
| 91892 |
22. Backup a Database:
The following will create a backup file named ‘Database_Name 20170506.BAK’ (Today’s date for example).
Please note that the directory ‘D:\DB_Backup\’ should already exist.
DECLARE @FILE_NAME VARCHAR(100)
SET @FILE_NAME = ‘D:\DB_Backup\Database_Name ‘ + CONVERT(VARCHAR, GETDATE(),112) + ‘.BAK’
BACKUP DATABASE Database_Name TO DISK= @FILE_NAME
Leave a Reply