Synonyms

A synonym is an alternate name for a schema scoped object. Synonyms allow client applications to use the single-part name of a synonym to reference a base object instead of a using a two-, three-, or four-part name to reference the base object.

Advantage

It replaces long multi-part object name in SQL Statement

It also provides an abstractions layer which will protect SQL statement using synonyms from changes in underlying objects (tables etc).

Example


-- CREATE SYNONYMS
CREATE SYNONYM MyWork
FOR SQLWorks.dbo.test

-- USE SYNONYMS
SELECT * FROM MyWork

-- DROP SYNONYMS
DROP SYNONYM MyWork

Ranking Functions #1

Ranking functions return a ranking value for each row in a partition. Depending on the function that is used.Ranking functions are nondeterministic.*

The four ranking functions are ROW_NUMBER(), NTILE(), RANK() and DENSE_RANK() in SQL.

#1 ROW_NUMBER() function

“Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.”
Syntax

ROW_NUMBER ( ) OVER ( [ partition_by_clause ] order_by_clause )

[ partition_by_clause ] is optional

partition_by_clause
Divides the result set produced by the FROM clause into partitions to which the ROW_NUMBER function is applied.
order_by_clause
Determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition.

Example

--********** RANKING FUNCTION #1 ROW_NUMBER() ********
-- Create table
CREATE TABLE RankTest (CandidateID int NOT NULL,
[Year] smallint NOT NULL,
Department VARCHAR(100) NOT NULL
);

-- Insert data
INSERT INTO RankTest (CandidateID, [Year], Department)
select 1,2010,'SOFTWARE' UNION ALL select 1,2010,'BIZINFO' UNION ALL
select 1,2010,'ECOMMERCE' UNION ALL select 1,2010,'BIZINFO' UNION ALL
select 1,2010,'ECOMMERCE' UNION ALL select 1,2010,'BIZINFO' UNION ALL
select 1,2010,'ECOMMERCE' UNION ALL select 1,2010,'ECOMMERCE' UNION ALL
select 1,2010,'BIZINFO' UNION ALL select 1,2010,'SOFTWARE' UNION ALL
select 2,2010,'SOFTWARE' UNION ALL select 2,2010,'BIZINFO' UNION ALL
select 2,2010,'ECOMMERCE' UNION ALL select 2,2010,'BIZINFO' UNION ALL
select 2,2010,'ECOMMERCE' UNION ALL select 2,2010,'BIZINFO' UNION ALL
select 2,2010,'ECOMMERCE' UNION ALL select 2,2010,'ECOMMERCE' UNION ALL
select 2,2010,'BIZINFO' UNION ALL select 2,2010,'SOFTWARE';

--ROW_NUMBER() #1
SELECT CandidateID ,[Year] , Department , ROW_NUMBER() OVER (PARTITION BY Department ORDER BY CandidateID)
FROM RankTest

--ROW_NUMBER() #2
SELECT CandidateID ,[Year] , Department , ROW_NUMBER() OVER (ORDER BY Department)
FROM RankTest

NEWID() Function

Creates a unique value of type uniqueidentifier.

We can use this function to randomly sort records in the table


Example

--***** NEWID() *********

SELECT TOP 10 * FROM wages ORDER BY NEWID()

You will get new set records everytime you run the above query

CUBE Operator in TSQL

We can use CUBE operator with GROUP BY clause to summarize data in a table .

Following are the specific differences between CUBE and ROLLUP:

* CUBE generates a result set that shows aggregates for all combinations of values in the selected columns.
* ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns.

If the ROLLUP keyword in the query is changed to CUBE, the CUBE result set is the same, except these two additional rows are returned at the end

Example:
--********* CUBE Operator ***********
SELECT
CASE WHEN GROUPING(CategoryName) = 1 THEN 'ALL Categories' ELSE CategoryName END AS Category_Name ,
CASE WHEN GROUPING(VENDOR) = 1 THEN 'ALL Vendors' ELSE VENDOR END AS Vendor, COUNT(URL) AS URLS
FROM testRoolUp
GROUP BY CategoryName , VENDOR
WITH CUBE

ROLLUP Operator

The ROLLUP operator is useful in generating reports that contain subtotals and totals. For example when we want the report which shows URL Count for each Category and Vendor , simply we can use group by Category and Vendor

SELECT CategoryName ,VENDOR , COUNT(URL) AS URLS
FROM testRoolUp
GROUP BY CategoryName , VENDOR
Output:

When we want report like below , Here ROLLUP and GROUPING comes into the picture and help us. ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns.




Example
-- *********** ROLLUP ****************
--Create Table
CREATE TABLE testRoolUp
(
CategoryName VARCHAR(100),
Vendor VARCHAR(100),
URL NVARCHAR(MAX)
)
-- Insert data
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Digital Camera' ,'Amazon', 'http://www.shopzilla.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Digital Camera' ,'Amazon', 'http://www.amazon.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Digital Camera' ,'Amazon', 'http://www.shopzilla.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Digital Camera' ,'Canon', 'http://www.shopzilla.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Digital Camera' ,'Canon', 'http://www.shopzilla.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Digital Camera' ,'Canon', 'http://www.shopzilla.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Digital Camera' ,'Canon', 'http://www.shopzilla.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Digital Camera' ,'Canon', 'http://www.shopzilla.com')

INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Camcorder' ,'Amazon', 'http://www.shopzilla.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Camcorder' ,'Amazon', 'http://www.amazon.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Camcorder' ,'Amazon', 'http://www.shopzilla.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Camcorder' ,'Canon', 'http://www.shopzilla.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Camcorder' ,'Canon', 'http://www.shopzilla.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Camcorder' ,'Canon', 'http://www.shopzilla.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Camcorder' ,'Canon', 'http://www.shopzilla.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Camcorder' ,'Canon', 'http://www.shopzilla.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Camcorder' ,'Canon', 'http://www.shopzilla.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Camcorder' ,'Canon', 'http://www.shopzilla.com')
INSERT INTO testRoolUp (CategoryName ,Vendor, URL) VALUES ('Camcorder' ,'Canon', 'http://www.shopzilla.com')

-- ROLLUP
SELECT CategoryName ,VENDOR , COUNT(URL) AS URLS
FROM testRoolUp
GROUP BY CategoryName , VENDOR
WITH ROLLUP



GROUPING clause add new column in result set. The value in new column can either be 0 or 1. If the new row is added by the ROLLUP then the value of GROUPING column is 1 else 0.

-- ROLLUP With GROUPING

SELECT
CASE WHEN GROUPING(CategoryName) = 1 THEN 'ALL Categories' ELSE CategoryName END AS Category_Name ,
CASE WHEN GROUPING(VENDOR) = 1 THEN 'ALL Vendors' ELSE VENDOR END AS Vendor, COUNT(URL) AS URLS
FROM testRoolUp
GROUP BY CategoryName , VENDOR
WITH ROLLUP

SET IMPLICIT_TRANSACTIONS

This option is very useful in testing environment , we can test/analyze data by running queries against a table and data can be easily rolled back to the old state.

SET IMPLICIT_TRANSACTIONS ON

When a connection is in implicit transaction mode we must explicitly committed or rolled back. Otherwise, the transaction and all of the data changes it contains are rolled back when the user disconnects.

--*********** Implicit Transaction ON**************

SET IMPLICIT_TRANSACTIONS ON;

UPDATE wages SET hourly_wage = 200

SELECT * FROM wages

ROLLBACK TRANSACTION
[OR]
COMMIT TRANSACTION

When you are not COMMIT/ROLLBACK transaction explicitly , and try to close the connection you will get the following confirmation message box.


We can enable Implicit Transaction using Query options also

Reset Identity Column

Even though we are deleting all the records in a table (which includes Identity column) sql server maintains Identity value.To resolve this problem we can use RESEED option.

DBCC CHECKIDENT (tablename, RESEED, 0)

Note : we can also use truncate table when there is no foreign key relationship

Example

--******** Reset Identity Column Value **********

CREATE TABLE test(id INT IDENTITY, NAME VARCHAR(10))

INSERT INTO test(name ) VALUES('a')
INSERT INTO test(name ) VALUES('a')
INSERT INTO test(name ) VALUES('a')

DELETE FROM test

-- The above statement will delete the record but maintain the identity value.

INSERT INTO test(name) VALUES ('a')

SELECT * FROM test

Output
id name
4 a

Database Snapshot

A database snapshot is a read only copy of our database, but not exactly a copy. Snapshots can be used for auditing & reporting purposes and Recovery.

Useful scenarios

Snapshots can be used in conjunction with database mirroring for reporting purposes. You can create a database snapshots on the mirror database and direct client connection requests to the most recent snapshot.

Maintaining historical data for report generation. You can create a database snapshot at the end of a given time period (such as a financial period) for later reporting.

Safeguarding data against administrative and user error. A database can be reverted back to the state it was in when the snapshot was created. Reverting is potentially much faster for this purpose than restoring from a backup.

In a test environment, it can be useful to quickly return a database back to the state it was prior a test run.

Note: Database snapshots are available only in the Enterprise Edition of Microsoft SQL Server

How Database Snapshots Work?

When the snapshot is first created, the sparse file is empty as shown below. When data is read from the snapshot, it is actually being read from the primary database files. As data changes in your primary database, SQL Server then writes out what the data looked like prior to the data change into a sparse file, so this way the data stays static.

The snapshot will then read the sparse file for the data that has been changed and continue to read the primary database for data that has not changed.




When the first update operation happens, the original data is copied into the sparse file. The database engine then updates the data in the source database.



Example

