TSQL - MS SQL Server Flashcards
What are the different authentication modes in SQL Server?
Windows authentication is the default, and is often referred to as integrated security because this SQL Server security model is tightly integrated with Windows. …
Mixed mode supports authentication both by Windows and by SQL Server.
(Judge)
What are Default Constraints?
A value used by a column if no value is supplied to that column while inserting data.
A Default value cannot be assigned for identity and timestamp values.
What are User Defined Data Types?
A custom data types created to fit the user’s needs. Allows you to extend the base SQL Server Data Types by providing a descriptive name and format to the database.
What are Cursors? What is the syntax for a Cursor in SQL Server?
A programming structure which allows row-by-row processing of the result sets.
Explanation of Cursor Syntax in SQL Server
Based on the example above, cursors include these components:
DECLARE statements - Declare variables used in the code block
SET\SELECT statements - Initialize the variables to a specific value
DECLARE CURSOR statement - Populate the cursor with values that will be evaluated
NOTE - There are an equal number of variables in the DECLARE CURSOR FOR statement as there are in the
SELECT statement. This could be 1 or many variables and associated columns.
OPEN statement - Open the cursor to begin data processing
FETCH NEXT statements - Assign the specific values from the cursor to the variables to match the DECLARE CURSOR FOR and SELECT statement
NOTE - This logic is used for the initial population before the WHILE statement and then again during each loop in
the process as a portion of the WHILE statement
WHILE statement - Condition to begin and continue data processing
BEGIN…END statement - Start and end of the code block
NOTE - Based on the data processing, multiple BEGIN…END statements can be used
Data processing - In this example, this logic is to backup a database to a specific path and file name, but this
could be just about any DML or administrative logic
CLOSE statement - Releases the current data and associated locks, but permits the cursor to be re-opened
DEALLOCATE statement - Destroys the cursor
Cursor Example to backup databases:
– 1 - Declare Variables
DECLARE @name VARCHAR(50) – database name
DECLARE @path VARCHAR(256) – path for backup files
DECLARE @fileName VARCHAR(256) – filename for backup
DECLARE @fileDate VARCHAR(20) – used for file name
– Initialize Variables
SET @path = ‘C:\Backup'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
– 2 - Declare Cursor
DECLARE db_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’)
– Open the Cursor
OPEN db_cursor
– 3 - Fetch the next record from the cursor
FETCH NEXT FROM db_cursor INTO @name
– Set the status for the cursor
WHILE @@FETCH_STATUS = 0
BEGIN
– 4 - Begin the custom business logic
SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’
BACKUP DATABASE @name TO DISK = @fileName
– 5 - Fetch the next record from the cursor
FETCH NEXT FROM db_cursor INTO @name
END
– 6 - Close the cursor
CLOSE db_cursor
– 7 - Deallocate the cursor
DEALLOCATE db_cursor
What is the Logical Order of Operations in SQL? (MS SQLServer)
- FROM, JOIN
- WHERE
- GROUP BY
- aggregate functions
- HAVING
- window functions
- SELECT
- DISTINCT
- UNION/INTERSECT/EXCEPT
- ORDER BY
- OFFSET
- LIMIT/FETCH/TOP
You can use window functions in SELECT and ORDER BY. However, you can’t put window functions anywhere in the FROM, WHERE, GROUP BY, or HAVING clauses.
What is s JOIN? What are the types of Joins?
- Joins are used in queries to explain the relationship between two or more tables. Joins also allow you to select data from a table depending upon data from another table.
- INNER JOIN -returns records that have matching values in both tables.
- LEFT JOIN or LEFT OUTER JOIN -returns all record from the LEFT table, and the matched record from the RIGHT table.
- RIGHT JOIN or RIGHT OUTER JOIN -returns all record from the RIGHT table, and the matched record from the LEFT table.
- FULL JOIN or FULL OUTER JOIN -retuins all records when there is a match in either the left or the right table.
- CROSS JOINs (the Cartesian Product)
What system function can be used to get the current User’s ID? (MS SQL Server)
USER_ID().
Other System Functions include USER_NAME() SYSTEM_USER() SESSION_USER, CURRENT_USER, USER, SUSER_SUD(), HOST_NAME()
What is a Trigger?
Triggers are special kinds of Stored Procedures that get executed automatically when INSERT, UPDATE or DELETE operation takes place on a table. Multiple triggers may be created for each action.
Triggers are used to implement business rules, auditing.
Triggers can also be used to extend referential integrity checks, but whenever possible, use CONSTRAINTS for this purpose instead of triggers as constraints are much faster.
The VIRTUAL TABLES: INSERTED and DELETED form the basis of Trigger architecture.
How do you invoke a trigger on demand?
Triggers can not be invoked on demand. They get triggered only when an associated INSERT, UPDATE, or
What is a SELF JOIN?
A SELF JOIN is the same as any other Join except that the two instances of the same table are joined in the query.
eg.
SELECT t1.employeename as employee, COALESCE (t2.employeename, ‘No Manager’) as manager
FROM employee t1
LEFT OUTER JOIN employee t2 ON t1.mgrid = t2.emplid
What SQL query will return the name of the first day of the month?
SELECT DATENAME(dw, DATEADD(dd, - DATEPART(dd, GETDATE() ) +1, GETDATE() ))
DATENAME
DATEADD
DATEPART
GETDATE()
Write a SQL query to Select the 6th highest value (salary, etc…). This is a favorite interview question.
SELECT MIN(salary)
FROM EmployeeTable
WHERE salary IN (SELECT TOP 6 salary from EmployeeTable ORDER BY
salary DESC))
How do you remove row duplicates by using the Row_Number() Over() Partitioning clause?
with cte AS ( SELECT Emp_Name, Company, Join_Date ,ROW_NUMBER() OVER (Partition By Emp_Name, Company, Join_Date Order By Emp_Name, Company, Join_Date ) RowNumber FROM Emp_Details ) Select * FROM cte Where RowNumber > 1
Explain the difference between window functions RANK and DENSE RANK.
RANK skips the number of positions after records with the same rank number. The ranking RANK_DENSE returns position numbers from 1 to 6 because it doesn’t skip records with the same rank number:
Unlike DENSE_RANK, RANK skips positions after equal rankings. The number of positions skipped depends on how many rows had an identical ranking. For example, Mary and Lisa sold the same number of products and are both ranked as #2. With RANK, the next position is #4; with DENSE_RANK, the next position is #3.
Both RANK and RANK_DENSE work on partitions of data:
SELECT RANK() OVER(ORDER BY sold products DESC) AS r,
DENSE_RANK() OVER(ORDER BY sold products DESC) AS dr,
first_name,
last_name,
month,
sold products
FROM sales_assistant;
Write a query to enforce the use of a particular index.
SELECT lastname FROM authors (index=aunmind)
– use (index=index_name) after the table name
What is ORDER BY and how is it different from a CLUSTERED INDEX?
The ORDER BY sorts query results by one or more columns up to 8,060 bytes. The Order By happens when we retrieve data from the database. Clustered indexes physically sort data while inserting/updating the table.
Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be stored in only one order.
What is the difference between a UNION and a JOIN?
A JOIN selects columns from two or more tables. A UNION selects rows.
What are different ways to delete duplicate rows from a SQL Table?
- SQL delete duplicate Rows using Group By and having clause
In this method, we use the SQL GROUP BY clause to identify the duplicate rows. The Group By clause groups data as per the defined columns and we can use the COUNT function to check the occurrence of a row. For example:
SELECT [FirstName], [LastName], [Country], COUNT(*) AS CNT FROM [SampleDB].[dbo].[Employee] GROUP BY [FirstName], [LastName], [Country] HAVING COUNT(*) > 1;
- Delete duplicate Rows using Common Table Expressions (CTE) with a SQL ROW_NUMBER function.
WITH CTE([firstname], [lastname], [country], duplicatecount)
AS (SELECT [firstname], [lastname], [country],
ROW_NUMBER() OVER(PARTITION BY [firstname], [lastname], country]
ORDER BY id) AS DuplicateCount
FROM [SampleDB].[dbo].[employee] )
SELECT * FROM CTE;
- Delete duplicate Rows using the RANK function.
SELECT E.ID, E.firstname, E.lastname, E.country, T.rank FROM [SampleDB].[dbo].[Employee] E INNER JOIN ( SELECT *, RANK() OVER(PARTITION BY firstname, lastname, country ORDER BY id) rank FROM [SampleDB].[dbo].[Employee] ) T ON E.ID = t.ID;
- Use Sort Operator in an SSIS package for removing duplicating rows.
- SQL UNION function. To remove the duplicate rows, use the same table and UNION it with an empty result set returned by the same table as shown below
SELECT col1,col2
FROM DuplicateRcordTable
UNION
SELECT col1,col2
FROM DuplicateRcordTable
WHERE 1=0
Provide a sample script to apply Table Permissions.
GRANT SELECT ON ‘project’ TO ‘Jim’
REVOKE SELECT ON ‘project’ TO ‘Jim’
DENY CREATE TABLE TO ‘David’
GRANT VIEW DEFINITION ON SCHEMA :: dbo TO ‘Alex’
GRANT SELECT ON ‘dbo’.’Customer’ TO ‘Alex’
