SQL Interview – Flashcards

RaiseError generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY...CATCH construct.

Master database is system database and it contains information about running server's configuration. When SQL Server 2005 is installed, it usually creates master, model, msdb, tempdb, resourcedb and the distribution system database by default. Only the Master database is the one which is absolutely a must-have database. Without the Master database, the SQL Server cannot be started. This is the reason why it is extremely important to backup the Master database. To rebuild the Master database, run Setup.exe, verify, and repair a SQL Server instance, and rebuild the system databases. This procedure is most often used to rebuild the master database for a corrupted installation of SQL Server.

The xml data type lets you store XML documents and fragments in a SQL Server database. An XML fragment is an XML instance that has a missing single top-level element. You can create columns and variables of the xml type and store XML instances in them. The xml data type and associated methods help integrate XML into the relational framework of SQL Server.

In SQL SERVE 2008, Data Compression comes in two flavors: Row Compression Page Compression Row Compression Row compression changes the format of physical storage of data. It minimize the metadata (column information, length, offsets etc) associated with each record. Numeric data types and fixed-length strings are stored in variable-length storage format, just like Varchar. (Read more here) Page Compression Page compression allows common data to be shared between rows for a given page. It uses the following techniques to compress data: Row compression. Prefix Compression. For every column in a page, duplicate prefixes are identified. These prefixes are saved in compression information headers which resides after the page header. A reference number is assigned to these prefixes and that reference number is replaced where ever those prefixes are being used. Dictionary Compression Dictionary compression searches for duplicate values throughout the page and stores them in CI. The main difference between prefix and dictionary compression is that the former is only restricted to one column while the latter is applicable to the complete page.

The Transact-SQL programming language provides DBCC statements that act as Database Console Commands for SQL Server. DBCC commands are used to perform the following tasks. Maintenance tasks on database, index, or filegroup. Tasks that gather and display various types of information. Validation operations on a database, table, index, catalog, filegroup, or allocation of database pages. Miscellaneous tasks such as enabling trace flags or removing a DLL from memory.

There are multiple ways to do this. "Detach Database" from one server and "Attach Database" to another server. Manually script all the objects using SSMS and run the script on a new server. Use Wizard of SSMS.

Run the following query in the Query Editor. USE ; GO SELECT SCHEMA_NAME(schema_id) AS schema_name ,name AS table_name FROM sys.tables WHERE OBJECTPROPERTY(OBJECT_ID,'IsIndexed') = 0 ORDER BY schema_name, table_name; GO

There are multiple ways to do this. 1) INSERT INTO SELECT This method is used when table is already created in the database earlier and data have to be inserted into this table from another table. If columns listed in the INSERT clause and SELECT clause are same, listing them is not required. 2) SELECT INTO This method is used when table is not created earlier and it needs to be created when data from one table must be inserted into a newly created table from another table. The new table is created using the same data types as those in selected columns.

Catalog views return information that is used by the SQL Server Database Engine. Catalog Views are the most general interface to the catalog metadata and provide the most efficient way to obtain, transform, and present customized forms of this information. All user-available catalog metadata is exposed through catalog views.

A Pivot Table can automatically sort, count, and total the data stored in one table or spreadsheet and create a second table displaying the summarized data. The PIVOT operator turns the values of a specified column into column names, effectively rotating a table. In simpler word UNPIVOT table is reverse of PIVOT Table, however it is not exactly true. UNPIVOTING is for sure reverse operation to PIVOTING but if during PIVOTING process data aggregated the UNPIVOT table does not return to original table.

Filestream allows you to store large objects in the file system and have these files integrated within the database. It enables SQL Server-based applications to store unstructured data such as documents, images, audios and videos in the file system. FILESTREAM basically integrates the SQL Server Database Engine with New Technology File System (NTFS); it basically stores the data in varbinary (max) data type. Using this data type, the unstructured data is stored in the NTFS file system, and the SQL Server Database Engine manages the link between the Filestream column and the actual file located in the NTFS. Using Transact-SQL statements users can insert, update, delete and select the data stored in FILESTREAM-enabled tables.

