· 8 years ago · Oct 18, 2017, 08:08 PM
1-- Show databases and info
2 SELECT * FROM sys.databases
3 sp_configure;
4 sp_help 'tablename';
5 sp_helpfile;
6 sp_helpIndex 'tablename';
7
8-- Help
9 DBCC HELP('checkdb');
10 DBCC HELP ('?');
11
12 -- Managing blocked processes
13 sdf -- Considered replacement for sys.sysprocesses
14 SELECT * FROM sys_sysprocesses WHERE blocked > 0;
15 SELECT * FROM sys.dm_tran_locks; -- Look for request_session_id
16 DBCC INPUTBUFFER(56); -- 56 is spid
17 --BOL is books online
18
19-- Killing processes
20 KILL 1234; -- 1234 is spid from select * from sysprocesses or
21
22-- Index usage stats
23 SELECT object_name(object_id) AS TableName, * FROM sys.dm_db_index_usage_stats
24 WHERE db_name(database_id) = 'databasebane'
25
26 -- Fragmentation percent
27 SELECT object_name(object_id) AS TableName, * FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, NULL);
28 -- Params are dbid (db), objectid (table), indexid (index), mode
29 -- e.g.
30 SELECT object_name(object_id) AS TableName, * FROM sys.dm_db_index_physical_stats(DB_ID('dbname'), NULL, NULL, NULL, NULL);
31
32
33-- Fixing fragmentation (Rebuild requires table lock, reorg usespage locks)
34 ALTER INDEX ALL on dbo.TableName
35 REBUILD -- Completely rebuilds index from scratch
36 --REORG rearranges pages
37 WITH(ONLINE = ON) --
38
39 -- Fillfactor is amount of pages full
40
41--- Create non cluster index (non for non clustered cl for clustered)
42 -- Note you can write click on 'Missing Index' when "Execution Plan" is enabled
43
44 CREATE NONCLUSTERED INDEX nonColumnName ON dbo.dbname(columnname);
45 CREATE CLUSTERED UNDEX clColumnName ON dbo.dbname(columnname);
46
47 -- Destroy index
48 DROP INDEX tablename.nonColumnName;
49
50-- OS
51 SELECT * FROM sys.dm_os_performance_counters;
52 SELECT * FROM sys.dm_os_sys_info;
53 SELECT * FROM sys.dm_os_windows_info;
54
55 -- Get process
56 use master
57 GO
58 select * from sysprocesses (nolock) where blocked = 0 and spid in (
59 select blocked from sysprocesses (nolock) where blocked <> 0
60 )
61 GO
62
63-- Files and Extensions
64 --MDF - Master Data File
65 --NDF - Non master data file
66 --LDF - Log data file
67 CREATE DATABASE DB!
68 ON PRIMARY
69 (
70 NAME = DB1Data1,
71 FILENAME = 'C:\MSSQLDB\DB1Data1.mdf',
72 SIZE = 10MB
73 ),
74 FILEGROUP fgCurrent
75 (
76 NAME = DB1Data2,
77 FILEGROUP = 'D:\MSSQLDB\DB1Data2.ndf',
78 SIZE = 10MB
79 ),
80 (
81 NAME = DB1Data3,
82 FILENAME = 'D:\MSSQLDB\DBData3.ndf'
83 SIZE = 10MB
84 )
85 LOG ON
86 (
87 NAME = DB1Log1,
88 FILENAME = 'E:\MSSQLDB\DB1Log1.ldf',
89 SIZE = 10MB
90 )
91
92-- Filegroups
93 ALTER DATABASE dbname ADD FILEGROUP fgname
94
95 -- Add file
96 ALTER DATABSE dbname ADD FILE (NAME = N'newfile', SIZE 10MB, FILENAME = N'D:\MSSQLDB\FG3.ndf');
97
98 -- Create table on filegrtoup
99 CREATE TABLE T1 (Col1 INT) ON fgCurrent;
100
101 SELECT * FROM sys.filegroups;
102
103 SELECT * FROM sys.database_files;
104 SELECT * FROM sys.data_spaces;
105 --(data_space_id)
106
107 --Note ds and df are aliased from above
108 SELECT df.file_id, df.name, ds.name as Filegroup
109 FROM sys.data_spaces df
110 INNER JOIN sys.data_spaces ds
111 ON df.data_spaces_id = ds.data_spaces_id
112
113 --All files on server
114 SELECT * FROM sys.master_files
115
116 --
117
118-- SERVER SIDE TRACE (SST)
119 SELECT * FROM sys.traces;
120
121 --File->Export->Trace Definition ()
122 --(export: sp_completed and statement_completed) Remember to add filename
123
124-- Auditing SQL Server
125 -- Right click audit -> select 'New Audit'
126
127-- Backup
128 -- RPO Recovery Point Objective (How much data can you afford to lose)
129 -- RTO Recovery Time Objective (How long can you afford to be down)
130
131 -- Full Backup Including options
132 BACKUP DATABASE dbname
133 TO DISK = 'c:\backup\dbname.bak',
134 WITH INIT, -- optional prevents concatenating backups to single file and ovverides existing backup
135 FORMAT, -- optional forces backup even if existing file has corrupted header (reformatted)
136 STATS = 10; -- optional show stats every 10 perent
137
138 -- Log backups
139 BACKUP LOG dbname
140 TO DISK = 'c:\backup\dbnamelog1.trn';
141
142 -- Differential Backups (Requires full backup)
143 BACKUP DATABASE dbname
144 TO DISK = 'c:\backup\dbname.bak',
145 WITH INIT,
146 FORMAT,
147 STATS = 10,
148 DIFFERENTIAL;
149
150 -- Filegroup backups (Can add multiple file groups)
151 BACKUP DATABASE dbname
152 FILEGROUP = 'FG1'
153 TO DISK = 'c:\backup\dbname.bak',
154 WITH INIT,
155 FORMAT,
156 STATS = 10;
157
158 -- System backup (model can be pulled from another db)
159 -- Can backup master, msdb, model, tempdb with same syntax as above
160 -- Same with log master, log msdb, log model, logtempdb
161
162-- Basic system restore
163 RESTORE database databasename
164 FROM DISK = 'c:\backup\dbname.bak'
165 --WITH REPLACE, -- Use when db already exists
166 --STATS = 10
167
168 -- Restore with move
169 RESTORE database databasename
170 FROM DISK = 'c:\backup\dbname.bak'
171 WITH
172 MOVE 'dbname' TO 'c:\newbackup\dbname.bak'
173
174 -- Restore log backup
175 RESTORE LOG databasename
176 FROM DISK = 'c:\backup\dbnamelog.trn'
177 WITH stats = 10, norecovery
178
179
180-- INDEXES
181 -- Clustered: Table is physically ordered in index order
182
183 -- Non clustered: Value in index points to row of individual pages
184
185 -- Index fragmentation:
186 -- Internal: Fragmentation within page itself
187 -- External: Pages are not located next to eachother
188
189
190
191--TRIGGER (auto execute on trigger. Do not use)
192 CREATE TRIGGER||PROCEDURE triggername
193 ON dbo.table
194 AFTER INSERT,UPDATE,DELETE
195 AS BEGIN
196 PRINT 'ok!'
197 END
198
199 -- Trigger types
200 -- DML: Data Manipulation Language
201 -- DDL: Data Definition Language (ON DATABASE)
202 -- Logon
203
204--Stored Procedures (SP):
205 CREATE PROCEDURE GetstudentnameInOutputVariable
206 (
207 @studentid INT, --Input parameter , Studentid of the student
208 @studentname VARCHAR (200) OUT, -- Output parameter to collect the student name
209 @StudentEmail VARCHAR (200)OUT -- Output Parameter to collect the student email
210 )
211 AS
212 BEGIN
213 SELECT @studentname= Firstname+' '+Lastname,
214 @StudentEmail=email FROM tbl_Students WHERE studentid=@studentid
215 END
216
217 -- To execute:
218 GetstudentnameInOutputVariable
219 -- or EXECUTE GetstudentnameInOutputVariable;
220
221--Synonym
222 SELECT * FROM Food.dbo.snack
223 --=
224 CREATE SYNONYM Snack FOR Food.dbo.snack
225
226--Drop vs Detach
227 --Detach does not delete files
228 --Attach:
229 CREATE DATABASE DBName
230 ON (FILENAME = 'C:\MyDBs\CD')
231 FOR ATTACH;
232
233--Scale Out I/O (Spread across database/server)
234 --SEPERATE Data and Log Files!
235
236--Move filegroup
237ALTER database [databasename]
238MODIFY FILE
239(
240 NAME = DB1Data2,
241 FILENAME = 'C:\Newfolder\Newfile.ndf'
242)
243
244-- Recover Models
245 --Simple: No log backups. Full only
246 --Bulk Logged: Logs and differentials but not PiT (Point in Time)
247 --Full: Adds PiT (Point in Time)
248
249-- Options
250 --Autoclose: deallocates all objects from memory
251 --Autoshrink: Automatically shrinks db every 30 minutes (FRAGMENTS DB Don't USE!)
252 --recovery_model_desc: See above
253
254--Stats
255 --Synchronous forces queries to wait when stats are out of date
256 --Asynchronous allows queries to continie with old stats and builds new stats in bg (better methinks)
257 SELECT name, is_auto_update_stats_on, is_auto_update_stats_async_on FROM sys.databases
258
259 ALTER database dbname SET auto_update_statistics_async ON
260
261-- Restricted Access
262 -- Single User: allows only one user
263 -- Restricted User: Allows only db_owner, sysadmin or dbcreatot
264 -- Multi User: Allows all users
265 ALTER database dbname SET single_user
266 ALTER database dbname SET restricted_user
267 ALTER database dbname SET multi_user
268
269-- Compression
270 -- Row Level
271 -- Simplest, Cheapest, Doesn't Store 0 or NULL, Changes fixed-length vars to variable-length vars
272 -- Page Level
273 -- More resources, adds prefix and dictionary compression
274 -- Prefix Compression
275 -- Looks for and replaces repeating patterns at begining of column
276 -- Dictionary Compression
277 -- Looks for and replaces repeating patterns anywhere on page
278 sp_estimate_data_compression_savings 'dbo', 'table', NULL, NULL, 'ROW'; --OR Page
279
280-- Buffers
281 -- Clean: Unchanged buffer
282 -- Dirty: Modified buffer
283 dbcc dropcleanbuffers -- drops clean buffers (Don't run live)
284
285--ShrinkDatabase and ShrinkFile (Dangerous 1: takes forever, 2: Fragments Indexes)
286 DBCC SHRINKDATABASE(N'databasename', 10); --10 is target percentage
287
288 -- Shrink file:
289 DBCC SHRINKFILE('logiles', 5); -- 5 is target file size in MB
290
291--FreeProcCache
292
293-- CheckDB and CheckTable (Check for consitancy errors)
294 DBCC CHECKDB(databasename); --(databasename) is optional uses current db by default (Look for minimum repair level)
295 -- Put db in single user mode to fix
296 DBCC CHECKDB(datanasename, repair_allow_data_loss); -- repair_allow_data_loss is minimum repair level
297
298-- SQLPerf
299 DBCC SQLPERF(logspace); -- Gives logspace usage for all databases --GET LOG SPACE USAGE
300
301-- InputBuffer Shows last query spid ran spid is in () in tab eg. SqlQuery.sql (57) 57 is spid
302 DBCC inputbuffer(57) -- WHERE spid is 57
303
304-- Table Partitioning (Every table is single partitioned)
305 SELECT * FROM sys.partitions WHERE OBJECT_NAME([object_id]) = 'tablename';
306
307 CREATE PARTITION FUNCTION RangePF1(int)
308 AS RANGE LEFT FOR VALUES(1, 500, 1000); -- ranges fo into filegroups (e.g. FG1, FG2, FG3)
309 GO
310 CREATE PARTITION RangePF1
311 TO (FG1, FG2, FG3, FG4) -- FG4 is used to hold all data above 1000
312
313 CREATE CLUSTERED INDEX clustID ON dbo.tablename(ID) -- ID is column
314 ON RangePF1(ID)
315
316 SELECT * FROM sys.partitions
317 WHERE OBJECT_NAME([object_id]) = 'tablename';
318
319 CREATE CLUSTERED INDEX ClustID ON db.tablename(ID);
320 ALTER TABLE dbo.tablename
321 SWITCH PARTITION 4
322 TO db.tablename PARTITION 1
323
324-- Bookmark Lookups
325
326-- Covering Index
327 -- Add multiple columns to nonclustered index
328 CREATE CLUSTERED INDEX ClustID ON db.tablename(col1, col2);
329 ALTER TABLE dbo.tablename
330 WITH(drop_existing = on);
331
332-- Set statistics IO
333 SET STATISTICS IO ON; -- Look for scan count and logical reads (ignore physical reads)
334
335
336-- SERVER LEVEL SECURITY
337 -- Create login
338 CREATE LOGIN [computer\user] FROM WINDOWS; -- Create from windows
339
340 CREATE LOGIN [computer\group] FROM WINDOWS; -- Create by group
341
342 CREATE LOGIN [username] WITH PASSWORD=N'1234' MUST_CHANGE,
343 DEFAULT_DATABASE = [master],
344 CHECK_EXPIRATION = ON, -- enforces password expiration restrictions in AD (Active Directory)
345 CHECK_POLICY = ON; -- Forces AD password policy
346
347 -- Roles
348 ALTER SERVER ROLE
349 ADD MEMBER [username]
350 GO;
351
352 sp_srvrolepermission 'diskadmin';
353
354 -- Granting permissions
355 --(Can right click on server and click on permissions)
356 GRANT VIEW SERVER STATE, CONTROL SERVER TO [username];
357
358 -- Permissions granted immediately no need to flush privileges
359
360-- Database Level Security
361 -- execute as user
362 EXECUTE_AS_USER = 'username';
363
364 -- Roles (Expand DB->Security->Roles->Database Roles in GUI)
365 CREATE ROLE [Rolename]
366
367 -- Grant role same as grant user
368 GRANT execute, view definition TO [Rolename];
369
370 -- Add uesr to role
371 ALTER ROLE [Rownane] ADD MEMBER [username];
372
373-- Schema
374 CREATE SCHEMA [Schemaname] AUTHORIZATION [username];
375
376 -- Schema security
377 --ALTER AUTHORIZATION ON dbo.table TO [dbo]; is
378 ALTER AUTHORIZATION ON schema::Schemaname TO dbo;
379
380 -- Grant at schema level
381 GRANT EXECUTE ON schema::Schemaname TO [username];
382
383-- Transaction Logs (Tracks db changes, Allows for rollback, changes written to log first)
384
385-- Patch Management
386 -- RTM: - Release to manufacturer (Market)
387 -- CU: Cumilitive update (hotfixes)
388 -- SP: Servuce Pack
389
390-- Concurrency Tools and Blocking Conditions (xlock locks until finished)
391
392 -- Hold exclusive rows until transaction is committed
393 BEGIN TRAN
394 SELECT * FROM dbo.tablename(xlock)
395 WHERE zip BETWEEN 75401 AND 7800
396 COMMIT TRAN
397
398-- Troubleshooting Data
399 -- SQL Profiler Tools -> Profiler
400 -- Trace name (arbitrary)
401 -- Rollover creat
402
403 -- Dynamic Management Views(Funtions) (DMVs)
404
405-- Temporary table
406 SELECT COLUMNS INTO #temptable FROM table WHERE id = 1;
407
408-- POWERSHELL
409 get-service --ps
410 get-process --ps -auxgext.txt -- command > /text.txt
411 get-member --(gm)
412 get-service | ?{$_.Name -eq "SQL"} --Get service by name e.g. SQL
413 get-service | ?{$_.Name -eq "SQL"} | %{$_.Stop()} -- Same with start -- ? = where -- % = foreach
414 pscd drive -- List drives Can access registry as logical drive
415
416
417-- Variables
418 DECLARE @theVar VARCHAR(32);
419 SET @theVar = 'Your Mom!';
420
421-- Procedure with variables
422 CREATE PROCEDURE count_by_lname(@lname VARCHAR(64))
423 AS
424 BEGIN
425 SELECT COUNT(*) FROM [Case] WHERE LName LIKE @lname;
426 END
427
428 count_by_lname @lname = 'Smailey';
429
430-- If Statement
431 IF (SELECT COUNT(*) FROM [Case] WHERE LName LIKE 'Smailey') > 100
432 PRINT 'OK'
433 ELSE
434 PRINT 'Nope'
435
436
437-- Second If Statement
438 IF (SELECT COUNT(*) FROM [Case] WHERE LName LIKE 'Smailey') > 100
439 BEGIN
440 PRINT 'OK'
441 END
442 ELSE
443 BEGIN
444 PRINT 'Nope'
445 END
446
447-- Create and execute function
448 CREATE FUNCTION functionName (@LName AS VARCHAR(64)) RETURNS VARCHAR(MAX)
449 AS
450 BEGIN
451 RETURN('You typed:' + @LNAME)
452 END
453
454 SELECT dbo.functionName('Test');
455
456-- Create table values function
457 CREATE FUNCTION smaileys() RETURNS TABLE
458 AS
459 RETURN
460 SELECT CaseID, FName, LName FROM [Case] WHERE LName LIKE 'SMAILEY';
461
462 SELECT * FROM dbo.smaileys();
463
464-- Table variables (array)
465 DECLARE @dood TABLE
466 (
467 firstname VARCHAR(MAX),
468 lastname VARCHAR(MAX)
469 );
470 INSERT INTO @dood (firstname, lastname) VALUES ('Matthew', 'Smailey');
471 SELECT * FROM @dood;
472
473 -- Common table expressions
474 WITH filmcounts AS (
475 SELECT filmCountryID, COUNT(*) AS numberOfFilms FROM films GROUP BY filmCountryID
476 ) SELECT FROM filmcounts AVG(numberOfFilms);
477
478--Cursor 'FETCH NEXT', 'FETCH FIRST', 'FETCH LAST', 'FETCH PRIOR'
479 DECLARE user_cursor CURSOR
480 FOR SELECT CaseID, FName LName FROM [Case] WHERE LNAME LIKE 'Smailey';
481
482 OPEN user_cursor
483 FETCH NEXT FROM user_cursor
484
485 WHILE @@FETCH_STATUS = 0
486 FETCH NEXT FROM user_cursor
487
488 Close user_cursor
489 DEALLOCATE user_cursor
490
491-- Transactions
492 BEGIN TRANSACTION --Begin transaction (TRAN for short)
493 INSERT INTO table (id) VALUES (1);
494 COMMIT -- Transaction gets committed
495
496 -- Rollback transaction (undo)
497 ROLLBACK TRANSACTION;
498
499 --Naming transaction
500 BEGIN TRAN transaction_name;
501 COMMIT TRAN transaction_name;