Tricky Questions
ORDER OF EXECUTION FOR INTEGRITY CHECK
1. Identity Insert Check
2. Nullability constraint
3. Data type check
4. Instead of trigger
5. Primary key
6. Check constraint
7. Foreign key
8. DML Execution (update statements)
9. After Trigger
---------------------------------------------------------------------
FIND OUT # OF RECORDS FROM A TABLE
1. SELECT rows FROM sys.sysindexes WHERE id = OBJECT_ID('UserManagement.CISFUser') AND indid < 2
2. SELECT COUNT(*) FROM UserManagement.CISFUser
3. SELECT COUNT(1) FROM UserManagement.CISFUser
4. SP_SPACEUSED 'UserManagement.CISFUser'
---------------------------------------------------------------------
HOW TO CREATE SORTED VIEW
CREATE VIEW vw
AS
SELECT TOP 100 PERCENT * FROM TableName ORDER BY ColumnName
---------------------------------------------------------------------
COUNT(*) Vs. COUNT(ColumnName)
COUNT(*) à total number of rows from a table
COUNT(ColumnName) à total number of rows from a table WHERE ColumnName IS NOT NULL
-------------------------------------------------------------------
DELETE Vs. TRUNCATE
Delete
|
Truncate
| |
Speed
|
Slower
|
Faster
|
WHERE
|
Can be applied
|
Cannot be applied
|
IDENTITY
|
Cannot reset
|
Will reset
|
TRIGGER
|
Will fire
|
Won’t fire
|
Transaction Log
|
Will be logged
|
Won’t be logged
|
REFERENTIAL INTEGRITY
|
If Child table doesn’t have corresponding record from master table then it will allow
|
Will never ever allow if any referential integrity exists; no matter child table has record or not
|
Table Variable
|
Can be deleted
|
Cannot be truncated
|
CDC
|
Will allow
|
Won’t allow if CDC is enabled on table
|
----------------------------------------------------------------------------------
TEMP TABLE Vs. TABLE VARIABLE
Temp Table
|
Table variable
| |
Syntax
|
CREATE TABLE #TEMP (A INT)
|
DECLARE @TEMP TABLE (A INT)
|
Storage
|
Tempdb
|
Memory
|
Locking & Logging
|
Enabled
|
Disabled (improves performance)
|
Index
|
Possible
|
Not possible
|
Truncate
|
Possible
|
Not possible
|
Scope
|
Will be available in nested SP also
|
Will be limited
|
Dynamic Query
|
Will be allowed inside Dynamic Query
|
Won’t be allowed
|
When to use
|
When large number of records are there and which involves search.
Index creation will help us
|
Small number of records are used. Logging and Locking won’t be there so will be faster
|
Final word
|
No theory has proved which one is good and which one is not. It all depends on requirement and number of records
| |
---------------------------------------------------------------------
SELECT 3/4. WHAT IT WILL RETURN?
ZERO. If you say SELECT 3.0/4.0 then it will return 0.75
---------------------------------------------------------------------
HOW TO INSERT DESIRED VALUE IN IDENTITY COLUMN?
CREATE TABLE IdentityCheck (A INT IDENTITY(1,1), B VARCHAR(10))
INSERT INTO IdentityCheck (B) VALUES ('A'), ('B')
SET IDENTITY_INSERT IdentityCheck ON
INSERT INTO IdentityCheck (A,B) VALUES (5, 'C')
SET IDENTITY_INSERT IdentityCheck OFF
---------------------------------------------------------------------
HOW TO RESET IDENTITY TO DESIRED VALUE?
DBCC CHECKIDENT(IdentityCheck, 'RESEED',100)
Next value would be 101. Even you can reset to negative values or some already existing values. It won't throw any error but when you try to insert only that time it will check and it will throw error depends on other constraints like PK, Unique, Check etc..
---------------------------------------------------------------------
WHAT ARE THE DEFAULT SYS DATABASE AND THEIR PURPOSE?
MASTER --> It holds the information about all the databases and contains system objects
MSDB --> It contains information about sql agent, job, dts pckg & some replication information
TEMPDB --> It contains temporary SPs and Tables (global & local)
MODEL --> It is template database used in creation of any new database
---------------------------------------------------------------------
UNION Vs. UNION ALL
UNION will give only distinct values and UNION ALL will give all the values
However, output of all below 3 queries will be SAME.
-- Query 1
SELECT 1
UNION
SELECT 1
UNION ALL
SELECT 2
-- Query 2
SELECT 1
UNION ALL
SELECT 1
UNION
SELECT 2
-- Query 3
SELECT 1
UNION
SELECT 1
UNION
SELECT 2
--------------------------------------------------------------------
DIFFERENT WAYS TO IMPORT DATA
- BCP
- BULK INSERT
- OPENROWSET
- OPENQUERY
- LINKED SERVER
- OPENDATASOURCE
- OPENQUERY
- LINKED SERVER
- OPENDATASOURCE