sqlcmd is enhanced version of the isql and osql, and it provides way more functionality than other two options. In other words, sqlcmd is better replacement of isql (which will be deprecated eventually) and osql (not included in SQL Server 2005 RTM). sqlcmd can work in two modes - i) BATCH and ii) interactive modes.

TABLESAMPLE allows you to extract a sampling of rows from a table in the FROM clause. The rows retrieved are random and they are not in any order. This sampling can be based on a percentage of number of rows. You can use TABLESAMPLE when only a sampling of rows is necessary for the application instead of a full result set.

ROW_NUMBER() returns a column as an expression that contains the row's number within the result set. This is only a number used in the context of the result set; if the result changes, the ROW_NUMBER() will change.

Ranking functions return a ranking value for each row in a partition. All the ranking functions are non-deterministic. The different Ranking functions are as follows: ROW_NUMBER () OVER ([] ) Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. RANK () OVER ([] ) Returns the rank of each row within the partition of a result set. DENSE_RANK () OVER ([] ) Returns the rank of rows within the partition of a result set, without any gaps in the ranking.

Change Data Capture (CDC) records INSERTs, UPDATEs, and DELETEs applied to SQL Server tables and makes a record available of what changed, where, and when, in simple relational 'change tables' rather than in an esoteric chopped salad of XML. These change tables contain columns that reflect the column structure of the source table you have chosen to track along with the metadata needed to understand the changes that have been made.

In SQL Server 2005 and earlier versions, there is no inbuilt functionality to know which row was recently changed and what the changes were. However, in SQL Server 2008, a new feature known as Change Data Capture (CDC) has been introduced to capture the changed data.

CPU pressure is a state wherein the CPU is fully occupied with currently assigned tasks and there are more tasks in the queue that have not yet started.

If you want to import data only through T-SQL query, then use OPENDATASOURCE function. To repeatedly get data from another server, create a linked server and then use the OPENQUERY function or use 4-part naming. If you are not adhered with T-SQL, then it is better to use import/export wizard, and you can save it as a SSIS package for future use.

When a small number of rows are requested by a query, the SQL Server optimizer will try to use a non-clustered index on the column or columns contained in the WHERE clause to retrieve the data requested by the query. If the query requests data from columns not present in the non-clustered index, then the SQL Server must go back to the data pages to get the data in those columns. Even if the table has a clustered index or not, the query will still have to return to the table or clustered index to retrieve the data. In the above scenario, if table has clustered index, it is called bookmark lookup (or key lookup); if the table does not have clustered index, but a non-clustered index, it is called RID lookup.

ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE: Specifies whether to roll back after a specified number of seconds or immediately if transaction is not complete. NO_WAIT: Specifies that if the requested database state or option change cannot complete immediately without waiting for transactions to commit or roll back on their own, then the request will fail.

In case of GETDATE, the precision is till milliseconds, and in case of SYSDATETIME, the precision is till nanoseconds.

The following query can be used to know if Automatic Statistic Update: SELECT is_auto_create_stats_on,is_auto_update_stats_on FROM sys.databases WHERE name ='YOUR DATABASE NAME

We can use the following query to find the size of index. SELECT * FROM sys.indexes WHERE OBJECT_ID=OBJECT_ID('HumanResources.Shift')

Seek Predicate is the operation that describes the b-tree portion of the Seek. Predicate is the operation that describes the additional filter using non-key columns. Based on the description, it is very clear that Seek Predicate is better than Predicate as it searches indexes, whereas in Predicate, the search is on non-key a column - which implies that the search is on the data in page, files itself.

SQL server 2008 has introduced a policy management framework, which is the latest technique for SQL server database engine. SQL policy administrator uses SQL Server Management Studio to create policies that can handle entities on the server side like the SQL Server objects and the instance of SQL Server databases. It consists of three components: policy administrators (who create policies), policy management, and explicit administration. Policy-based management in SQL Server assists the database administrators in defining and enforcing policies that tie to database objects and instances. These policies allow the administrator to configure and manage SQL server across the enterprise.

The following advantages can be achieved by appropriate administration of policy management system. It interacts with various policies for successful system configuration. It handles the changes in the systems that are the result of configuration against authoring policies. It reduces the cost of ownership with simple elaboration of administration tasks. It detects various compliance issues in SQL Server Management Studio.

