Use of prefix N for literal strings in Sql Server

When dealing with Unicode string constants in SQL Server you must precede all Unicode strings with a capital letter N. The "N" prefixes stands for National Language in the SQL-92 standard, and must be uppercase. If you do not prefix a Unicode string constant with N, SQL Server will convert it to the non-Unicode code page of the current database before it uses the string.

The Prefix N conveys to the Sql Server that following literal string is of Unicode type. While storing Unicode (i.e. Japanese, Korean, Chinese etc) Characters in NChar, NVarchar or NText columns or variables we need to prefix the literal strings by letter N.

See the example

Creating a table

Create Table MultiLanguage (Languageid int, LanguageName varchar(10), Language nvarchar(100))

Now I am inserting some records without using prefix N

Insert into MultiLanguage values (1, 'Hindi', 'मेरा नाम बागेश कुमार सिंह है') Insert into MultiLanguage values (2, 'Urdu', 'میرا نام باگےش کمار سنگھ ہے') Insert into MultiLanguage values (3, 'English', 'My Name is Bagesh kumar singh') Insert into MultiLanguage values (4, 'Gujrati', 'મારું નામ કુમાર સિંહ Bagesh છે') Insert into MultiLanguage values (5, 'Malayalam','എന്റെ പേര് കുമാർ സിംഗ് Bagesh ആണ്') Insert into MultiLanguage values (6, 'Marathi', 'माझे नाव कुमार सिंग Bagesh आहे') Insert into MultiLanguage values (7, 'punjabi', 'ਮੇਰਾ ਨਾਮ ਕੁਮਾਰ ਸਿੰਘ Bagesh ਹੈ') Insert into MultiLanguage values (8, 'Tamil', 'என் பெயர் குமார் சிங் Bagesh ஆகும்') Insert into MultiLanguage values (9, 'Telagu','నా పేరు కుమార్ సింగ్ Bagesh ఉంది')

See the output

  

I mean Unicode string show?? ? Here we are not getting the correct value.

Inserting the value using N prefix

Insert into MultiLanguage values (1, 'Hindi', N'मेरा नाम बागेश कुमार सिंह है') Insert into MultiLanguage values (2, 'Urdu', N'میرا نام باگےش کمار سنگھ ہے') Insert into MultiLanguage values (3, 'English', N'My Name is Bagesh kumar singh') Insert into MultiLanguage values (4, 'Gujrati', N'મારું નામ કુમાર સિંહ Bagesh છે') Insert into MultiLanguage values (5, 'Malayalam', N'എന്റെ പേര് കുമാർ സിംഗ് Bagesh ആണ്') Insert into MultiLanguage values (6, 'Marathi', N'माझे नाव कुमार सिंग Bagesh आहे') Insert into MultiLanguage values (7, 'punjabi', N'ਮੇਰਾ ਨਾਮ ਕੁਮਾਰ ਸਿੰਘ Bagesh ਹੈ') Insert into MultiLanguage values (8, 'Tamil', N'என் பெயர் குமார் சிங் Bagesh ஆகும்') Insert into MultiLanguage values (9, 'Telagu', N'నా పేరు కుమార్ సింగ్ Bagesh ఉంది')

See the output

  

Thanks!

SQL script to find the RAM used by database

Memory is one of the important areas to investigate/troubleshoot performance issues in SQL Server. We will out how much space is being taken by each database in the buffer cache (nothing but physical RAM).

Use below sql script to find the space details which is taken by databases in RAM:

DECLARE @total_buffer INT; SELECT @total_buffer = cntr_value FROM   sys.dm_os_performance_counters WHERE  Rtrim([object_name]) LIKE '%Buffer Manager'        AND counter_name = 'Total Pages'; ; WITH src      AS (SELECT database_id,                 db_buffer_pages = Count_big(*)          FROM   sys.dm_os_buffer_descriptors          GROUP  BY database_id) SELECT [db_name] = CASE [database_id]                      WHEN 32767 THEN 'Resource DB'                      ELSE Db_name([database_id])                    END,        db_buffer_pages,        db_buffer_MB = db_buffer_pages / 128,        db_buffer_percent = CONVERT(DECIMAL(6, 3), db_buffer_pages * 100.0 / @total_buffer) FROM   src ORDER  BY db_buffer_MB DESC;

