SQL SERVER Interview Questions

• What is RDBMS?
• What are the Properties of the Relational Tables?
• What is Normalization?
• What is De-normalization?
• How is ACID property related to Database?
• What are the Different Normalization Forms?

• What is a Stored Procedure?
• What is a Trigger?
• What are the Different Types of Triggers?
• What is a View?
• What is an Index?
• What is a Linked Server?
• What is a Cursor?
• What is Collation?

• What is the Difference between a Function and a Stored Procedure?
• What is subquery? Explain the Properties of a Subquery?
• What are Different Types of Join?
• What are Primary Keys and Foreign Keys?
• What is User-defined Functions? What are the types of User-defined Functions that can be created?

• What is an Identity?
• What is DataWarehousing?
• What languages BI uses to achieve the goal?
• What is Standby Servers? Explain Types of Standby Servers.
• What is Dirty Read?
• Why can’t I use Outer Join in an Indexed View?
• What is the Correct Order of the Logical Query Processing Phases?

• Which TCP/IP port does the SQL Server run on? How can it be Changed?
• What are the Difference between Clustered and a Non-clustered Index?
• What are the Different Index Configurations a Table can have?
• What are Different Types of Collation Sensitivity?
• What is OLTP (Online Transaction Processing)?
• What’s the Difference between a Primary Key and a Unique Key?
• What is Difference between DELETE  and TRUNCATE Commands?

• What are Different Types of Locks?
• What are Pessimistic Lock and Optimistic Lock?
• When is the use of UPDATE_STATISTICS command?
• What is the Difference between a HAVING clause and a WHERE clause?
• What is Connection Pooling and why it is Used?
• What are the Properties and Different Types of Sub-Queries?
• What are the Authentication Modes in SQL Server? How can it be Changed?

• Which Command using Query Analyzer will give you the Version of SQL Server and Operating System?
• What is an SQL Server Agent?
• Can a Stored Procedure call itself or a Recursive Stored Procedure? How many levels of SP nesting is possible?
• What is Log Shipping?
• Name 3 ways to get an Accurate Count of the Number of Records in a Table?
• What does it mean to have QUOTED_IDENTIFIER ON? What are the Implications of having it OFF?
• What is the Difference between a Local and a Global Temporary Table?
• What is the STUFF Function and How Does it Differ from the REPLACE Function?
• What is PRIMARY KEY?
• What is UNIQUE KEY Constraint?
• What is FOREIGN KEY?

• What is CHECK Constraint?
• What is NOT NULL Constraint?
• What is the difference between UNION and UNION ALL?
• What is B-Tree?
• How to get @@ERROR and @@ROWCOUNT at the Same Time?
• What is a Scheduled Job or What is a Scheduled Task?
• What are the Advantages of Using Stored Procedures?
• What is a Table Called, if it has neither Cluster nor Non-cluster Index? What is it Used for?
• Can SQL Servers Linked to other Servers like Oracle?
• What is BCP? When is it Used?

• What Command do we Use to Rename a db, a Table and a Column?
• What are sp_configure Commands and SET Commands?
• How to Implement One-to-One, One-to-Many and Many-to-Many Relationships while Designing Tables?
• What is Difference between Commit and Rollback when Used in Transactions?
• What is an Execution Plan? When would you Use it? How would you View the Execution Plan?

• What is Difference between Table Aliases and Column Aliases? Do they Affect Performance?
• What is the difference between CHAR and VARCHAR Datatypes?
• What is the Difference between VARCHAR and VARCHAR(MAX) Datatypes?
• What is the Difference between VARCHAR and NVARCHAR datatypes?
• Which are the Important Points to Note when Multilanguage Data is Stored in a Table?
• How to Optimize Stored Procedure Optimization?
• What is SQL Injection? How to Protect Against SQL Injection Attack?
• How to Find Out the List Schema Name and Table Name for the Database?
• What is CHECKPOINT Process in the SQL Server?

