create table

--Complete table definitions

--This example shows complete table definitions with all constraint definitions for three tables (jobs, employee, and publishers) created in the pubs database.

/* ************************** jobs table ************************** */
CREATE TABLE jobs
(
job_id smallint
IDENTITY(1,1)
PRIMARY KEY CLUSTERED,
job_desc varchar(50) NOT NULL
DEFAULT 'New Position - title not formalized yet',
min_lvl tinyint NOT NULL
CHECK (min_lvl >= 10),
max_lvl tinyint NOT NULL
CHECK (max_lvl <= 250)
)

/* ************************* employee table ************************* */
CREATE TABLE employee
(
emp_id int
CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED
CONSTRAINT CK_emp_id CHECK (emp_id LIKE
'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' or
emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]'),
/* Each employee ID consists of three characters that
represent the employee's initials, followed by a five
digit number ranging from 10000 through 99999 and then the
employee's gender (M or F). A (hyphen) - is acceptable
for the middle initial. */
fname varchar(20) NOT NULL,
minit char(1) NULL,
lname varchar(30) NOT NULL,
job_id smallint NOT NULL
DEFAULT 1
/* Entry job_id for new hires. */
REFERENCES jobs(job_id),
job_lvl tinyint
DEFAULT 10,
/* Entry job_lvl for new hires. */
pub_id char(4) NOT NULL
DEFAULT ('9952')
REFERENCES publishers(pub_id),
/* By default, the Parent Company Publisher is the company
to whom each employee reports. */
hire_date datetime NOT NULL
DEFAULT (getdate())
/* By default, the current system date is entered. */
)

/* ***************** publishers table ******************** */
CREATE TABLE publishers
(
pub_id char(4) NOT NULL
CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED
CHECK (pub_id IN ('1389', '0736', '0877', '1622', '1756')
OR pub_id LIKE '99[0-9][0-9]'),
pub_name varchar(40) NULL,
city varchar(20) NULL,
state char(2) NULL,
country varchar(30) NULL
DEFAULT('USA')
)

Creating a database

Creating a database without specifying files

CREATE DATABASE mytest

Creating a database that specifies the data and transaction log files

CREATE DATABASE Sales
ON
( NAME = Sales_dat,
FILENAME = '''+ @data_path + 'saledat.mdf'',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = Sales_log,
FILENAME = '''+ @data_path + 'salelog.ldf'',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )'
);
GO

Creating a database by specifying multiple data and transaction log files

