Software Engineer
CREATE DATABASE myNewDatabase
DROP DATABASE database_name [, database_name...]
USE master GO EXEC sp_dboption myOldData, 'Single User', True EXEC sp_renamedb 'myOldData', 'myNewData' EXEC sp_dboption myNewData, 'Single User', False GO
Traditionally known as CRUD - Create, Read, Update, and Delete
CREATE TABLE tablename (column_name column_type [(column_width[,column_precision])] ...)
CREATE TABLE practice ( fname VARCHAR(20) NOT NULL, lname VARCHAR(30) NOT NULL, hire_date SMALLDATETIME NULL, ssn CHAR(11) NOT NULL ) GO -- 'NOT NULL' is the default.
EXECUTE sp_help practice -- shows structure of table
ALTER TABLE XYZ ADD mi CHAR(1) NULL GO
Tables can also be created with the "SELECT INTO" construct SELECT select_list INTO new_table_name FROM table_list WHERE search_condition Note: if new_table_name starts with #, it will be put in the temp tables ##, it will be in temp tables and be global (accessable to all other sessions) Example using Temp tables:
SELECT * INTO #tempcustomers2 FROM customers SELECT * FROM #tempcustomers2
Temp tables go away when the server is restarted or at the end of the connection.
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyTableName' AND COLUMN_NAME = 'newcolumn') BEGIN ALTER TABLE MyTableName ADD newcolumn varchar(32) NOT NULL DEFAULT '' END
INSERT INTO table_name [(column_id1,column_id2...)]
VALUES (value1,value2,...)
examples:
INSERT INTO autos (1996,'ford') -- INTO is an optional keyword in SQLServer
INSERT tablename VALUES ('a','b',DEFAULT,i) -- DEFAULT is a key word
INSERT INTO title VALUES(1001,'The Odyssey','Homer',NULL)
INSERT INTO table (c17,c4,c8,c3) SELECT a,b,c,d FROM ...
INSERT INTO table (c17,c4,c8,c3) EXECUTE sp_mystored_procedure
INSERT INTO OtherDatabaseName..users SELECT * FROM users WHERE loginid ='mfincher'
UPDATE tbl SET col = value|col|expr UPDATE table_name SET column_id = expr WHERE condition
update users set password = 'newpass' WHERE user = 'quest'
update users set password = 'newpass' WHERE (UserID > 1) AND (UserID < 113)
update users set password = 'newpass', change='Y' WHERE user = 'quest'
UPDATE discounts SET discount = discount + .1 WHERE lowqty >= 100
UPDATE employees SET LNAME = UPPER(LNAME)
UPDATE titles SET ytd_sales = (SELECT SUM(qty) FROM sales WHERE sales.title_id = titles.title_id AND ord_date BETWEEN '01/01/94' AND '12/31/94')
INSERT adult SELECT ju.member_no, ad.street, ad.city, ad.state, ad.zip, ad.phone_no, DATEADD(YY, 1, GETDATE()) FROM juvenile ju -- aliasing juvenile to ju JOIN adult ad -- aliasing adult to ju ON ju.adult_member_no = ad.member_no WHERE (DATEADD(YY, 18, ju.birth_date) < GETDATE())
DELETE FROM mytable WHERE userid < 50 DELETE tablename -- deletes all rows in a table (see "DROP TABLE" to delete a table) TRUNCATE TABLE tablename -- deletes all rows, but doesn't log transaction DELETE titles WHERE title_id in (SELECT title_id FROM ...)
In the example below, "dtime" is a column containing the datetime. "datepart(yy,dtime)" produces the year, "str(datepart(dy,dtime),3)" produces a right justified day-of-the-year.
SELECT cast(datepart(yy,dtime) as varchar(4)) + ' ' + str(datepart(dy,dtime),3) as 'Year Day',
COUNT(time) AS 'Key Question Pages',
avg(time) AS 'avg time (msec)'
FROM sdrespondentpagetimes
WHERE name='Key'
group by cast(datepart(yy,dtime) as varchar(4)) + ' ' + str(datepart(dy,dtime),3)
order by cast(datepart(yy,dtime) as varchar(4)) + ' ' + str(datepart(dy,dtime),3) asc
The results:
Year Day Key Question Pages avg time (msec) --------- ------------------ --------------- 2004 51 13 768 2004 54 10 747 2004 56 6 1090 2004 57 34 702 ... 2004 351 1 6000 2004 362 14 548 2005 4 3 629 2005 5 20 834 2005 7 10 469 2005 10 1 3593 2005 11 1228 269
Top rated books on SQL Server development:
osql -Umoi -P"mypassword"
-Q"INSERT DATA_TABLE (column1, column2) values col1_value, col2_value"
osql -Umyuserid -P"mypassword" -itestSQL.SQL
osql -Umoi -P -Q"SELECT column1, column2 FROM DATA_TABLE" -oOutput_file
A better way to handle the password is to set an environmental variable, "OSQLPASSWORD", so the "-U" option may be omitted.
SELECT DISTINCT name FROM SYSDATABASES
SELECT SERVERPROPERTY('ProductLevel')
Returns:
'RTM' = shipping version.
'SPn' = service pack version
'Bn', = beta version.
SETUSER 'mynewname'
GO
(SQL statements)
EXECUTE sp_adduser 'newloginid'
EXECUTE sp_addlogin 'newloginid','password'
EXEC sp_helpdb -- or for one database EXEC sp_helpdb 'dbname'
USE mydatabase
EXECUTE sp_adduser newloginid
GRANT [EXECUTE|ALL] ON TableName TO myloginid
dbcc checkdb(mydb)
also try dbcc checkalloc(db4)
SELECT db_name()
USE master SELECT name FROM sysdatabases
SELECT getDate()
SELECT First = au_fname,
au_lname 'Last Name',
au_id AS 'Identification Number'
FROM authors
Produces:
First Last Name Identification Number
-----------------------------------------------------------
Johnson White 172-32-1176
Marjorie Green 213-46-8915
SELECT DISTINCT column_name FROM mytable -- note: this also does an implicit sort
SELECT column_name, COUNT(column_name) as mycount FROM table_name
GROUP BY column_name ORDER BY mycount [ASC | DESC]
SELECT tablea.A1, tableb.B1 FROM tablea, tableb
WHERE tablea.mykey = tableb.mykey
UPDATE table1 SET mycolumn = '2' WHERE userid IN
( SELECT userid FROM table2 WHERE table2.dissat <> 'somevalue')
SELECT a.title_id, a.au_id, b.au_id FROM titleauthor a INNER JOIN titleauthor b ON a.title_id = b.title_id WHERE a.au_id < b.au_id
UPDATE mytable SET mycolumn = 'x-'+mycolumn
| Wildcard | Meaning |
|---|---|
| % | Any string of zero or more characters |
| _ | Any single character |
| [ ] | Any single character within the specified range ([a-f]) or set |
| ([abcdef]) | |
| [^] | Any single character not within the specified range ([^a-f]) or set ([^abcdef]) |
LIKE 'Mc%' searches for all names that begin with the letters "Mc" (McBadden). SELECT column_name FROM table WHERE mystring LIKE '%[XYZ]' To find all fields which contain a carriage return: SELECT userid, Zip FROM mytable WHERE Zip like '%'+char(10)+'%'
(note this can be very dangerous since it affects all subsequent commands)
SET rowcount 4 SELECT TableName,ColumnName,Language Title,LU_Table,Description,Logic,Type FROM DataGroup SET rowcount 0 -- turns off rowcounting
USE sqldemo SELECT * FROM authors --23 rows UNION SELECT * FROM newauthors --8 rows ORDER BY authors.au_lname
UNION will surpress duplicates, use UNION ALL to show all rows
/* ** This query will search through the member_no column looking for ** member numbers that have duplicate values and also show the number ** of duplicates found for each member number. */ SELECT member_no, Number_of_Dups = count(*) FROM member GROUP BY member_no HAVING count(*) > 1
CREATE PROCEDURE pname @var vartype[=defaultvalue][,@var2 vartype] AS ... GO
DECLARE @varname type[,@varname type]* -- to define a variable DECLARE @x int SELECT @x = 5
CREATE PROCEDURE emplist AS SELECT empname, title, salary FROM emp ORDER BY title GO
CREATE PROCEDURE AuthorState @statevar CHAR(2) = '__' -- if no arg passed, '__' is the default AS SELECT state, au_fname, au_lname FROM authors WHERE state LIKE @statevar ORDER BY au_lname GO To test it: EXECUTE AuthorState 'UT'
SELECT @LanguageStringID = MAX(LanguageStringID) FROM LanguageString
IF EXISTS ( SELECT name FROM sysobjects WHERE type = 'P' AND name = 'addadult' ) DROP PROCEDURE addadult
or you can do this:
IF OBJECTPROPERTY(object_id('dbo.YourStoredProcName'), N'IsProcedure') = 1
DROP PROCEDURE [dbo].[YourStoredProcName]
GO
CREATE PROCEDURE addadult
@lastname shortstring,
@firstname shortstring,
@middleinitial letter = NULL,
@street shortstring = NULL,
@city shortstring = NULL,
@state statecode = NULL,
@zip zipcode = NULL,
@phone_no phonenumber = NULL
AS
DECLARE @member_no member_no
IF @middleinitial = NULL OR
@street = NULL OR
@city = NULL OR
@state = NULL OR
@zip = NULL OR
@phone_no = NULL
BEGIN
PRINT " "
PRINT " Please reenter this Adult Member."
PRINT " You must provide Last name, First name, Middle initial, "
PRINT " Street, City, State, Zip, and Phone number."
PRINT " "
RETURN
END
BEGIN TRANSACTION
SELECT @member_no = (SELECT max(member_no) FROM member HOLDLOCK) + 1
INSERT member
( member_no, lastname, firstname, middleinitial, photograph)
VALUES
(@member_no, @lastname, @firstname, @middleinitial, NULL )
IF @@error <> 0
BEGIN
ROLLBACK TRAN
RETURN
END
INSERT adult
( member_no, street, city, state, zip, phone_no, expr_date)
VALUES
(@member_no, @street, @city, @state, @zip, @phone_no, dateadd(year,1,getdate()) )
IF @@error != 0
BEGIN
ROLLBACK TRAN
RETURN
END
PRINT " "
PRINT " *** Adult Member added *** "
PRINT " "
COMMIT TRANSACTION
GO
CREATE PROCEDURE overdue_books
AS
SELECT member.lastname,copy_no,due_date FROM loan
JOIN member ON loan.member_no = member.member_no
WHERE DATEDIFF(DAY,GETDATE(),due_date)>1
GO
Example using the OUTPUT parameter
** This script file creates a procedure, LOANCOUNT, ** that returns a status of 0 if a member has any ** loans. If a member has no loans, it returns a ** status of -1. USE library GO CREATE PROC loancount @member_no member_no, @loancount int OUTPUT AS IF EXISTS (SELECT * FROM loan WHERE member_no = @member_no) BEGIN SELECT @loancount=COUNT(*) FROM loan WHERE member_no = @member_no RETURN (0) END ELSE RETURN (-1) GO --NOTE: if any syntax errors in a batch, nothing in batch runs -- all the statements are rolled together for syntax checking -- if any statements have problems the "GO" is not executed.
IF(condition) BEGIN ... END ELSE BEGIN ... END
Example:
IF (SELECT COUNT(member_no)
FROM loan
WHERE member_no = @param) <= 4
RETURN 1
ELSE
RETURN 2
GO
set nocount on declare @i int SELECT @i = 0 while (@i < 10) BEGIN SELECT 'i = ' + convert(char(5),@i) SELECT @i = @i + 1 END set nocount off
Code to insert 3000 rows into the database
DECLARE @i INT SET @i=10000 WHILE(@i <= 30000) BEGIN INSERT INTO mytable (date, thread, level, logger, server, userid, message) VALUES (GETDATE(),'0000','INFO','XYZ1','LogFile',@i,'my message') SET @i=@i+1 END
Using the WHILE statement this stored procedure deletes records older than a specific number of days. Deleting all the rows can cause the database to freeze or the query can timeout.
-- trims table to only keep specific number of previous days in table which must have a datetime field named 'dtime'
-- example: EXEC sd_trimTable 'practice', 50
-- this would remove all rows older than 50 days in slices of 1000
IF OBJECTPROPERTY(object_id('dbo.sd_trimTable'), N'IsProcedure') = 1
DROP PROCEDURE dbo.sd_trimTable
GO
CREATE PROCEDURE sd_trimTable @tableName char(128), @trimDays int
AS
DECLARE @trimDate datetime
DECLARE @cmd char(240)
DECLARE @counter int
SET @trimDate = DATEADD(day,-@trimDays,GETDATE())
SET @counter = 1
SET @cmd = 'DELETE TOP (1000) FROM '+ (@tableName)+' WHERE dtime < ''' + CONVERT(VARCHAR(10), @trimDate, 111) + ''''
SELECT @cmd
WHILE 1 = 1
BEGIN
EXEC (@cmd)
IF @@ROWCOUNT = 0
BREAK
WAITFOR DELAY '00:00:00.2' --give other processes time to access table
END
GO
DECLARE @next VARCHAR(30) SELECT @next = ' ' --Makes it "not null" WHILE @next IS NOT NULL BEGIN SELECT @next = MIN(name) FROM sysobjects WHERE type = 'U' --User Tables AND name > @next ORDER BY name EXECUTE sp_spaceused @next END
CREATE PROCEDURE checkmem
@param INT
AS
IF (SELECT COUNT(member_no)
FROM loan
WHERE member_no = @param) <= 4
RETURN 1
ELSE
RETURN 2
GO
To use this construct:
DECLARE @ret INT
EXECUTE @ret = checkmem 100
set nocount on
declare @i int SELECT @i = 2
declare @si char(1)
while (@i < 5)
BEGIN
SELECT @si = convert(char(1),@i)
SELECT 'i = ' + convert(char(5),@i)
exec('CREATE DATABASE db'+@si+' ON sndat = 1 LOG ON snlog = 1')
SELECT @i = @i + 1
END
set nocount off
sp_tables -- to show a list of tables in a database: -- or SELECT name FROM sysobjects WHERE type = 'u'
You may need to add the 'NOT NULL' option if your column is an indexed column
ALTER TABLE MyTableName ALTER COLUMN MyColumnName varchar(32)
Sometimes you can't modify a column if it has a constraint. First you must drop the constraint, then modify the column, then re-add the constraint.
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[SampleSources]') AND name = N'PK_SampleSources') ALTER TABLE [dbo].[SampleSources] DROP CONSTRAINT [PK_SampleSources] alter table SampleSources Alter Column name varchar(65) NOT NULL /****** Object: Index [PK_SampleSources] Script Date: 08/06/2010 09:11:22 ******/ ALTER TABLE [dbo].[SampleSources] ADD CONSTRAINT [PK_SampleSources] PRIMARY KEY CLUSTERED ( [name] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
You have to declare and set the variable
declare @id int select @id = 4140 SELECT userid FROM mytable WHERE userid > @id ORDER BY userid
set nocount on
declare @next varchar(30)
SELECT @next = ' '
while @next IS NOT NULL
BEGIN
SELECT @next = MIN(name) FROM sysobjects WHERE type = 'u' and name > @next
SELECT 'next is ' + @next
IF @next IS NOT null
BEGIN
EXECUTE ('UPDATE STATISTICS ' + @next)
END
END
set nocount on
set nocount on
declare @tablename varchar(90)
declare @mycount varchar(90)
SELECT @mycount = ' '
SELECT @tablename = ' '
while @tablename IS NOT NULL
BEGIN
SELECT @tablename = MIN(name) FROM sysobjects WHERE type = 'u' and name > @tablename
IF @tablename IS NOT null
BEGIN
SELECT @mycount = 'select '''+@tablename+''', count(*) from ' + @tablename
execute (@mycount)
END
END
set nocount on
use master
declare @next varchar(30)
SELECT @next = ' '
while @next IS NOT NULL
BEGIN
SELECT @next = MIN(name) FROM sysdatabases WHERE dbid > 5 AND name > @next
--SELECT 'next is ' + @next
IF @next IS NOT null
BEGIN
SELECT 'Looking at database',@next
END
END
set nocount on
RAISERROR('** this is my message',16,1) WITH LOG
RAISERROR('** this is my message',servity,state) WITH LOG
--error serverity 1-16 ok, 17-18 serious, 19-25 fatal
ALTER DATABASE library ON library_dev1 = 1 -- MEG to increase by
To increase size of log file:
ALTER DATABASE library ON logdevname = size in 2k
PRINT 'this is a print'
PRINT 'SELECT sorta does the same thing, but not quite, sometimes you need a PRINT'
-- SIMPLE CASE EXPRESSION
SELECT Category =
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'business' THEN 'Business'
WHEN 'psychology' THEN 'Psychology'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not Yet Categorized'
END,
CONVERT(varchar(30), title) AS 'Shortened Title',
Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type
COMPUTE AVG(price) BY type
SELECT m.member_no,
m.lastname,
'Member Type' =
CASE
WHEN m.member_no IN
(SELECT member_no
FROM juvenile j
WHERE j.member_no = m.member_no)
THEN 'Juvenile'
ELSE 'Adult'
END,
expr_date
FROM member m, adult a, juvenile j
WHERE j.adult_member_no = a.member_no
AND (m.member_no = a.member_no OR m.member_no = j.member_no)
AND expr_date < GETDATE()
ORDER BY m.member_no
SELECT SUM(ytd_sales) FROM titles to get the number of something: SELECT COUNT(*) FROM Datagroup WHERE Language = 'ENU' COUNT([ALL|DISTINCT]expression) -- note: count can have options - GROUP BY - COMPUTE or COMPUTE BY - WITH CUBE or WITH ROLLUP
SELECT respondents.surveyName,
min(minValue) as 'desired min age',
min(a1) as 'actual min age',
abs(min(minValue) - min(a1)) as 'diff min age',
max(maxValue) as 'desired max age',
max(a1) as 'actual max age',
abs(max(maxValue) - max(a1)) as 'diff max age',
(min(minValue)+max(maxValue))/2 as 'desired avg age' ,
avg(a1) as 'actual avg age',
abs((min(minValue)+max(maxValue))/2 - avg(a1)) as 'diff avg age',
abs(min(minValue) - min(a1))+abs(max(maxValue) - max(a1))+ abs((min(minValue) +
max(maxValue))/2 - avg(a1)) as 'Total Skew'
from respondents join surveyDirectorQuotas on surveyDirectorQuotas.surveyName =
respondents.surveyName
--where respondents.surveyName = 'EClientE2'
and quotaQuestion = '_age'
group by respondents.surveyName
COMPUTE count(respondents.surveyName),
sum(abs(min(minValue) - min(a1))+abs(max(maxValue) - max(a1))+
abs((min(minValue)+max(maxValue))/2 - avg(a1)))
SELECT AVG(NULLIF(royaltyper, 100))
AS 'Average Shared Royalty %'
FROM titleauthor
Wonderful function that returns a specified value (in this example -1) if the query returns null.
SELECT ISNULL( (SELECT userid FROM billing WHERE pid = @pid) ,-1)
/* What database is now open? */ SELECT "Open Database is:",DB_NAME()
/* What is it's unique database identifier? */ SELECT "Database ID is:",DB_ID()
/* What is the current host machine name? */ SELECT "Host Name is:",HOST_NAME()
/* What is the login name of the current user? */ SELECT "Login Name is:", SUSER_NAME()
/* What is the database user name of the current user? */ SELECT "DB User Name is:",USER_NAME()
SELECT price,
(price * 1.1) AS 'New Price',
title
FROM titles
--Round to 2 decimals
SELECT price,
CONVERT(MONEY, (price * 1.1)) AS 'New Price',
title
FROM titles
ABS (numeric_expr) Absolute value of the numeric
CEILING (numeric_expr) Smallest integer greater than or equal to
the numeric expression.
COS (float_expr) Trigonometric cosine of the specified angle (in radians)
RAND ([seed]) Random approximate numeric (float) value between 0 and 1,
ROUND (numeric_expr, length) Numeric expression rounded off to the
length (or precision)
SELECT price,
FLOOR(price) AS 'Floor Price',
CEILING(price) AS 'Ceiling Price'
FROM titles
BEGIN TRANSACTION -- forces all or none of the following commands INSERT ... INSERT ... IF() ROLLBACK TRANSACTION COMMIT TRANSACTION
IF @@error <> 0 BEGIN ROLLBACK TRAN RETURN END
SELECT * FROM authors (nolock) -- will read even locked pages SET TRANSACTion isolation level READ UNCOMMITTED -- lets reader look SELECT * FROM authors sp_tableoption tablename,'INSERT row lock', true -- to turn on InsertRowLock
CONVERT (type, var) Converts types (eg, convert(real,'12.5'))
RTRIM (char_expr) Removes trailing blanks.
LOWER (char_expr) Converts uppercase character data to lowercase.
LTRIM (char_expr) Removes leading blanks.
SUBSTRING(expression, start, length) Returns part of a
character or binary string.
STUFF (char_expr1, start, length, char_expr2) Deletes length
characters FROM char_expr1 at start and then inserts
char_expr2 into char_expr1 at start.
UPPER (char_expr) Converts lowercase character data to uppercase.
Examples:
SELECT 'The price for ' +
CONVERT(char(38), title) -- doing a substring but appends spaces to make it 38
+ ' is $' + CONVERT(varchar(10), price) FROM titles
SELECT fname + ' ' + lname + ' was hired on ' +
CONVERT(char(8), hire_date, 1)
FROM employee
SELECT LOWER(lastname + SUBSTRING(firstname,1,2)) FROM member WHERE lastname = 'ANDERSON'
--Round to 2 decimals
SELECT price,
CONVERT(MONEY, (price * 1.1)) AS 'New Price',
title
FROM titles
-- shows last name, authors first initial
SELECT au_lname + ', ' + SUBSTRING(au_fname,1,1) + '.' AS 'Name',
au_id
FROM authors
/* What is the current date and time? */ SELECT 'Current date and time is',GETDATE() /* What is a week from today? */ SELECT 'A week from today is',DATEADD(WEEK,1,GETDATE()) /* How many days between now and end of century? */ SELECT 'Days until Year 2000 is',DATEDIFF(DAY,GETDATE(),'01/01/2000') /* What is the name of the current month? */ SELECT 'Name of this month is',DATENAME(MONTH,GETDATE()) /* What is the name of the day for today? */ SELECT 'Day of the week is',DATENAME(WEEKDAY,GETDATE()) /* What is the day of the month? */ SELECT 'Current day of month is',DATEPART(DAY, GETDATE()) /* get date differences in seconds */ SELECT userID, DATEDIFF(second,dtime , getdate()) FROM respondents
How to sum counts over a day into a total. Sometimes you have a field you want to find the total for a day, like how many invoices did we send each day of this last year. Here's and example of getting a count for every day of the year
SELECT datepart(year,dtime) AS 'year',datepart(month,dtime) AS 'month',datepart(day,dtime) AS 'day', count(*) AS 'count' FROM sdRespondents GROUP BY datepart(year,dtime),datepart(month,dtime),datepart(day,dtime) ORDER BY datepart(year,dtime),datepart(month,dtime),datepart(day,dtime)
ORDER BY sorts results sets
SELECT column_name, COUNT(column_name) as mycount FROM table_name ORDER BY mycount [ASC | DESC]
SELECT colA,colB,colC FROM table1 ORDER BY colB, colC
and you can add in the direction of the sort
SELECT colA,colB,colC FROM table1 ORDER BY colB ASC, colC DESC
SELECT colA,colB,colC FROM table1 ORDER BY 2, 3
SELECT member_no,loan=count(member_no) FROM loanhist GROUP BY member_no HAVING count(member_no) > 50 member_no loan --------- ----------- 2 442 3 130 4 104 5 260 6 78
SELECT member_no,
date=loanhist.out_date,
fine=loanhist.fine_assessed,
paid=loanhist.fine_paid
FROM loanhist
WHERE (member_no BETWEEN 100 AND 150) AND (fine_assessed - ISNULL(fine_paid,0) > 0)
ORDER BY member_no
COMPUTE sum(fine_paid), sum(fine_assessed) BY member_no
Joins allow you to retrieve columns from two or more tables usually within the same database, but also among different databases.
SELECT pub_name, title FROM titles INNER JOIN publishers --Note: INNER is optional ON titles.pub_id = publishers.pub_id SELECT pub_name, title FROM titles, publishers WHERE titles.pub_id = publishers.pub_id
% use Join Types 90 Inner 0 Cross 6 Outer 1 Self 3 Union
SELECT stor_name, qty, title FROM titles INNER JOIN sales ON titles.title_id = sales.title_id INNER JOIN stores ON stores.stor_id = sales.stor_id
SELECT co.isbn,
co.copy_no,
co.on_loan,
ti.title,
ti.author,
it.language,
it.cover,
it.loanable
FROM title ti
INNER JOIN copy co
ON ti.title_no = co.title_no
INNER JOIN item it
ON it.title_no = co.title_no
WHERE
co.isbn = 1500 OR co.ISBN = 1000
ORDER BY co.isbn
SELECT member.member_no,
lastname,firstname,middleinitial,
isbn,
log_date
FROM member LEFT OUTER JOIN reservation
ON member.member_no = reservation.member_no
WHERE member.member_no = 340 OR member.member_no = 341
ORDER BY member.member_no
SELECT member_no , count(*) FROM reservation WHERE isbn IN (SELECT item.isbn FROM title JOIN item ON title.title_no = item.title_no WHERE title LIKE "%Programmer%") group by member_no
CREATE TABLE table1 ( mykey int NOT NULL , letter1 char(1) NOT NULL, ) INSERT INTO table1 (mykey,letter1) VALUES (1,'a') INSERT INTO table1 (mykey,letter1) VALUES (2,'b') INSERT INTO table1 (mykey,letter1) VALUES (3,'c') CREATE TABLE table2 ( mykey int NOT NULL , letter2 char(1) NOT NULL, ) INSERT INTO table2 (mykey,letter2) VALUES (1,'d') INSERT INTO table2 (mykey,letter2) VALUES (2,'e') INSERT INTO table2 (mykey,letter2) VALUES (4,'g')
| TABLE: table1 | |
|---|---|
| mykey [int(10)] |
letter1 [char(1)] |
| 1 | a |
| 2 | b |
| 3 | c |
| TABLE: table2 | |
|---|---|
| mykey [int(10)] |
letter2 [char(1)] |
| 1 | d |
| 2 | e |
| 4 | g |
SELECT table1.mykey,table1.letter1,table2.letter2 FROM table1 INNER JOIN table2 ON table1.mykey = table2.mykey which produces: mykey letter1 letter2 ----------- ------- ------- 1 a d 2 b e
SELECT table1.mykey,table1.letter1,table2.letter2 FROM table1 CROSS JOIN table2 which produces: mykey letter1 letter2 ----------- ------- ------- 1 a d 1 a e 1 a g 2 b d 2 b e 2 b g 3 c d 3 c e 3 c g
SELECT table1.mykey,table1.letter1,table2.letter2 FROM table1 LEFT OUTER JOIN table2 ON table1.mykey = table2.mykey
SELECT table1.mykey,table1.letter1,table2.letter2 FROM table1 RIGHT OUTER JOIN table2 ON table1.mykey = table2.mykey mykey letter1 letter2 ----------- ------- ------- 1 a d 2 b e (null) (null) g
SELECT table1.mykey,table1.letter1,table2.letter2 FROM table1 FULL OUTER JOIN table2 ON table1.mykey = table2.mykey mykey letter1 letter2 ----------- ------- ------- 1 a d 2 b e 3 c (null) (null) (null) g
CREATE TABLE table3 ( mykey int NOT NULL , letter3 char(1) NOT NULL, ) INSERT INTO table3 (mykey,letter3) VALUES (1,'d') INSERT INTO table3 (mykey,letter3) VALUES (2,'e') INSERT INTO table3 (mykey,letter3) VALUES (5,'h') Then we execute, SELECT table1.mykey,table1.letter1,table2.letter2,table3.letter3 FROM table1 FULL OUTER JOIN table2 ON table1.mykey = table2.mykey FULL OUTER JOIN table3 ON table1.mykey = table3.mykey Which yields: mykey letter1 letter2 letter3 ----------- ------- ------- ------- 1 a d d 2 b e e 3 c (null) (null) (null) (null) g (null) (null) (null) (null) h
SQL Server considers a NULL to be ambiguous, so one NULL is not the same as another NULL. You can force a JOIN if you know the NULLs should match by using something like "(c.color = p.color OR (c.color IS NULL AND p.color IS NULL))".
CREATE TABLE colors (
team varchar(16) NOT NULL,
color varchar(32) NULL,
)
CREATE TABLE points (
color varchar(32) NULL,
points int
)
INSERT INTO colors VALUES ('lobsters','red')
INSERT INTO colors VALUES ('swans','white')
INSERT INTO colors VALUES ('jellyfish',NULL)
INSERT INTO points VALUES ('red',100)
INSERT INTO points VALUES ('white',90)
INSERT INTO points VALUES (NULL,80)
SELECT * FROM colors c JOIN points p ON c.color = p.color
-- returns:
--lobsters red red 100
--swans white white 90
SELECT * FROM colors c JOIN points p ON (c.color = p.color OR (c.color IS NULL AND p.color IS NULL))
-- returns:
--lobsters red red 100
--swans white white 90
--jellyfish NULL NULL 80
--This is a 'classic' subquery. --Show all titles that have a price --larger than average of all titles. USE pubs SELECT price, title FROM titles WHERE price > (SELECT AVG(price) FROM titles)
SELECT title_id AS Title,
qty AS Quantity,
(SELECT SUM(qty) FROM sales) AS 'Total Sales',
(CONVERT(money,qty)/(SELECT SUM(qty) FROM sales))*100
AS '% of Total'
FROM sales
SELECT title_id, title FROM titles WHERE title_id IN (SELECT title_id FROM sales)
Triggers are bits of code that are executed when an operation occurs on a table. Triggers can be set for INSERT,UPDATE, or DELETE
CREATE TRIGGER deltrig ON emp FOR DELETE AS DECLARE @currentuser VARCHAR(30) SELECT @currentuser = 'Sorry, ' + USER_NAME() PRINT @currentuser PRINT 'Employees can''t be deleted!' ROLLBACK TRANSACTION GO
SELECT name, crdate FROM sysobjects WHERE type = 'TR' EXECUTE sp_helptext deltrig -- show code, if not encrypted EXECUTE sp_depends deltrig -- what tables does it affect
drop trigger IF EXISTS ( SELECT name FROM sysobjects WHERE type = 'TR' AND name = 'member_insert' ) DROP TRIGGER member_insert
CREATE TRIGGER member_insert
ON member
FOR INSERT
AS
/*
** Don't allow duplicate primary keys.
** This is normally done via a unique
** index rather than a trigger. It is
** included here just as an example.
*/
IF (SELECT count(*)
FROM member, inserted
WHERE member.member_no = inserted.member_no) > 1
BEGIN
RAISERROR ('Transaction will not be processed. Two members cannot have the same member_no.',10,1)
ROLLBACK TRANSACTION
END
ELSE
/*
** Auto generate a primary key value, if needed
** (A zero got in there because it is the default;
** ie, the user did not enter a member number.)
** Works only if one row at a time is being added.
*/
IF (SELECT count(*)
FROM member
WHERE member.member_no = 0) > 0
BEGIN
UPDATE member
SET member_no = (SELECT max(member_no) FROM member) + 1
WHERE member_no = 0
END
GO
An identity forces SQL server to have a unique value in a field. The starting value and the increment value can be set as follows
CREATE TABLE stores2 ( stor_id int identity(1000,10) NOT NULL , -- starts at 1000, increments by 10 stor_name varchar (40) NULL , )
To see the values of these,
SELECT IDENT_SEED('class'), IDENT_INCR('class') -- shows initial identity and increment
SET IDENTITY_INSERT [TableName] ON
Example:
SET IDENTITY_INSERT Pubs..class ON -- allows an identity to be inserted
SELECT MAX(IDENTITYCOL) FROM stores2 -- identitycol is a keyword refering to a tables' identity column
-- Defining constraints Primary Key Unique Foreign Key Default Check in syscomments a field called 'text' contains your constraits sp_helpconstraint or sp_help <tablename> -- more friendly
Comments are surrounded with '/*' and '*/', but cannot, for some bizzare reason contain a 'GO' command. Everything after a '--' is a comment
Constraints can be set on the value of valid fields.
CREATE TABLE Tbl_lvl ( ItemCode CHAR(6) NOT NULL, UnitCost SMALLMONEY NOT NULL, UnitPrice SMALLMONEY NOT NULL, CONSTRAINT pricediff CHECK (UnitPrice > UnitCost) ) GO
CREATE TABLE Payroll
(
Empno CHAR(3) NOT NULL,
Checkno CHAR(10) NOT NULL
CONSTRAINT U_Checkno
UNIQUE NONCLUSTERED (checkno),
Paydate SMALLDATETIME NOT NULL
CONSTRAINT DF_Paydate
DEFAULT GETDATE(),
Amount SMALLMONEY NOT NULL
CONSTRAINT CK_Amount
CHECK (Amount BETWEEN 10000 AND 100000),
CONSTRAINT PK_Payroll
PRIMARY KEY CLUSTERED
(empno,checkno,paydate)
WITH FILLFACTOR=25,
CONSTRAINT FK_Payroll
FOREIGN KEY (empno) REFERENCES emp (empno)
)
-- must drop constraint before index
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'member_ident')
ALTER TABLE member
DROP CONSTRAINT member_ident
IF EXISTS (SELECT name FROM sysindexes WHERE name = 'member_ident')
DROP INDEX member.member_ident
ALTER TABLE member
ADD CONSTRAINT member_ident PRIMARY KEY CLUSTERED (member_no) WITH FILLFACTOR = 90
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'state_default')
ALTER TABLE adult
DROP CONSTRAINT state_default
GO
ALTER TABLE adult
ADD CONSTRAINT state_default DEFAULT 'WA' FOR state
GO
to add a primary key restaint:
alter table mytable ADD primary key (mykeyid, myothercolumn)
CREATE VIEW authorlist
(AuthorName, Location)
AS
SELECT SUBSTRING(au_fname + ' ' + au_lname,1,25),
SUBSTRING(city + ', ' + state + ' ' + zip,1,25)
FROM authors
GO
SELECT * FROM authorlist
--
-- see your views:
SELECT name, crdate
FROM sysobjects
WHERE type = 'V'
-- to see your source code for the view
EXECUTE sp_helptext AuthorList
EXECUTE sp_depends AuthorList -- list tables needed for this view
CREATE VIEW Managers
WITH ENCRYPTION
AS
SELECT empno, empname, title
FROM emp
WHERE title='MANAGER'
WITH CHECK OPTION -- updates/inserts into this view must satisfy WHERE clause
GO
SELECT * FROM managers
sp_helptext managers
--This should fail the CHECK
INSERT INTO managers
VALUES ('999', 'GATES, BILL', 'MCT') -- fails because MCT != MANAGER
What Service Packs does my SQL Server have installed?
try
SELECT @@version
-- old style
IF EXISTS ( SELECT name FROM sysobjects
WHERE type = 'V' AND name = 'stuff' )
DROP VIEW stuff
-- new style
IF OBJECT_ID('stuff', 'V') IS NOT NULL
DROP VIEW stuff
go
IF OBJECT_ID('traffic_data', 'U') IS NOT NULL
DROP TABLE traffic_data
GO
/* C = CHECK constraint D = DEFAULT (constraint or stand-alone) F = FOREIGN KEY constraint PK = PRIMARY KEY constraint P = SQL stored procedure FN = SQL scalar function U = Table (user-defined) UQ = UNIQUE constraint V = View X = Extended stored procedure */
If the results of your queries do not always have to be perfect, you can increase query speed and reduce contention by using "READ UNCOMMITTED", which does not lock a table during a SELECT and allows "dirty" reads.
SELECT COUNT(*) FROM dbo.Authors WITH(NOLOCK)
This will not lock the table and make the query faster.
Use the following command to make an entire session use no locking
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED GO
CREATE INDEX [CLUSTERED|NONCLUSTERED] name ON table (col1,col2,...) EXECUTE sp_helpindex emp shows info on index CNTRL -- skips to next heading limit: composite index length < 900 chars
SET SHOWPLAN ON -- debug statement to show if indexes are being used GO SELECT * FROM authors WHERE city = 'Kent' --table scan SELECT * FROM authors WHERE au_id = '123-45-6789' --clustered index SET SHOWPLAN OFF GO --
-------------- -- OPTIMIZER HINTS -------------- SET SHOWPLAN ON GO SELECT au_lname, au_fname, phone FROM authors (INDEX = 1) -- INDEX=0 is none, INDEX=indexname WHERE au_lname = 'Smith' SET SHOWPLAN OFF GO
-------------- -- UPDATE STATISTICS -- updates statistics that the optimizer uses to determine which index to use on a query -------------- --Plug in a value for TABLENAME
SELECT 'Index Name' = sysindexes.name,
'Statistics Date' = STATS_DATE(sysindexes.id, sysindexes.indid)
FROM sysobjects, sysindexes
WHERE sysobjects.name = 'authors'
AND sysobjects.id = sysindexes.id
UPDATE STATISTICS authors
-- show fragmentation
DECLARE @tbl INT
SELECT @tbl = OBJECT_ID('sales')
DBCC SHOWCONTIG (@tbl)
------------- check databases
DBCC CHECKDB -- should be run once a week on all database,
-- should be done before a backup
--
daily - dump database, update statistics on all indexes
hourly - dump transaction log
weekly - drop and recreate indexes
-- show performance data
SET SHOWPLAN ON -- shows indexes being used
SET STATISTICS IO ON
SET STATISTICS TIME ON --
-- drop an index if it exists
IF EXISTS (SELECT name FROM sysindexes WHERE name = 'reserve_item_link')
DROP INDEX reservation.reserve_item_link
GO
-- example of showing the elapsed time
DECLARE @message char(255) DECLARE @began datetime SELECT @began = GETDATE()
CREATE UNIQUE INDEX reserve_ident ON reservation (member_no, isbn)
WITH FILLFACTOR = 75
CREATE INDEX reserve_item_link ON reservation (isbn)
SELECT @message = 'Time (in minutes:seconds) to create reservation related indexes. '
+ CONVERT( char(2), DATEDIFF(ss,@began,GETDATE())/60 ) + ':'
+ CONVERT( char(2), DATEDIFF(ss,@began,GETDATE())%60 )
PRINT @message
GO
SELECT 'Connections: ',@@connections SELECT 'CPU Busy ',@@cpu_busy SELECT 'Cursor Rows ',@@cursor_rows SELECT 'DBTS ',@@dbts SELECT 'Error ',@@error SELECT 'Fetch Status ',@@fetch_status SELECT 'Identity ',@@identity SELECT 'Idle ',@@idle SELECT 'IO Busy ',@@io_busy SELECT 'Language ID ',@@langid SELECT 'Language ',@@language SELECT 'Max Connections ',@@max_connections SELECT 'Max Precision ',@@max_precision SELECT 'MS Version ',@@microsoftversion SELECT 'Nest Level ',@@nestlevel SELECT 'Options ',@@options SELECT 'Pack Received ',@@pack_received SELECT 'Pack Sent ',@@pack_sent SELECT 'Packet Errors ',@@packet_errors SELECT 'Procedure ID ',@@procid SELECT 'Row Count ',@@rowcount -- how many rows were effected on last operation SELECT 'Server Name ',@@servername SELECT 'Service Name ',@@servicename SELECT 'SPID ',@@spid SELECT 'Text Size ',@@textsize SELECT 'Time Ticks ',@@timeticks SELECT 'Total Errors ',@@total_errors SELECT 'Total Read ',@@total_read SELECT 'Total Write ',@@total_write SELECT 'Tran Count ',@@trancount SELECT 'Version ',@@version
rem rem BCPDEMO.BAT rem rem This batch file exports all AUTHORS FROM the rem PUBS database to an ASCII file called RESULTS.TXT rem rem /c indicates 'character' format vs. /n for 'native' format rem /t indicates the field terminator (default is TAB) rem /S indicates the Server to connect to rem /U indicates the Login ID rem /P is the password (if any) rem rem Type in BCP with no options to see parameter summary rem rem After you run this, go into NOTEPAD and rem check out the results in 'results.txt' rem rem *** CHANGE SERVER NAME BELOW BEFORE RUNNING *** bcp pubs.dbo.authors out results.txt /c /t"|" /Sstudentx /Usa /P
--Display all 'registered' ESP's
EXECUTE sp_helpextendedproc
-- to get the last inserted identity
INSERT Respondents (test) VALUES ('N') SELECT @@IDENTITY
---- misc xp_*
USE master
GO
--Display all devices
EXECUTE xp_cmdshell 'dir c:\mssql\data\*.dat /os'
PRINT ''
--Send a message to the NT Event Log
EXECUTE xp_logevent 60000, 'Stop sign - worst kind!', error
PRINT ''
--Show current NT accounts that can access SQL Server
EXECUTE xp_logininfo
PRINT ''
--Display current SQL Server version and build info
EXECUTE xp_msver
PRINT ''
--Display all local NT groups
EXECUTE xp_enumgroups
--XP_REGREAD allows you to read Registry Key values
SET NOCOUNT ON
GO
DECLARE @org VARCHAR(50)
DECLARE @own VARCHAR(50)
EXECUTE xp_regread 'HKEY_LOCAL_MACHINE',
'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\CurrentVersion',
'RegisteredOrganization',@param = @org output
EXECUTE xp_regread 'HKEY_LOCAL_MACHINE',
'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\CurrentVersion',
'RegisteredOwner',@param = @own output
PRINT 'Whoever set up SQL Server for this class typed these in:'
PRINT ' '
SELECT 'Registered Organization is '+UPPER(@org)
SELECT 'Registered Owner is '+UPPER(@own)
SET NOCOUNT OFF
GO
-->
Cursors allow an entire result set to be captured and manipulted as a single piece of data.
******************************************************-
DECLARE @currentvalue char(32)
DECLARE mycursor CURSOR FOR
SELECT distinct myvalue FROM mytable
OPEN mycursor
FETCH NEXT FROM mycursor INTO @currentvalue
WHILE (@@fetch_status = 0) -- while OK
BEGIN
print @currentvalue
exec('SELECT mycolumn FROM ' + @currentvalue ) -- some usefull work
FETCH NEXT FROM mycursor INTO @currentvalue
END
CLOSE mycursor
DEALLOCATE mycursor
GO
******************************************************-
--Count the number of members that have
--a 'J' as a middle initial value
USE Library
SET NOCOUNT ON
DECLARE @mivalue char(1)
DECLARE @counter int
DECLARE @strcounter char(50)
SELECT @counter=0
DECLARE micount CURSOR FOR
SELECT middleinitial FROM member
OPEN micount
FETCH NEXT FROM micount
INTO @mivalue -- move to first row
WHILE (@@fetch_status = 0) -- while OK
BEGIN
IF @mivalue = 'J' SELECT @counter = @counter + 1
FETCH NEXT FROM micount
INTO @mivalue
END
CLOSE micount
DEALLOCATE micount
SELECT @strcounter = 'Number of J initials:' +CONVERT(char(10),@counter)
PRINT @strcounter
GO
-- RAISERROR
sp_addmessage 55555, 16, 'Mitchs test message',us_english ,true
RAISERROR(55555,16,1)
SELECT title,copy_no,due_date,DATEDIFF(DAY,due_date,GETDATE()) FROM overdue WHERE DATEDIFF(DAY,due_date,GETDATE()) > 14
You can convert a column in place.
ALTER TABLE MyTableName ALTER COLUMN [MyColumnName] NVARCHAR(3000)
This finds any non-consecutive numbers in the userID column.
SELECT A.userID + 1
FROM SDRespondents AS A
WHERE NOT EXISTS (
SELECT B.userID FROM SDRespondents AS B
WHERE A.userID + 1 = B.userID)
GROUP BY A.userID;
This is useful in simulating database failure to stop general access to a database.
EXECUTE sp_dboption library, 'dbo use only', TRUE -- dbo user only EXECUTE sp_dboption library, 'dbo use only', FALSE
alter database myDatabaseName set SINGLE_USER WITH ROLLBACK IMMEDIATE alter database myDatabaseName set MULTI_USER
EXECUTE sp_addtype zipcode, 'char(10)' EXECUTE sp_addtype phonenumber, 'char(13)', NULL EXECUTE sp_droptype <typename> -- to get rid of it
to get a list of UDDT
sp_help
example:
CREATE TABLE member ( member_no member_no NOT NULL, -- member_no is a User Defined Data Type lastname shortstring NOT NULL, firstname shortstring NOT NULL, middleinitial letter NULL, photograph image NULL )
USE MAster EXECUTE sp_dboption mydb, 'trunc. log on chkpt.',true to set SELECT into/bulkcopy EXECUTE sp_dboption mydb, 'SELECT into/bulkcopy',true
chars are treated as varchar's if NULL is a possible value.
Binary Large OBject - BLOB
SET NOCOUNT ON --suppress 'rows affected' msg SET NOCOUNT OFF
create proc displaytable
@tbl varchar(30)
as
EXECUTE ('SELECT * FROM ' + @tbl)
SELECT @cmd = "create database "+@projabbrev+" ON "+@datadevice+" = 6 LOG ON "+@logdevice+" = 6" SELECT @cmd EXECUTE (@cmd)
A batch is a set of one or more SQL statements submitted together and executed as a single group. A script is a series of one or more batches submitted one after the other. A script is a file, with a default extension of .SQL Comparison operators (=,<>,>,<,>=,<=), BETWEEN, IN, LIKE, IS NULL, and IS NOT NULL
| Database | Function |
|---|---|
| Master: | Controls all user databases and SQL Server as a whole |
| Model: | Serves as a template when creating new user databases |
| Msdb: | Provides support for the SQL Executive service |
| Tempdb: | Used for temporary working storage for all connections |
| pubs: | A sample database |
2. Allocation Unit: 1/2MB increments of database storage space Extent: Eight 2K pages of table or index storage space Page: The basic unit of I/O in SQL Server (2K in size)
--deleteViews
-- This deletes all views which start with an X
-- trivial example of using cursors in sql server 6.5
IF EXISTS ( SELECT name FROM sysobjects
WHERE type = 'P' AND name = 'deleteViews' )
DROP PROCEDURE deleteViews
go
******************----------------
GO
CREATE PROCEDURE deleteViews
@databasename varchar(30)
AS
DECLARE @viewname SYSNAME,
@sqls CHAR(255),
@fullviewname CHAR(255)
SELECT @sqls = 'DECLARE view_cursor CURSOR FOR SELECT name FROM '
+ @databasename + '..sysobjects WHERE type = ''v'' and name LIKE ''X%'' '
--
exec(@sqls)
OPEN view_cursor
FETCH NEXT FROM view_cursor INTO @viewname
WHILE (@@FETCH_STATUS=0)
BEGIN
SELECT @fullviewname = @databasename + '..' + @viewname
SELECT 'current viewname is ' + @fullviewname
--EXEC ('DROP VIEW ' + @fullviewname)
FETCH NEXT FROM view_cursor INTO @viewname
END
CLOSE view_cursor
DEALLOCATE view_cursor
GO
SELECT name FROM syscolumns WHERE id in (SELECT id FROM sysobjects where name = 'mycolumn')
SELECT * FROM information_schema.columns
RESTORE FILELISTONLY
FROM DISK =
'C:\Program Files\Microsoft SQL Server\MSSQL$AUS158\BACKUP\SDRespondents.bak'
RESTORE DATABASE SDRespondents
FROM DISK =
'C:\Program Files\Microsoft SQL Server\MSSQL$AUS158\BACKUP\SDRespondents.bak'
WITH MOVE 'SDRespondents_Data' TO
'C:\Program Files\Microsoft SQL Server\MSSQL$AUS158\Data\SDRespondents.mdf',
MOVE 'SDRespondents_log' TO
'C:\Program Files\Microsoft SQL Server\MSSQL$AUS158\Data\SDRespondents.ldf'
GO
Use "FOR XML AUTO" or "FOR XML EXPLICIT"
SELECT * FROM sdgroups FOR XML AUTO
When you get an exception like this:
Exception: System.Net.WebException Message: The operation has timed out
You need to increase the timeout setting by opening Enterprise Manager, right clicking on your server and select "Properties", then "Connections", then set "Remote query timeout" to be something larger.
Bad example where password is embedded (but occassionally useful for testing on dev boxes)
Application Name=myApplication; Data Source=myDatabaseServer; user id=sa;password=mypassword; database=myDatabaseName
Real solution where password is not in connection string
Data Source=myDatabaseServer; Trusted_Connection=yes; database=myDatabaseName
Devices are containers for databases and their logs.
DISK INIT creates devices:
To create data devices you must be in master.
DISK INIT name='LIBRARY_DEV1',
physname = 'c:\MSSQL\DATA\LIBRARY.DAT',
vdevno = 100, -- must be unique, use sp_helpdevice()
-- to find currently used device_number(s)
size = 10240 -- in 2K blocks
DISK INIT
name='LIBRLOG_DEV2',
physname = 'c:\MSSQL\DATA\LIBRLOG.DAT',
vdevno = 101,
size = 4096
GUIDs are 16 byte binary integers created to be unique identifiers across database instances.
SELECT newid()
Produces:
B3A15B59-AD75-4D8B-8888-0D839C84C301
We create the first row by using newid() to have sqlserver create the GUID for us, the second row we create by feeding it a GUID directly.
CREATE TABLE guidpractice
(
guid UNIQUEIDENTIFIER,
fname VARCHAR(20) NOT NULL,
lname VARCHAR(30) NOT NULL,
)
insert guidpractice (guid,fname,lname) values (newid(),'Adam','Smith')
insert guidpractice (guid,fname,lname) values ('857CFD44-8BB4-4D05-AEF1-22B62142A7FF','Adam','Smith')
select * from guidpractice
Produces:
DA4414FD-7178-4DE2-8C77-86817B04ECF8 Adam Smith 857CFD44-8BB4-4D05-AEF1-22B62142A7FF Adam Smith
CREATE TABLE guidpractice2
(
guid UNIQUEIDENTIFIER DEFAULT newid(),
fname VARCHAR(20) NOT NULL,
lname VARCHAR(30) NOT NULL
)
GO
INSERT guidpractice2 (fname,lname) VALUES ('Adam','Smith')
INSERT guidpractice2 (fname,lname) VALUES ('Adam','Smith')
SELECT * FROM guidpractice2
Produces:
guid fname lname ------------------------------------ -------------------- ------ D6A672EB-39A5-42E9-92C6-0C7DD0F9324D Adam Smith 609876C7-92A4-4D78-8393-19D72904F194 Adam Smith
SELECT title,copy_no,due_date,DATEDIFF(DAY,due_date,GETDATE()) FROM overdue WHERE DATEDIFF(DAY,due_date,GETDATE()) > 14
EXECUTE sp_dboption library, 'dbo use only', TRUE -- dbo user only EXECUTE sp_dboption library, 'dbo use only', FALSE
You can define local variables with the "@" sign. These are used in parameterized sql commands.
DECLARE @lang varchar(12) set @lang = 'en-UK' SELECT name from SDTextDefinitions WHERE text LIKE '%buy%' and lang=@lang
You need to preface it with the wildcards in separate strings like this
DECLARE @lang varchar(12) DECLARE @searcher varchar(12) set @lang = 'en-UK' set @searcher = 'buy' SELECT name from SDTextDefinitions WHERE text LIKE '%'+@searcher+'%' and lang=@lang
DECLARE @mydate datetime
SET @mydate = '2010-11-11 00:00:00.00'
SELECT count(time) AS 'number', avg(time) AS 'time(ms)' FROM myTable
WITH (nolock)
WHERE
dtime > @mydate AND dtime < DATEADD(day,1,@mydate)
EXECUTE sp_addtype zipcode, 'char(10)' EXECUTE sp_addtype phonenumber, 'char(13)', NULL EXECUTE sp_droptype <typename> -- to get rid of it
to get a list of UDDT
sp_help
CREATE TABLE member ( member_no member_no NOT NULL, -- member_no is a User Defined Data Type lastname shortstring NOT NULL, firstname shortstring NOT NULL, middleinitial letter NULL, photograph image NULL )
USE Master EXECUTE sp_dboption mydb, 'trunc. log on chkpt.',true to set SELECT into/bulkcopy EXECUTE sp_dboption mydb, 'SELECT into/bulkcopy',true
SET NOCOUNT ON --suppress 'rows affected' msg SET NOCOUNT OFF
create proc displaytable
@tbl varchar(30)
as
EXECUTE ('SELECT * FROM ' + @tbl)
SELECT @cmd = "create database "+@projabbrev+" ON "+@datadevice+" = 6 LOG ON "+@logdevice+" = 6" SELECT @cmd EXECUTE (@cmd)
A batch is a set of one or more SQL statements submitted together and executed as a single group. A script is a series of one or more batches submitted one after the other. A script is a file, with a default extension of .SQL Comparison operators (=,<>,>,<,>=,<=), BETWEEN, IN, LIKE, IS NULL, and IS NOT NULL
| Database | Function |
|---|---|
| Master: | Controls all user databases and SQL Server as a whole |
| Model: | Serves as a template when creating new user databases |
| Msdb: | Provides support for the SQL Executive service |
| Tempdb: | Used for temporary working storage for all connections |
| pubs: | A sample database |
WAITFOR DELAY '00:00:59' -- sleeps for 59 seconds
SELECT @cmd = "create database "+@projabbrev+" ON "+ @datadevice+" = 6 LOG ON "+@logdevice+" = 6" SELECT @cmd EXECUTE (@cmd)
SELECT name FROM syscolumns WHERE id in (SELECT id FROM sysobjects where name = 'mycolumn')
SELECT * FROM information_schema.columns
RESTORE FILELISTONLY
FROM DISK =
'C:\Program Files\Microsoft SQL Server\MSSQL$AUS158\BACKUP\SDRespondents.bak'
The above command will show two rows. The database name and the log file name are in the first column, 'LogicalName'. These should be inserted below for 'SDRespondents_Data' and 'SDRespondents_log' respectively.
RESTORE DATABASE SDRespondents
FROM DISK =
'C:\Program Files\Microsoft SQL Server\MSSQL$AUS158\BACKUP\SDRespondents.bak'
WITH MOVE 'SDRespondents_Data' TO
'C:\Program Files\Microsoft SQL Server\MSSQL$AUS158\Data\SDRespondents.mdf',
MOVE 'SDRespondents_log' TO
'C:\Program Files\Microsoft SQL Server\MSSQL$AUS158\Data\SDRespondents.ldf'
GO
SELECT start_time, [text], status, total_elapsed_time, DB_Name(database_id), blocking_session_id, wait_type, wait_time, cpu_time, command, logical_reads, text_size, row_count, session_id FROM sys.dm_exec_requests AS R CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS S WHERE session_id <> @@SPID;
Use "FOR XML AUTO" or "FOR XML EXPLICIT"
SELECT * FROM sdgroups FOR XML AUTO
Since SQL doesn't have a isAlphaOrNumber() function you can use this user function from our friends at simple-talk.com
IF OBJECT_ID(N'IsAlnum') IS NOT NULL
DROP FUNCTION IsAlnum
GO
CREATE FUNCTION dbo.[IsAlnum] (@string VARCHAR(MAX))
/*
Select dbo.isalnum('how many times must I tell you')
Select dbo.isalnum('345rtp')
Select dbo.isalnum('co10?')
*/
RETURNS INT
AS BEGIN
RETURN CASE WHEN PATINDEX('%[^a-zA-Z0-9]%', @string) > 0 THEN 0
ELSE 1
END
END
GO
create table test (Description varchar(255))
insert into test (Description) VALUES ('imok')
insert into test (Description) VALUES ('imok2')
insert into test (Description) VALUES ('i''mNot ok')
select * from test where dbo.IsAlnum(Description) = 1 /* selects only rows containing only letters and numbers */
One option is to user FMTONLY, but it's rumored to cause false negatives.
SET FMTONLY ON -- lets test the next statement select * from test2 SET FMTONLY OFF Msg 208, Level 16, State 1, Line 5 Invalid object name 'test2'.
You can also use "SET PARSEONLY ON" and "SET PARSEONLY OFF" , but this just checks syntax, not if a tablename is misspelled.
Bad example where password is embedded (but occassionally useful for testing on dev boxes)
Application Name=myApplication; Data Source=myDatabaseServer; user id=sa;password=mypassword; database=myDatabaseName
Real solution where password is not in connection string
Data Source=myDatabaseServer; Trusted_Connection=yes; database=myDatabaseName
Devices are containers for databases and their logs.
DISK INIT creates devices:
To create data devices you must be in master.
DISK INIT name='LIBRARY_DEV1',
physname = 'c:\MSSQL\DATA\LIBRARY.DAT',
vdevno = 100, -- must be unique, use sp_helpdevice()
-- to find currently used device_number(s)
size = 10240 -- in 2K blocks
DISK INIT
name='LIBRLOG_DEV2',
physname = 'c:\MSSQL\DATA\LIBRLOG.DAT',
vdevno = 101,
size = 4096
sp_password 'myusername', 'my+stro'
"A SQL query walks into a bar. He approaches two tables and says, Mind if I join you?"