Interview Questions

What is the use of data-tier application (DAC)?

Data-tier application is a management entity that will define the SQL objects (e.g tables, views, instance objects, logins). It allows packaging of SQL server objects into a portable artifact called a DAC package.

What is the maximum size per database for SQL Server Express?

2005 express 4GB 2008 express 4GB 2008 R2 express 10GB 2012 express 10GB 2014 express 10GB 2016 express 10GB

What is RDBMS?

A RDBMS stands for Relational Database Management System and is a type of DBMS that uses a row-based table structure to connect related data elements. Using this system allows the user to connect related data across multiple tables by establishing relationships between the data. RDBMS is a program that allows you to create, update, and administer a relational database. The RDBMS Includes functions that maintain the security, accuracy, integrity, and consistency of the data.

What is the difference between a user-defined function (UDF) and a stored procedure?

A Stored procedure is a set of sql statements with a given name , which is physically stored in database in the compiled form. Once the stored procedure has been "stored", client applications can execute the stored procedure over and over again without sending it to the database server again and without compiling it again. Stored procedures improve performance by reducing network traffic and CPU load. User defined functions are database objects which encapsulates one or more sql statements for reuse, which can accept zero or more parameters and return either a value or table. User defined functions contain useful logic for using another query or other database objects like stored procedures.

How can you validate a backup copy of your database?

A backup strategy, is a very important part of your database. With the backups you are not only able to save your data you are also able to periodically check your database backups. Multiple ways can validate your backups. For example, specify database files and let the Oracle Recovery Manager (RMAN) select backups to use during the restoration of those files, as it would for an actual restore function. This particular procedure ensures that your available backups are adequate to restore the specific database. Another example would be to selecting specific backup sets or individual image copies in Oracle Recovery Manager (RMAN) and correctly validate each of them. This particular procedure indicates whether or not a backup exists and if it can be restored. For this validation approach, one can use the steps described in "Validating Selected Backups." For this restoration approach, one can use the steps portrayed in "Validating Backups for Restore Operations.

What is a deadlock? How can you identify and resolve a deadlock?

A deadlock occurs when two or more processes are waiting on the same resources, and each process is waiting for the other to complete before it can move forward. Deadlocks are managed using three basic techniques. Deadlock Prevention: the transaction aborts when there is a possibility of a deadlock Deadlock Avoidance: the transaction must obtain all the locks it needs before it executes. Deadlock Detection: the DBMS periodically test the database for deadlocks. If encountered the "victim" transaction is aborted and the other continues. There are multiple ways to identify deadlocks i.e. Profile Deadlock Graph, DMB - sys.dm_tran_locks, and Extended Events.

What is a FOREIGN KEY?

A foreign key is a column or group of columns in a relational database that provides a link between data in two different tables. It acts as a cross reference between tables connecting to primary keys to establish a link. These links allow users to access connected data between various tables. The foreign key table is referred to as the child table. Foreign keys are used to ensure referential integrity because it validates the primary key to the foreign key.

What is a subquery? Explain the properties of a subquery?

A subquery is a query inside another query. Subqueries are used to further restrict data to be retrieved by the main query. Subqueries are used in the select, insert, update, and delete statement along with various operators. Some of the rules associated with subqueries include: the subquery must be enclosed in parenthesis, it must be put on the right hand side of a comparison operator, cannot contain an order by clause, and a query cannot contain more than one subquery.

What is a table called, if it has neither cluster nor non-cluster index?

A table is either referred to as a unindexed table or heap. A table that has a non-clustered index is called a heap. Which simply means that the pages are not linked together by pointers. Unindexed tables are useful when you want to store data quickly.

What is a view?

A view a virtual table based on the result of an SQL statement that is stored in the database with an associated name. Once created, a view can be queried just like a table in a database. Views increase security by limiting the degree that tables are exposed to individuals. Updatable views update data through the view.

What is bidirectional transactional replication?

Bidirectional transactional replication is a transactional replication topology that allows two servers to exchange changes with each other. Each server publishes data and then subscribes to a publication with the same data from the other server. Changes that are replicated to other servers are not brought back to the original server.

What is Business Intelligence (BI)?

Business Intelligence, in the world of SQL, is a series of tools used to transform data into decision-useful information. Applications of BI include analytics, performance metrics, and enterprise reporting.

What is collation?

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, and character width.

What is the use of the Dedicated Admin Connection (DAC)?

Dedicated Admin Connection is a diagnostic connection that allows for troubleshooting SQL server when issues arise. Only those with sysadmin role are allowed access.

What is de-normalization?