To have a better grip on the concept of Policy-based management, there are some key terms you need to understand. Target - A type of entity that is appropriately managed by Policy-based management. For example, a table, database and index, to name a few. Facet -A property that can be managed in policy-based management. A clear example of facet is the name of Trigger or the Auto Shrink Property of database. Conditions - Criteria that specifies the state of facet to true or false. For example, you can adjust the state of a facet that gives you clear specifications of all stored procedures in the Schema 'Banking'. Policy - A set of rules specified for the server objects or the properties of database.

A "FILLFACTOR" is one of the important arguments that can be used while creating an index. According to MSDN, FILLFACTOR specifies a percentage that indicates how much the Database Engine should fill each index page during index creation or rebuild. Fill-factor is always an integer valued from 1 to 100. The fill-factor option is designed for improving index performance and data storage. By setting the fill-factor value, you specify the percentage of space on each page to be filled with data, reserving free space on each page for future table growth. Specifying a fill-factor value of 70 would imply that 30 percent of each page will be left empty, providing space for index expansion as data is added to the underlying table. The fill-factor setting applies only when the index is created or rebuilt.

Fill-factor settings of 0 and 100 are equal!

If fill-factor is set to 100 or 0, the Database Engine fills pages to their capacity while creating indexes. The server-wide default FILLFACTOR is set to 0. To modify the server-wide default value, use the sp_configure system stored procedure. To view the fill-factor value of one or more indexes, use sys.indexes. To modify or set the fill-factor value for individual indexes, use CREATE INDEX or ALTER INDEX statements. Creating a clustered index with a FILLFACTOR < 100 may significantly increase the amount of space the data occupies because the Database Engine physically reallocates the data while building the clustered index.

ROLLUP clause is used to do aggregate operation on multiple levels in hierarchy. If we want sum on different levels without adding any new column, then we can do it easily using ROLLUP. We have to just add the WITH ROLLUP Clause in group by clause.

ORDER BY clause does not work in View. Regular queries or Stored Procedures give us flexibility when we need another column; we can add a column to regular queries right away. If we want to do the same with Views, then we will have to modify them first. Index created on view not used often. Once the view is created and if the basic table has any column added or removed, it is not usually reflected in the view till it is refreshed. One of the most prominent limitations of the View it is that it does not support COUNT (*); however, it can support COUNT_BIG (*). UNION Operation is now allowed in Indexed View. We cannot create an Index on a nested View situation means we cannot create index on a view which is built from another view. SELF JOIN Not Allowed in Indexed View. Outer Join Not Allowed in Indexed Views. Cross Database Queries Not Allowed in Indexed View.

It is an index that can satisfy a query just by its index keys without having needed to touch the data pages. It means that when a query is fired, SQL Server doesn't need to go to the table to retrieve the rows, but can produce the results directly from the index as the index covers all the columns used in query.

When data are deleted from any table, the SQL Server does not reduce the size of the table right away; however, it marks those pages as free pages, showing that they belong to the table. When new data are inserted, they are put into those pages first. Once those pages are filled up, SQL Server will allocate new pages. If you wait for sometime, the background process de-allocates the pages, finally reducing the page size.

There are three types of wait types, namely, Resource Waits. Resource waits occur when a worker requests access to a resource that is not available because that resource is either currently used by another worker or it's not yet available. Queue Waits. Queue waits occur when a worker is idle, waiting for work to be assigned. External Waits. External waits occur when an SQL Server worker is waiting for an external event.

If your Transaction Log file was growing too big and you wanted to manage its size, then instead of truncating transaction log file, you should choose one of the options mentioned below. 1) Convert the Recovery Model to Simple Recovery If you change your recovery model to Simple Recovery Model, then you will not encounter the extraordinary growth of your log file. However, please note if you have one long running transaction it will for sure grow your log file till the transaction is complete. 2) Start Taking Transaction Log Backup In this Full Recovery Model, your transaction log will grow until you take a backup of it. You need to take the T-Log Backup at a regular interval. This way, your log would not grow beyond some limits.

No, we can't see definition of encrypted stored procedure in Activity Monitor.

