For IT Professionals

How to retrieve a particular identifier when multiple tables have identifier columns

What is an Identity Column? An identity column is a method to build primary keys. Like an auto-number column or sequence column in other databases, the identity column in SQL Server generates consecutive integers as new rows are inserted into the database. Advantages of using Identity column as primary keys are:

• Integers are easier to manually recognize and edit than GUIDs.
• Integers are small and fast.
• An Identity column used as a primary key with a clustered index (a common, but poor practice) may be extremely fast when retrieving a single row with a single user. However, that configuration will cause lock-contention hot spots on the database.

Identity column values are created by SQL Server as the row is being inserted. Note that for the Identity column the NOT NULL option is essential.

How to Create an Identity Column for a Table:

Select the identity column in the table view and expand Identity specification in the Column Property window and then set the property Is Identity to Yes.

Type a value in the Identity increment cell. This value is the increment that is added to the Identity Seed for each subsequent row. The default value is 1.

Type value in Identity Seed cell. This value is assigned to the first row in the Table. Default value is 1.

Identity Column does not guarantee that the value used as an identifier will be unique throughout the database across tables. Note that the data type to be selected for Identity columns are int, smallint, tinyint, decimal or numeric. Below is the code for creating unique identifiers.

CREATE TABLE mySchema.Customer (
CustID INT  IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED,
CustName VARCHAR(30) UNIQUE NOT NULL,
City VARCHAR(50) NOT NULL
)
ON [Primary]

We need to retrieve an identifier of a row of data in a table to insert the same value in that table or another table to maintain relationships.

Normally, we use the function @@IDENTITY which returns the last identity value that was inserted in the database. But, what we need is the identity value of a particular table in which the last record was inserted. We use UDFs to accomplish this task. A typical UDF code snippet is given below:

CREATE   FUNCTION GetComIdentity() RETURNS INT AS
BEGIN
RETURN (IDENT_CURRENT('com_msg'))
END

CREATE TABLE admin.com_msg (
    [id_msg] [int] IDENTITY (1, 1) NOT NULL ,
    [idtop_msg] [int] NOT NULL ,
    [idmsg_msg] [int] NULL ,
    [id_init_msg] [int] NOT NULL CONSTRAINT  [DF__com_m__id_in__4257997F] DEFAULT ([admin].[GetComIdentity]()),
    [brief_msg] [varchar] (200) NULL,
     PRIMARY KEY  CLUSTERED
    (
        [id_msg]
    ) WITH  FILLFACTOR = 90  ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Creating Tables with SQL Scripts

We can create a Database tables in SQL Server using any of the following two modes:

• Using Graphical Environment – Object Explorer
• Using Scripts

The graphical environment is intuitive and is easy to use. It is a great tool for the beginner to get started quickly.

An experienced programmer prefers to use scripts to create the Database tables. A script is a batch file which is run in the SQL Server environment.

The following CREATE TABLE DDL command creates the Customer table. The table name, including the name of the schema is provided, followed by the table’s columns. The code directs SQL Server to create the table on the primary filegroup. Apart from the columns, the only information you normally supply when creating a table is the name.

CREATE TABLE mySchema.Customer (
CustID INT  NOT NULL PRIMARY KEY NONCLUSTERED,
CustName VARCHAR(30) UNIQUE NOT NULL,
City VARCHAR(50) NOT NULL
)
ON [Primary]

Note:

A filegroup is a collection of files. A file or filegroup cannot be used by more than one database. Every database comprises of a Primary filegroup which contains the system tables.

If you are developing a database for mass deployment or repeat installations, having the code in one location helps in mass deployment. Working with SQL scripts is similar to developing an application with Visual Basic or C#.

Another advantage of using scripts is that the existing database can be upgraded to the most current version of the database without running ‘change scripts’ or restoring the backup.

Drawbacks

The T-SQL commands may be unfamiliar and the size of the script may become overwhelming.

If the foreign-key constraints are embedded within the table, the table-creation order is very strict. If the constraints are applied after the tables are created, the table-creation order is no longer a problem; however, the foreign keys are distanced from the tables in the script.

How to implement business rules in a Microsoft Sql Server Database?

Business rules are rules or conditions that drive your entire software application system. Business Rules ensure that your data is correct and of use at any point of time. A software application system developed for a Health care domain will have a set of business rules, which may be entirely or partly different from those for a Banking and Financial software system. System analysts while designing the software application system, decide where to implement the business rules – whether on the client side or at the server side. MS SQL Server offers the following means to implement business rules: Constraints, Stored Procedures, Triggers and Rules.

Common examples of simple business rules: A sales order should not have a negative sales amount and should have a unit price, which is greater than zero. Another example is, when a sales order is being generated the stock level should not fall below the zero level. This is required to ensure that there is stock available to fulfill the order. Using an RDBMS, we can incorporate these rules right into the database itself. Let us see how we implement these business rules in SQL Server database by using Constraints.

A Constraint is an object that exists only within the table either at column level or table level. A Constraint is a high-level data validation check or business logic check performed at the database-engine level. Implementing constraints has undergone changes with every release of SQL Server. A common type of Constraint used is Check constraint. Check constraints are useful for ensuring the enforcement of general data-validation rules or simple business rules. A constraint is defined using the same rules that we use in a WHERE clause of a SQL Query.

Examples

To Limit Month column to appropriate numbers

SQL:

BETWEEN 1 AND 12

To Limit to a specific list of shippers

SQL:

IN(‘UPS’,’Fed Ex’,’USPS’)

To ensure Price is a positive number

SQL:

UnitPrice >=0

To Reference another column in the same row and to validate shipment date is always greater than order date.

SQL:

Shipdate >=OrderDate

Note:

Almost anything we put in a WHERE clause can also be put in a constraint.  Constraints are fast compared to the alternatives like rules and triggers.

Another example of a using a Constraint:

In an accounting software database, consider the customer registration table. We need to check for a valid date in a date field while recording the customer registration. Note that we cannot have a date in the system that is a future date. To enforce this validation we use the following constraint.

ALTER TABLE Customers
ADD CONSTRAINT CK_CN_CustomerDate
CHECK
(date<=GETDATE())

If we try to insert a record that violates the CHECK constraint, MS SQL Server will report an error.

Next: Client and Server responsibilities, which a programmer should be aware of.