Saturday 16 February 2013

Fetching larger String in SQL Server :its Based on ASCII codes

SQL server sorts string column on the basis of their ASCII codes for example I have a table BOOKS



if i want to get only those books which are greater than c++, it will return only those records whose starting alphabet is greater in ascii than c like



similarly 











Sunday 10 February 2013

Which one is Quicker , Exists or Join ?

EXISTS : is used to return a Boolean value
JOIN : returns a whole other table

EXISTS is only used to test if a sub query returns results, and short circuits as soon as it does. JOIN is used to extend a result set by combining it with additional fields from another table to which there is a relation. SO EXISTS  is fast and  JOIN  is slow comparatively. Let see it with an example

SET STATISTICS IO ON
SET STATISTICS TIME ON


SELECT * FROM BOOKS WHERE EXISTS
(
    SELECT NewSeqCol FROM MYTABLE
)



SELECT * FROM BOOKS
JOIN MYTABLE ON BOOKS.id = MYTABLE.NewSeqCol



above are the result sets returned from both of the queries , as you can see from the elapsed time durations difference is clear.



How to convert DB timestamp to datetime / date in SQL Server ?

As far as i'm aware of this , its not possible to convert timestamp value to datetime or date in SQL Server, timestamp  is just binary representation of values which are generated when any value is inserted in database , these values are unique in whole database.