Naming convention should be used for any database objects in order to maintain consistency and accuracy. Naming convention is not defined by Microsoft; you can define your own naming convention and can follow it throughout your database. Herewith, I am giving naming convention guideline which I am following. There could be a debate in some naming convention rules but as I told you this is what I am following.

Database Naming Convention Guide line:

Use only alphabetic value for database name. Don’t use hyphen or underscore in database name. Don’t use space in database name also or else you have to surround your database name every time with square brackets.

Table Naming Convention Guide line:

Use plural name for table name. Some people would like to debate on this topic for using singular name for table and they might have their own reason as well.

Abbreviation of module should be used as prefix like: purInventoryMaster. This will help us managing tables of modules together.

Each word of table name should be capital to make it more readable like InventoryMaster is more readable than inventorymaster or INVENTORYMASTER.

Space or underscore shouldn’t be used in table name and should starts with alphabetic character.

You should also create and use appropriate Schema with table name and reserve word should not be used.

Column Naming Convention Guide line:

Column name represent entity of your table. Column name should represent the entity rather than table name. It should use first letter of each word capital like CustomerName. Space should be avoided in column name so that you don’t need to bother about square brackets uses.

Reserve word should not be used.

Stored Procedure and Function Naming Convention Guide line:

Always give appropriate name to Stored Procedure of Function show that use can understand its usage by just reading the name, they don’t need to view the code inside. You can use verb, noun etc. to make it clear like UpdateCustomerInfo or maybe you can suffix SP’s input parameter like GetOrderIDbyCustomerName etc.

Don’t use sp_ as a prefix for stored procedure as it has special meaning to it. I wrote one article for that, if you are interested then do visit it at http://ritesh-a-shah.blogspot.com/2009/03/create-your-own-system-stored-procedure.html

Indexes Naming Convention Guide line:

You can have clustered or non-clustered index and maybe unique or non-unique index, generally you used to assign index on one or more columns. I used to given index name like NcSalesSalesID out of which “NC” belongs to “NonClustered”, “Sales” is a table name and “SalesID” is a column name.

Above are the basic guidelines for naming convention. List can grow more but these are the basics I am using.

Rererence: Ritesh Shah

Whoever is aware with SQL-Server must be aware with simple INSERT statement. Generally we used INSERT records in table with either of the following SQL statements.
--create first table for demonstration

CREATE TABLE CAR

(

ID int IDENTITY(1,1),

CarName VARCHAR(16),

CarDesc VARCHAR(50)

)

1.)

--must give column value in sequence of column defined in table.

Insert into Car values ('Honda CRV','SUV')

OR

2.)

--you can change the sequence order of column after table name

--and can give value in defined order in INSERT statement

Insert into Car (CarName,CarDesc) values ('Honda Pilot','SUV')

3.)

Above are the common INSERT statement and widely used as well. Apart from these, there are several ways for INSERT statement.

With SELECT……..UNION ALL

INSERT INTO Car(CarName,CarDesc)

SELECT 'Toyota a','toyota' UNION ALL

SELECT 'Toyota b','toyota' UNION ALL

SELECT 'Toyota C','toyota'

4.)

Suppose you have one more table from which you want to populate your CAR table.

--create first table for demonstration

CREATE TABLE CAR_Master

(

ID int IDENTITY(1,1),

CarName VARCHAR(16),

CarDesc VARCHAR(50)

)

INSERT INTO Car_Master(CarName,CarDesc)

SELECT 'Mercedes s','Mercedes' UNION ALL

SELECT 'Mercedes c','Mercedes'

Now, I will populate CAR table from CAR_Master table.

INSERT INTO Car(CarName,CarDesc)

SELECT CarName,CarDesc FROM Car_Master where CarName like 'm%'

5.)

If you wish to populate your INSERT statement with stored procedure than do follow the below given query.

--Create SP which will return result set

--NOTE: You can use multiple resultset in one SP

--and all the records will be INSERTed to our table

--Make sure, you have same number of column with same datatype in

--all record set you choose in SP

CREATE PROC CarNames

AS

SET NOCOUNT ON

SELECT CarName,CarDesc FROM Car_Master

RETURN

Now simply, I can run following INSERT query.

INSERT INTO CAR (CarName,CarDesc)

EXEC carnames

Reference: Ritesh Shah

