• Home
  • DBA Scripts
    • Oracle Scripts
    • SQL Server Scripts
  • Knowledge Base
    • Oracle Database
    • MS SQL Server
    • MongoDB
    • MariaDB
  • Troubleshoot
    • Oracle Database Issues
    • SQL Server Issues
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • Courses
    • Oracle Database
    • Oracle DBA L1
    • Oracle DBA L2
    • Oracle DBA L3
  • Home
  • DBA Scripts
    • Oracle Scripts
    • SQL Server Scripts
  • Knowledge Base
    • Oracle Database
    • MS SQL Server
    • MongoDB
    • MariaDB
  • Troubleshoot
    • Oracle Database Issues
    • SQL Server Issues
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • Courses
    • Oracle Database
    • Oracle DBA L1
    • Oracle DBA L2
    • Oracle DBA L3
home/Knowledge Base/Interview Questions/SQL Server Interview Questions/MS SQL Server Notes
Popular Search:Oracle, SQL Server, MongoDB

MS SQL Server Notes

119 views 0 October 31, 2023 sandeep

1. SQL Server Versions:

  • SQL Server 1.0
  • SQL Server 4.0
  • SQL Server 6.0
  • SQL Server 7.0
  • SQL Server 8.0 (SQL Server 2000)
  • SQL Server 9.0 (SQL Server 2005)
  • SQL Server 10.0 (SQL Server 2008)
  • SQL Server 10.5 (SQL Server 2008 R2)
  • SQL Server 11.0 (SQL Server 2012)
  • SQL Server 12.0 (SQL Server 2014)
  • SQL Server 13.0 (SQL Server 2016)

2. SQL Server Editions:

  • Data Center
  • Enterprise
  • Enterprise Evaluation
  • Standard Edition
  • Developer Edition
  • BI Edition
  • Web Edition
  • Workgroup Edition
  • Express Edition
  • Mobile Edition
3. SQL Server 2014/ 2016 Pre-requisites:
  • WMI: Windows Management Instrumentation – SQL Server monitoring & Management
  • msxml: Microsoft XML Parser – Database management & SQL Server connection
  • MDAC: Microsoft Data Access Component – Remote access to SQL Server
  • Firewall: Secure connection to SQL Server
4. SQL Server Components:
  • Database Engine: Store real time data
  • SQL Server Analysis Services: Data for analysis & forecast
  • SQL Server Integration Services: Data warehouse
  • SQL Server Reporting Services: Report data, visualization
  • Data Quality Service: Ensure correct/ consistent data store
5. SQL Server Tools:
  • SSMS
  • DTA
  • DMA
  • PSDIAG
  • SQLCMD
  • SQL Server Profiler Tool
  • SQL Server Configuration Manager
6. SQL Server Default DB:
  • Master: Store server properties
  • Model: Template to create new DB
  • Tempdb: Used by developers
  • MSDB: Used by DBA’s
  • Resource DB: Hidden DB, read-only Db, control other DB
7. Collation: A property to specify language and character setting for data storage.
8. Filestream: A property to store big data files and cloud data. Also, used for memory tables.
9. Creating SQL Server Database: Default Name = MSSQLSERVER
– Two files are auto created
  A. Datafile (mdf)
  B. TLog or Audit Log (ldf)
  Additional file (ndf) can be created.
– Datafile contains pages = 8kb size
    8 Pages = 1 Extent
  Group of Files is called FileGroup
  Collection of few consecutive Pages = “Virtual Log Files”
  Start location of every log file is called “Mini LSN”
  LSN: Log Sequence Number
10. Views: Stores static select queries
  • SCHEMABINDING : prevent orphan views
  • WITH CHECK OPTION : prevent unwanted inserts
  • ENCRYPTION : hide view definition
