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 employeeCREATE FUNCTION getAverageSalary(@salary int)
RETURNS int
AS
BEGIN RETURN(SELECT @salary)
ENDCREATE 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
ENDinserted” 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 -1Select * 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<2Select SERVERPROPERTY('productversion')HAVING CLAUSE and WHERE CLAUSE is :HAVING CLAUSE is used only with the SELECT statement. It is generally used in a GROUP BY 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, 122218074013402287551118 (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 RECOMPILEWITHRECOMPILE in the stored procedure itself.