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?"