Denormalization is a strategy used by database managers to increase the performance of a database infrastructure. It takes normalized data and adds redundant data to reduce certain types of problems that arise with queries when you combine different tables data into a single table. Pros of denormalization include faster data retrieval and simpler queries. It is important to note that denormalization doesn't mean to not normalize data.

What are DMVs and DMFs used for?

Dynamic management views (DMV) and dynamic management functions (DMF) are dynamic management objects that allow you to look at the internal workings of SQL server. It returns metadata of the system state that can give users a sense of how it is performing. It replaces the need to query system tables to get system information from those tables.

What is ETL?

ETL stands for Extract, Transform, and Load. It is the process of collecting data from one or more locations into a destination system that presents the data differently from the original location. Extracting exports data from the source. Transforming then changes the data to the form expected by the end location. Finally, loading imports the data into the end destination system

What is a fact table?

Fact tables often store quantitative information of a business process used for analysis and can be categorized by measurement events, i.e., transactional, periodic snapshots, and accumulating snapshots. The location of the fact table is at the center of the star schema (or snowflake schema) encircled by dimension tables. There are two basic column types in a fact table,measures columns and foreign key columns. Different types of measures include additive, non-additive, and semi-additive.

What are GROUPING SETS?

GROUPING SETS are made up of groups of columns that are grouped together using the GROUP BY clause. It is able to define multiple group sets in the same query. Typically a single aggregate query is will define a single group by set. Another way to do this is by using a union of the aggregates based on columns selected in each set

What is a scheduled job and what is a scheduled task?

If there is a consistent or foreseeable cycle then a scheduled task will allow the user to automate it. It can also run in an order by creating job steps. This makes it so the user has control over how things are executed. If one of the automated tasks fails the user can configure the SQL server agent to continue running the other tasks or terminate all together.

What is Service Broker?

In SQL Server, the Service Broker provide native support for messaging and queuing in the SQL Server Database Engine. It enables developers to create applications that use the database engine components to communicate between disparate databases, and build distributed and reliable applications

Define HIERARCHYID datatypes?

It is a data type that allows you to create relationships between data elements in a single table. A HIERARCHYID is typically used to represent a position in a hierarchy tree structure which can be used to identify its breadth and depth.

What is a covered index?

It is an ordinary index, the only reason it says covered is because it covers all columns needed from a specific table. It takes away the need to access the physical table for a given query or operation. It can have more columns than the original table if required.

What is log shipping?

It is the process that automates a backup of the transaction logs from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. Log shipping provides disaster recovery and supports limited read-only access to secondary databases.

What is change Data Capture (CDC) in SQL Server 2008?

It keeps track of insert, update, and delete activity that is used in the SQL server table. This allows users to view the changes in a format that is easy to read. Column info and metadata required to apply changes is saved for row changes and is saved in change tables that copy the column structure of tracked source tables.

Which TCP\IP port does the SQL Server run on? How can it be changed?'

It runs on port 1433. Its change from the Network Utility TCP\IP properties

What is the CHECKPOINT process in SQL Server?

It will look at the transaction log and find the last checkpoint that occured. If a transaction had been started and committed before the last checkpoint, nothing needs to occur. If a transaction did a commit operation after the checkpoint then the DBMS will use the transaction logs to redo the transaction and then update the database. This is done using the after values in the transaction log. Changes are performed in ascending order. Transactions using a rollback operation after the last checkpoint or left active before the failure occurred then nothing needs to happen

What is the difference between a local temporary table and a global temporary table?

Local temporary table only lasts while a connection is made, if it is inside a compound statement, the it will only last for the compound statement. Global temporary table will stay in the database permanently, but rows only will last within a given connection. When connection is lost then the data in the table ceases to exist. The table is still there for when the database is accessed again the next time.

What is the NOLOCK hint?

NOLOCK hints allow the SQL to read data from tables by ignoring any locks that are placed on that data. By ignoring locks, the database improves query performance because the locks don't bottleneck the access of data. The downside to NOLOCK hint is the possibility of the database performing "dirty reads" on the data.

Can we insert data if the clustered Index is disabled?

No, when a clustered index is disabled it makes it so the data rows cannot be accessed.

What is OLTP (Online Transaction Processing)?

Online transaction processing is a software program that is capable of supporting transaction applications over the internet. They are typically used for order entries, financial transactions, customer relationship management, and retail sales. OTP's are typically decentralized systems with the ability to maintain concurrency.

How do you get data from a database on another server?

Open SQL server Right click on the database name and go to tasks Then left click on export data Provide authentication and then select the source you want to copy the data Put where you want to copy the data Continue with following steps in import/export wizard

