Add Column between two other columns

At least there will be one question in my mail box about how to add a column in between two existing columns in a table???

There is no direct query/script to achieve this becoz in general, there is no significance for the order of the columns in the table.

If you really need this then you can try the below apporach

Lets assume that you want to add ENAME column to the EMP table in between EMP_ID and SALARY.

1. Add the the new column to the existing table say EMP
now the order will be EMP_ID,SALARY,ENAME

2. Create a new table EMP_NEW with the desired table structure and column order

3. Insert values from your existing EMP to this EMP_NEW

4. Drop the existing table EMP and rename EMP_NEW to EMP.


You can do the above steps only when no one is performing any DML operations on the table to avoid any missing rows.

Create CLR Stored Procedure

With the latest version of SQL Server 2005, we have the the ability to write CLR from any .Net language.

Lets dig into more details:
I have started leaning C#, so let me show you how to write this code in C#. Please bare with me on the logic :)

Step:
1. Start Visual Studio 2005
2. Add new file
3. Select Class with C#
4. Mark the name as DemoProcCls.cs and write the below lines of code.

using System.Data.SqlServer;
using System.Data.SqlTypes;
public class DemoProcCls
{
public static void DemoProc()
{
SqlPipe sqlPipe = SqlContext.GetPipe();
sqlPipe.Send("Hello World");
}
}

i) Compile the code into assembly
You need to find out the SQL Server path of "sqlaccess.dll".

csc /target:library D:\DemoProcCls.cs /r:"sqlaccess.dll"

ii) Load the above created assembly in SQL with the use of CREATE PROCEDURE
create procedure SampleDemoProc
as external name DemoProcCls.DemoProcCls.DemoProc

and execute the stored procedure as

exec SampleDemoProc

Guidelines for Partitioned Tables

You must plan to create the following database objects before partitioning a table or index:

Partition function
Partition scheme

A partition function defines how the rows of a table or index are mapped to a set of partitions based on the values of certain columns, called partitioning columns.

A partition scheme maps each partition specified by the partition function to a filegroup.

Planning the Partition Function
There are two factors to consider when planning a partition function: the column whose values determine how a table is partitioned, known as the partitioning column, and the range of values of the partitioning column for each partition. This range of values determines the number of partitions that make up your table. A table can have a maximum of 1,000 partitions.

The choices you have for the partitioning column and the values range are determined primarily by the extent to which your data can be grouped in a logical way, such as by date, and whether this logical grouping is adequate for managing subsets of data.

For example, under the partitioning scenario of the AdventureWorks sample database, the TransactionHistory and TransactionHistoryArchive tables are partitioned on the TransactionDate field. The range of values for each partition is one month. The TransactionHistory table maintains the year's most current transactions, while TransactionHistoryArchive maintains older transactions. By partitioning the tables in this way, a single month's worth of old data can be transferred quickly and efficiently from TransactionHistory to TransactionHistoryArchive on a monthly basis.


For more information check out the below link
http://msdn.microsoft.com/en-us/library/ms180767.aspx

Full Text Search in SQL Server

Full text search refers to a technique for searching a computer-stored document or database. In a full text search, the search engine examines all of the words in every stored document as it tries to match search words supplied by the user. Full-text searching techniques became common in online bibliographic databases in the 1970s. Most Web sites and application programs (such as word processing software) provide full text search capabilities. Some Web search engines, such as AltaVista employ full text search techniques, while others index only a portion of the Web pages examined by its indexing system

Full-text Index
* Regular SQL indexes stored under control of the database which they are defined. But full-text indexes stored in the file-system, but administered through the database.
* Only one full-text index can be defined per table.
* Regular SQL indexes, will be updated whenever the new rows has been added, modified, or deleted. But, Full-text index is based on population. Because, full-text service will use more system resources.

Full-text Catalogue
A full-text Catalogue contains full-text indexes in a database. Each catalogue can serve the indexing needs to one or more tables in within a database. A maximum of 256 full-text catalogs can be created on each server.

Full-text enabled Database
A database must be enabled for the full-text service. More than one full-text catalogues can be created and dropped in an enabled full-text database.


Full-text enabled Table
A table should be enabled for full-text support. While creating full-text index, the table will be associated with full-text catalogue. After the table is enabled, you can populate it with the data in columns enabled for the full-text support.

Table Difference

TableDiff.exe is a table comparison tool that comes with the sql server.

It's installed in the following location:

"D:\Program Files\Microsoft SQL Server\90\COM\TableDiff.exe"if your SQL Server is installed in the program files on D: drive.
I normally install everything on D drive.

This compares 2 tables in the same database on the same server and creates a new table called DiffsTable that holds the differences:



"D:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe"
-sourceserver MyServer1
-sourcedatabase MyDatabase1
-sourcetable MyTable1
-destinationserver MyServer1
-destinationdatabase MyDatabase1
-destinationtable MyTable2
-et DiffsTable


For more information check out the below link
http://msdn2.microsoft.com/en-us/library/ms162843.aspx

Avoid NULLs in database

Joe Celko said it best: "NULLs confuse people..."

My sentiments exactly. Of course, I don't expect to convince you by flashing a few quotes from reputable authors. Let's talk for a minute about what exactly NULLs do that cause this type of reaction. The first problem is that the definition of NULL is "unknown." So, one problem is determining whether one value is (not) equal to another value, when one or both values are NULL. This trickles down to many problems for a database engine and any associated applications. The following list details some of those problems:
 they are interpreted differently depending on compatibility level and ANSI settings;

For example, let's consider two values, x and y, that are both NULL. Since the definition of NULL is unknown, then you can't say x = y. However, with the ANSI setting ANSI_NULLs, this can be different. When this setting is FALSE, x = y ... however, when TRUE, x <> y. Confusing, no?

 the storage engine has to do extra processing for each row to determine if the NULLable column is in fact NULL -- this extra bit can have performance implications;
 they produce weird results when using calculations, comparisons, sorting and grouping;
 they create problems with aggregates and joins, such as different answers for COUNT(*) vs. COUNT(fieldname);
 they produce unpredictable results in statistics computations, particularly WITH ROLLUP and WITH CUBE;
 applications must add extra logic to handle inserting and retrieving results, which may or may not include NULL values;
 they cause unpredictable results with NOT EXISTS and NOT IN subqueries (working backwards, SQL determines that NULL columns belong or do not belong to the result set, usually for the wrong reasons);
 no language that supports embedded SQL has native support for NULL SQL values.

Sql Database Table Design

When creating a SQL database you need to ask - what is the information that I want to capture? Do not try to capture everything on one SQL database. Simplify it. It is OK to create several SQL databases with specific types of information you want to capture for an organization. You can always create a sql command to gather the information from the separate databases.

Afterwards, you can get down to designing the TABLES. Below are three rules about SQL Table design. I know that rules are made and then broken, but in general follow the 3 Rules below (if you can).

RULE 1: In a Table, do not repeat data.

RULE 2: Repeated data goes in another Linked table.

RULE 3: Row Data must only rely on the Table's Primary Key.