Eg: create view VW_COURSE with schemabinding, encryption as
select course_id, course_name, course_dur from dbo.course where course_name=’COMPUTER’ with check option
=> Can not drop base table when view created on it with schemabinding
11. Procedures: Stores dynamic, DDL, DML statements, accepts parameters, faster compared view & functions.
Stored procedures are database objects used for storing any type of TSQL statements.
– Procedures are faster compared functions.
– Procedures generates an automated complied plan for future executions.
exec sp_recomplie ‘<procedure name>’ — Procedure will be marked for compilation and actual recomp occurs during next time procedure is executed.
exec sp_help ‘<procedure name>’
exec sp_helptext ‘<objectname>’
12. Triggers: DB objects to store any type of T-SQL statements.
  • DML Triggers: Two triggers are auto created 1. Inserted & 2. Deleted also called Memory Tables or Magic Tables.
  • DDL Triggers: 1. Database scoped DDL & 2. Server scoped DDL.
Eg: create trigger TRIGGER1 on COURSE for insert as begin insert into STUDENTS select * from inserted END
13. Transactions: Mechanism to execute complete set of operation or nothing, means “atomic execution”
  • Auto commit: auto start & auto end
  • Explicit Transaction: Manual start & manual end
  • Implicit Transaction: Auto start & manual end
– Commit: save stored transaction details from DB log file to DB data file.
– Checkpoint: Internal process to perform actual commit operations. Governed by “Master DB”.
14. ACID Properties:
  • Atomicity: Every transaction should be executed
  • Consistency: Transaction failure should not impact state of DB.
  • Isolation: Some transaction may or may not depend on other.
  • Durability: Committed transaction is permanent. No data loss.
15. Locks: When ever we perform any operation “locks” are auto allocated. These locks are used to control concurrency in the database.
– These locks are managed by “Lock Manager”. Instance Lock Manager & OS Lock Manager
– Type of Locks:
  • Shared LockSSelect data access
  • Update LockUActual data update in table
  • Exclusive LockXDML operation on table or view
  • Intent LockISRow level lock during data retrieval
  • IX Row level lock during data modification
  • Metadata LockMDRead metadata from primary file of DB for DDL operation
  • Schema LockSCH-SReport metadata in DB and its objects. Eg: SP_HELPDB
  • SCH-M Modify metadata in DB and its objects. Eg: ALTER
=> Lockable Resources: DB, Filegroup, File, Page & DB Objects
=> Lock Escalation: (by lock manager)
– If more than 50% of rows are locked, entire page is locked.
– If more than 50% of pages are locked, entire table is locked.
exec sp_lock
select * from sysprocesses where blocked <> 0
16. Backups: A mechanism to create standby copy of the database in a file format. These are called “Backup Files”.
– Level of Backups & Restores:
  • Complete DB Level Backup
  • Filegroup Level Backup
  • File Level Backup
=> How to take T-SQL backups?
  • Full: backup database [UNI] to disk=’E:\backup\fulldb.bak’ with format
  • Differential: backup database [UNI] to disk=’E:\backup\db_diff.bak’ with format, differential
  • Log: backup log [UNI] to disk=’E:\backup\DB_Log.trn’ with format
– Types of Backups:
  • Full Backup: All pages (Data Pages, Index & Log Pages)
  • Differential Backup: Pages that contain changes since last full backup
  • Log Backup: All pages from log file are back up.
  • Copy Only Backup: Special type of full backup called isolated backup used for replication & DB Migration.
  • Partial Backup: Backup of Read/ Write file group. Do not include Read Only filegroup of the DB.
  • Mirrored Backup: Backup taken to multiple location at same time. One location is duplicate of another.
  • Split Backup: Backup taken at multiple location at same time. One location is continuous part of another.
  • Hot Backup: Backup performed on online DB.
  • Cold Backup: Backup performed on off-line DB. Used for migration from one server to another.
17. Restores: A mechanism to use existing backups and recover the database.
– Phases of restore:
  •   Copy Phase: Content of the backup files are copied to SQL Server.
  •   Redo Phase: Committed transactions from log file are roll forwarded to data file.
  •   Undo Phase: Open transactions from the log file are roll back [undo].
