· 6 years ago · Sep 06, 2019, 10:54 AM
1***To create a Table***
2```sql
3 create table risk_clos_rank(
4 id_num int IDENTITY(1,1) NOT NULL,
5 username nvarchar(100),
6 datetime_of_decision DATETIME
7 );
8
9 CREATE TABLE TheNameOfYourTable (
10 ID INT NOT NULL IDENTITY(1,1),
11 DateAdded DATETIME DEFAULT(getdate()) NOT NULL,
12 Description VARCHAR(100) NULL,
13 IsGood BIT DEFAULT(0) NOT NULL,
14 TotalPrice MONEY NOT NULL,
15 CategoryID int NOT NULL REFERENCES Categories(ID),
16 PRIMARY KEY (ID)
17 );
18```
19***To create a copy of table( doesnt create constraints like primary key, not null , indexes ect)***
20```sql
21 SELECT * INTO NewTable FROM OldTable
22 Eg. SELECT * INTO clos_ext_bkup FROM clos_ext;
23```
24***To create a copy of table with its data (create and insert)***
25```sql
26 SELECT expressions INTO new_table FROM tables [WHERE conditions];
27 SELECT employee_id AS contact_id, last_name, first_name INTO contacts FROM employees WHERE employee_id < 1000;
28```
29
30The format of new_table is determined by evaluating the expressions in the select list. The columns in new_table are created in the order specified by the select list. Each column in new_table has the same name, data type, nullability, and value as the corresponding expression in the select list.
31
32***Inserting Data from another table ( only insert)***
33```sql
34 INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool'
35 INSERT INTO contacts (contact_id, last_name, first_name) SELECT employee_id, last_name, first_name FROM employees WHERE employee_id <= 100;
36```
37***Inserting Multiple values***
38```sql
39 INSERT INTO table1 (First, Last)
40 VALUES
41 ('Fred', 'Smith'),
42 ('John', 'Smith'),
43 ('Michael', 'Smith'),
44 ('Robert', 'Smith');
45```
46***To add a column***
47```sql
48 ALTER TABLE table_name ADD column_1 column-definition,column_2 column-definition,column_n column_definition;
49 alter table risk_user_approval_tree add lineusr nvarchar(100);
50 ALTER TABLE table ADD columnname BIT CONSTRAINT Constraint_name DEFAULT 0 WITH VALUES
51```
52***To add a auto increment***
53```
54 ALTER TABLE 'tableName' ADD 'NewColumn' INT IDENTITY(1,1);
55```
56***To add a column with computed value***
57```sql
58 ALTER TABLE dbo.Products ADD RetailValue AS (QtyAvailable * UnitPrice * 1.5);
59```
60***To delete/drop a column***
61```sql
62 ALTER TABLE table_name DROP COLUMN column_name;
63```
64***To drop a table***
65```sql
66 DROP TABLE tablename;
67```
68***To modify a column***
69```sql
70 ALTER TABLE table_name ALTER COLUMN column_name column_type;
71```
72***To update a row***
73```sql
74 UPDATE clos_customer_master SET Prev = 'Reactivation' WHERE Prev = 'Reactivate';
75```
76
77***To update a row from select clause***
78```sql
79 UPDATE table SET Col1 = i.Col1, Col2 = i.Col2 FROM ( SELECT ID, Col1, Col2 FROM other_table) i WHERE i.ID = table.ID;
80```
81The subquery results are substituted into the outer query. As we need table object in outer query, we need to make an alias of inner query.
82
83***To add a primary key***
84```sql
85 ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);
86```
87***To find the name of constraints***
88```sql
89 SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'tablename'
90```
91***To find name of Primary key constraint***
92```sql
93 SELECT name FROM sys.key_constraints WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = N'CLOS_ext';
94```
95***Drop primary key***
96```sql
97 ALTER TABLE table_name DROP CONSTRAINT constraint_name;
98```
99***To rename a column (alter command doesnt work here)***
100```sql
101 sp_rename 'table_name.old_column_name', 'new_column_name', 'COLUMN';
102 sp_rename 'cl_ff_docm.WINAME', 'WI_NAME', 'COLUMN';
103```
104***To rename a table***
105```sql
106 sp_rename 'old_table_name', 'new_table_name';
107```
108***To top 10% of records***
109```sql
110 SELECT TOP(10) * FROM CLOS_EXT
111```
112***To find when a table was altered***
113```sql
114 SELECT [name] , create_date, modify_date FROM sys.tables;
115```
116***To find which table contains a given column***
117```sql
118 SELECT * FROM INFORMATION_SCHEMA.COLUMNS;
119
120 SELECT OBJECT_SCHEMA_NAME (c.object_id) SchemaName,
121 o.Name AS Table_Name,
122 c.Name AS Field_Name,
123 t.Name AS Data_Type,
124 t.max_length AS Length_Size,
125 t.precision AS Precision
126 FROM sys.columns c
127 INNER JOIN sys.objects o ON o.object_id = c.object_id
128 LEFT JOIN sys.types t on t.user_type_id = c.user_type_id
129 WHERE o.type = 'U'
130 -- and o.Name = 'YourTableName'
131 ORDER BY o.Name, c.Name
132```
133***To find which table has which constraint and on which column.***
134```sql
135 Select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE;
136```
137***Selcting based on case***
138```sql
139 SELECT CASE
140 WHEN <test> THEN <returnvalue>
141 WHEN <othertest> THEN <returnthis>
142 ELSE <returndefaultcase>
143 END AS <newcolumnname>
144 FROM <table>
145
146 Eg.
147 SELECT ProductNumber, Name, "Price Range" =
148 CASE
149 WHEN ListPrice = 0 THEN 'Mfg item - not for resale'
150 WHEN ListPrice < 50 THEN 'Under $50'
151 WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'
152 WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
153 ELSE 'Over $1000'
154 END
155 FROM Production.Product
156 ORDER BY ProductNumber ;
157```
158***Adding row numbers to the result*** //here we are creating
159```sql
160 SELECT ROW_NUMBER() OVER(ORDER BY name ASC) AS Row#, name,
161 recovery_model_desc
162 FROM sys.databases
163 WHERE database_id < 5;
164```
165***While Loop***
166```sql
167 DECLARE @MaxCount INTEGER
168 DECLARE @Count INTEGER
169 DECLARE @Txt VARCHAR(MAX)
170 SET @Count = 1
171 SET @Txt = ''
172 SET @MaxCount = (SELECT MAX(RowID) FROM ConcatenationDemo)
173 WHILE @Count<=@MaxCount
174 BEGIN
175 IF @Txt!=''
176 SET @Txt=@Txt+',' + (SELECT Txt FROM ConcatenationDemo
177 WHERE RowID=@Count)
178 ELSE
179 SET @Txt=(SELECT Txt FROM ConcatenationDemo WHERE RowID=@Count)
180 SET @Count += 1
181 END
182 SELECT @Txt AS Txt
183
184 DECLARE @i int
185 SET @i = 0
186 WHILE (@i < 10)
187 BEGIN
188 SET @i = @i + 1
189 PRINT @i
190 IF (@i >= 10)
191 BREAK
192 ELSE
193 CONTINUE
194 END
195```
196***Try / Catch Statements***
197```sql
198 BEGIN TRY
199 -- try / catch requires SQLServer 2005
200 -- run your code here
201 END TRY
202 BEGIN CATCH
203 PRINT 'Error Number: ' + str(error_number())
204 PRINT 'Line Number: ' + str(error_line())
205 PRINT error_message()
206 -- handle error condition
207 END CATCH
208```
209***To get date in DD/MM/YYYY format***
210```sql
211 SELECT CONVERT(varchar, GETDATE(), 103);
212```
213***To get all foreign keys refrencing a given table***
214```sql
215 EXEC sp_fkeys 'TableName'
216```
217***To get datatype, size of columns of a table***
218```sql
219 EXEC sp_columns CLOS_EXT;
220```
221***To get empty string after concatenation of a string with NULL***
222
223When ***SET CONCAT_NULL_YIELDS_NULL*** is ON, concatenating a null value with a string yields a NULL result.
224
225For example, SELECT 'abc' + NULL yields NULL.
226
227When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself (the null value is treated as an empty string).
228
229For example, SELECT 'abc' + NULL yields abc.
230
231***To compile without executing***
232```sql
233 SET NOEXEC ON;
234```
235When SET NOEXEC is ON, SQL Server compiles each batch of Transact-SQL statements but does not execute them.
236
237***Updating data from another table***
238```sql
239 UPDATE table SET Col1 = i.Col1, Col2 = i.Col2 FROM ( SELECT ID, Col1, Col2 FROM other_table) i WHERE i.ID = table.ID
240```
241***Check if column exists in table***
242```sql
243 IF EXISTS(SELECT 1 FROM sys.columns
244 WHERE Name = N'columnName'
245 AND Object_ID = Object_ID(N'schemaName.tableName'))
246 BEGIN
247 -- Column Exists
248 END
249```
250***Converting Multi row data into a comma separated string***
251```sql
252 DECLARE @Names VARCHAR(8000)
253 SELECT @Names = COALESCE(@Names + ', ', '') +
254 ISNULL(Name, 'N/A')
255 FROM People
256```
257***Nvarchar***
258allows storing of unicode data
259
260***To remove duplicate rows***
261```sql
262 select distinct * into t2 from t1;
263 delete from t1;
264 insert into t1 select * from t2;
265 drop table t2;
266```
267***Check if the table exists***
268```sql
269 IF (EXISTS (
270 SELECT *
271 FROM INFORMATION_SCHEMA.TABLES
272 WHERE TABLE_SCHEMA = 'TheSchema'
273 AND TABLE_NAME = 'TheTable')
274 )
275 BEGIN
276 --Do Stuff
277 END
278```
279***Find tables with given column name***
280```sql
281 select * from INFORMATION_SCHEMA.COLUMNS
282 where COLUMN_NAME like '%clientid%'
283 order by TABLE_NAME
284```
285***Find all user tables***
286```sql
287 SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'
288```
289***Allows explicit values to be inserted into the identity column of a table.***
290```sql
291 SET IDENTITY_INSERT dbo.Tool ON
292```
293The ***DBCC CHECKIDENT*** management command is used to reset identity counter. Example:
294```sql
295 DBCC CHECKIDENT ('[TestTable]', RESEED, 0);
296 GO
297```
298
299***DECLARE and SET Varibales***
300```sql
301 DECLARE @Mojo int
302 SET @Mojo = 1
303 SELECT @Mojo = Column FROM Table WHERE id=1;
304```
305***Add a Foreign Key***
306```sql
307 ALTER TABLE Products WITH CHECK ADD CONSTRAINT [FK_Prod_Man] FOREIGN KEY(ManufacturerID) REFERENCES Manufacturers (ID);
308```
309***Add a NULL Constraint***
310```sql
311 ALTER TABLE TableName ALTER COLUMN ColumnName int NOT NULL;
312```
313***Set Default Value for Column***
314```sql
315 ALTER TABLE TableName ADD CONSTRAINT DF_TableName_ColumnName DEFAULT 0 FOR ColumnName;
316```
317***Create an Index***
318```sql
319 CREATE INDEX IX_Index_Name ON Table(Columns)
320```
321***Check Constraint***
322```sql
323 ALTER TABLE TableName ADD CONSTRAINT CK_CheckName CHECK (ColumnValue > 1)
324```
325***Single Line Comments***
326```sql
327 SET @mojo = 1 --THIS IS A COMMENT
328```
329***Multi-Line Comments***
330```sql
331 /* This is a comment
332 that can span
333 multiple lines
334 */
335```
336***User Defined Function***
337```
338 CREATE FUNCTION dbo.DoStuff(@ID int)
339 RETURNS int
340 AS
341 BEGIN
342 DECLARE @result int
343 IF @ID = 0
344 BEGIN
345 RETURN 0
346 END
347 SELECT @result = COUNT(*)
348 FROM table WHERE ID = @ID
349 RETURN @result
350 END
351 GO
352 SELECT dbo.DoStuff(0);
353```
354***Pivot - To convert rows into columns***
355```sql
356 SELECT Wi_name, Often, Sometimes, Never, NA
357 FROM
358 (
359 SELECT Wi_name, Past_due, 'Selected' T, '' F
360 FROM NG_CA_MISCELLANEOUS_DETAILS
361 ) P1
362 PIVOT
363 (
364 MAX(T) for Past_due IN ([Often], [Sometimes], [Never],[NA])
365 )
366 P2 ORDER BY WI_NAME;
367```
368
369***WITH (NOLOCK)***
370
371is the equivalent of using READ UNCOMMITED as a transaction isolation level. While it can prevent reads being deadlocked by other.
372
373### ***Finding the last identity inserted into a table***
374
375- ***@@IDENTITY*** returns the last identity value generated for any table in the current session, across all scopes. You need to be careful here, since it's across scopes. You could get a value from a trigger, instead of your current statement.
376
377- ***SCOPE_IDENTITY()*** returns the last identity value generated for any table in the current session and the current scope. Generally what you want to use.
378
379- ***IDENT_CURRENT('tableName')*** returns the last identity value generated for a specific table in any session and any scope. This lets you specify which table you want the value from, in case the two above aren't quite what you need (very rare). You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into.
380
381@@IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session.
382
383However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope. That is, if there was a second IDENTITY inserted based on a trigger after your insert, it would not be reflected in SCOPE_IDENTITY, only the insert you performed.
384
385
386IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT.
387
388Identity doesn’t guarantee uniqueness. If you want that, make a PK or add a unique index.