I have covered basic information about Full Text Search at http://ritesh-a-shah.blogspot.com/2009/03/full-text-search-in-sql-server-2005.html

Configuring and maintaining Full Text Search Catalog at http://ritesh-a-shah.blogspot.com/2009/03/full-text-search-in-sql-server-2005_16.html

And now finally I am going to show you how to select data after configuring the catalog with Full Text Search in Microsoft SQL Server 2005.

Contains:

Use Adventureworks

select * from HumanResources.Employee

WHERE Contains(HumanResources.Employee.LoginID,'"*je*"')

I have used Double Quote and Asterisk as wild card as Full Text Search uses standard DOS convention as a wild card. Above query will return all the records whose “LoginID” column contain “je” anywhere in the data.

Note: I gave “LoginID” column name in CONTAINS but if you wish to search “je” in every column which has been added to index while creating catalog, you can use “*” there.

Use Adventureworks

select * from HumanResources.Employee

WHERE Contains(HumanResources.Employee.*,'"*je*"')

FreeText:

Use Adventureworks

select * from HumanResources.Employee

WHERE freetext(HumanResources.Employee.*,'jolynn0 david0')

Contains uses exact word search while FreeText uses fuzzy or approximate word search. Above query will show all the records which either contain “jolynn0” or “david0”.

Reference: Ritesh Shah

So far sub-total was very tedious task to do but with COMPUTE clause in SQL-Server 2005 it became fun. Don’t you believe me? Let me take you towards COMPUTER and you will believe me for sure.

COMPUTE clause is nothing but the aggregate query tacked on to the end of a normal query. This query simply returns the simple detail rows with the specific aggregate summary for that result set only.

Let us see it practically.

--CREATE on table for demonstration

create table BookAuthor

(

AuthorName VARCHAR(50),

BookCategory VARCHAR(10),

TotalBook INT

)

--Insert some records

INSERT INTO BookAuthor

SELECT 'A','C LANG',2 UNION ALL

SELECT 'B','C LANG',3 UNION ALL

SELECT 'C','SQL',2 UNION ALL

SELECT 'A','SQL',4

After creating above table and insert the records. What will we do??? If we wish to find which author wrote book for which category and total number of book written for every book category. Well, we have age old solution for this but I am going to show your new approach by COMPUTE clause.

SELECT AuthorName,BookCategory,TotalBook

FROM BookAuthor

ORDER BY BookCategory

COMPUTE Sum(totalBook)

BY BookCategory

Above query will return you all rows from table BookAuthor and will show your total per Book Category. Remember if you wish to use book category as group by (in BY clause) in COMPUTE clause you have to define it in order by of simple SELECT query as well.

If you will not mention Book Category in Order By clause of SELECT statement, you will be greeted by following error.

Msg 143, Level 15, State 1, Line 2

A COMPUTE BY item was not found in the order by list. All expressions in the compute by list must also be present in the order by list.

Reference: Ritesh Shah

You might be aware with Aggregate function like MIN, MAX, SUM, COUNT etc. there are many types of Aggregate function supported by Microsoft SQL Server 2005 along with CLR Aggregate function. This is new and amazing feature which helps you create your own customize AGGREGATE function with the power of CLR and use it in SQL Server 2005. Isn’t it amazing? Yes, It is. Let us have a journey of creating CLR SQL Server Aggregate function to implement it in SQL Server 2005.

I have created COUNT aggregate function. It is actually inbuilt function but this is to give demo of creating aggregate function in SQL-Server 2005.

We will first create new project from Visual Studio 2008:

1) File->New->Project

2) From Visual C# tree on left panel select Database option

3) Select “SQL Server Project” from right side panel.

4) Give the name to your project. I gave “TestAggregate”

5) Click “OK”

6) Now from your solution explorer , right click on project name, select “Add->New ->Add Item”

7) Select “Aggregate” (as we are going to create aggregate function)

8) Give name to it. I gave “TestAGGR” then click “ADD” button.

After following above steps, you will see TestAGGR.cs file with some necessary namespace and methods.

Here is the brief description of those methods.

INIT(): will be executed once per aggregation

Accumulate(): will accumulate core logic and run per records.

Merge(): merge data from different method together.

Terminate(): finally returns the value.