• How does Using a Separate Hard Drive for Several Database Objects Improves Performance Right Away?
• How to Find the List of Fixed Hard Drive and Free Space on Server?
• Why can there be only one Clustered Index and not more than one?
• What is Difference between Line Feed (\n) and Carriage Return (\r)?
• Is It Possible to have Clustered Index on Separate Drive From Original Table Location?
• What is a Hint?
• How to Delete Duplicate Rows?
• Why the Trigger Fires Multiple Times in Single Login?

• What is Aggregate Functions?
• What is Use of @@ SPID in SQL Server?
• What is the Difference between Index Seek vs. Index Scan?
• What is the Maximum Size per Database for SQL Server Express?
• How do We Know if Any Query is Retrieving a Large Amount of Data or very little data?
• What is the Difference between GRANT and WITH GRANT while Giving Permissions to the User?
• How to Create Primary Key with Specific Name while Creating a Table?
• What is T-SQL Script to Take Database Offline – Take Database Online
• How to Enable/Disable Indexes?
• Can we Insert Data if Clustered Index is Disabled?
• How to Recompile Stored Procedure at Run Time?
• Is there any Performance Difference between IF EXISTS (Select null from table) and IF EXISTS (Select 1 from table)?
• What is Difference in Performance between INSERT TOP (N) INTO Table and Using Top with INSERT?
• Does the Order of Columns in UPDATE statements Matter?

• What are the basic functions for master, msdb, model, tempdb and resource databases?
• What is the Maximum Number of Index per Table?
• Explain Few of the New Features of SQL Server 2008 Management Studio
• Explain IntelliSense for Query Editing
• Explain MultiServer Query
• Explain Query Editor Regions
• Explain Object Explorer Enhancements
• Explain Activity Monitors

• What is Service Broker?
• Where are SQL server Usernames and Passwords Stored in the SQL server?
• What is Policy Management?
• What is Database Mirroring?
• What are Sparse Columns?
• What does TOP Operator Do?
• What is CTE?
• What is MERGE Statement?
• What is Filtered Index?
• Which are the New Data Types Introduced in SQL SERVER 2008?

• What are the Advantages of Using CTE?
• How can we Rewrite Sub-Queries into Simple Select Statements or with Joins?
• What is CLR?
• What are Synonyms?
• What is LINQ?
• What are Isolation Levels?
• What is Use of EXCEPT Clause?
• What is XPath?
• What is NOLOCK?
• What is the Difference between Update Lock and Exclusive Lock?

• How will you Handle Error in SQL SERVER 2008?
• What is RAISEERROR? What is RAISEERROR?
• How to Rebuild the Master Database?
• What is the XML Datatype?
• What is Data Compression?
• What is Use of DBCC Commands?
• How to Copy the Tables, Schema and Views from one SQL Server to Another?
• How to Find Tables without Indexes?

• How to Copy Data from One Table to Another Table?
• What is Catalog Views?
• What is PIVOT and UNPIVOT?
• What is a Filestream?
• What is SQLCMD?
• What do you mean by TABLESAMPLE?
• What is ROW_NUMBER()?
• What are Ranking Functions?
• What is Change Data Capture (CDC) in SQL Server 2008?

• How can I Track the Changes or Identify the Latest Insert-Update-Delete from a Table?
• What is the CPU Pressure?
• How can I Get Data from a Database on Another Server?
• What is the Bookmark Lookup and RID Lookup?
• What is Difference between ROLLBACK IMMEDIATE and WITH NO_WAIT during ALTER DATABASE?
• What is Difference between GETDATE and SYSDATETIME in SQL Server 2008?
• How can I Check that whether Automatic Statistic Update is Enabled or not?
• How to Find Index Size for Each Index on Table?
• What is the Difference between Seek Predicate and Predicate?
• What are Basics of Policy Management?
• What are the Advantages of Policy Management?