A data warehouse is the main repository of an organization's historical data, its corporate memory. It contains the raw material for management's decision support system. The critical factor leading to the use of a data warehouse is that a data analyst can perform complex queries and analysis, such as data mining, on the information without slowing down the operational systems (Ref: Wikipedia). Data warehousing collection of data designed to support management decision making. Data warehouses contain a wide variety of data that present a coherent picture of business conditions at a single point in time. It is a repository of integrated information, available for queries and analysis.

Business Intelligence (BI) refers to technologies, applications and practices for the collection, integration, analysis, and presentation of business information and sometimes to the information itself. The purpose of BI is to support better business decision making. Thus, BI is also described as a decision support system (DSS). BI systems provide historical, current, and predictive views of business operations, most often using data that has been gathered into a data warehouse or a data mart and occasionally working from operational data.

Dimensional table contains textual attributes of measurements stored in the facts tables. Dimensional table is a collection of hierarchies, categories and logic which can be used for user to traverse in hierarchical nodes.

Dimensional data model concept involves two types of tables and it is different from the 3rd normal form. This concept uses Facts table, which contains the measurements of the business, and Dimension table, which contains the context (dimension of calculation) of the measurements.

Fact table contains measurements of business process. Fact table contains the foreign keys for the dimension tables. For instance, if your business process is 'paper production', 'average production of paper by one machine' or 'weekly production of paper' will be considered as the measurement of business process.

There are four different fundamental stages of Data Warehousing. Offline Operational Databases: Data warehouses in this initial stage are developed by simply copying the database of an operational system to an off-line server where the processing load of reporting does not impact on the operational system's performance. Offline Data Warehouse: Data warehouses in this stage of evolution are updated on a regular time cycle (usually daily, weekly or monthly) from the operational systems, and the data is stored in an integrated reporting-oriented data structure Real Time Data Warehouse: Data warehouses at this stage are updated on a transaction or event basis, every time an operational system performs a transaction (e.g. an order or a delivery or a booking) Integrated Data Warehouse: Data warehouses at this stage are used to generate activity or transactions that are passed back into the operational systems for use in the daily activity of the organization.

There are two different ways to load data in dimension tables. Conventional (Slow): All the constraints and keys are validated against the data before it is loaded; this way data integrity is maintained. Direct (Fast): All the constraints and keys are disabled before the data is loaded. Once data is loaded, it is validated against all the constraints and keys. If data is found invalid or dirty, it is not included in index, and all future processes on this data are skipped.

Foreign keys of dimension tables are primary keys of entity tables. Foreign keys of facts tables are primary keys of Dimension tables.

Data Mining is the process of analyzing data from different perspectives and summarizing it into useful information.

A view takes the output of a query and makes it appear like a virtual table; and it can be used in place of tables. A materialized view provides indirect access to table data by storing the results of a query in a separate schema object.

OLTP is abbreviation of On-Line Transaction Processing. This system is an application that modifies data At the very instant it is received and has a large number of concurrent users.

OLAP is abbreviation of Online Analytical Processing. This system is an application that collects, manages, processes and presents multidimensional data for analysis and management purposes.

Data Source OLTP: Operational data is from original data source of the data OLAP: Consolidation data is from various sources. Process Goal OLTP: Snapshot of business processes which do fundamental business tasks OLAP: Multi-dimensional views of business activities of planning and decision making Queries and Process Scripts OLTP: Simple quick running queries ran by users. OLAP: Complex long running queries by system to update the aggregated data. Database Design OLTP: Normalized small database. Speed will be not an issue because of a small database, and normalization will not degrade performance. This adopts the entity relationship (ER) model and an application-oriented database design. OLAP: De-normalized large database. Speed is an issue because of a large database and de-normalizing will improve performance as there will be less tables to scan while performing tasks. This adopts star, snowflake or fact constellation mode of subject-oriented database design. Back up and System Administration OLTP: Regular Database backup and system administration can do the job. OLAP: Reloading the OLTP data is considered as a good backup option. What are Normalization Forms? There are different types of normalization forms such as 1NF: Eliminate Repeating Groups Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain. 2NF: Eliminate Redundant Data If an attribute depends on only part of a multi-valued key, remove it to a separate table. 3NF: Eliminate Columns Not Dependent On Key If attributes do not contribute to a description of the key, then remove them to a separate table. All attributes must be directly dependent on the primary key BCNF: Boyce-Codd Normal Form If there are non-trivial dependencies between candidate key attributes, then separate them out into distinct tables. 4NF: Isolate Independent Multiple Relationships No table may contain two or more 1:n or n:m relationships that are not directly related. 5NF: Isolate Semantically Related Multiple Relationships There may be practical constrains on information that justify separating logically related many-to-many relationships. ONF: Optimal Normal Form A model limited to only simple (elemental) facts, as expressed in Object Role Model notation. DKNF: Domain-Key Normal Form A model free from all modification anomalies. Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.

