SQL Server interview Q&A soon to come

Note: Each question/Ans posted will be verified by our technical team before posting.

SQL Server Question and Answer - (SQL Server Database Q&A)

Q1. What is the difference between a primary key and a unique key?

Primary key	Unique key
constraints used to uniquely identify the column on which they are defined	constraints used to uniquely identify the column on which they are defined
by default creates a clustered index on the column	by default creates a non clustered index on the column
Doesnt allow Null	Allows only 1 Null Value

Q2. What is User defined Data Types?

Ans. User defined data types are used to extend the base SQL Server data types by providing descriptive name and format to a column name. For example, there is a column called 'ITjobZone' that appears in many tables. In all these tables it should be varchar(15). In this case one can create user defined data type ITjobZone of varchar(15) and use it across the tables.

CREATE TYPE ITjobZone
FROM varchar(15) NOT NULL

EXEC sp_Addtype ITjobZone, 'varchar(15)','Not Null'

Drop Type ITjobZone is used to drop a user defined Data Type.

EXEC sp_Droptype 'ITjobZone'

Have a better ans? Click here to post.

Q3. Define candidate key, alternate key, composite key?

Ans. A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table.

If the table has more than one candidate key, one of them will be the primary key and rest are called alternate keys.

A key formed by combining at least two or more columns is called composite keys.

Q4. What are defaults?

Ans. Defaults specify what values are used in a column if you do not specify a value for the column when you insert a row. Defaults can be anything that evaluates to a constant, such as a constant, built-in function, or mathematical expression.

CREATE table TEST(
dtDate datetime DEFAULT getdate()
)

IDENTITY columns and Timestamp columns cant have defaults bound to them.

Q5. What are Pages and Extents in SQL Server?

Ans. The fundamental unit of storage in SQL Server is the Page.
The disk space allocated to a data file (.mdf or .ndf) in a database is logically divided into numbered pages contiguously from 0 to n. Disk I/O operations are performed at the page level. That is, SQL Server reads or writes whole data pages.

Extents are a collection of eight physically contiguous pages and are used to efficiently manage the pages. All pages are stored in extents.

Q6. What are GAM and SGAM?

Ans. SQL Server uses these two types of allocation maps to record the allocation of extents.
Global Allocation Map (GAM)
GAM pages record what extents have been allocated. Each GAM covers 64,000 extents, or almost 4 GB of data. The GAM has one bit for each extent in the interval it covers. If the bit is 1, the extent is free; if the bit is 0, the extent is allocated.

Shared Global Allocation Map (SGAM)
SGAM pages record which extents are currently being used as mixed extents and have at least 1 unused page. Each SGAM covers 64,000 extents, or almost 4 GB of data. The SGAM has one bit for each extent in the interval it covers. If the bit is 1, the extent is being used as a mixed extent and has a free page. If the bit is 0, the extent is not used as a mixed extent, or it is a mixed extent and all its pages are being used.

Page Free Space (PFS) pages record the allocation status of each page, whether an individual page has been allocated, and the amount of free space on each page. The PFS has one byte for each page, recording whether the page is allocated, and if so, whether it is empty, 1 to 50 percent full, 51 to 80 percent full, 81 to 95 percent full, or 96 to 100 percent full.

Q7. What are Constraints and What are the different types of Constraints?

Ans. Constraints helps the RDBMS to enforce the integrity of the database automatically, without a need to create rules or triggers
Single and Double

different Types of constraints : NOT NULL, CHEK, UNIQUE, PRIMARY KEY, FOREIGN KEY. NOT NULL specifies that the column does not accept NULL values.

CHECK constraints enforce domain integrity by limiting the values that can be put in a column.

UNIQUE constraints enforce the uniqueness of the values in a set of columns. In a UNIQUE constraint, no two rows in the table can have the same value for the columns. Primary keys also enforce uniqueness, but primary keys do not allow for NULL as one of the unique values.

PRIMARY KEY constraints identify the column or set of columns that have values that uniquely identify a row in a table.

FOREIGN KEY constraints identify and enforce the relationships between tables.

Q8. What is a Join and explain different types of join?

Ans. Joins are used to retrieve data from two or more tables based on logical relationship between tables
Various Types of joins are Inner join, Outer Join and Cross Join. Outer joins are further classified as Left Outer Join, Right Outer Join and Full Outer Join.

Q9 What is SQL injection?

Ans. SQL Injection is an attack in which malicious code is inserted into strings that are later passed to SQL Server for parsing and execution. Any procedure that constructs SQL statements should be reviewed for injection vulnerabilities, because SQL Server will execute all syntactically valid queries it receives.

Q10. What connections does SQL Server supports?

Ans. Windows Authentication - Uses windows Active Directory for authentication.
SQL Server- The User name and Password are stored and managed by SQL Server.

Q11. Which is trusted and which one is un trusted connection?

Ans. Windows Authentication is trusted because the username and password are checked with the Active Directory, the SQL Server authentication is untrusted, since SQL Server is the only verifier participating in the transaction.

Q12. What are the benefits of stored procedures?

Ans. The benefits of using stored procedures in SQL Server rather than Transact-SQL programs stored locally on client computers are:
- Are registered at the server.
- They can have security attributes (such as permissions) and ownership chaining, and certificates can be attached to them.
- Users can be granted permission to execute a stored procedure without having to have direct permissions on the objects referenced in the procedure.
- They can enhance the security of your application.
- Parameterized stored procedures can help protect your application from SQL Injection attacks. For more information see SQL Injection.
- They allow modular programming.
- You can create the procedure once, and call it any number of times in your program. This can improve the maintainability of your application and allow applications to access the database in a uniform manner.
- They are named code allowing for delayed binding.
- This provides a level of indirection for easy code evolution.
- They can reduce network traffic.

More to come soon -> HOT JOBS.