Unicode Fundamentals in SQL server 2005

Unicode maps code points to characters, but does not actually specify how the data will be represented in memory, in a database, or on a Web page.

What is Unicode and what is UTF-8??

Unicode is clear from above statement now come to UTF-8.Its a encoding scheme(What is Encoding ?click here). In short you can say that There is a Hex value for each and every character which is defined in Character Set of Unicode.Like that many other Encoding scheme are there UTF-8,UTF-16,UTF-32.

The thing is All the character will have different HEX value for each Encoding.

Information that is stored in Unicode in Microsoft SQLServer 2000 and Microsoft SQLServer 2005 uses the UCS-2 encoding, which stores every character as two bytes, regardless of which character is used. Therefore, the Latin letter "A" is treated the same way as the Cyrillic letter Sha (), the Hebrew letter Lamed (), the Tamil letter Rra (), or the Japanese Hiragana letter E (). Each one has a unique code point (for these letters, the code points are U+0041, U+0248, U+05DC, U+0BB1, and U+3048, respectively, where each four-digit hexadecimal number represents the two bytes that UCS-2 uses).

Now as you know UCS-2 is a old Encoding nowa days its outdated.It strictly use a 2 byte encoding no matter you are storing ASCII value or non ASCII value.So It means 2 power of 16 that means it can store up to 65536 Different characters.

Now if we will have SQL SERVER 2005 Centric views then Storage part in a nutshell work like this.

-UCS-2 is obsolete: It can no longer represent every Unicode character.

-For Nonunicode data types like char,varchar,text sql server will take 1 byte to store data for each character 

-For Unicode data types like nchar,nvarchar sql server will take take 2 bytes to store data for each character as It is using UTF-16 internally to store data(MySQL is using Internally UTF-8 to store data so it again Depend on collation and character set which you are using for DB,Table,Column)

-UTF-8 in a few XML-centric places, without you doing anything weird.

-So you can say that its not using UTF-8 Or MSSQL does not support utf-8 Encoding.

IF you are writing .NET framework code,Java code or C# code all the things are stored into UTF-16 format internally and Microsoft Products works good will UTF-16 Encoding.ASP.NET used in C# is again a UTF-8 encoding  This all things work in background you don't need to bother.

Now lets compare sizes of these three encodings:

In non-Unicode data types (TEXT, VAR CHAR), only a single byte is used per character, and only characters of the collation's code page can be stored 

Now for if particular collation you want to move on you can use the collation and keep it data type as VAR CHAR and it will store.Internal conversions will be made from code page and will be stored and it can work up to limited characters which are defined in code page.

Now first thing what is Code page?

In sql server A code page also known as a character set, is a set of 256 uppercase and lowercase letters, numbers, and symbols. The printable characters of the first 128 values are the same for all character set choices. The last 128 characters, sometimes referred to as extended characters, differ from set to set. The code page for SQL Server determines both the types of characters that SQL Server recognizes in your database, and the results that SQL Server returns for queries that involve character comparisons.Total 1252 Pages are there to work with

If you want to see the page number for particular collation then you can use the Inbuilt following function:

(If collation is not having a code page it will return 0)

SELECT COLLATIONPROPERTY('SQL_Latvian_CP1257_CS_AS', 'CodePage')

Will give result 1257.

This Code page will have total ASCII representations as well as Extended Chars for that particular Collation

Now Working with multiple Language a same time

Its impossible that we can find all the characters of multiple languages in single code page.The same time you need to work with nvarchar like Unicode data types.But there Each Character you are going to store whether a ASCII value or Non ASCII value it will always accommodate 2 Bytes.So we can say it is space consuming.At a same time we no need to do any internal conversions like in code pages sql server was doing so again a good thing that it will be same for what ever client want to see where ever will be one single character for each encoded number.The Unicode specification defines a single encoding scheme for most characters widely used in businesses around the world. All computers consistently translate the bit patterns in Unicode data into characters using the single Unicode specification. This ensures that the same bit pattern is always converted to the same character on all computers. Data can be freely transferred from one database or computer to another without concern that the receiving system will translate the bit patterns into characters incorrectly.

