clustered index
is an index that rearranges the table in the order of the index itself. Its leaf nodes contain data pages. A table can have only one clustered index.non-clustered index
is an index that does not re-arrange the table in the order of the index itself. Its leaf nodes contain index rows instead of data pages. A table can have many non-clustered indexes.salary
’ column values from ‘employee
’ tableSELECT MIN(salary) AS MinSalary, MAX(salary) AS MaxSalary, SUM(salary) AS TotalSalary
FROM employee
CREATE FUNCTION getAverageSalary(@salary int)
RETURNS int
AS
BEGIN RETURN(SELECT @salary)
END
CREATE TABLE Users (
id int NOT NULL,
first_name varchar(255) NOT NULL,
last_name varchar(255) NOT NULL,
age int CHECK (age >= 18 AND age <= 60)
);
INSERT, UPDATE, DELETE
, etc. happens on the database server. You can use it, for example, to enforce referential integrity in the database. Suppose you want to delete an author from the ‘authors’ table. You can use triggers to delete all rows in the ‘books’ table which has ‘author_id’ as of the deleted author.INSERT, DELETE,
or UPDATE
.CREATE, ALTER, DROP
, etc.LOGON
event is raised whenever a user’s session is created.CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT | UPDATE | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;
INTENT locks
are used to indicate at a higher level which locks are applied within a lock hierarchy. SELECT, DELETE, INSERT, UPDATE
, etc).inserted
" and "deleted
". These tables are called magic tables because they do not exist physically.inserted
" table stores the data of last inserted row or “insert operation”. deleted
" table stores the data of last deleted row or “delete operation”.ID
’ and ‘Name
’.inserted
” and “deleted
” magic tables.CREATE TRIGGER Inserted_Trigger
ON Employee
FOR INSERT
AS
BEGIN
SELECT * FROM Inserted
END
inserted
” table.inserted
” or “deleted
” magic tables. OUTPUT returns the copy of data inserted into or deleted from a table.INSERT INTO Employee OUTPUT Inserted.* VALUES('Narayan')
INSERT, UPDATE, DELETE
activities applied on the tables. So, as the name suggests, Change Data Capture is used to capture the data that has been changed recently. The column information and metadata required to apply the changes to the target environment are captured for the modified rows and are eventually stored in the change tables. These change tables are the mirror image of the original column structure. JOIN
returns records with matching values in both tablesLEFT JOIN
returns all records from the left table and matching records from the right tableRIGHT JOIN
returns all records from the right table and matching records from the left tableULL JOIN
returns all records from both tablesDELETE
and TRUNCATE
commands are :DELETE
is a DML
command, whereas TRUNCATE
is a DDL
commandDELETE
removes records and records each deletion in the transaction log, whereas TRUNCATE
deallocates pages and records each deallocation in the transaction logTRUNCATE
is generally considered quicker as it makes less use of the transaction logCREATE TABLE #<tablename>
CREATE TABLE ##<tablename>
OLE-DB
provider to give a link. OLE-DB
provider which has link to connect with the SQL server group. +1,-1 or 0
.SIGN(-35) returns -1
Select * from sys.objects where type='tr'
UNION
command is used. It is similar to JOIN
command.UNION ALL
command is equal to the UNION
command, except that UNION ALL
selects all values. It will not remove duplicate rows, instead it will retrieve all rows from all tables.Select * from <tablename> Select count(*) from <tablename> Select rows from sysindexes where id=OBJECT_ID(tablename) and indid<2
Select SERVERPROPERTY('productversion')
HAVING CLAUSE
and WHERE CLAUSE
is :HAVING CLAUSE
is used only with the SELECT
statement. It is generally used in a GROUP B
Y clause in a query.GROUP BY
is not used, HAVING
works like a WHERE
clause. HAVING
clause can be used with the aggregate function.SELECT expr1, expr2, expr3..,
FROM tables
WHERE condition
GROUP BY expr1, expr2, expr3..,
HAVING condition;
WHERE
clause is applied to each row before they become a part of the GROUP BY
function in a query. 'WHERE
' clause cannot use with the aggregate function.WHERE condition;
COALESCE
is used to return first non-null expression within the arguments. This function is used to return a non-null from more than one column in the arguments. COALESCE
accepts all the values but it only returns non-null value present in the expression.COALESCE(expr1, expr2, expr3,......,expr n)
session object
is used to maintain the session of each user. If a user enters into an application, he gets a session id, and when he leaves application, then the session id is deleted. If he enters again into the application, he gets a different session id, but for application object
, once ad id is generated, it maintains the whole application. SQL SERVER 2005/2000
.IF
" condition for the SQL Server
. The most common trace flags used with SQL Server are :1204, 1205, 1222
1807
4013
4022
8755
1118
(SQL 2005 and 2008).FLOOR function
is used to round up a non-integer value to the previous least integer. Floor expression returns a unique value after rounding down the expression.FLOOR (expression)
sp_lock
:sp_lock [ [ @spid1 = ] 'session ID1' ] [ , [@spid2 = ] 'session ID2' ]
[ ; ]
USE SampleDB;
GO
EXEC sp_lock;
GO ​
UPDATE _STATISTICS
is used to update the information used by indexes such as the distribution of key values for one or more statistic groups in the mentioned indexed view or table. SCOPE_IDENTITY
is used to create identity value for tables in the current execution scope.ASCII
, CHAR
, LEFT
, etc. string functions.TCL
is Transaction Control Language
Commands which are used to manage the transactions in the SQL Server
. RECOMPILE
.Exe <SPName> WITH RECOMPILE
WITHRECOMPILE
in the stored procedure itself.