--************ Database Snapshot ************
--1. Create
CREATE DATABASE DB_Snapshot_2009 ON (
NAME = 'test',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AWDB_Snapshot1.ss')
AS SNAPSHOT OF test; -- database name


--2.Recovery
RESTORE DATABASE test
FROM DATABASE_SNAPSHOT = 'DB_Snapshot_2008'

--3.Drop
Drop database AWDB_Snapshot_20080522

Extract Domain name from URL

The following query extracts domain name from the URL

Example:

URL :http://www.shopzilla.com/sharp-lc46e77u-46-in-1080p-lcd-hdtv/1029258582/compare

Domain : www.shopzilla.com

-- Set Domain_name

update Computers_and_Software
set Domain_Name = a.domain
from
(select Product_URL , replace(substring(Product_URL , 0 , charindex('/' , replace(Product_URL , '//' , '||'))), 'http://' , '') as domain from Computers_and_Software )a
where Computers_and_Software.Product_URL = a.Product_URL



-- Step1
update Computers_and_Software
set Domain_Name = a.domain
from
(
select Product_URL , replace(substring(Product_URL , 0 , charindex('//' , replace(Product_URL , 'http://' , '|||||||'))), 'http://' , '') as domain from Computers_and_Software
where Domain_Name = ' '
) a
where Computers_and_Software.Product_URL = a.Product_URL


--Step2
update Computers_and_Software
set Domain_Name = a.domain
from
(
select Product_URL , replace(substring(Product_URL , 0 , charindex('?' , replace(Product_URL , 'http://' , '|||||||'))), 'http://' , '') as domain from Computers_and_Software
where Domain_Name = ' '
) a
where Computers_and_Software.Product_URL = a.Product_URL


select * from Computers_and_Software
where Domain_Name = ' '

--Step3
update Computers_and_Software
set Domain_Name = Product_URL
where Domain_Name = ' '

CheckSum Function

Returns the checksum value computed over a row of a table, or over a list of expressions. CHECKSUM is intended for use in building hash indexes.

comparing row (with large size data ex: html source) values in a table can be a difficult and resource intensive process.

Using checksum we can create Hash value for comparison instead of large data

Drawback

If one of the values in the column list changes, the checksum of the list also generally changes. However, there is a chance that the checksum will not change (i.e. collisions ).

So we can use Checksum function when our application can tolerate occasionally missing a change.


Testing

I have tested for Html_source column with 6.5 million data , only less then 100 hash values are wrong but the performance is extraordinary ,

when we need a immediate comparison solution for columns with large data CheckSum function is the best choice

Example

--******* CheckSum Function ***********

-- Generate hash value

ALTER TABLE Compress_Data1
ADD hashvalue AS CHECKSUM(source) PERSISTED

-- Find duplicate using hash value instead of Source

SELECT hashvalue , COUNT(hashvalue) AS Duplicate
FROM Compress_Data1
GROUP BY hashvalue
HAVING COUNT(hashvalue) > 1

COALESCE (T-SQL)

Returns the first nonnull expression among its arguments.If all arguments are NULL, COALESCE returns NULL.

COALESCE(expression1,...n) is equivalent to the following
CASE expression:
CASE
WHEN (expression1 IS NOT NULL) THEN expression1
WHEN (expression2 IS NOT NULL) THEN expression2
...
ELSE expressionN
END

Example

--************ coalesce *******

CREATE TABLE dbo.wages
(
emp_id tinyint identity,
hourly_wage decimal NULL,
salary decimal NULL,
commission decimal NULL,
num_sales tinyint NULL
);

INSERT dbo.wages (hourly_wage, salary, commission, num_sales)VALUES(10.00, NULL, NULL, NULL),
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)VALUES(20.00, NULL, NULL, NULL),
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)VALUES(30.00, NULL, NULL, NULL)
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)VALUES(40.00, NULL, NULL, NULL)
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)VALUES(NULL, 10000.00, NULL, NULL)
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)VALUES(NULL, 20000.00, NULL, NULL)
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)VALUES(NULL, 30000.00, NULL, NULL)
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)VALUES(NULL, 40000.00, NULL, NULL)
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)VALUES(NULL, NULL, 15000, 3)
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)VALUES(NULL, NULL, 25000, 2)
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)VALUES(NULL, NULL, 20000, 6)
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)VALUES(NULL, NULL, 14000, 4)

SELECT * FROM dbo.wages

SELECT CAST(COALESCE(hourly_wage * 40 * 52, salary, commission * num_sales) AS money) AS 'Total Salary'
FROM dbo.wages
ORDER BY 'Total Salary';

-- using CASE Statement

SELECT CAST(CASE WHEN hourly_wage IS NOT NULL THEN (hourly_wage * 40 * 52)
WHEN salary IS NOT NULL THEN salary
WHEN commission IS NOT NULL AND num_sales IS NOT NULL THEN (commission * num_sales)
END AS MONEY) AS 'Total Salary'
FROM dbo.wages
ORDER BY 'Total Salary';

Table Row Count

Many times we need to find out how many rows a particular table has?. Generally Select Count(*) From table query is used for same. It becomes very time consuming if a table is having bulk records.

Here is an another way to find the rows count in table very quickly no matter how many millions rows that table is having:

SELECT object_name(object_id) As TableName ,row_count
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('Token_Output');

Note: 'Token_Output' - Table Name

In SQL Server 2008 we have the following option

exec sp_rowcount 'table name'