About us |  Help us improve
intelliVERB - Get a fair search of the Internet !
Visual studio .net
SQL Server
Oracle
Main  >  DDL Commands  >  CREATE Email this page to friend
CREATE DEFAULT
CREATE FUNCTION
CREATE INDEX
CREATE TABLE
 
 
 
 
 
TIPTop
The combination of owner.table_name must be unique within the database. table_name can contain a maximum of 128 characters, except for local temporary table names (names prefixed with a single number sign (#)) that cannot exceed 116 characters.

SqlCREATE TABLETop

--Basic command

CREATE TABLE Employee
(
 --Set the Empl_Id as identity column
 Empl_Id int IDENTITY (1, 1) NOT NULL ,
 First_Name varchar (50) NOT NULL ,
 Last_Name varchar (50) NOT NULL ,
 Picture_Url varchar (200)  NULL ,
 Creation_Date datetime NULL ,
 --set Empl_Id as primary key
 CONSTRAINT PK_Employee PRIMARY KEY  CLUSTERED
 (
  Empl_Id
 ) 
)
GO

--local temporary table

CREATE TABLE #MyTable (Tb_id INT PRIMARY KEY, Name varchar(50))

-- Run a drop table statement after using a local temp table
--global temporary table visible to all SQL Server connections

CREATE TABLE ##MyTable (Tb_id INT PRIMARY KEY, Name varchar(50))

-- Before dropping a global temp table make sure no process need it.
--Table type variable

DECLARE @MyTable (Tb_id INT PRIMARY KEY, Name varchar(50))

--No need to drop a table type variable. SQLServer manages it like others variable


TIPTop

Some built in functions are not allowed in user defined function such as :

GETDATE ,GETUTCDATE ,NEWID ,RAND ,TEXTPTR , @@CONNECTIONS , @@TOTAL_ERRORS , @@CPU_BUSY , @@TOTAL_READ , @@IDLE , @@TOTAL_WRITE , @@IO_BUSY , @@MAX_CONNECTIONS , @@TIMETICKS , @@PACK_RECEIVED , @@PACK_SENT , @@PACKET_ERRORS


SqlCREATE FUNCTIONTop
CREATE FUNCTION EmployeeList (@DeptId int)
RETURNS TABLE
AS
RETURN (SELECT Empl_Id,First_Name,Last_Name,DOB
      FROM dbo.Employee
      WHERE Dept_Id = @DeptId order by First_Name)

TIPTop

IGNORE_DUP_KEY: when specify for an index, SQL Server will allow duplicate during INSERT.

FILLFACTOR: is the percentage of rows SQL server place on each index pages.
set Fillfactor=100 only if no INSERT or UPDATE will occur on the table. 0 is the default value.

PAD_INDEX: To be use only with FILLFACTOR it specify the fill percentage for intermediate table.


SqlCREATE INDEXTop

--unique index

CREATE UNIQUE CLUSTERED INDEX EmplVacation_ndx ON Empl_Vac (Empl_Id)

--composite index
CREATE INDEX EmplVacation_ndx1 ON Empl_Vac (Vac_Id, Empl_Id)
--using fillfactor

CREATE INDEX Department_ndx ON Department (Dept_Id)
WITH PAD_INDEX, FILLFACTOR = 10


SqlCREATE DEFAULTTop

CREATE DEFAULT Position AS 'Developer'
EXEC sp_addtype PositionType, 'varchar(50)', 'NULL'
EXEC sp_bindefault Position, PositionType, futureonly

--we create a datatatype PositionType and bind it to the default Position

You last visited
Inheritance C# .net
 
 
Make a secure donation now with PayPal!