– Types of Restore:
  •   Full database restore
  •   Filegroup restore
  •   File Level restore
  •   Page Level restore
  – Restore Options:
  •   NORECOVERY: DB in “RESTORING STATE”, used in case more backups to restore.
  •   RECOVERY: DB in “ONLINE STATE”, not ready for further restores.
  •   STANDBY: DB in Online – READ ONLY state.
– Restores can be performed using:
  •   User Interface
  •   T-SQL scripts
  => How to restore remaining part of DB after partial restore completed and DB is online?
  • Using “Tail Log Backup”
Eg: backup log [UNI] to disk=’E:\backups\log.trn’ with format, NORECOVERY
=> Verify backup file is valid?
  • restore verifyonly from disk=’E:\backup\fulldb.bak’
=> Verify backup file restore information?
  • restore filelistonly from disk=’E:\backup\fulldb.bak’
=> Restore using T-SQL: (Full, Diff & Logs backup available)
  •   restore database [UNI] from disk=’E:\backup\fulldb.bak’ with NORECOVERY
  •   restore database [UNI] from disk=’E:\backup\db_diff.bak’ with NORECOVERY
  •   restore log [UNI] from disk=’E:\backup\DB_Log.trn’ with RECOVERY
=> Partial Recovery:
  • restore database [UNI] filegroup=’PRIMARY’ from disk=’E:\backup\fulldb.bak’ with NORECOVERY, PARTIAL
  • restore database [UNI] filegroup=’ACCOUNTS’ from disk=’E:\backup\fulldb.bak’ with NORECOVERY
  • restore database [UNI] from disk=’E:\backup\db_diff.bak’ with NORECOVERY
  • restore log [UNI] from disk=’E:\backup\DB_Log.trn’ with RECOVERY
=> Page Restore:
  • alter database [UNI] set single_user
  • restore database [UNI] page=’1:256′ from disk=’E:\backup\db_diff.bak’ with replace   — File ID & Page ID
  • alter database [UNI] set multi_user
  • select * from MSDB..SUSPECT_PAGES
Tags:MS SQL Server

Was this helpful?

Yes  No
Related Articles
  • SQL Server DBA Interview Questions
  • Important Counters (perfmon.exe)
  • Restoring Backup via Scripts
  • Restore Backup in SQL Server
  • SQL Server JOBS
  • Backup using T-SQL
SQL Server Interview Questions
  • MS SQL Server Notes
  • SQL Server DBA Interview Questions
Popular Articles
  • Upgrade Oracle Database from 11.2.0.3 to 12.1.0.2
  • Upgrading MariaDB on Windows
  • Oracle DBA Basic Interview Questions Part A
  • Purging Oracle Sysaux Tablespace
  • Client – Server Architecture
KB Categories
  • Oracle Database
    • Oracle RAC
    • Oracle ASM
    • Oracle GoldenGate
    • Oracle Tuning
    • Oracle 11g Database
    • Oracle Database Upgrade
    • Oracle 12c Database
    • ALL KB Oracle
    • Oracle 18c Database
    • Oracle Standby Database
  • MongoDB
  • MS SQL Server
  • MySQL
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • MariaDB
Database Organization

Database Organization (DB ORG) is knowledge base for DBA to learn and execute the fundamental of different databases under one website. DB ORG is a non-profit initiative. ORACLE, MS SQL Server, MongoDB, MariaDB, Couchbase

Join Our Community
  • KnowledgeBase
  • Documentation
  • Troubleshoot
  • FAQ
Information Links
  • About DBOrg
  • Licenses
  • Terms
  • Privacy Policy
Contact Us
    DB ORG - Database Administration,
    Knowledge Base for DBA
    Mail: support@databaseorg.com
    WhatsApp: (+91) 9306440957
    Monday to Friday: EST - 11:30 AM to 06:30 PM (IST - 9:00 PM to 4:00 AM)
  • © 2023 Database Organization - DB ORG. All Rights Reserved.

Popular Search:Oracle, SQL Server, MongoDB

WhatsApp DB Org