Unicode data is stored using the nchar, nvarchar, and ntext data types in SQL Server. Use these data types for columns that store characters from more than one character set. Use nvarchar when a column's entries vary in the number of Unicode characters (up to 4,000) they contain. Use nchar when every entry for a column has the same fixed length (up to 4,000 Unicode characters). Use ntext when any entry for a column is longer than 4,000 Unicode characters.

Keep in mind this things While select a Datatype as Unicode data type

Note  The n prefix for these data types comes from the SQL-92 standard for National (Unicode) data types.

Use of nchar, nvarchar, and ntext is the same as char, varchar, and text, respectively, except that:

Unicode supports a wider range of characters.

More space is needed to store Unicode characters.

The maximum size of nchar and nvarchar columns is 4,000 characters, not 8,000 characters like char and varchar.

Unicode constants are specified with a leading N: N'A Unicode string'.

All Unicode data uses the same Unicode code page. Collations do not control the code page used for Unicode columns, only attributes such as comparison rules and case sensitivity.

Thanks and Regards 

Kamesh

UTF-8 and UTF-16???

UTF-8 and UTF-16 are 2 different ways for representing your unicode characters. To briefly touch on the technical side, in UTF-8, anywhere between one to four 8-bit units are used to represent a particular unicode character. In UTF-16, 16 bit units are used to represent (in either big-endian or little-endian format) a particular unicode character.  [There is also UTF-32 format which is mostly used in Unix only].