• What are Policy Management Terms?
• What is the ‘FILLFACTOR’?
• Where in MS SQL Server is ’100’ equal to ‘0’?
• What are Points to Remember while Using the FILLFACTOR Argument?
• What is a ROLLUP Clause?
• What are Various Limitations of the Views?
• What is a Covered index?
• When I Delete any Data from a Table, does the SQL Server reduce the size of that table?
• What are Wait Types?
• How to Stop Log File Growing too Big?
• If any Stored Procedure is Encrypted, then can we see its definition in Activity Monitor?

SQL SERVER – DBCC RESEED Table Identity Value – Reset Table Identity

DBCC CHECKIDENT can reseed (reset) the identity value of the table. For example, YourTable has 25 rows with 25 as last identity. If we want next record to have identity as 35 we need to run following T SQL script in Query Analyzer.

DBCC CHECKIDENT (yourtable, reseed, 34)

If table has to start with an identity of 1 with the next insert then table should be reseeded with the identity to 0. If identity seed is set below values that currently are in table, it will violate the uniqueness constraint as soon as the values start to duplicate and will generate error.

Restore failed for server - system.data.sqlclient.sqlError: exclusive access could not be obtained because database is in use

Error Readout:
Restore Failed for Server -- System.Data.SqlClient.SqlError:Exclusive access could not be obtained because the database is in use

Solution-1:
Run this SQL

USE master
GO
ALTER DATABASE <database name>
SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE <database name>
SET ONLINE

Explanation:
Are you trying to restore a database. Slow down now buddy, there might be other people doing things to that database right now too. You could be polite, run a sp_who, see who's using your database and ask them if it would be ok to do a restore, but who are we kidding it's much more fun/quick to just kick them off. That's exactly what the above statement does.


Solution-2: 
Close all the applications which have a chance to access your database and try to restore. If you get same error after doing this then you can take your database offline by right click on the database. Go to Tasks and click on Make Offline. Once this done. Make it Online with the same manner and try to restore again. 

Solution-3:
Change the database access mode to single user.

USE 
MASTER 
GO      

ALTER DATABASE MyDatabase  
SET SINGLE_USER WITH ROLLBACK IMMEDIATE   
Go      

RESTORE DATABASE MyDatabase  
FROM DISK = N'[file address]'   
Go      
ALTER DATABASE MyDatabase  
SET MULTI_USER with ROLLBACK IMMEDIATE   
Go 

SQL SERVER – Fix: Error: 15138 – The database principal owns a schema in the database, and cannot be dropped

Error: 15138
The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

Workaround / Resolution / Fix:

Let us assume that user was trying to delete user which is named as 'siva' and it exists in the database 'AdventureWorks'.

Now run following script with the context of the database where user belongs.

USE AdventureWorks;
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('siva');


In my query I get following two schema as a result.

Now let us run following query where I will take my schema and and alter authorization on schema. In our case we have two schema so we will execute it two times.

ALTER AUTHORIZATION ON SCHEMA::db_denydatareader TO dbo;
ALTER AUTHORIZATION ON SCHEMA::db_denydatawriter TO dbo;

Now if you drop the database owner it will not throw any error.

Here is generic script for resolving the error:

SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('YourUserID');

Now replace the result name in following script:

ALTER AUTHORIZATION ON SCHEMA::YourSchemaName TO dbo;

 

Top 12 Features of SQL Server 2012

Features of SQL Server 2012

