Techie's Diary

While working on multi language support we need to insert different language data in same field or different fields of a SQL server table. There are two basic rules one need to keep in mind while storing multi lingual or Unicode data: ·          First is column must be of Unicode data type (i.e., nchar, nvarchar, ntext). ·          And second is that the value must be prefixed with N while insertion. Below is the sample script with output to understand it better. USE MASTER GO -- Drop and Create TestMultiLingualDB IF db_id ( 'TestMultiLingualDB' ) IS NOT NULL       DROP DATABASE TestMultiLingualDB GO SET NOCOUNT ON GO CREATE DATABASE TestMultiLingualDB GO USE TestMultiLingualDB GO CREATE TABLE MultiLanguage ( Id INT , Var_Field VARCHAR ( 50 ), NVar_Field NVARCHAR ( 50 )) GO -- Insert Hindi Characters INSERT ...

While updating in SQL server via LINQ to SQL sometimes you get the exception: “System.NotSupportedException: SQL Server does not handle comparison of NText, Text, Xml, or Image data types” To fix this exception let’s see the possible solutions: Solution 1: If you are getting this exception with NText, Text, or Image data type fields, the reason is that NText, Text and Image types are deprecated and these fields must be replaced with the NVARCHAR(MAX), VARCHAR(MAX) and VARBINARY(MAX) types respectively. These types support string operators, including equality comparison. And you should be fine then. Solution 2: If you are getting this exception with XML field of your table, then reason is XML field can never be compared as a string. In order to fix this issue open the dbml file with xml editor and set the “ updatecheck” to “Never” as follows: < column   canbenull = "true"   dbtype = "Xml"   name = "PermissionsXml"   type...

While inserting or updating data into SQL table one may get exception like “String or binary data would be truncated. The statement has been terminated.” Reason: This exception occurs when length of your DB column data type is less as compared to value entered by user. For example: You have one Email field with data type as Nvarchar(20) in Employee table, and user try to enter testuseremail@testemail.com in this email field, if you see email entered by user is of 27 characters which exceeds DB column value and user will get this error. Solution: To solve this error change the size of your DB field, i.e. in above scenario we need to increase the size of Email field, change it to Nvarchar(50) and you’ll see error is gone.

Many developers ask how one can get the time or specific Hour and Minute from the DateTime field in SQL Server. Well answer is quite simple -- Pass the current date and get Hour and Minute. SELECT CONVERT ( NVARCHAR ( 5 ), GETDATE (), 114 ) AS "Hour:Minute" GO We can also use SQL TIME type if we need to get all three components(i.e. Hour, Minute and Second) of a date. -- Use TIME Type to get Hour Minute and Second SELECT CONVERT ( TIME , GETDATE ()) AS "Hour:Minute:Second" GO In SQL Server 2012 there is new Format Function , with the help of this we can also fetch out Hour and Minute from a DateTime field. -- Use FORMAT Function to get Hour and Minute. SELECT FORMAT ( GETDATE () , 'hh:mm' ) AS "Hour:Minute" GO

SQL Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis; it’s like recordset in the ASP. SQL Server Cursor is a row base operator but cursor is not recommended because of performance issue as it create different execution plan for each rows.  So one should look out for other i.e. try to implement logic with the help of while loop or CASE statement or JOIN, SELECT, GROUP etc. statements. But few times the cursor is recommended and useful, for example if you just need to update your DB only once through Query Analyzer and don’t really bothered about performance, then cursor can be handful; SQL Server Cursor Basics: Before jumping to example, let’s see basics of SQL Cursor statements. Statement Description DECLARE Declare variables used in the code block SET\SELECT Initialize the variables to a specific value DECLARE CURSOR Populate the cursor with values that will ...

If you need to look for a specific word or substring within a string, you can achieve it with the help of SQL Server CHARINDEX function CHARINDEX function is used to search for specific word or substring in overall string and returns its starting position of match. If no matching word found then it will return 0. CHARINDEX syntax: CHARINDEX ( expressionToFind, expressionToSearch [, start_location ] ) Example to find specific word: DECLARE @testStr VARCHAR ( 250 ) SET @testStr = 'This is a test string' SELECT CHARINDEX ( 'test' , @testStr ) Output: ----------- 11 (1 row(s) affected) Example to find word with specific start location defined: DECLARE @testStr VARCHAR ( 250 ) SET @testStr = 'This is a test string' SELECT CHARINDEX ( 'test' , @testStr , 5 ) Output: ----------- 11 (1 row(s) affected)

In SQL Server one may face the issue of unable to insert or update Unicode characters in column of NVARCHAR type. Reason: By default when you insert into NVARCHAR column, SQL Server try to save data as simple string. Solution: Use 'N' as prefix with NVARCHAR, The N' prefix indicates NCHAR or NVARCHAR data. It tells SQL Server to convert a string into NCHAR. Example: DECLARE @TestVar NVARCHAR ( 20 ) SET @TestVar = N'≤ 2' SELECT @TestVar

You can create a copy of your existing table with the help of T-SQL command. For this use “ SELECT INTO ” to extract all the rows from an existing table into the new table. Make sure the new table must not exist already. SELECT * INTO New_TableName FROM Old_TableName Example given below will copy the “Product” table to a new table called “Product_Copy” SELECT * INTO Product_Copy FROM Product You can also create the new table from a specific subset of columns in the original table. For that, you need to specify the column names to copy instead of “*”. Only specified columns are included in the new table. SELECT ProductId, ProductName, Description, Price INTO Product_Copy FROM Product In above example only ProductId, ProductName, Description and Price columns are copied to “Product_Copy” table.

Sometime while working with SQL Server we have a value which is in database but we are not aware which table or which column is containing that value. Try finding some specific strings in a database with a number of tables, each with many columns and tens of thousands of records is a difficult thing to do if one tries to look out manually. In this case we can obtain required information from the database by writing SQL query. Let’s write one stored procedure and then we can execute this stored procedure by providing search string. CREATE PROCEDURE Search_From_AllTables        @SearchValue NVARCHAR ( 500 ) -- Search string AS BEGIN        /**** Declare @searchQuery ****/        DECLARE @searchQuery NVARCHAR ( MAX ) = N''        /**** DROP [#TempResults] table if existing ****/        IF OBJECT_...