Cardinality Estimator Series I: Learning Selectivity factor of Select Query and Predicate

A predicate is nothing but an expression that you write in your where clause that evaluates to either TRUE, FALSE or UNKNOWN.

Now there are two category of predicate

• Filter Predicate
• Join Predicate

Filter Predicate, the one which is used to search your result set generally the condition you write in ‘WHERE or HAVING’ clause.

Join Predicate, The equality and non-equality operator that are used while joining table in your DML statements are considered as join predicate.

Example

select e.emp_name,d.dept_name from dept d inner join emp e

on d.dept_id = e.dept_id  --Join Predicate

where d.dept_name = 'hr'  --Filter Predicate

Selectivity, It is a measure of how selective your predicate is? The formula to find the selectivity is

Selectivity = Rows qualifies a predicate / Total no of rows present in table.

Selectivity is always lays in the range of 0-1 ,

0.0     is considered as high selectivity

1.0     is considered as low selectivity

For example

select * from Sales.SalesOrderHeader

where SalesOrderID > 74000

so the selectivity of SalesIderId > 7400 will be

=Total record qualify SalesOrderId predicate / Total record present in SalesOrderHeader table

select count(*) as TotalRecord from sales.SalesOrderHeader

TotalRecord

-----------

31465

select count(*) as TotalQualifyingRows from Sales.SalesOrderHeader

where SalesOrderID > 74000

TotalQualifyingRows

-------------------

1123

So the Selectivity will be = 1123/31465.0

               

select 1123/31465.0 as Selectivity

Selectivity

---------------------------------------

0.0356904

Be in this world with your code; make sure your code speak loud!

Post Reference: Vikram Aristocratic Elfin Share

Database still act as though they are running in earlier version even though it has been upgraded to latest version; Setting the Compatibility Mode in SQL Server

Whenever you are attaching or restoring an earlier version of database to the latest version using backup and restore Or using detach and attach method, the compatibility mode of SQL Server does not change automatically.

That’s the reason you are not able to take the advantage of latest feature of latest SQL Server version, and the need arise to change the compatibility level of your database.

Changing the compatibility Mode

Once you restore or attach database, the first thing you need to see the compatibility level for the attached database.

To change the compatibility mode of your database you need

1)       Go to database Node

2)       Go to option

3)       Check the compatibility Property and set the appropriate for your database

The number which are associated with each version of SQL Server are listed below

60 : SQL Server 6.0

65: SQL Server 6.5

70: SQL Server 7.0

80: SQL Server 2000

90: SQL Server 2005

100: SQL Server 2008

110: SQL Server 2012

120: SQL Server 2014

To see/Check the compatibility level of your database

select name,compatibility_level from sys.databases

name                  compatibility_level

--------------------- -------------------

master                120

tempdb                120

model                 120

msdb                  120

TestDB                120

AdventureWorksDW2012  110

AdventureWorks2012    110

AdventureWorks2008R2  100

(8 row(s) affected)

Now here you can see the AdventureWorks2008R2  is on 100 (SQL Server 2008) compatibility level, so if you want to use the feature of SQL Server 2014 you need to set the compatibility level to 120

Changing the Compatibility level of database

use master

go

Alter database AdventureWorks2008R2

set COMPATIBILITY_LEVEL = 120

Command(s) completed successfully.

Now lets see the updated compatibility level of database by querying the sys.databases

select name,compatibility_level from sys.databases

name                  compatibility_level

--------------------- -------------------

master                120

tempdb                120

model                 120

msdb                  120

TestDB                120

AdventureWorksDW2012  110

AdventureWorks2012    110

AdventureWorks2008R2  120

(8 row(s) affected)

So here we can see, the compatibility level of database has changed to 120.

Live with code, that the ultimate source of amusement and pleasure you have on this earth!

 

Post Reference: Vikram Aristocratic Elfin Share

Way to Sort your table without using ORDER BY clause

There are few way to sort your table without using Order By clause, Lets see the same. For our practical we are creating the following table.

 create table TAB_TestSortingWithoutOrderBy

(col1 varchar(5) primary key,

col2 varchar(5) not null unique);

Now let’s insert few records in it

insert into TAB_TestSortingWithoutOrderBy values('S','AA')

insert into TAB_TestSortingWithoutOrderBy values('D','EE')

insert into TAB_TestSortingWithoutOrderBy values('A','BB')

insert into TAB_TestSortingWithoutOrderBy values('C','XX')

select * from TAB_TestSortingWithoutOrderBy

col1  col2

----- -----

S     AA

A     BB

D     EE

C     XX

(4 row(s) affected)

First Way

Now I am writing a simple query with where predicate on col1 which is checking the condition col1 < ‘zzz’, since we have clustered index set on col1, the clustered index will comes to picture. Now since clustered index comes into picture which stored records in logical sorting order the result will be ordered.

select * from TAB_TestSortingWithoutOrderBy

where col1 < 'zzz'

col1  col2

----- -----

A     BB

C     XX

D     EE

S     AA

Second Way

We can also force our clustered index to come into picture while scanning record. Lets see how we can do,

Here in this query we are finding the index detail i.e the index id

select * from sys.indexes where object_id = OBJECT_ID('TAB_TestSortingWithoutOrderBy')

name                             index_id

--------------------------------------------

PK__TAB_Test__357D0D3EE8D60CB3   1

UQ__TAB_Test__357D0D3C32B8019D   2

Now we can use this index to sort our table data

SELECT * FROM TAB_TestSortingWithoutOrderBy WITH(INDEX(PK__TAB_Test__357D0D3EE8D60CB3));

OR

SELECT * FROM TAB_TestSortingWithoutOrderBy WITH(INDEX(1));

col1  col2

----- -----

A     BB

C     XX

D     EE

S     AA

Conclusion: we can bring index into picture to sort our result.

Take Complete risk with Production; Life with CODE is now happening…

 

Post Reference: Vikram Aristocratic Elfin Share