Microsoft has introduced SQL Server 2012 to the world and it's time for IT professionals to start to come to speed on what's new in this highly anticipated version of SQL Server.
1. AlwaysOn Availability Groups -- This feature takes database mirroring to a whole new level. With AlwaysOn, users will be able to fail over multiple databases in groups instead of individually. Also, secondary copies will be readable, and can be used for database backups. The big win is that your DR environment no longer needs to sit idle.
2. Windows Server Core Support -- If you don't know what Windows Server Core is, you may want to come up to speed before Windows 8 (MS is making a push back to the command line for server products). Core is the GUI-less version of Windows that uses DOS and PowerShell for user interaction. It has a much lower footprint (50% less memory and disk space utilization), requires fewer patches, and is more secure than the full install. Starting with SQL 2012, it is supported for SQL Server.
3. Columnstore Indexes -- This a cool new feature that is completely unique to SQL Server. They are special type of read-only index designed to be use with Data Warehouse queries. Basically, data is grouped and stored in a flat, compressed column index, greatly reducing I/O and memory utilization on large queries.
4. User-Defined Server Roles -- DBAs have always had the ability to create custom database role, but never server wide. For example, if the DBA wanted to give a development team read/write access to every database on a shared server, traditionally the only ways to do it were either manually, or using undocumented procedures. Neither of which were good solutions. Now, the DBA can create a role, which has read/write access on every DB on the server, or any other custom server wide role.
5. Enhanced Auditing Features -- Audit is now available in all editions of SQL Server. Additionally, users can define custom audit specifications to write custom events into the audit log. New filtering features give greater flexibility in choosing which events to write to the log.
6. BI Semantic Model -- This is replacing the Analysis Services Unified Dimensional Model (or cubes most people referred to them). It's a hybrid model that allows one data model will support all BI experiences in SQL Server. Additionally, this will allow for some really neat text infographics
7. Sequence Objects -- For those folks who have worked with Oracle, this has been a long requested feature. A sequence is just an object that is a counter -- a good example of it's use would be to increment values in a table, based a trigger. SQL has always had similar functionality with identity columns, but now this is a discrete object.
8. Enhanced PowerShell Support -- Windows and SQL Server admins should definitely start brushing up on their PowerShell scripting skills. Microsoft is driving a lot of development effort into instrumenting all of their server-based products with PowerShell. SQL 2008 gave DBAs some exposure to it, but there are many more in cmdlets in SQL 2012.
9. Distributed Replay -- Once again this is answer to a feature that Oracle released (Real Application Testing). However, and in my opinion where the real value proposition of SQL Server is, in Oracle it is a (very expensive) cost option to Enterprise Edition. With SQL, when you buy your licenses for Enterprise Edition, you get everything. Distributed replay allows you to capture a workload on a production server, and replay it on another machine. This way changes in underlying schemas, support packs, or hardware changes can be tested under production conditions.
10. PowerView -- You may have heard of this under the name "Project Crescent" it is a fairly powerful self-service BI toolkit that allows users to create mash ups of BI reports from all over the Enterprise.
11. SQL Azure Enhancements -- These don't really go directly with the release of SQL 2012, but Microsoft is making some key enhancements to SQL Azure. Reporting Services for Azure will be available, along with backup to the Windows Azure data store, which is a huge enhancement. The maximum size of an Azure database is now up to 150G. Also Azure data sync allows a better hybrid model of cloud and on-premise solutions

What happens when a SQL Server instance is restarted?

SQL Server instance will stop and then start again. Yes, this is obvious and there are lot more things that happen when a restart command is issued on an SQL Server instance.

First of all, the service stops and before the service stops,

1. Checkpoint is issued on all databases
2. Check for the jobs that are running and stop them
3. Release the locks on database files to Operating System
4. Release the memory used by SQL Server instance
5. Flush the metadata collected for DMV’s and DMF’s
6. Record an event in default trace and event viewer regarding the SQL Server instance shutdown

During the starting of SQL Server service,

1. The service is authenticated by verifying the credentials provided in the logon account and the service is started.
2. Startup parameters (master database data file path, log file path and error log file path, etc… if any) are verified
3. The port on which SQL server is listening is opened.
4. Memory is allocated
5. Read master database metadata for information about user databases
6. Attach all the user database
7. Undergo database recovery phases (Analysis, redo and undo phases.)
8. Obtain lock on the database files
9. tempdb files are allocated based on the initial size settings and other setting like collation are copied from model database.
10. An entry to default trace is recorded about the start of SQL Server instance
11. All the events are recorded to SQL Server log file and event viewer
12. Accept connections to databases
13. Start the metadata collection for DMV’s and DMF’s
14. Recompile Stored Procedures

