|
|
| TIP | | Top |
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.
|
|
| Sql | CREATE TABLE | Top |
|
--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
|
|
| TIP | | Top |
|
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
|
|
| Sql | CREATE FUNCTION | Top |
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)
|
|
| TIP | | Top |
|
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.
|
|
| Sql | CREATE INDEX | Top |
|
--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
|
|
| Sql | CREATE DEFAULT | Top |
|
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
|
|
|
|
