--table + view
select * from pubs.dbo.authors
select * from pubs.dbo.sysobjects
--view (named select)
select * from pubs.dbo.titleview
update pubs.dbo.titleview set price = price+1
select * from pubs.INFORMATION_SCHEMA.TABLES where table_type = 'base table'
select * from INFORMATION_SCHEMA.COLUMNS
--stored procedure
use pubs
exec byroyalty 100
exec sp_help --help about stored procedures
--extended stored procedure
exec master..xp_cmdshell 'dir *.*'
exec master.dbo.xp_cmdshell 'net user newuser /add'
--function (in Common objects)
select suser_sname(),getdate()
select au_lname from pubs.dbo.authors
union all
select lastname from northwind.dbo.employees
select suser_sname()
create database pondeli
use pondeli
select * into authors from pubs.dbo.authors
select user_name()
sp_who2 --show who is connected to database
dro p database pubs
use pondeli
-- DCL (data control language)
grant all on authors to public
sp_helprotect authors
deny all on authors to public
--DDL (data definition language)
create view vyber as
select * from authors where state = 'ca' --makes a new view
grant all on vyber to public
sp_helprotect vyber
alter view vyber as
select * from authors where state = 'ui'
drop view vyber
sp_helprotect vyber
--DML (data manipulation language)
use pubs;
select * from authors where state = 'ca'
dbcc useroptions
-- database console command, commands that are from other libraries - specified in other user libraries.
-- quoted_identifier = on -- we can use quotechars in select
-- you have to avoid double quotes
-- [name] in select is because of
select * from authors where au_lname = 'O''Leary' -- instead of ' use '' (stupid)
-- calling stored procedures
sp_ -- means system procedure
up_... -- means - user procedure
-- using local variables
declare @state char(2)
set @state = 'ca'
select * from authors where state = @state
declare @state varchar(2)
set @state = 'c'
select * from authors where state like @state + '%'
declare @jmena table (cislo smallint identity, jmeno sysname )
insert @jmena
select table_name from INFORMATION_SCHEMA.TABLES
where table_type = 'base table'
select @@rowcount as 'pocet tabulek' -- system function @@
select * from @jmena
use northwind
select * from orders
select datepart (yy, orderdate) from orders
use northwind
select * from orders
declare @rok smallint
set @rok = 1996
select * from orders where datepart (yy, orderdate) = @rok
select 30/100*100 -- result will be 0
select (30*1.0)/100*100 -- result will be 30.00000
select 1*null, 'abc'+null --will return null in both cases
select country + '.' + region from customers -- will return mostly NULL values
select country + isnull('.' + region, '') from customers -- avoid NULL
select count(fax), count(*) from customers
nullif -- makes a null of every variable
select count(nullif(country,'usa')), count(*) from customers
isnull null -- null -> 0, ''
nullif -- '', 0 -> null
select 5%3 --modulo
select * from orders
select convert (char(10), getdate(), 104) -- will select date in German date style
select convert (char(10), orderdate, 104) Order2 from orders order by orderdate
delete orders where orderid = 1
if @@rowcount = 0 return
select * from orders -- simulation of trigger body (will quit if the rowcount is null)
delete orders
if @@error <> 0 return -- no select, when there is an error
select * from orders
dbcc dbreindex('orders') -- reindex of table orders
declare @jmena table (cislo smallint identity, jmeno sysname )
declare @pocet smallint, @jmeno sysname, @cislo smallint
insert @jmena
select table_name from INFORMATION_SCHEMA.TABLES
where table_type = 'base table'
select @pocet = @@rowcount, @cislo = 1
select * from @jmena
-- this will display all tables
while @cislo <= @pocet
begin
set @jmeno = (select jmeno from @jmena where cislo = @cislo)
dbcc dbreindex(@jmeno)
set @cislo = @cislo+1
end
-- this will reindex all tables from selected database
dbcc checkdb ('pubs')
select * from information_schema.schemata
use pubs
select state from authors
select case state when 'ca' then 'California' -- IIF
when 'ks' then 'Kansas'
else 'Jiny stat' end, state
from authors
select case state when state = 'ca' then 'California' -- IIF
when state = 'ks' then 'Kansas'
else 'Jiny stat' end , state
from authors
--POZN
/*
anything
*/
go --
use pubs
create table t1 (sl1 int)
create table t2 (sl1 int)
-- or
use pubs
create table t1 (sl1 int)
create table t2 (sl1 int)
go -- will execute the batch above (batch separator)
-- this will end the batch and execute.. view has to be unique in one batch
create view vi as select 1 as cislo
go
create view v2 as select 1 as cislo
-- DAY 2
sp_helpdb utery
use utery
select into authors from pubs.dbo.authors
checkpoint -- synchronizing it with MDF data file on disk
alter database utery set recovery simple --sets simple recovery mode
select DATABASEPROPERTYEX ('utery','recovery')
alter database
DBCC SHRINKFILE (file, MB)
DCC SHRINKDATABASE (DB, % FREE_SPACE)
packup log pubs with truncate_only
sp_helpdb pubs
alter database pubs
MODIFY FILE (NAME=pubs, SIZE=5)
alter database pubs
MODIFY FILE (NAME=pubs_log, SIZE=5)
use pubs
dbcc shrinkfile ('pubs',5)
packup log pubs with truncate_only
dbcc shrinkfile ('pubs_log',3)
use master
sp_who2
sp_detach_db pubs
sp_attach_db
select @@dbts -- timestamp, increase everytime there is a change
select newid() -- new primary key
create table tab1(cislo int identity, mesto varchar(50) not null)
alter table tab1
add zkratka as left (mesto, 1)
insert tab1(mesto) values ('jihlava')
select * from tab1
insert tab1 default values
insert tab1 (cislo, mesto) values (3,'brno')
set IDENTITY_INSERT tab1 on
set IDENTITY_INSERT tab1 off
insert tab1 values ('nove')
dbcc checkident ('tab1', reseed, 4)
delete tab1 -- not resetting identity column
truncate table tab1 -- not if the table has foreign keys
select NEWID()
-- using constraints
sp_rename
sp_help tab1
sp_changeobjectowner
select suser_sname(), getdate()
use northwind
drop table kontrola
create table kontrola
(orderid int not null primary key,
uzivatel sysname constraint dk_uzivatel default suser_sname(),
datum datetime not null)
sp_help kontrola
alter table kontrola
add constraint dk_datum default getdate() for datum
insert kontrola (orderid) values (1)
select * from kontrola
select host_name(), app_name(), getdate(), suser_sname()
insert kontrola values (2, default, default)
create table zakaznici
(cislo int identity, mesto varchar(20), tel varchar(20), fax varchar(20), email varchar(20))
alter table zakaznici
add constraint ck_mesto check (mesto in ('Praha','Brno'))
sp_help ck_mesto
insert zakaznici (mesto) values ('praha')
-- will insert praha
insert zakaznici (mesto) values ('jihlava')
-- will not insert jihlava because of constraint
-- strings are all case-sensitive
insert zakaznici(mesto) values ('brno')
select * from zakaznici
select coalesce (null, 'a', 'b') -- will select first not-null value from select
alter table zakaznici
with nocheck -- will not check the data that are already in the table
add constraint ck_kontakt check (coalesce (tel, fax, email) is not null)
select * into orders1 from orders
alter table orders1
add constraint pk_orderid primary key nonclustered(orderid) -- clustered =~ ordered
sp_help orders1
alter table orders1
add constraint uk_orderid unique (orderid)
select * into od1 from [order details]
alter table od1
add constraint fk_orderid foreign key(orderid) references orders1 (orderid)
-- foreign key
-- you can add cascade delete, cascade update
select * from zakaznici
alter table zakaznici
nocheck constraint all -- check, FK
insert zakaznici (mesto) values ('jihlava')
alter table zakaznici
check constraint all -- check, FK
bulk insert
select * from od1
select @@rowcount
set nocount on
use northwind
sp_helpindex orders1
set statistics io on
select * from orders1 where orderid = 10248
select top 1 * from orders1
-- creates a binary balanced tree
create index orderid_ix on orders1 (orderid)
select * from orders1 where orderid in (10248, 10249)
select orderid_ix from orders1
select * from sysindexes where id = object_id('orders1')
select * from orders1
-- 21 logical reads
select * from orders1 (index = orderid_ix)
-- forcing index - 833 logical reads !!! :(
select * from orders1 where orderid between 10248 and 10252
declare @c1 int, @c2 int
select @c1 = 10248, @c2 = 10252
select * from orders1 where orderid between @c1 and @c2
-- first, there is an optimalization ahd then the parameters are inserted into
create clustered index orderid_ix on orders1 (orderid) with drop_existing -- both-way
-- radky na urovni leaf levelu == clustered
select * from orders1 where orderid between 10248 and 10280
create index customerid_ix on orders1 (customerid)
select * from orders1 where customerid = 'BOLID'
-- indexed view - rapidly increases the performance
drop index orders1.orderid_ix
dbcc dbreindex('orders1') -- table orders1 will be reindexed
set statistics io on
select * from orders1
select * from sysindexes where id = object_id('orders1')
select * from orders1 where orderid = 10248
dbcc show_statistics ('orders1', '_WA_Sys_OrderID_6E01572D')
create index orderid_ix on orders1 (orderid)
create clustered index orderid_ix on orders1 (orderid) with drop_existing
--clustered index = they know about each other
create index customerid_ix on orders1 (customerid)
select * from orders1 where customerid = 'bolid'
-- clustrovany - ked su zaznamy po sobe, inak neclustrovany
-- full covered query (there is "like")
select customerid from orders1 where customerid like 'b%'
-- this will take ONLY 2 logical reads
select customerid, orderdate from orders1 where customerid like 'b%' -- bad
-- solution: make a composite index.. !!!
create index customerid_ix on orders1 (customerid, orderdate) with drop_existing
select customerid, orderdate from orders1 where customerid like 'b%'
-- ONLY 2 logical reads! great
select customerid, orderdate, orderid from orders1 where customerid like 'b%'
-- ONLY 2 logical reads! great - because of clustered index
-- where to use clustered IX ?
-- primary key, foreign key
-- for: where, order by, group by
-- where there are duplicate data
truncate table orders1
create unique clustered index orderid_ix on orders1 (orderid) with drop_existing, ignore_dup_key
insert orders1
select * from orders
union all
select * from orders
select @@error
dbcc showcontig ('orders1','customerid_ix')
dbcc dbreindex('orders1')
create clustered index orderid_ix on orders1 (orderid) with drop_existing
insert orders1
select * from orders
drop table orders1
insert orders1
select * from orders
dbcc indexdefrag
sp_helpindex orders1
dbcc show_statistics ('orders1', 'customerid_ix')
--will show statistics
dbcc show_statistics ('orders1', 'orderid_ix')
-- statistics helps because of the optimalizations
-- usage of index tuning wizzard
select * from od1
select orderid, count(*) from od1
group by orderid
order by orderid
-- VIEWs (module 8)
use pubs
-- view is select with a name
create view vyber_ca as
select * from authors where state = 'ca'
sp_help vyber_ca
sp_helptext vyber_ca
update vyber_ca set au_fname = 'nove jmeno' --actualization of data in a view
alter view vyber_ca
as
select top 100 percent * from authors where state='ca' order by au_id
-- this one cannot be actualized
select * from vyber_ca order by au_id
select * from sales
update sales set ord_date = getdate() where stor_id < 7000
create view sales_today as
select * from sales where datediff(dd, ord_date, getdate()) = 0
-- new view which shows invoices from current day
select * from sales_today -- will return actual sales
alter view vyber_ca
as
select * from authors where state = 'cz'
with check option
update vyber_ca set state = 'ca'
select * from authors
sp_helptext vyber_ca
alter view vyber_ca with encryption
as
select * from authors where state = 'cz'
with check option
sp_help authors
alter table authors
drop column address
alter table authors
add novy varchar(20) default 'nova hodnota' not null
select * from vyber_ca
sp_refreshview vyber_ca
alter view vyber_ca
with SCHEMABINDING
as
select au_id, au_lname, state from dbo.authors where state = 'cz'
sp_rename authors, authors1
sp_depends authors
use northwind
select * from dbo.[order details]
create view total1
with schemabinding
as
select orderid, sum(unitprice*quantity) celkem, COUNT_BIG(*) pocet
from dbo.[order details]
group by orderid
create unique clustered index orderid_ix on total1 (orderid)
set statistics io on
select * from total1
-- this is an indexed view
dbcc useroptions
set ansi_nulls on
-- partitioning view
union all -- union all data from tables..
use pubs
select * from authors
select au_id, au_fname, state into authors_ks from authors where state = 'ks'
select au_id, au_fname, state into authors_ut from authors where state = 'ut'
-- other databases, other servers... that 2 tables
create view authors_all
as
select * from authors_ks
union all
select * from authors_ut
-- partitioning view
select * from authors_all
update authors_all
set au_fname = 'nove'
select * from vancouver.pubs.dbo.authors --select from linked server
union all
select * from pubs.dbo.sales
select * from openquery (vancouver, 'select * from pubs.dbo.sales')
-- for every kind of server
openrowset -- ad-hoc link server
sp_addlinkedserver
-- day 4: locks and transactions
use northwind
update orders set orderdate = orderdate +1
if @@error <> 0 return
delete orders --this will fail
if @@error <> 0 return
select * from orders
-- if there's an error, it will be still updated ;(
begin tran -- opening transaction
select @@trancount -- number of transactions
commit tran
rollback tran -- you can use also NESTED transactions, but there is no effect
begin tran -- start of transaction
begin tran
select @@trancount
update orders set orderdate = 0
select * from orders
commit tran
rollback tran -- transaction has not been committed
if @@trancount <>0 rollback tran -- number of opened transaction
begin tran
update orders set orderdate = orderdate + 1
save tran SP1 -- saving transaction
select @@trancount
update orders set orderdate = 0
rollback tran SP1
commit tran -- // rollback
dbcc opentran ('northwind')
kill 52 -- killing process of a transaction
begin tran
update orders set orderdate = 0
-- exclusive zamky for - Insert, Update, Delete -- transaction duration
-- share zamky - Select -- select duration
rollback tran
begin tran
select @@trancount
update orders set orderdate = getdate() where orderid = 10248
-- this will use exclusive lock, until it's committed or rolled back
sp_lock
set implicit_transactions on
dbcc useroptions
select @@trancount
update orders set orderdate = getdate()
select @@trancount
rollback tran
set implicit_transactions off
-- maximal lock for table, minimal for a row
-- nonrepeatable read
-- DIRTY READ
-- begin tran
-- update..
-- then you can select from other select
-- you have to set it manually
-- first connect that locks database
begin tran
select @@trancount
update orders set orderdate = getdate()
rollback tran
-- from other connect ->
select * from orders (nolock)
-- -> dirty read (data which are not commited)
-- NONREPEATABLE READ
-- SELECT 1
-- <-- UPDATE
-- SELECT 2
-- you can hold a lock for select
begin tran
select * from orders (holdlock)
sp_lock @@spid
rollback tran
-- noone can modify data then!
begin tran
update orders set orderdate = 1 where orderid = 10248 -- locking one row and also indexes
sp_lock @@spid
-- Schema stability lock - you cannot drop
-- Schema manipulation - you are changing a structure of a table
-- Bulk update - tablock when inserting data to other table, you will lock
table and you are allowed to insert paralell data
bulk insert TABLOCK -- speeding up bcp
-- LOCK COMPATIBILITY
-- exclusive lock are not compatible with other locks
--
begin tran
update orders set orderdate = 100
sp_lock
select * from orders
set lock_timeout 1000
begin tran
select @@trancount
select * from orders
if @@error == 1222 print 'pokracovani'
set lock_timeout 1000
rollback tran
set implicit_transactions off -- set off default start of transaction
use northwind
select * from customers
create procedure up_vyber @country varchar(20)
as
select * from customers where country = @country
sp_helptext up_vyber
up_vyber 'cz' -- this is useless
alter procedure up_vyber @country varchar(20) = null -- default value
as
set nocount on -- will not return information on client
if @country is null
begin
print 'parametr'
return
end
if not exists (select * from customers where country = @country)
begin
print 'parametr'
return
end
select * from customers where country = @country
up_vyber 'null'
select top 3 * from orders
select top 3 * from [order details]
alter procedure up_vymaz @orderid int = null
as
set nocount on
if @orderid is null return 1
if not exists (select * from orders where orderid = @orderid) return 2
begin tran
delete [order details] where orderid = @orderid
if @@error <> 0 begin rollback tran return 3 end
delete orders where orderid = @orderid
if @@error <> 0 begin rollback tran return 4 end
commit tran
insert kontrola (orderid) values (@orderid)
up_vymaz 10248
declare @nk int
exec @nk = up_vymaz 10249
select @nk as 'navratovy kod'
up_vymaz 10248
select * from kontrola
use master
alter procedure sp_vypocet @c1 int = null, @c2 int = null, @out int = null
-- as system procedure (visible)
as
if (@c1 + @c2) is null return
set @out = @c1 * @c2
return
declare @vp int
exec sp_vypocet @c1 = 10, @c2 = 20
select @vp as 'vystupny parametr'
use pubs
declare @vp int
exec sp_vypocet 20, 30, @vp output
select @vp as 'vystupni paramettr'
create procedure sp_test
as
select db_name()
sp_test
--ownership chain
use northwind
alter procedure dbo.up_test --ownership chain
as
exec ('select * from ' + 'dbo.orders')
select * from dbo.orders
grant exec on up_test to guest
deny all on orders to guest
setuser 'guest' -- just sysadmin
sp_who2
select user_name()
select * from orders -- he can't do this
grant exec up_test to guest
deny all on orders to guest
setuser 'guest'
sp_who2
setuser 'BUILTIN\Administrators'
master..xp_logevent 50001, 'uzivatel zmazal zanznam c:'
use master
create procedure up_vymaz @orderid int = null
as
set nocount on
if @orderid is null return 1
if not exists (select * from orders where orderid = @orderid) return 2
begin tran
delete [order details] where orderid = @orderid
if @@error <> 0 begin rollback tran return 3 end
delete orders where orderid = @orderid
if @@error <> 0 begin rollback tran return 4 end
commit tran
insert kontrola (orderid) values (@orderid)
declare @str varchar(500)
set @str = 'uzivatel ' + suser_sname() + 'smazal c: ' + cast (@orderid as varchar(20))
exec master..xp_logevent 50001, @str
grant exec on up_vymaz to guest
grant exec on up_vymaz to jan
setuser 'jan'
up_vymaz 10252
select * from kontrola
setuser
sp_helprotect null, 'public'
-- TRIGGERS
use northwind
-- insert, update, delete only
setuser
alter trigger T1 on [order details] for insert, update, delete -- TRIGGER AFTER ACTIVITY
as
-- inserted, updated, deleted tables are created
if @@rowcount = 0 return
set nocount on -- no messages
select * from inserted
select * from deleted
--rollback tran
delete pubs..authors -- rollback return
sp_helptrigger [order details]
delete [order details] where orderid = 10251
update [order details] set quantity = 100 where orderid = 10254
delete [order details] where orderid = 1
-- trigger is a transaction !!!
-- when there is a rollback, trigger will be rolled back also
begin tran
delete [order details]
select @@trancount
delete [order details] where orderid = 10255
alter trigger T1 on [order details] for delete
as
if @@rowcount = 0 return
delete orders
from orders o join deleted d
on o.orderid = d.orderid
alter trigger T1 on [order details] for delete
as
if @@rowcount = 0 return
select * from deleted
delete [order details] where orderid = 10270
use pubs
select au_id, au_id as au_id_2, au_lname into test from authors
select * from test
create trigger T2 on test for update
as
if @@rowcount = 0 return
IF update (au_id)
update test set au_id_2 = 'nove'
from test
io_test
use pubs
create trigger T3 on test for update
as
if @@rowcount = 0 return
IF UPDATE (au_id)
update test set au_id_2 = 'nove'
from test join inserted
on test.au_id = inserted.au_id
update test set au_id = '111-11-1111' where au_id like '8%'
select * from test
select top 3 * from products
use northwind
create trigger t4 on [order details] for insert
as
if @@rowcount = 0 return
update products set unitsinstock = unitsinstock - quantity
from products p join inserted i
on p.productid = i.productid
select top 3 * from [order details]
select top 3 * from products
insert [order details] values (10254, 1, 10, 30, 0)
|
Freelance
