Techno Freak Search

Search Results

Wednesday, March 18, 2015

NOT IN CONDITION RETURNS EMTPTY RESULT SET

NOT IN query returns the empty result means NULL value exist in the list of the SubQuery.You need explicitly exclude them using IS NOT NULL Syntax.

SELECT *
FROM PurchaseOrder 
WHERE PurchaseOrderID NOT IN ( SELECT  PurchaseOrderID FROM PurchaseOrderDetail
WHERE PurchaseOrderID IS NOT NULL )

Reason behind writing IS NOT NULL condition in Subquery is that It will evaluate the expression based truth tables.In NULL condition what will happen the expression is Unknown state. It will ignore other records also.

Wednesday, March 11, 2015

NULLIF Function

Definition : The NULLIF function takes two arguments. If the two arguments are equal, then NULL is returned. Otherwise, the first argument is returned. The syntax for NULLIF is as follows: NULLIF ("expression 1", "expressions 2")
Real world example of NULLIF : If you want to compare column with empty string or null condition in SQL Server then generally we are write two condition with OR operator.
Sample : SELECT  COLUMN1,COLUMN2 
  FROM TABLE1 
 WHERE ( COLUMN1 IS NULL OR COLUMN1 = ‘’)

Instead of writing two condition you can use NULLIF function and write the Single condition.

Sample : SELECT  COLUMN1,COLUMN2 
  FROM TABLE1 
                 WHERE NULLIF (COLUMN1,’’) IS NULL

Thursday, May 29, 2014

Discontinued TEXTand NTEXT data type in the Next Version Of SQL Server

Advantage to use navarchar(max) over text data type :

  1.       Use the string functions in navarchar(max) data type.
  2. 2    The default setting for NVARCHAR(MAX) is to store its text value in the table structure, unless the text is over 8,000 bytes at which point it behaves like an NTEXT and stores the text value in the LOB , and stores a pointer to the text in the table.
  3. 3     SQL Server 2014 ntext and text will be Discontinued features.
  4.        You can easily convert text to navarchar(max) column.
  5.        For performance reasons and future support it is advisable to change it to navarchar(max).




Tuesday, May 27, 2014

Migrate FROM SQL Server 2008 to 2012 -Understand the terms Discontinued features and Deprecated features properly





Once a feature appears on the list, it moves from bottom to the top, i.e. it is first marked as “Deprecated” and then “Discontinued”. We know of “Breaking change” comes later on in the product life cycle.
What this means is that if you want to know what features would not work with SQL Server 2012 (and you are currently using SQL Server 2008 R2), you need to refer the list of breaking changes and discontinued features in SQL Server 2012.

LOBs Vs BLOBs Vs CLOBs


- LOB (Large OBject) refers to any of these types, It's either BLOB or CLOCB .Which have a special way of being stored internally, on special pages called 'LOB Pages'.

- BLOB means Binary Large OBject and only refers to image.

- CLOB means Character Large OBject and refers to text or ntext.

A column of LOB data can store up to 2GB worth of data, so that could be lots of pages. 

P.S : Next version of SQL Server 2012 and 2014 , Microsoft will be discontinued the data type ntext and text. This both the data types replacing with varchar(max) and nvarchar(max)  which will support the existing data type and you can convert existing records also.

Thursday, May 22, 2014

Convert Empty String Value to Data Time Format


If we can convert Empty string value to date time or when we add empty string in Date Time column by default date time value will be added in the column.

Default Value IS : 1900-01-01 00:00:00.000


Followers

Ratings & Review