Finding the missing indexes of a particular database in sql server

With the help of below Query we will find the missing indexes of a particular database.

SELECT '[' + Sch.NAME + '].[' + Tab.[name] + ']' AS TableName,        Ind.[name]                                AS IndexName,        Substring((SELECT ', ' + AC.NAME                   FROM   sys.[tables] AS T                          INNER JOIN sys.[indexes] I                                  ON T.[object_id] = I.[object_id]                          INNER JOIN sys.[index_columns] IC                                  ON I.[object_id] = IC.[object_id]                                     AND I.[index_id] = IC.[index_id]                          INNER JOIN sys.[all_columns] AC                                  ON T.[object_id] = AC.[object_id]                                     AND IC.[column_id] = AC.[column_id]                   WHERE  Ind.[object_id] = I.[object_id]                          AND Ind.index_id = I.index_id                          AND IC.is_included_column = 0                   ORDER  BY IC.key_ordinal                   FOR XML PATH('')), 2, 8000)    AS KeyCols,        Substring((SELECT ', ' + AC.NAME                   FROM   sys.[tables] AS T                          INNER JOIN sys.[indexes] I                                  ON T.[object_id] = I.[object_id]                          INNER JOIN sys.[index_columns] IC                                  ON I.[object_id] = IC.[object_id]                                     AND I.[index_id] = IC.[index_id]                          INNER JOIN sys.[all_columns] AC                                  ON T.[object_id] = AC.[object_id]                                     AND IC.[column_id] = AC.[column_id]                   WHERE  Ind.[object_id] = I.[object_id]                          AND Ind.index_id = I.index_id                          AND IC.is_included_column = 1                   ORDER  BY IC.key_ordinal                   FOR XML PATH('')), 2, 8000)    AS IncludeCols FROM   sys.[indexes] Ind        INNER JOIN sys.[tables] AS Tab                ON Tab.[object_id] = Ind.[object_id]        INNER JOIN sys.[schemas] AS Sch                ON Sch.[schema_id] = Tab.[schema_id] ORDER  BY TableName

See the output

Rebuild Index in sql server

Index maintenance is one of most important task for a DBA to perform the database performance & tuning. It is important to know to when an index requires be rebuilding or reorganizing, this consideration done on the basis of INDEX FRAGMENTATION PERCENTAGE.

If fragmentation percentage is less than 30% then REORGANIZE INDEX fragmentation is more than 30% then REBUILD INDEX. Below query will do the job, it’ll generate index rebuild or reorganize statement based on fragmentation percent level and this query will work fine in SQL Server 2005/2008 and higher versions. There will an option included in the output script and that is ONLINE=ON. This will get executed only in Enterprise, Developer and Evaluation Edition of SQL Server. 

SELECT Db_name(a.database_id)   [Db Name],        Object_name(a.object_id) Table_Name,        a.index_id,        b.NAME,        a.avg_fragmentation_in_percent,        record_count,        a.avg_fragment_size_in_pages,        page_count,        fragment_count,        b.fill_factor,        a.avg_page_space_used_in_percent,        a.record_count,        CASE          WHEN a.avg_fragmentation_in_percent > 30 THEN 'ALTER INDEX ' + b.NAME + ' on  '            + Db_name(a.database_id) + '.'            + Object_schema_name((a.object_id)) + '.'            + Object_name(a.object_id)            + ' REBUILD with (FILLFACTOR= 80 , SORT_IN_TEMPDB = ON, ONLINE = ON) '          WHEN a.avg_fragmentation_in_percent < 30 THEN 'ALTER INDEX ' + b.NAME + ' on  '           + Db_name(a.database_id) + '.'           + Object_schema_name((a.object_id)) + '.'           + Object_name(a.object_id) + ' REORGANIZE  '        END                      AS [SCRIPT] FROM   sys.Dm_db_index_physical_stats (Db_id(), NULL, NULL, NULL, 'DETAILED') AS a        JOIN sys.indexes AS b          ON a.object_id = b.object_id             AND a.index_id = b.index_id WHERE  a.database_id = Db_id()        AND a.avg_fragmentation_in_percent <> 0 ORDER  BY a.object_id