ODS is the abbreviation of Operational Data Store ‑ a database structure that is a repository for near real-time operational data rather than long-term trend data. The ODS may further become the enterprise-shared operational database, allowing operational systems that are being re-engineered to use the ODS as their operation databases.

Entity Relationship (ER) Diagrams are a major data modeling tool and will help organize the data in your project into entities and define the relationships between the entities. This process has enabled the analyst to produce a good database structure so that the data can be stored and retrieved in a most efficient manner. An entity-relationship (ER) diagram is a specialized graphic that illustrates the interrelationships between entities in a database. A type of diagram used in data modeling for relational data bases. These diagrams show the structure of each table and the links between tables.

ETL is abbreviation of extract, transform, and load. ETL is software that enables businesses to consolidate their disparate data while moving it from place to place, and it doesn't really matter that that data is in different forms or formats. The data can come from any source. ETL is powerful enough to handle such data disparities. First, the extract function reads data from a specified source database and extracts a desired subset of data. Next, the transform function works with the acquired data - using rules or lookup tables, or creating combinations with other data - to convert it to the desired state. Finally, the load function is used to write the resulting data to a target database.

VLDB is abbreviation of Very Large Database. For instance, a one-terabyte database can be considered as a VLDB. Typically, these are decision support systems or transaction processing applications serving a large number of users.

No. OLTP database tables are normalized, and it will add additional time to queries to return results. Additionally, the OLTP database is small; it does not contain data from a long period (many years), which needs to be analyzed. A OLTP system is basically an ER model and not a Dimensional Model. If a complex query is executed on an OLTP system, it may lead to heavy overhead on the OLTP server that will affect the normal business processes.

The foreign keys of facts tables are primary keys of Dimension tables. It is clear that the fact table contains columns which are a primary key to another table that itself make a normal form table.

A lookup table is the table placed on the target table based upon the primary key of the target; it just updates the table by allowing only modified (new or updated) records based on the lookup condition.

An aggregate table contains the summary of existing warehouse data, which is grouped to certain levels of dimensions. It is always easy to retrieve data from aggregated tables than visiting original table which has millions of records. Aggregate tables reduce the load in the database server and improve the performance of the query, and they also can retrieve the result quickly.

Data warehousing captures business activity data. Real-time data warehousing captures business activity data as it occurs. As soon as the business activity is complete and there is data about it, the completed activity data flows into the data warehouse and becomes available instantly.

Conformed dimensions mean the exact same thing with every possible fact table to which they are joined. They are common to the cubes.

Conformed dimensions are the dimensions which can be used across multiple Data Marts in combination with multiple facts tables accordingly.

Time dimensions are usually loaded by a program that loops through all possible dates that may appear in the data. 100 years may be represented in a time dimension, with one row per day.

Level of granularity means the level of detail that you put into the fact table in a data warehouse. Level of granularity implies the detail you are willing to put for each transactional fact.

Non-additive facts are facts that cannot be summed up for any of the dimensions present in the fact table. However, they are not considered as useless. If there are changes in dimensions, the same facts can be useful.

A fact table that does not contain numeric fact columns is called a factless facts table.

SCD is the abbreviation of slowly changing dimensions. SCD applies to cases where the attribute for a record varies over time. There are three different types of SCD. SCD1: The new record replaces the original record. Only one record exists in database - current data. SCD2: A new record is added into the customer dimension table. Two records exist in the database - current data and previous history data. SCD3: The original data is modified to include new data. One record exists in database - new information is attached with old information in same row.

