1/19/11 Notes

Use DB
Create Table Frogs (
ID int primary key identity(1,1),
,Legs int not null
,Color varchar(8982)
)


mdf - data ROM
ldf - logging

two types of tables, temp tables and table variables
syntax and when to use each type

There are two types of Temp Tables, they are local and global.
Local temp tables have a single # sign as the first character in their names, they are visible only to the current connection for the user, and they are deleted when the user disconnects from the instances of the server.
Global temp tables have a double # sign as the first character in their names, they are visible to any user after they are created, and they are deleted when all users referencing the tables have disconnected from the server.
Local temp table:

Use DB
Create Table #Frogs (
ID int primary key identity(1,1),
,Legs int not null
,Color varchar(8982)
)

Global temp table:
Use DB
Create Table ##Frogs (
ID int primary key identity(1,1),
,Legs int not null
,Color varchar(8982)
)

A table variable uses the @ symbol as the first character in the name and it deletes when the script you are looking at finishes running. (As long as the script takes to run)

declare @mynumber int
set @mynumber = 3
select @mynumber

Declare @Frogs table;
id int primary key identity(1,1)
, legs int not null
)
Insert into @Frogs (legs) values (@mynumber)
Insert into @Frogs (legs) values (4)

select * from @Frogs

go