As such, UTF-8 is the most popular and standard format for Web Pages. So, if you are designing web pages, you can opt for conversion to UTF-8 format always (which is the default setting in azhagi's 'unicode converter'). For other purposes, you may choose UTF-8 or UTF-16 according to your specifications and requirements.

Implementation of Multiple languages in MYSQL

How to enable Multiple language support in mysql??

FIrst you need to go through What is unicode ??how it works ?? for better understandingThen you can go through this Article Which is totally practical

Steps:
1.Get all the character sets of MySQL
mysql>SHOW CHARACTER SET;

2.See all the collations for particular Char sets
mysql>SHOW COLLATION LIKE '%latin%';

Will give you result for latin Charsets
3.We will be going to set utf8 CHARSET;

First Highest priority will be given to query which COLLATIONS and Charset it wants then will be columns and if not mention will be table if not mention will be database
So If you are going to fire this query

  mysql>SELECT c1 COLLATE utf8_bin FROM aa
 
  Will override all the collations you mention and will use utf8_bin.now if not mention then table collation would be used and so on....

4.Remember one thing to meet this collation requirment one must set the client side collation and charset should meet up with server side mentioned parameters means both the collations and charset must be matching

SET character_set_client = 'utf8';
SET character_set_connection = 'utf8';
SET character_set_database = 'utf8';
SET character_set_results = 'utf8';
SET character_set_server = 'utf8';  

This will keep character_set on server side of utf8 and will procedd work on that basis you can set this into my.cni file or you can fire this stmts on Starting of Session will work
now for all your char set is utf8 and now you need to set collation

SET collation_connection='utf8_bin'
SET collation_database ='utf8_bin'
SET collation_server='utf8_bin'


Which would set the collation of the server.

7.Now your database is ready to deal with multi languages.Now create one table

CREATE TABLE t1
(
c1 varchar(20);
)

INSERT INTO aa values ('Њ');

Will get data into that now fire select query you will get this as a dataIf you are getting question mark or junk value then its not configured yet.


8.Now change table or column level collation whichever is high in priority and check it will display the junk data so now your table is ready to accept multilanguage values

Now enable hindi language in OS

Step A: To enable Indic Languages in Windows XP
Go to Start-> Control Panel > Regional & Language Options >Click
on Languages Tab (the following screen will appear)
Tick the Check box to Install files for complex scripts... and click OK.

2. (Following message would appear) Click OK

3. You will be required to place the Windows XP CD in the CD drive to enable
Indic languages including Hindi
Reboot the System

Thnks and Regards 

Kamesh

(First ever blog 100% written and ingenuited by me)

What is UTF-8??
-UCS[1] Transformation Format — 8-bit.Now here UCS stands for Universal Character SET.As this is widely used transformation unit over the WEB and is very prominently used.Actually ASCII stores values from 0 to 128 and that 128 incorporates all the Alphabates like smaller case a to z and UPPER CASE A TO Z.after this its also contains some non printable chars like carriage return new lines(/n) and symbols which can represent one proper sentence with punctuation you can say it consists all symbols on key board.Now the question is how ascci is stored in a computer as you are aware that all the values are stored in bits(0,1) on your Disk.Now ASCII List with your characters:


After this how it is stored on disk second major thing

Up to the mark very much it is cleared how Coputer is dealing with ascii and how it is stored.Over all your things on your computer is goin to breathe in 0 and 1

Now ASCII was only content English chars more or less some punc symbols Upper the mentioned byte can store upto 255 values means different combinations out of that 128 wer allooted Standard for ASCII now the time was what about another 128 chars.So big players in IT started use as per there require symbols and for different Languagaes.but it was not standard over a world.Now one machine will mean some other character for extended ascii and some will mean something else so it will create a mess up there UTF 8 came into picture.

First to under stand How UTF 8 process you need to be aware of some  terms like COLLATIONS,ENCODINGS,CHARACTER SET what are the for that follow the below link


What is collationa n charsets??? Click on link


Presumming you are aware of above mention terms Now i think we can proceed as i tell you UTF-8 can store upto 4 bytes per char
AS you can see below cited table first range is like ASCII and everything is unchanged over there.So the first 128 characters (US-ASCII) need one byte. The next 1,920 characters need two bytes to encode.This includes Latin letters with diacritics and characters from the Greek, Cyrillic, Coptic, Armenian, Hebrew, Arabic, Syriac and Tana alphabets. Three bytes are needed for the rest of the Basic Multilingual Plane (which contains virtually all characters in common use). Four bytes are needed for characters in the other planes of Unicode, which include less common CJK characters and various historic scripts.


How unicode is store into disk??

To understand the way UTF-8 works, we have to examine the binary representation of each byte. If the first bit (the high-order bit) is zero, then it’s a single-byte character, and we can directly map its remaining bits to the Unicode characters 0 – 127. If the first bit is a one, then this byte is a member of a multi-byte character (either the first character or some followup of it).

For a multi-byte character (any character whose Unicode number is 128 or above), we need to know how many bytes will make up this character. This is stored in the leading bits of the first byte in the character. We can identify how many total bytes will make up this character by counting the number of leading 1’s before we encounter the first 0. Thus, for the first byte in a multi-byte character, 110xxxxx represents a two-byte character, 1110xxxx represents a three-byte character, and so on.


Lets go with an Example



Above cited char in image we want to store as Its Decimal value with utf8 is 362 it require 2 bytes to store the value
now binary value would be 101101010 But its oing to stoer the binary value of the HExadeimal form so HExaDecimal is 16A for 362 and Binary would be 000101101010
But UTF8 have its own encoding standard and it will be converted to abide the above rules of leading ones and all that after that we will get one hex value that is C5AA and that is stored on a disk


How it will be stored
First bit would be 1 now as its a multibyte character as 1 more byte will make a leading this character so second value would be 1
now we got binary is 11XXXXXX:XXXXXXXX

So we encountered Zero now so third would be 110XXXXX:XXXXXXXX (1110xxxx represents a three-byte character, and so on.)

Now With this we got one sequence and now binary would be attached atlast of this

So now the binary for utf8 form for cited image would be 11000101:10101010

What is Encoded Byte in term in UTF8?

So encoded byte is the hexadecimal value for that particular Symbol or character See the mentioned image

Maintaining hierarchical data in Flat database tables

Introduction hierarchical 

Most users at one time or another have dealt with hierarchical data in a SQL database and no doubt learned that the management of hierarchical data is not what a relational database is intended for. The tables of a relational database are not hierarchical (like XML), but are simply a flat list. Hierarchical data has a parent-child relationship that is not naturally represented in a relational database table.

For our purposes, hierarchical data is a collection of data where each item has a single parent and zero or more children (with the exception of the root item, which has no parent). Hierarchical data can be found in a variety of database applications, including forum and mailing list threads, business organization charts, content management categories, and product categories. For our purposes we will use the following product category hierarchy from an fictional electronics store:

These categories form a hierarchy in much the same way as the other examples cited above. In this article we will examine two models for dealing with hierarchical data in MySQL, starting with the traditional adjacency list model.

The Adjacency List Model

Typically the example categories shown above will be stored in a table like the following (I'm including full CREATE and INSERT statements so you can follow along):

CREATE TABLE category(
        category_id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(20) NOT NULL,
        parent INT DEFAULT NULL
);

INSERT INTO category VALUES(1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(3,'TUBE',2),
        (4,'LCD',2),(5,'PLASMA',2),(6,'PORTABLE ELECTRONICS',1),(7,'MP3 PLAYERS',6),(8,'FLASH',7),
        (9,'CD PLAYERS',6),(10,'2 WAY RADIOS',6);

SELECT * FROM category ORDER BY category_id;
+-------------+----------------------+--------+
| category_id | name                 | parent |
+-------------+----------------------+--------+
|           1 | ELECTRONICS          |   NULL |
|           2 | TELEVISIONS          |      1 |
|           3 | TUBE                 |      2 |
|           4 | LCD                  |      2 |
|           5 | PLASMA               |      2 |
|           6 | PORTABLE ELECTRONICS |      1 |
|           7 | MP3 PLAYERS          |      6 |
|           8 | FLASH                |      7 |
|           9 | CD PLAYERS           |      6 |
|          10 | 2 WAY RADIOS         |      6 |
+-------------+----------------------+--------+
10 rows in set (0.00 sec)

In the adjacency list model, each item in the table contains a pointer to its parent. The topmost element, in this caseelectronics, has a NULL value for its parent. The adjacency list model has the advantage of being quite simple, it is easy to see thatFLASH is a child ofmp3 players, which is a child of portable electronics, which is a child of electronics. While the adjacency list model can be dealt with fairly easily in client-side code, working with the model can be more problematic in pure SQL.

Retrieving a Full Tree

The first common task when dealing with hierarchical data is the display of the entire tree, usually with some form of indentation. The most common way of doing this is in pure SQL is through the use of a self-join:

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';

+-------------+----------------------+--------------+-------+
| lev1        | lev2                 | lev3         | lev4  |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS          | TUBE         | NULL  |
| ELECTRONICS | TELEVISIONS          | LCD          | NULL  |
| ELECTRONICS | TELEVISIONS          | PLASMA       | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS  | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS   | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL  |
+-------------+----------------------+--------------+-------+
6 rows in set (0.00 sec)

Finding all the Leaf Nodes

We can find all the leaf nodes in our tree (those with no children) by using a LEFT JOIN query:

SELECT t1.name FROM
category AS t1 LEFT JOIN category as t2
ON t1.category_id = t2.parent
WHERE t2.category_id IS NULL;

+--------------+
| name         |
+--------------+
| TUBE         |
| LCD          |
| PLASMA       |
| FLASH        |
| CD PLAYERS   |
| 2 WAY RADIOS |
+--------------+

Retrieving a Single Path

The self-join also allows us to see the full path through our hierarchies:

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS' AND t4.name = 'FLASH';

+-------------+----------------------+-------------+-------+
| lev1        | lev2                 | lev3        | lev4  |
+-------------+----------------------+-------------+-------+
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |
+-------------+----------------------+-------------+-------+
1 row in set (0.01 sec)

The main limitation of such an approach is that you need one self-join for every level in the hierarchy, and performance will naturally degrade with each level added as the joining grows in complexity.

Limitations of the Adjacency List Model

Working with the adjacency list model in pure SQL can be difficult at best. Before being able to see the full path of a category we have to know the level at which it resides. In addition, special care must be taken when deleting nodes because of the potential for orphaning an entire sub-tree in the process (delete the portable electronics category and all of its children are orphaned). Some of these limitations can be addressed through the use of client-side code or stored procedures. With a procedural language we can start at the bottom of the tree and iterate upwards to return the full tree or a single path. We can also use procedural programming to delete nodes without orphaning entire sub-trees by promoting one child element and re-ordering the remaining children to point to the new parent.

Source : Net-search i came across this article

New to Unicode ??? Go through this

In order for a computer to be able to store text and numbers that humans can understand, there needs to be a code that transforms characters into numbers. The Unicode standard defines such a code by using character encoding.

Character Encoding

All character encoding does is assign a number to every character that can be used. I could if I really wanted to make a character encoding right now. For example, I could say "A" becomes the number 13, "a" = 14, "1" = 33, "#" = 123 and so on. My character encoding scheme might work brilliantly on my computer but when I come to send some text to another computer I will have problems. It won't know what I'm talking about unless it understands my encoding scheme too.

This is where industry wide standards come in. If the whole computer industry uses the same character encoding scheme, all the computers can display the same characters.

What is Unicode?

ASCII which stands for American Standard Code for Information Interchange became the first widespread encoding scheme. However, it is limited to only 128 character definitions. Which is fine for the most common English characters, numbers and punctuation but is a bit limiting for the rest of the world. They naturally wanted to be able to encode their characters too. And, for a little while depending on where you were, there might be a different character being displayed for the same ASCII code. In the end, the other parts of the world began creating their own encoding schemes and things started to get a little bit confusing. Not only were the coding schemes of different lengths, programs needed to figure out which encoding scheme they were meant to be using.

It became apparent that a new character encoding scheme was needed and the Unicode standard was created. The objective of Unicode is to unify all the different encoding schemes so that the confusion between computers can be limited as much as possible. These days the Unicode standard defines values for over 100,000 characters and can be seen at the Unicode Consortium. It has several character encoding forms, UTF standing for Unicode Transformation Unit:

• UTF-8: only uses one byte (8 bits) to encode English characters. It can use a sequence of bytes to encode the other characters. UTF-8 is widely used in email systems and on the Internet.
• UTF-16: uses two bytes (16 bits) to encode the most commonly used characters. If needed, the additional characters can be represented by a pair of 16-bit numbers.
• UTF-32: uses four bytes (32 bits) to encode the characters. It became apparent that as the Unicode standard grew a 16-bit number is too small to represent all the characters. UTF-32 is capable of representing every Unicode character as one number.

Code Points

A code point is the value that a character is given in the Unicode standard. The values according to Unicode are written as hexadecimal numbers and have a prefix of "U+". For example to encode the characters I looked at earlier, "A" is U+0041, "a" is U+0061, "1" is U+0031, "#" is U+0023. These code points are split into 17 different sections called planes. Each plane holds 65,536 code points. The first plane, which holds the most commonly used characters, is known as the basic multilingual plane.

Code Units

The encoding schemes are made up of code units. They are way to provide an index for where a character is positioned on a plane. For instance, with UTF-16 each 16-bit number is a code unit. The code units can be transformed into code points. For example, the flat note symbol "♭" has a code point of U+1D160 and it lives on the second plane of the Unicode standard. It would be encoded using the combination of the following two 16-bit code units: U+D834 and U+DD60 .

For the basic multilingual plane the values of the code points and code units are identical. This allows a shortcut for UTF-16 that saves a lot of storage space. It only needs to use one 16-bit number to represent those characters.

Concept how index works in SQL SERVER 2005

Concepts of Page and indexes
In SQL Server, the page size is 8 KB.Tables and indexes are stored as a collection of 8-KB pages This means SQL Server databases have 128 pages per megabyte.Each page begins with a 96-byte header that is used to store system information about the page. This information includes the page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page.Data rows are put on the page serially, starting immediately after the header. A row offset table starts at the end of the page, and each row offset table contains one entry for each row on the page. Each entry records how far the first byte of the row is from the start of the page. The entries in the row offset table are in reverse sequence from the sequence of the rows on the page.





Row cannot span over the pages.It can only cover 8060 bytes This is done whenever an insert or update operation increases the total size of the row beyond the 8060 byte limit. When a column is moved to a page in the ROW_OVERFLOW_DATA allocation unit, a 24-byte pointer on the original page in the IN_ROW_DATA allocation unit is maintained. If a subsequent operation reduces the row size, SQL Server dynamically moves the columns back to the original data page

Extents are the basic unit in which space is managed. An extent is eight physically contiguous pages, or 64 KB. This means SQL Server databases have 16 extents per megabyte.


1 Extent = 64 KB = 8 Page

Per mb 16 Extents

Two Types:

MIX Extent means more than one object of database is holding these extents

UNIFORM Extent means that Each pages under the extents are hold by single Objcet of database


A new table or index is generally allocated pages from mixed extents.

Table Organization in SQL SERVER

A table is contained in one or more partitions and each partition contains data rows in either a heap or a clustered index structure. The pages of the heap or clustered index are managed in one or more allocation units, depending on the column types in the data rows.

Indexed views have the same storage structure as clustered tables.

When a heap or a clustered table has multiple partitions, each partition has a heap or B-tree structure that contains the group of rows for that specific partition. For example, if a clustered table has four partitions, there are four B-trees; one in each partition.

Cluster tables is always implementing a B Tree index and Heap table will not have any order of rows

select au.*,p.*,DDPS.used_page_count*8,si.name,si.*,so.* from sys.objects so
INNER JOIN sys.indexes si
ON so.object_id = si.object_id
INNER JOIN sys.dm_db_partition_stats DDPS
ON DDPS.Object_id = si.object_id
INNER JOIN sys.partitions P
ON P.Object_id = si.object_id
INNER JOIN sys.allocation_units AU
ON AU.Container_ID = p.Partition_ID
where so.type='U'

HEAP TABLE

A heap is a table without a clustered index.IF Heap table has multiple partitions then each heap table has its own structure for that partitions.

The column first_iam_page in the sys.system_internals_allocation_units system view points to the first IAM page in the chain of IAM pages that manage the space allocated to the heap in a specific partition..Table scans or serial reads of a heap can be performed by scanning the IAM pages to find the extents that are holding pages for the heap. Because the IAM represents extents in the same order that they exist in the data files.

CLUSTERED TABLE

In SQL Server, indexes are organized as B-trees. Each page in an index B-tree is called an index node. The top node of the B-tree is called the root node. The bottom level of nodes in the index is called the leaf nodes. Any index levels between the root and the leaf nodes are collectively known as intermediate levels. In a clustered index, the leaf nodes contain the data pages of the underlying table. The root and intermediate level nodes contain index pages holding index rows. Each index row contains a key value and a pointer to either an intermediate level page in the B-tree, or a data row in the leaf level of the index. The pages in each level of the index are linked in a doubly-linked list.
Same like heap clustered has each structure for each partition.





NON LCUSTERED INDEXES

Nonclustered indexes have the same B-tree structure as clustered indexes but the underlying data pages are not in sorted order.Nonclustered indexes can be defined on a table or view with a clustered index or a heap. Each index row in the nonclustered index contains the nonclustered key value and a row locator. This locator points to the data row in the clustered index or heap having the key value.

The row locators in nonclustered index rows are either a pointer to a row or are a clustered index key for a row, as described in the following:

If the table is a heap, which means it does not have a clustered index, the row locator is a pointer to the row. The pointer is built from the file identifier (ID), page number, and number of the row on the page. The whole pointer is known as a Row ID (RID).

If the table has a clustered index, or the index is on an indexed view, the row locator is the clustered index key for the row. If the clustered index is not a unique index, SQL Server makes any duplicate keys unique by adding an internally generated value called a uniqueifier. This four-byte value is not visible to users. It is only added when required to make the clustered key unique for use in nonclustered indexes. SQL Server retrieves the data row by searching the clustered index using the clustered index key stored in the leaf row of the nonclustered index.

The page collections for the B-tree are anchored by root_page pointers in the sys.system_internals_allocation_units

I got one solution from Mr Pinal dave and its like

cluster index is always stored with table and non clustered is stored seperately infact if you read abouve things throughly i m sure you will come to know that both indexes are storing what things under there leaf nodes..

SQL SERVER 2005: Get a table from string list

Hi,

 Below mentioned function will convert a given list of strings into a table format and will return a table.though its not a perfect optimized as varchar takes only (8000) chars.If solution for further improvement available will be heartily appreciated


GO
IF OBJECT_ID('testing.dbo.funGetTableFromList') IS NOT NULL
DROP FUNCTION funGetTableFromList;
GO
CREATE FUNCTION funGetTableFromList
(
@varSeprator VARCHAR(1),
@varString VARCHAR(MAX)
)
RETURNS @ResultTable TABLE
(
ResultString VARCHAR(4000)
)
AS
BEGIN
DECLARE @value VARCHAR(MAX);
DECLARE @Del char(1);

SET @Del=@varSeprator;
SET @varString=@varString+@Del;

WHILE charindex(@Del,@varString,0)<>0

BEGIN

SELECT @value=RTRIM(LTRIM(SUBSTRING(@varString,1,CHARINDEX(@Del,@varString,0)-1))),
@varString=RTRIM(LTRIM(SUBSTRING(@varString,CHARINDEX(@Del,@varString,0)+1,len(@varString))));
 
INSERT INTO @ResultTable(ResultString) VALUES(@value);

END

RETURN
END

Make a Call :
SELECT * from  dbo.funGetTableFromList('|','one|two|Kamesh|Threee|you|me|we|group|part Oracle|DB|Mysql');


Thanks and regards
Kamesh shah

SQL SERVER 2005 - Procedure to create Batch with start and end time for particular days

Hi,
This logic will help you to generate the batches for the days for particular time spans and you can use this to generate batches with particular details

Pass the arguments of Start Date,End Date,Start Time,End Time and give the time interval in minutes and it will generate the result



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE ListTheDate

@varFromDate   VARCHAR(50),-- from date
@varToDate VARCHAR(50),-- to date
@varTimePartitionInMinute VARCHAR(50),-- time span in minutes
@PartStartTime VARCHAR(50),-- daily start time
@PartEndTime VARCHAR(50)-- daily end time

AS
BEGIN
DECLARE  @MidDate TABLE
(
MidDates DATETIME,
StartTime DATETIME,
EndTime DATETIME,
Name VARCHAR(50)
);
DECLARE @lCount INTEGER;
SET @lCount =0;
DECLARE @lDays INTEGER;
DECLARE @lTime INTEGER;
DECLARE @lCountTime INTEGER;

SET NOCOUNT ON;

SELECT @lDays=DATEDIFF(day,CONVERT(DATETIME,@varFromDate,103),CONVERT(DATETIME,@varToDate,103));

SELECT @lTime=DATEDIFF(minute,CONVERT(DATETIME,@PartStartTime,108),CONVERT(DATETIME,@PartEndTime,108));
WHILE (@lDays >= @lCount)
BEGIN

SET @lCountTime =0;
WHILE(@lTime > @lCountTime)

BEGIN
INSERT INTO @MidDate
(
MidDates,
StartTime,
EndTime,
Name
)
VALUES
(
DATEADD(day,@lCount,CONVERT(DATETIME,@varFromDate,103)),
DATEADD(minute,@lCountTime,CONVERT(DATETIME,@PartStartTime,108)),
DATEADD(minute,@lCountTime+@varTimePartitionInMinute,CONVERT(DATETIME,@PartStartTime,108)),
'Kamesh'
);

SELECT @lCountTime = @lCountTime + @varTimePartitionInMinute;

END

SELECT @lCount = @lCount + 1;

END

SELECT CONVERT(VARCHAR(15),MidDates,103) AS "Date",
Name,
CONVERT(VARCHAR(15),StartTime,108) AS "Batch Start Time",
CONVERT(VARCHAR(15),EndTime,108) AS "BATCH END TIME"

FROM @MidDate;


END
GO

Pass the XML in predefined format and make a insert for any table in database(SQL SERVER 2005)

The following code you can call in generic insert.You can just pass the XML in predefined format as given below and will insert the following values in column Any further improvements appreciated here
NOTES

<Params> and </params> will contain 1 row
ColumnName-pass column value
ColumnValue-pass column value

XML FORMAT


<DAL>
 <TableName TableIdentifier="TABLE NAME HERE">
<Params>
<Param ColumnName="ColumnName" ColumnValue="ColumnValue"/>
<Param ColumnName="ColumnName" ColumnValue="ColumnValue"/>
</Params>
<Params>
<Param ColumnName="ColumnName" ColumnValue="ColumnValue"/>
<Param ColumnName="ColumnName" ColumnValue="ColumnValue"/>
</Params>
 </TableName>
</DAL>


PROCEDURE


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[proAddGenericCategory]
@varGenericXML XML
AS
BEGIN
DECLARE @cmd NVARCHAR(4000);
DECLARE @MakeStament NVARCHAR(4000);
DECLARE @MakeColumnString VARCHAR(4000);
DECLARE @MakeValueString VARCHAR(4000);
DECLARE @lColumnName VARCHAR(256);
DECLARE @ColumnName VARCHAR(256);
DECLARE @lColumnValue VARCHAR(256);
DECLARE @NoOfRowsOfTemporaryTable SMALLINT;
DECLARE @lcounter SMALLINT;
DECLARE @lTableName VARCHAR(256);
DECLARE @NoOfColumns SMALLINT;
DECLARE @lColumnCounter VARCHAR(4000);
DECLARE @StoreXML TABLE
(
SerialNo SMALLINT IDENTITY(1,1),
ColumnName VARCHAR(256),
ColumnValue VARCHAR(256)
);


SET NOCOUNT ON;


BEGIN TRY
BEGIN TRANSACTION GenericInsert
BEGIN
SET @lcounter = 1;
SET @lColumnCounter = 1;
SET @MakeColumnString='';
SET @MakeValueString='';
SELECT @lTableName = Table1.Column1.value('(@TableIdentifier)[1]','VARCHAR(256)')
FROM   @varGenericXML.nodes('/DAL/TableName') as Table1(Column1);

SELECT  @NoOfColumns=Table1.Column1.value('count(Params/Param[1])','VARCHAR(20)')
FROM   @varGenericXML.nodes('/DAL/TableName') as Table1(Column1);


WHILE(@lColumnCounter <= @NoOfColumns)
BEGIN

SET @cmd='SELECT Table1.Column1.value('+'''(@ColumnName)[1]'''+','+'''VARCHAR(256)'''+'),
 Table1.Column1.value('+'''(@ColumnValue)[1]'''+','+'''VARCHAR(256)'''+')FROM
 @varXML.nodes('+'''/DAL/TableName/Params['+@lColumnCounter+']/Param'''+') as Table1(Column1)';

INSERT INTO @StoreXML EXEC sp_executesql @cmd,N'@varXML XML',@varXML=@varGenericXML;
SELECT @NoOfRowsOfTemporaryTable = MAX(SerialNo) FROM @StoreXML;

WHILE (@lCounter <= @NoOfRowsOfTemporaryTable)
BEGIN
SELECT @lColumnName = ColumnName,
@lColumnValue = ColumnValue
FROM @StoreXML
WHERE SerialNo = @lCounter;

SET @MakeColumnString = @MakeColumnString + @lColumnName +',';
SET @MakeValueString = @MakeValueString + ''''+@lColumnValue+''''+',';

SET @lCounter = @lCounter + 1;
END

SET @MakeColumnString= SUBSTRING(@MakeColumnString,1,LEN(@MakeColumnString)-1);
SET @MakeValueString= SUBSTRING(@MakeValueString,1,LEN(@MakeValueString)-2)+'''';

SET @MakeStament = 'INSERT INTO '+@lTableName+' ('+@MakeColumnString +')VALUES('+ @MakeValueString +');';

DELETE FROM @StoreXML;
EXECUTE(@MakeStament);

SET @MakeColumnString='';
SET @MakeValueString='';

SET @lColumnCounter = @lColumnCounter + 1;


END
COMMIT TRANSACTION GenericInsert;
SELECT 'True' AS RESULT;


END
END TRY


BEGIN CATCH

ROLLBACK TRANSACTION GenericInsert;
SELECT ERROR_MESSAGE() AS RESULT;


END CATCH




END



Thanks and regards
Kamesh Shah