Techno Freak Search

Search Results

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


Monday, May 12, 2014

What is difference between NVARCHAR and VARCHAR datatype in SQL Server ?

What is difference between NVARCHAR and VARCHAR datatype in SQL Server ?
 

NVARCHAR :
1. Unicode Variable Length character data type. It can store both non-Unicode and Unicode (i.e. Japanese, Korean etc)
2. It takes 2 bytes per Unicode/Non-Unicode
3. If Optional parameter value n is not specified in the variable declaration or column definition then it is considered as 1.
4. When dealing with Unicode string constants in SQL Server you must precede all Unicode strings with a capital letter N.

VARCHAR :

1. Non-Unicode Variable Length character
2. It takes 1 byte per character
3. If we know that data to be stored in the column or variable doesn’t have any Unicode characters

Which one to use ?

- An nvarchar column can store any Unicode data. A varchar column is restricted to an 8-bit codepage. Some people think that varchar should be used because it takes up less space. I believe this is not the correct answer. Codepage incompatibilities are a pain, and Unicode is the cure for codepage problems. With cheap disk and memory nowadays, there is really no reason to waste time mucking around with code pages anymore.

- All modern operating systems and development platforms use Unicode internally. By using nvarchar rather than varchar, you can avoid doing encoding conversions every time you read from or write to the database. Conversions take time, and are prone to errors. And recovery from conversion errors is a non-trivial problem.


Example :
CREATE TABLE #t_demo 
  ( 
     mailsub1 VARCHAR(30), 
     mailsub2 NVARCHAR(30) 
  ); 
 
INSERT INTO #t_demo 
            (mailsub1, 
             mailsub2) 
VALUES      ( N'كيف حالك؟', 
                        N'كيف حالك؟');  -- N’ Indicate It’s Unicode character , If we can’t specify that then It will display ???
 
SELECT * 
FROM   #t_demo; 
 
GO 
 
DROP TABLE #t_demo;

Reference Site : 
  • http://www.codeproject.com/Articles/579523/Character-String-Data-Types-and-Functions-SQL-Serv
  • http://support.microsoft.com/kb/239530
  • http://stackoverflow.com/questions/144283/what-is-the-difference-between-varchar-and-nvarchar

Tuesday, April 1, 2014

Use of dynamic SQL statement EXEC and sp_exectesql

Use of dynamic SQL statement EXEC and sp_exectesql EXEC : Using exec command you can create Dyanmic SQL statement but you need to pass extra quotes in parameter value. Check below example :
DECLARE @DynamicQuery varchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = 'FirstName,LastName, City'
SET @city = '''Surat''' -- Extra Quotes added in Parameter
SET @DynamicQuery = 'SELECT ' + @columnList + ' FROM Users WHERE City = ' + @city
EXEC (@DynamicQuery )
sp_exectesql : Using this command you can also build the Dynamic SQL query. advantage of using this approach is you didnot bother about extra quote
DECLARE @DynamicQuery varchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = 'FirstName,LastName, City'
SET @city = 'Surat' 
SET @DynamicQuery = 'SELECT '  + @columnList +  '  FROM Users WHERE City= @city '
EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city
Pass Multiple Parameter in sp_executesql Statement
-- Multiple Parameter in sp_executesql 

DECLARE @ParameterDefinition Nvarchar(1000);

SET @ParameterDefinition = N'@FirstName as Nvarchar(25), @LastName as Nvarchar(25)';

EXECUTE sp_executesql @SQLQUERY, @ParameterDefinition,@FirstName= @FirstName,@LastName= @LastName
Reference URL : http://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/

Followers

Ratings & Review