Hybrid SCDs are combination of both SCD 1 and SCD 2. It may happen that in a table, some columns are important and we need to track changes for them, i.e. capture the historical data for them, whereas in some columns even if the data changes, we do not care.

BUS Schema consists of a master suite of confirmed dimension and standardized definition of facts.

Star schema is a type of organizing the tables such that we can retrieve the result from the database quickly in the warehouse environment.

In Snowflake Schema, each dimension has a primary dimension table, to which one or more additional dimensions can join. The primary dimension table is the only table that can join to the fact table.

Star schema: A single fact table with N number of dimensions; all dimensions will be linked directly with a fact table. This schema is de-normalized and results in simple join and less complex query as well as faster results. Snow schema: Any dimension with extended dimensions is known as snowflake schema; dimensions maybe interlinked or may have one-to-many relationship with other tables. This schema is normalized, and results in complex join leading very complex query (as well as slower results).

ER modeling is used for normalizing the OLTP database design. Dimensional modeling is used for de-normalizing the ROLAP/MOLAP design.

If a table contains values, which are neither dimension nor measures, then it is called a degenerate dimension table.

Data modeling is probably the most labor intensive and time consuming part of the development process. The goal of the data model is to make sure that the all data objects required by the database are completely and accurately represented. Because the data model uses easily understood notations and natural language, it can be reviewed and verified as correct by the end users. In computer science, data modeling is the process of creating a data model by applying a data model theory to create a data model instance. A data model theory is a formal data model description. In data modeling, we are structuring and organizing data. These data structures are then typically implemented in a database management system. In addition to defining and organizing the data, data modeling will impose (implicitly or explicitly) constraints or limitations on the data placed within the structure. Managing large quantities of structured and unstructured data is a primary function of information systems. Data models describe structured data for storage in data management systems such as relational databases. They typically do not describe unstructured data, such as word processing documents, email messages, pictures, digital audio, and video.

A surrogate key is a substitution for the natural primary key. It is just a unique identifier or number for each row that can be used for the primary key to the table. The only requirement for a surrogate primary key is that it should be unique for each row in the table. It is useful because the natural primary key can change and this makes updates more difficult. Surrogated keys are always integer or numeric.

A number of very small dimensions may get lumped together to form a single dimension, i.e. a junk dimension - the attributes are not closely related. Grouping of Random flags and text Attributes in a dimension and moving them to a separate sub dimension is known as junk dimension.

A data mart (DM) is a specialized version of a data warehouse (DW). Like data warehouses, data marts contain a snapshot of operational data that helps business people to strategize based on analyses of past trends and experiences. The key difference is that the creation of a data mart is predicated on a specific, predefined need for a certain grouping and configuration of select data. A data mart configuration emphasizes easy access to relevant information (Reference: Wiki). Data Marts are designed to help the manager make strategic decisions about their business.

Data Warehouse is the place where the data is stored for analysis, whereas OLAP is the process of analyzing the data, managing aggregations, partitioning information into cubes for in depth visualization.

Cubes are logical representation of multidimensional data. The edge of the cube contains dimension members and the body of the cube contains data values. The linking in cube ensures that the data in the cubes remain consistent.

You can disconnect the report from the catalog to which it is attached by saving the report with a snapshot of the data.

An active data warehouse provides information that enables decision-makers within an organization to manage customer relationships nimbly, efficiently and proactively.

Data warehousing deals with all aspects of managing the development, implementation and operation of a data warehouse or data mart, including meta data management, data acquisition, data cleansing, data transformation, storage management, data distribution, data archiving, operational reporting, analytical reporting, security management and backup/recovery planning. Business intelligence, on the other hand, is a set of software tools that enable an organization to analyze measurable aspects of their business such as sales performance, profitability, operational efficiency, effectiveness of marketing campaigns, market penetration among certain customer groups, cost trends, anomalies and exceptions. Typically, the term 'business intelligence' is used to encompass OLAP, data visualization, data mining and query/reporting tools. (Reference: Les Barbusinski)

Master Data Services or MDS helps enterprises standardize the data people rely on to make critical business decisions. With Master Data Services, IT organizations can centrally manage critical data assets companywide and across diverse systems, enable more people to securely manage master data directly, and ensure the integrity of information over time.