Along with above methods, you can create methods as per your needs. Below is the copy of my TestAGGR.CS file. Have a look

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

[Serializable]

[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]

public struct TestAGGR

{

public void Init()

{

count = 0;

}

public void Accumulate(int Value)

{

count += 1;

}

public void Merge(TestAGGR value)

{

Accumulate(value.Terminate());

}

public int Terminate()

{

return count;

}

// This is a place-holder member field

private int count;

public int Getcount()

{

return count;

}

}

Once you done the above code in visual studio, build the project and copy the TestAggregate.dll from your BIN folder of your project and put it in “D”drive.

Now, we have done with Visual Studio and we will be moving to SQL-Server 2005 SSMS.

--enable your server for clr enable

--it is disable by default

sp_configure 'clr enable', 1

reconfigure

--create assembly of your DLL file

create assembly TestAggregate

authorization dbo

from 'D:\TestAggregate.dll'

with permission_set = safe

go

--create aggregate function of your DLL

CREATE AGGREGATE TestAggregate(@val Int)

RETURNS Int

EXTERNAL NAME TestAggregate.TestAGGR

go

--run your query and enjoy result

SELECT dbo.testaggregate(run) FROM PERSONALSCORE

I was Inspired to write this article by TECHNET and MSDN website of MICROSOFT.

http://technet.microsoft.com/en-us/library/ms131056.aspx#

http://msdn.microsoft.com/en-us/library/91e6taax(VS.80).aspx#

Reference: Ritesh Shah

UNION and new feature of SQL Server 2005 (INTERSECTION, EXCEPT):

You might have read my previous articles on JOIN as per those articles; JOIN is multiplication of data whereas UNION is addition.

UNION does nothing other than stack the data of multiple result sets in one result sets. While using the UNION you have to consider few things like: column name, number and aliases should be mentioned in first part of the SELECT. ORDER BY clause will sort the complete result sets, it will use the column name of first SELECT statement and should be write down after the last SELECT statement.

Let us create two demo tables to use UNION.

--create first table for demonstration

CREATE TABLE CAR

(

ID int IDENTITY(1,1),

CarName VARCHAR(16)

)

--create second table for demo

CREATE TABLE BIKE

(

ID int IDENTITY(1,1),

BikeName VARCHAR(16)

)

--Insert records in CAR table

INSERT INTO CAR

SELECT 'HONDA crv' UNION ALL

SELECT 'Mahi. Raunalt' UNION ALL

SELECT 'Test'

--Isert records in BIKE table.

INSERT INTO BIKE

SELECT 'HONDA Karishma' UNION ALL

SELECT 'Bajaj DTSI' UNION ALL

SELECT 'Test'

Now, both the select statement of above two tables will be merged and will return single result sets.

--use of union all

SELECT ID,CarName FROM CAR

UNION ALL

SELECT ID,BikeName FROM BIKE

Order by CarName

Above query returns all the records of both tables. Though we gave ORDER BY for CarName but it will sort complete second column which include some data of bikeName as well. You cannot give ORDER BY on BikeName column.

Above query was just an example, you can use UNION for less than or equal to 256 tables. This is not at all small amount.

Intersection Union: Intersection Union find common row in both data sets. As soon as you read this statement, you will say, ohh, this could be done by INNER JOIN as well. So, the answer is INNER JOIN matches two table horizontally and INTERSECTION matches two datasets vertically. There is one more difference in INTERSECTION and INNER JOIN is that, Intersection query will see NULL as common and includes the row in the intersection and INNER JOIN will not even includes two different row with NULL value in result sets.

Now, have a look at INTERSET usage in below query.

--use of intersect union

SELECT ID,CarName FROM CAR

INTERSECT

SELECT ID,BikeName FROM BIKE

Order by CarName

You will get only one row which is same in both the table.

EXCEPT: Except (a.k.a. DIFFERENCE UNION) finds records exists in one data sets and not available in another datasets. In above case we have three records in CAR table but third record is exist in BIKE table as well so EXCEPT will display only first two records. Have a look at below query.

--use of EXCEPT

SELECT ID,CarName FROM CAR

EXCEPT

SELECT ID,BikeName FROM BIKE

Order by CarName

Reference: Ritesh Shah

Business Logic with JOIN - Multiple Join examples in SQL-Server 2005:

I wrote my previous article about different type of JOIN in SQL-Server 2005. Due to the space constraint in that article, complex example has not been given there, only few simple examples. I felt to explode this topic further by giving solution to some really needed business logic.

If you want to create report for blood testing laboratory, who used to check samples of employee of different companies for different purposes. Following should be the table structure, we will be creating three table. One will store information about Order received from different companies. Another one will be the detail of that order, means which company sent how much samples of how much employee?? And the last one is further more details about how much test should be performs on which sample???

Let us create three tables.

--CREATING FIRST ORDER TABLE

USE [AdventureWorks]

GO

/****** Object: Table [dbo].[Orders] Script Date: 03/08/2009 12:24:39 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Orders](

[OrderID] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[OrderDate] [datetime] NOT NULL,

[CompanyName] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

PRIMARY KEY CLUSTERED

(

[OrderID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING ON

Now, child table of above.

USE [AdventureWorks]

GO

/****** Object: Table [dbo].[OrderDetails] Script Date: 03/08/2009 12:37:27 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[OrderDetails](

[OrderID] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SampleNumber] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[SampleOfEmployee] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[SampleReceivedDate] [datetime] NULL,

PRIMARY KEY CLUSTERED

(

[SampleNumber] ASC

)

) ON [PRIMARY]

GO

SET ANSI_PADDING ON

GO

ALTER TABLE [dbo].[OrderDetails] WITH CHECK ADD CONSTRAINT [fk_orderid] FOREIGN KEY([OrderID])

REFERENCES [dbo].[Orders] ([OrderID])

GO

ALTER TABLE [dbo].[OrderDetails] CHECK CONSTRAINT [fk_orderid]

One more child table.

USE [AdventureWorks]

GO

/****** Object: Table [dbo].[sampledetails] Script Date: 03/08/2009 12:43:00 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[sampledetails](

[SampleNumber] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[TestType] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

PRIMARY KEY CLUSTERED

(

[SampleNumber] ASC,

[TestType] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING ON

Now, this is time to insert few records on above tables.

--Received three order from two company

INSERT INTO Orders

SELECT 'L1000','2009-03-01','Test Inc.' UNION ALL

SELECT 'L1001','2009-03-02','Zeel Inc.' UNION ALL

SELECT 'L1002','2009-03-04','ABC Inc.'

--received two sample for first order, one sample for second order

--sample for third order yet to receive

INSERT INTO OrderDetails

SELECT 'L1000','L1000-01','James','2009-03-01' UNION ALL

SELECT 'L1000','L1000-02','John','2009-04-01' UNION ALL

SELECT 'L1001','L1001-01','Smita','2009-03-05'

--details of sample, which test suppose to be performed

--on which sample

INSERT INTO SampleDetails

SELECT 'L1000-01','Cancer' UNION ALL

SELECT 'L1000-01','AIDS' UNION ALL

SELECT 'L1000-02','BP' UNION ALL

SELECT 'L1001-01','AIDS' UNION ALL

SELECT 'L1001-01','Cancer'

Well, now we are ready to pull data out of three tables with different type of JOIN which we have seen in my previous article.

NOTE: many different logic can be used to perform following tasks, I am giving this just to explain the use of JOIN otherwise there are some more facility which can handle all of the below situation other than join.

If you want to get OrderID, OrderDate, Company (who gave the order??), SampleNumber, EmployeeName (for which employee company has sent the sample??), TestType (which test to be performed in above tables?)

You can use following query which has used one LEFT OUTER JOIN and one INNER JOIN.

SELECT O.OrderID,O.OrderDate,O.CompanyName,

OD.SampleofEmployee,OD.SampleNumber,SD.TestType

FROM

ORDERS AS O

LEFT OUTER JOIN

ORDERDETAILS AS OD

ON

O.OrderID=OD.OrderID

INNER JOIN

SampleDetails AS SD

ON

OD.SampleNumber=SD.SampleNumber

If there is a need to find how many samples came from which company. Use following query with aggregate function COUNT.

SELECT

O.CompanyName,Count(SampleNumber) as 'TOTAL'

FROM

ORDERS AS O

LEFT OUTER JOIN

ORDERDETAILS AS OD

ON

O.OrderID=OD.OrderID

GROUP BY O.CompanyName

ORDER BY TOTAL DESC

Reference: Ritesh Shah