Why is tempdb full, and how can I prevent this from happening?

SQL Server allocates a database called tempdb, primarily for worktable / #temp table usage. Sometimes, you will have one of the following symptoms:

Source: MSSQLSERVER
Event ID: 17052
Description: The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space

Server: Msg 1101, Level 17, State 10, Line 1
Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth.

Causes:

Usually, tempdb fills up when you are low on disk space, or when you have set an unreasonably low maximum size for database growth.

Many people think that tempdb is only used for #temp tables. When in fact, you can easily fill up tempdb without ever creating a single temp table. Some other scenarios that can cause tempdb to fill up:

• any sorting that requires more memory than has been allocated to SQL Server will be forced to do its work in tempdb;
• if the sorting requires more space than you have allocated to tempdb, one of the above errors will occur;
• DBCC CheckDB('any database') will perform its work in tempdb -- on larger databases, this can consume quite a bit of space;
• DBCC DBREINDEX or similar DBCC commands with 'Sort in tempdb' option set will also potentially fill up tempdb;
• large resultsets involving unions, order by / group by, cartesian joins, outer joins, cursors, temp  tables, table variables, and hashing can often require help from tempdb;
• any transactions left uncommitted and not rolled back can leave objects orphaned in tempdb;
  The following will tell you how tempdb's space is allocated:
  USE tempdb
  GO
  EXEC sp_spaceused

Short-term fix:

Restarting SQL Server will re-create tempdb from scratch, and it will return to its usually allocated size. In and of itself, this solution is only effective in the very short term; assumedly, the application and/or T-SQL code which caused tempdb to grow once, will likely cause it to grow again.

To shrink tempdb, you can consider using DBCC ShrinkDatabase, DBCC ShrinkFile (for the data or the log file), or ALTER DATABASE.

If you can't shrink the log, it might be due to an uncommitted transaction. See if you have any long-running transactions with the following command:

DBCC OPENTRRAN

Check the oldest transaction (if it returns any), and see who the SPID is (there will be a line starting with 'SPID (Server Process ID) : <number>'). Use that <number> in the following:

DBCC INPUTBUFFER(<spid>)

This will tell you at least a portion of the last SQL command executed by this SPID, and will help you determine if you want to end this process with:

KILL spid

System Databases Do's and Don'ts

System Databases Do's and Don'ts:

• Data Access - Based on the version of SQL Server query only the recommended objects.  In general the system database objects are being deprecated to a set of views, so be sure all of your scripts are accessing the right objects.  If not, you are going to have a big project in the future to convert all of your scripts.
• Changing Objects - Do not change system objects.  In SQL Server 2005 all of the database objects have been moved to the Resource database which stores the definition for the system objects and can be updated via new SQL Server releases independent of the data.
• New Objects - Creating objects in the system databases is not recommended.  If you have objects that are needed for the instance i.e. administrative items, just create a separate DBA database to store these objects.
• Sneaking a Peak - Up to this point, all of the T-SQL code for the tables, views, stored procedures, functions, etc. has been clear text.  So you can review the objects and learn from the techniques used by Microsoft.
• Dropping Objects - The most prominent reason to drop system objects are for specific types of lock downs and auditing in particular industries.  Although some of those practices are well documented, be sure you understand the ramifications related to administering and developing applications once those restrictions are in place.
• Security - Do not forget about the Public role and Guest user, they are the conduit for users to access the system objects.  So that should answer the question of how people (logins\users) can access the objects based on the object owner or schema, depending on the SQL Server version.
• Backups - Be sure to have a consistent backup process for your system databases.  Including the system databases with your user defined databases might be the best approach if a disaster occurs.
• Scope - Each SQL Server instance (including the Express Edition) has its own set of SQL Server system databases.  As such, if a single Windows server has multiple SQL Server instances installed, a change to one system database only impacts the single instance, not all instances on the Windows server.