What is PIVOT and UNPIVOT?

Pivot will change rows into columns and unpivot will do the opposite by changing columns back to rows. When you apply the opposite of the pivot you are simply applying the unpivot operator. This will revert the data back to the original dataset.

What is PowerPivot for Excel?

Power Pivot for Excel is an add-in used to create sophisticated data models and perform powerful data analysis. It allows you to combine large volumes of data from various sources, perform analysis on that data, and share insights with others easily. It allows the use of PivotCharts and Power BI to allow visualization of data.

What is SQL Profiler?

SQL Profiler is an interface used to create and manage traces. Events are saved in a trace file which can later be analyzed or used to replay a series of steps for diagnostic purposes. If what you are tracing becomes too large you are able to filter information based on what is desired. This makes it so only a subset of the total events are being collected. If too much is being traced it can cause the trace table to grow very large adding overhead to the server. (this is more likely to happen when monitoring takes place over a long time period).

What is the maximum number of columns a table can have?

SQL Server will allow a max of 1,024 columns in a standard user-defined table.

How is SQLCMD different from OSQL?

SQLCMD is the newer version introduced in SqlServer 2005, which supports the additional functionality offered in SQL Server 2005. It should be noted that SQLCMD is still capable to do everything that OSQL could.

Where are SQL Server usernames and passwords stored in SQL Server?

System Catalog Views, sys.server_principals and sys.sql_logins These are all stored in the master database.

Why can't I run TRUNCATE TABLE on a published table?

TRUNCATE TABLE is a data definition language (DDL) statement that doesn't track individual row deletions or data manipulation language (DML) triggers. It does not work because replication does not track changes caused by the activity. Transactional replication records changes using the transaction log. Merge replication records changes by DML triggers.

How do you rebuild the master database?

The basic steps for rebuilding a master database go as follows: Insert or download the SQL Server installation media At a command prompt, go to the disc drive or the extracted directory and based on the SQL Server version, run the appropriate command for rebuilding the master databa For 2008 or later use: Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD=StrongPassword ] [ /SQLCOLLATION=CollationName ] At a command prompt, run Services.msc Right-click the SQL Server instance and select Start.

What is the maximum row size for a table?

The maximum row size is 8,060 bytes for a table.

What is the difference between a HAVING clause and a WHERE clause?

The where clause filters information for a given query before any aggregation takes place. Having is used to filter values after the aggregation has taken place. It is important to note that you only use a having clause when you have a group by clause because it puts conditions on the groups.

What is Filestream in SQL Server?

This allows SQL server to store unstructured data on the file system. It leverages APIs and performance of the file system, while ensuring transaction consistency between unstructured data and structured data. It is able to store unstructured data by using varbinary(max) binary large object.

Why does the LOGON trigger fire multiple times during a single login in SSMS?

This is because when you logon through SQL server it actually logs on 3 times to access the Query window, Object explorer, and Intellisense.

What is PAD_INDEX?

This is used to determine if you want to apply a fill factor or not. By specifying ON, it will take the free space indicated by the fill factor and apply it to the intermediate page levels of the index. When it is OFF, the fill factor is ignored and the intermediate page levels are filled to have enough space for at least one row of the max size the index allows. The PAD_INDEX default is OFF.

Why can there be only one clustered index per table and not more

To understand why, it is important to understand what a cluster index does. A cluster index defines how data is ordered physically on a disk. The reason why there can only be one clustered index per table is because there is only one way to order the data physically.

What is transparent data encryption?

Transparent Data Encryption (TDE) encrypts all the data stored within the database's physical files and also any backup files created from the databases. It uses a database encryption keys stored in the database to do this. This helps protect data when it is stolen because it requires an access key to be able to get into the data.

What is the difference between VARCHAR and VARCHAR (MAX) datatypes?

VARCHAR will store up to 8000 characters. You can create an index on varchar column.Values are stored in rows. It also provides slightly better performance VARCHAR(MAX) will store up to 2GB. You cannot create an index on a varchar(max) column. Values can be stored in a row or out of a row. It also provides slight worse performance

What is XPath?

Xml Path Language (XPath) uses path-like syntax to identify and navigate nodes in an XML document. It also lets you compute values from the content of an XML document.

Can we rewrite sub-queries into simple select statements using joins and CTEs?

Yes, for joins, if the subquery is a correlated subquery a join is a much better choice because it will optimize the query itself. CTEs are temporary result sets defined within a single query. They are like a derived table and do not store data in an object. They only last for a particular query.