Select rows with TIES option

Its very common when a Developer/DBA wanted to see sample of few records, commonly they use SELECT TOP N records from a table being ordered by a column,  the query look like as

SELECT TOP N [COLUMNNAME] FROM TABLENAME ORDER BY [COLUMNNAME]

As we know above statement is that if the table has multiple records having the same value as that of the selected list then all those records will not be selected. It will select only one record. But if we want to select all the rows, with same value as the one selected we have to include WITH TIES option in the query. So the query would be

SELECT TOP N [COLUMNNAME] WITH TIES FROM TABLENAME ORDER BY [COLUMNNAME]
For demonstration purpose, below are the step by step details example

Step -1 , Create a Sample Table as PRODUCTLIST

Create table ProductList(ID Int Identity(1,1),PName varchar(30),Price Decimal(10,2))

Step -2 , Insert some sample data

insert into ProductList (Pname,Price) values
(‘Bajaj CFL’,    210.00),
(‘TL’,    135.00),
(‘Table Fan’    ,450.00),
(‘Iron’    ,450.00),
(‘Cable’    ,250.00),o
(‘USB Disk’    ,450.00),
(‘Floppy’    ,120.00),
(‘CD-R’    ,280.00),
(‘CD-W’    ,450.00),
(‘USB Cable’    ,180.00)

 

For testing, SELECT TOP 3 * from PRODUCTLIST Order by Price DESC will return only 3 records as

But SELECT TOP 3 WITH TIES * from ProductList Order by Price DESC will return more than 3 records as

 If we examine about performance, initial one without TIES is more better compare to WITH TIES option, Let see an example here, suppose we have to find top 1 record having maximum price, let see the execution plan

Here we can see, WITH TIES option is performing very poorly compare to initial Select TOP query code, this happened because of ORDER BY clause, to resolve this here we have to create a proper index here for same to get the optimal performance.

SQL Server Error Messages – Msg 8101

Sometime SQL Server error msg 8101 occurred, when anyone is trying to insert a new record into a table that contains an identity column without specifying the columns in the INSERT statement and trying to assigning a value to the identity column instead of letting SQL Server assign the value. Error displays as

Server: Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table “Table_Name” can only be specified when a column list is used and IDENTITY_INSERT is ON.

 The solution for above error is , we should include SET IDENTITY_INSERT ON,

Example :

SET IDENTITY_INSERT Table_name ON
Go
Insert into Table_Name (Col1,Col2,Col3,Col4)
Select Col1,Col2,Col3,Col4 from Any_Table_Name
Go

SET IDENTITY_INSERT Table_name OFF
Go

DATA DICTIONARY

As a DBA we have to maintain our all databases Dictionaries, Here is a Script , from which we can generate a full view of Tables structure details.

SELECT a.Name  [Table Name],
b.name  [Column Name],
c.name  [Data Type],
b.length [Column Length],
b.isnullable [Allow Nulls],
CASE WHEN d.name is null  THEN 0 ELSE 1 END  [Primary Key],
CASE WHEN e.parent_object_id is null THEN 0 ELSE 1 END [ForiegnKey],
CASE WHEN e.parent_object_id is null
THEN ‘-‘ ELSE g.name END [Reference Table],
       CASE WHEN h.value is null THEN ‘-‘ ELSE h.value END [Description]
from sysobjects as a
join syscolumns as b on a.id = b.id
join systypes as c on b.xtype =  c.xtype
left join (SELECT so.id,sc.colid,sc.name FROM syscolumns sc
JOIN sysobjects so ON o.id = sc.id
JOIN sysindexkeys si ON so.id = si.id and sc.colid =  si.colid
WHERE si.indid = 1) d on a.id = d.id and b.colid = d.colid
Left join sys.foreign_key_columns as e on a.id = e.parent_object_id
          and b.colid = e.parent_column_id
left join sys.objects as g on e.referenced_object_id = g.object_id
left join sys.extended_properties as h on a.id = h.major_id
and b.colid = h.minor_id
where a.type = ‘U’
order by a.name

We can also get somehow details, not as above , using below

SELECT *  FROM INFORMATION_SCHEMA.COLUMNS

JJJ