Recovery Models

Simple  Recovery Model:

The simple recovery model does not use the transaction log for recovery.
The transaction log can’t be backed up and is automatically truncated at checkpoints.
If you use the simple recovery model, you can only restore full database backups. The advantage to using the simple recovery model is that there is less transaction log management. 

Transactional replication, log shipping, or data mirroring is not allowed in the simple recovery model, as
there is no transaction log.

Disadvantages:
    Not for production systems
    Point in time recovery not possible
    Least data resistant recovery model

Syntax:

ALTER DATABASE <database_name> 

SET RECOVERY SIMPLE

Full Recovery Model:

The Full Recovery Model is the most resistant to data loss of all the recovery models.
The Full Recovery Model makes full use of the transaction log – all database operations are written to the transaction log. This includes all DML statements, but also whenever BCP or bulk insert is used.

Benefits:
    Most resistant to data loss
    Most flexible recovery options - including point in time recovery

Disadvantages:
    Can take up a lot of disk space
    Requires database administrator time and patience to be used properly

Recovery to a point of failure or point in time involves:
    Backing up the currently active transaction log
    Restoring the most recent full database backup without recovery
    Restoring the most recent differential database backup without recovery
    Restoring in sequence any transaction log backups without recovery
    Restoring the last transaction log backup with recovery

Syntax:

ALTER DATABASE <database_name> 

SET RECOVERY FULL

Bulk-Logged Recovery Model:

Databases using the bulk-logged recovery model minimally log bulk operations to the Microsoft SQL Server transaction log.
These operations include CREATE INDEX, SELECT … INTO, writetext, updatetext, and BULK INSERT.
The transaction log does not record sufficient information to recover these changes if media failure occurs after a bulk operation.
You can recover a database to the point of failure, but your data may not be consistent if it was changed by a bulk operation. The process of restoration is the same as that of full database recovery.

Syntax:

ALTER DATABASE <database_name> 

SET RECOVERY BulkLogged

System database in SQL Server

System Databases:

Master:

• The Master database is core database to manage SQL server instance.
• It contains the meta data of the our database

              Instance configuration

              Logins

              End points

              Files of each database

              Linked\remote server

• Per instance configuration.
• The database ID for Master database is 1.

Resource:

• It was introduced in SQL Server 2005.
• It contains tables, meta data, stored procedure that SQL Server needs to run.
• Prior to SQL Server 2005 the system related data was stored in master database.
• It is read-only database that is not accessible via SSMS.
• It does not contain the information about instance and database, because it is only written during an installation of new service pack.
• Rollback to the old service pack. Prior to SQL Server 2005, it couldn't be rolled back to the previous version.
• The database ID for the Resource database is 32767.

Temp:

• It is used for to store temporary objects i.e. tables, views, cursors, create or rebuild indexes sorted in TEMPDB. etc..
• Each time a SQL Server instance is restarted, the TempDB database is reset to its original size.
• So permanent objects can not be created in this database.
• The database ID for the TempDB database is 2.

Model:

• It is template database for all use defined databases.
• When ever a new database is created a copy of the model database is created and renamed with the name of the database being created.
• The only time this does not apply is when you restore or attach a database from a different server.
• The database ID for the Model database is 3.

MSDB:

• This is mostly the SQL Server Agent's database.
• SQL Server Agent uses this database for storing automated job definitions, job schedules, operators and alert definitions.
• SSIS also use for storing SSIS packages.
• The database ID for the MSDB database is 4.

Distribution:

• This database was created when configuring the SQL Server replication.
• This database is responsible for replicate the data.
• It stores the all types of replication history.