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 

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.

How to fetch a record with multiple status' ?

i ve got a question from one of my colleagues and it made me think about it. Question was how to get a record which have more than one status rows in status columns

here is example data

SHIPMENT_CODE            IS_PROCESSED
23456                                              0
23457                                              1
23456                                              1
23458                                              0
23459                                              1
23457                                              0

i have to fetch only those rows which have both status' e.g.

SHIPMENT_CODE           
23456                                           
23457

i know its not the optimal solution but something is better than nothing :D
if anyone got a good solution please share it over here, mine is below.

SELECT * 
FROM SHIPMENTS 
WHERE SHIPMENT_CODE IN (
                  SELECT SHIPMENT_CODE           
                  FROM SHIPMENTS
                  WHERE IS_PROCESSED= 1    )
AND NAME IN (
                 SELECT SHIPMENT_CODE           
                 FROM SHIPMENTS
                 WHERE IS_PROCESSED= 0    )                                          

How to find IDENTITY column of a table?

At times when we are working on a large database we know most of the tables and their columns , but in case you don't know or you don't have designed database then you may need to find identity column of a particular table like i needed to find today.
Let see how we can do it
create a test table

-- Create a table
CREATE TABLE [dbo].[BOOKS](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [NAME] [varchar](40) NULL,
)
GO
-- Inserting values into BOOKS table.
INSERT INTO BOOKS (NAME) VALUES ('C++')
INSERT INTO BOOKS (NAME) VALUES ('.NET')
INSERT INTO BOOKS (NAME) VALUES ('PROGRAMMING LANGUAGES')
INSERT INTO BOOKS (NAME) VALUES ('SQL')




now let see its IDENTITY column

SELECT O.NAME, C.NAME
FROM SYS.OBJECTS O
INNER JOIN SYS.COLUMNS C ON O.OBJECT_ID = C.OBJECT_ID
WHERE C.IS_IDENTITY = 1
AND O.NAME LIKE 'BOOKS'
 


which is our required.
there is another way to find it using

sp_MShelpcolumns <TableName>
like well use it this way 

sp_MShelpcolumns BOOKS
it will return multiple columns of the table but the colum having col_identity = 1 is your required one.
 here is its result 

 


i found out required column this way you are welcome to try it ;)

What is IDENTITY_INSERT in SQL Server?

its a property in SQL server by turning it ON It allows a user to insert explicit values in an identity column of a table, SQL server considers the new inserted value as identity.The setting of SET IDENTITY_INSERT is set at execute or run time and not at parse time.

let see an example how it works

create gap to see deletion of an identity value



an attempt to insert it again



turn on IDENTITY_INSERT




Note :At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

here is the complete code

-- Create BOOKS table.
CREATE TABLE BOOKS (id int IDENTITY PRIMARY KEY, NAME varchar(40))
GO
-- Inserting values into BOOKS table.
INSERT INTO BOOKS (NAME) VALUES ('C++')
INSERT INTO BOOKS (NAME) VALUES ('.NET')
INSERT INTO BOOKS (NAME) VALUES ('JAVA')
INSERT INTO BOOKS (NAME) VALUES ('SQL')
GO

SELECT * FROM BOOKS
GO

-- Create a gap in the identity values.
DELETE BOOKS
WHERE NAME = 'JAVA'
GO

SELECT *
FROM BOOKS
GO

-- Attempt to insert an explicit ID value of 3;
-- should return a warning.
INSERT INTO BOOKS (id, NAME) VALUES(3, 'PROGRAMMING LANGUAGES')
GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT BOOKS ON
GO

-- Attempt to insert an explicit ID value of 3
INSERT INTO BOOKS (id, NAME) VALUES(3, 'PROGRAMMING LANGUAGES')
GO

SELECT *
FROM BOOKS
GO
-- Drop BOOKS table.
DROP TABLE BOOKS