'CREATE DATABASE Archive
ON
PRIMARY
(NAME = Arch1,
FILENAME = '''+ @data_path + 'archdat1.mdf'',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Arch2,
FILENAME = '''+ @data_path + 'archdat2.ndf'',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Arch3,
FILENAME = '''+ @data_path + 'archdat3.ndf'',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20)
LOG ON
(NAME = Archlog1,
FILENAME = '''+ @data_path + 'archlog1.ldf'',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
(NAME = Archlog2,
FILENAME = '''+ @data_path + 'archlog2.ldf'',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20)'
);
GO
 
Creating a database that has filegroups

CREATE DATABASE Sales
ON PRIMARY
( NAME = SPri1_dat,
FILENAME = '''+ @data_path + 'SPri1dat.mdf'',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),
( NAME = SPri2_dat,
FILENAME = '''+ @data_path + 'SPri2dt.ndf'',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),
FILEGROUP SalesGroup1
( NAME = SGrp1Fi1_dat,
FILENAME = '''+ @data_path + 'SG1Fi1dt.ndf'',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
( NAME = SGrp1Fi2_dat,
FILENAME = '''+ @data_path + 'SG1Fi2dt.ndf'',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
FILEGROUP SalesGroup2
( NAME = SGrp2Fi1_dat,
FILENAME = '''+ @data_path + 'SG2Fi1dt.ndf'',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
( NAME = SGrp2Fi2_dat,
FILENAME = '''+ @data_path + 'SG2Fi2dt.ndf'',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = Sales_log,
FILENAME = '''+ @data_path + 'salelog.ldf'',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )'
);
GO

Attaching a database

'CREATE DATABASE Archive
ON (FILENAME = '''+ @data_path + 'archdat1.mdf'')
FOR ATTACH');
GO

create function

create function myfuns(@x int) returns char(10)
as
begin
declare @xx char(10)
select @xx = nam from Table_1 where id= @x
return @xx
end

create stored procedure

create proc myproc
@id int,
@nam char(10)
as
insert into Table_1 values(@id,@nam)

Create sample Database with data

Northwind
----------
http://www.4shared.com/file/71580360/c9e94375/instnwnd.html
Pubs
-----
http://www.4shared.com/file/71579808/62655b7b/instpubs.html

Cursors

SQL Server is very good at handling sets of data. For example, you can use a single UPDATE statement to update many rows of data. There are times when you want to loop through a series of rows a perform processing for each row. In this case you can use a cursor.

Please note that cursors are the SLOWEST way to access data inside SQL Server. The should only be used when you truly need to access one row at a time. The only reason I can think of for that is to call a stored procedure on each row.

The basic syntax of a cursor is:
--------------------------------

----------------------------------------------------------------------------------
DECLARE @AuthorID char(11) DECLARE c1 CURSOR READ_ONLYFORSELECT au_idFROM authorsOPEN c1FETCH NEXT FROM c1INTO @AuthorIDWHILE @@FETCH_STATUS = 0BEGIN PRINT @AuthorID FETCH NEXT FROM c1 INTO @AuthorIDENDCLOSE c1DEALLOCATE c1
-----------------------------------------------------------------------------------

The DECLARE CURSOR statement defines the SELECT statement that forms the basis of the cursor. You can do just about anything here that you can do in a SELECT statement. The OPEN statement statement executes the SELECT statement and populates the result set. The FETCH statement returns a row from the result set into the variable. You can select multiple columns and return them into multiple variables. The variable @@FETCH_STATUS is used to determine if there are any more rows. It will contain 0 as long as there are more rows. We use a WHILE loop to move through each row of the result set.

The READ_ONLY clause is important in the code sample above. That dramatically improves the performance of the cursor.

In this example, I just print the contents of the variable. You can execute any type of statement you wish here. In a recent script I wrote I used a cursor to move through the rows in a table and call a stored procedure for each row passing it the primary key. Given that cursors are not very fast and calling a stored procedure for each row in a table is also very slow, my script was a resource hog. However, the stored procedure I was calling was written by the software vendor and was a very easy solution to my problem. In this case, I might have something like this:

EXEC spUpdateAuthor (@AuthorID)

instead of my Print statement. The CLOSE statement releases the row set and the DEALLOCATE statement releases the resources associated with a cursor.

If you are going to update the rows as you go through them, you can use the UPDATE clause when you declare a cursor. You'll also have to remove the READ_ONLY clause from above.

----------------------------------------------------------------------------
DECLARE c1 CURSOR FORSELECT au_id, au_lnameFROM authorsFOR UPDATE OF au_lname
----------------------------------------------------------------------------

You can code your UPDATE statement to update the current row in the cursor like this

---------------------------------
UPDATE authors
SET au_lname = UPPER(Smith)
WHERE CURRENT OF c1
----------------------------------





-------------------------------------------------------------------------
http://www.sqlteam.com/article/cursors-an-overview

what is Surrogate Keys ?

the following articale from this link
http://decipherinfosys.wordpress.com/2007/02/01/surrogate-keys-vs-natural-keys-for-primary-key/

Surrogate Keys vs Natural Keys for Primary Key?

This topic probably is one of those that you cannot get any two database developers/DBAs to agree upon. Everyone has their own opinion about this and it is also one of the most discussed topics over the web when it comes to data modeling. Rather than taking any side :-), we are just listing out our experiences when it comes to chosing between a surrogate key vs the natural keys for the tables.0

Surrogate Key:

Surrogate keys are keys that have no “business” meaning and are solely used to identify a record in the table. Such keys are either database generated (example: Identity in SQL Server, Sequence in Oracle, Sequence/Identity in DB2 UDB etc.) or system generated values (like generated via a table in the schema).

Natural Key:

Keys are natural if the attribute it represents is used for identification independently of the database schema. What this basically means is that the keys are natural if people use them example: Invoice-Numbers, Tax-Ids, SSN etc.

Design considerations for choosing the Primary Key:
Primary Key should meet the following requirements:

- It should be not null, Unique and should apply to all rows.

- It should be minimal (i.e. less number of columns in the PK: ideally it should be 1, if using composite keys, then make sure that those are surrogates and using integer family data-types).

- It should be stable over a period of time (should not change i.e. update to the PK columns should not happen).

Keeping these in mind, here are the pros and cons of Surrogate vs. Natural keys:

Surrogate Key

I prefer surrogate keys to be DB controlled rather than being controlled via a next-up table in the schema since that is a more scalable approach.

Pros:

- Business Logic is not in the keys.

- Small 4-byte key (the surrogate key will most likely be an integer and SQL Server for example requires only 4 bytes to store it, if a bigint, then 8 bytes).

- Joins are very fast.

- No locking contentions because of unique constraint (this refers to the waits that get developed when two sessions are trying to insert the same unique business key) as the surrogates get generated by the DB and are cached - very scalable.

Cons:

- An additional index is needed. In SQL Server, the PK constraint will always creates a unique index, in Oracle, if an index already exists, PK creation will use that index for uniqueness enforcement (not a con in Oracle).

- Cannot be used as a search key.

- If it is database controlled, for products that support multiple databases, different implementations are needed, example: identity in SS2k, before triggers and sequences in Oracle, identity/sequence in DB2 UDB.

- Always requires a join when browsing the child table(s).

Natural Key

Pros:

- No additional Index.

- Can be used as a search key.

Cons:

- If not chosen wisely (business meaning in the key(s)), then over a period of time additions may be required to the PK and modifications to the PK can occur.

- If using strings, joins are a bit slower as compared to the int data-type joins, storage is more as well. Since storage is more, less data-values get stored per index page. Also, reading strings is a two step process in some RDBMS: one to get the actual length of the string and second to actually perform the read operation to get the value.

- Locking contentions can arise if using application driven generation mechanism for the key.

- Can’t enter a record until value is known since the value has some meaning.

Choosing Surrogate vs. Natural Keys:

There is no rule of thumb in this case. It has to be evaluated table by table:

- If we can identify an appropriate natural key that meets the three criteria for it to be a PK column, we should use it. Look-up tables and configuration tables are typically ok.

- Data-Type for the PK: the smaller the better, choose an integer or a short-character data type. It also ensures that the joins will be faster. This becomes even more important if you are going to make the PK as a clustered index since non-clustered indexes are built off the clustered index. RDBMS processes integer data values faster than the character data values because it converts characters to ASCII equivalent values before processing, which is an extra step.