SQL Interview Questions Updated

What is the T-SQL script to take database offline and to put it online?

-- To Take the Database Offline Right-click and take it offline ALTER DATABASE [myDB] SET OFFLINE WITH ROLLBACK IMMEDIATE GO -- To Put the Database Online ALTER DATABASE [myDB] SET ONLINE GO Day13

How to enable and disable indexes?

--Disable Index ALTER INDEX [IndexName] ON TableName DISABLE GO --Enable Index ALTER INDEX [IndexName] ON TableName REBUILD GO Day13

How to optimize a stored procedure? We need to know this one.

1) Check the network 2) Use SQL Profiler 3) Comment code out and run it to see what is slowing the stored procedure down.

What are the different types of triggers?

1) DML Trigger (SUDI) are executed when a DML operation like INSERT, UPDATE, or DELETE is fired on a table or a view. There are two types of DML Triggers A) Instead of Trigger Instead of Triggers are fired in place of the triggering action such as an insert, update, or delete. B) After Trigger After triggers are executed following the triggering action, such as an insert, update, or delete. 2) DDL Trigger (Dr. Cat) This type of trigger is fired against Drop Table, Create Table, Alter Table or Login events. DDL Triggers are always After Triggers. DDL Commands: "Dr. Cat" D-drop, R-rename, C-create, A-alter, -Truncate. DML Commands: "SUDI". S-select, U-update, D-delete, I-insert. Day03

Facts Table

1. It contains numeric measures(metrics) of the business. 2. has a PK key value that is typically a concatenated key composed of the PK of the Dimension Table. 3. Joined to Dimension Tables through FKs that reference PKs in the Dimension tables. 4. Tables are narrow, but many records.

Name three ways to get an accurate count of the number of records in a table.

1. SELECT * FROM table1 2. SELECT COUNT(*) FROM table1 3. Select a column from a table and look at the count at the lower right for the count Day08

Which command using Query Analyzer will give you the version of SQL Server and operating system?

1. SELECT @@VERSION 2. SELECT SERVERPROPERTY ('Edition') AS Edition, SERVERPROPERTY (ProductLevel') AS ProductLevel, SERVERPROPERTY ('ProductVersion') AS ProductVersion GO Day08

What are important points to note when multi-language data is stored in a table?

1. The column must be of Unicode data type (NCHAR, NVARCHAR, NTEXT). 2. The value must be prefixed with N while insertion. For example: INSERT INTO table (Hindi_col) values (N'hindi data') Day11

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

A HEAP. A table without an index is good for fast storing of data. Day09

What is the use of @@ SPID in SQL Server?

A SPID returns the session ID of the current user process. And using that session ID, we can find out the last query that was executed. Day13

How to find the list of fixed hard drive and free space on server? DBA question

A built-in stored procedure. EXEC master.. xp_fixeddrives Day12

What is connection pooling and why is it used?

A cache of database connections maintained so that the connections can be reused when future requests to the database are required. They are used to enhance the performance of executing commands on a database. Minimizes the cost of opening and closing connections. Day07

What is OLTP (Online Transaction Processing)?

A category of data processing that is focused on transaction-oriented tasks. It typically involves inserting, updating, and/or deleting small amounts of data in a database. It also mainly deals with large numbers of transactions by a large number of users. Ensures data integrity. Day06

What is a SQL Profiler?

A graphical tool that allows system administrators to monitor events. An interface to create and manage traces and analyze and replay trace results. Events are saved in a trace file that can later be analyzed or used to replay a specific series of steps when trying to diagnose a problem.

What are the differences between a local temporary table and a global temporary table?

A local temporary table exists only for the duration of a connection, or if defined inside a compound statement, for the duration of the compound statement. A global temporary table remains in the database accessible across the connections. Once the connection where the original global table is declared dropped this becomes unavailable. Can be seen by all users connected to the server. Day08

What is a cursor?

A loop that allows row by row processing in a result set. In order to work with a cursor, we need to perform some steps in the following order: • Declare cursor • Open cursor • Fetch row from the cursor • Process fetched row • Close cursor • Deallocate cursor Day03

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

A maximum size of 4 GB per database, which excludes all the log files. Day13

How does using a separate hard drive for several database objects improves performance right away?

A non-clustered index and tempdb can be created on a separate disk to improve performance. The processor does not have to go out and do a lot of work which makes it faster. Day12

What is an index?

A physical structure containing pointers to the data. Indexes are used to speed up queries. Day03

What is a sub-query? Explain the properties of a sub-query.

A query within a query. • Sub-queries allow a SELECT statement to be executed arbitrarily within the body of another SQL statement • Sub-query is executed by enclosing it in a set of parentheses • Sub-queries are generally used to return a single row as an atomic value although they may be used to compare values against multiple rows with the IN keyword • Sub-query can be used as a parameter to a function call • Sub-query can be used anywhere an expression can be used Day04

What is collation?

A set of rules that determine how data is sorted and compared. Day03

What is an identity?

A special type of column that is used to automatically generate key values based on a provided seed (starting point) and increment. Day05

What is a trigger?

A stored procedure that is invoked automatically when a predefined event (INSERT, DELETE, or UPDATE) occurs. Day03

What is a view?

A virtual table. It does not exist in the database until you put a clustered index on it. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views. Day03

What are user-defined functions? What are the types of user-defined functions that can be created?

Allow defining its own T-SQL functions that can accept zero or more parameters and return a single scalar data value or a table data type. • Scalar User-defined Function returns one of the scalar data types. Data types not supported are: text, ntext, image, and timestamp. • Inline Table-value User-defined Function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL SELECT command and in essence provide us with a parameterized, non-updateable view of the underlying tables. • Multi-statement Table-value User-defined Function returns a table, and it is also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement. Day04

What is a linked server?

Allows remote access and the ability to write queries directly on a database on another machine. Day03

What is the difference between index seek vs. index scan?

An index scan reads all the rows in a table row by row. Try to avoid using index scans whenever you can. Index seek is when you use the index to pinpoint the records that are needed to satisfy the query and it is faster. Day13

Why is there only one clustered index and not more than one?

At the leaf level, there can only be one clustered index. The data pages are stored at the leaf level. At the leaf level in a non-clustered index, there are many pointers pointing to a particular location in the memory. It physically stores the data, or arranges data in one order (depends on which column(s) you have defined clustered index and in which order). A set of data can be only stored in only one order; that is why only one clustered index is possible. Day12

What is BCP? When is it used?

BCP (BulkCopy) is a tool used to copy huge amount of data from tables and views. It does not copy the complete structure from source to destination. It is used when you need to import and export large amounts of data in and out of SQL Server databases quickly and easily. Day09

How do we know if a query is retrieving a large amount of data or very little data? DBA question

By using the "Client Statistics" feature in SSMS. Day13

What is the difference between CHAR and VARCHAR datatypes?

CHAR is a fixed-length character data type with a specified set length and the storage size of the CHAR value is equal to the maximum size for this column. Can use when the data entries in a column are expected to be the same size. VARCHAR is a variable-length character data type with a specified maximum length. If a string is less than the maximum length, then it is stored verbatim without any extra characters, e.g. names and emails. Day11

How to create primary key with specific name while creating a table?

CONSTRAINT [PK_TestTable] PRIMARYKEYCLUSTERED ============================= CREATE TABLE [dbo].[TestTable]( [ID] [int] IDENTITY(1,1)NOTNULL, [FirstName] [varchar](100)NULL, CONSTRAINT [PK_TestTable] PRIMARYKEYCLUSTERED ([ID] ASC)) GO Check dynamic management views or system tables and look to see if index exist and if it does, drop it. Then recreate it. Day13

What is the difference between commit and rollback when used in transactions?

Commit makes it permanent and rollback goes back to the original state before the transaction started. Day10

What is the difference between line feed (\n) and carriage return (\r)? Do not need to know this.

Different operating systems have a different way of understanding new line. Mac only understands '\r' as new line, while Unix and Linux understand '\n' as new line character. Our favorite OS Windows needs both the characters together to interpret as new line, which is '\r\n'. This is the reason why a file created in one OS does not open properly in another OS and makes it messy. Day12

What is the correct order of the logical query processing phases?

FROM ON OUTER WHERE GROUP BY CUBE | ROLLUP HAVING SELECT DISTINCT TOP ORDER BY Day05

What is the difference between GRANT and WITH GRANT while giving permissions to the user?

GRANT is giving you permission to do something in the database and WITH GRANT means giving you permission to give someone permission in the database. In case of only GRANT, the username cannot grant the same permission to other users. On the other hand, with the option WITH GRANT, the username will be able to give the permission after receiving requests from other users. Day13

What is a hint?

Hints are options and strong suggestions specified for enforcement by the SQL Server query processor on DML statements. The hints override any execution plan the query optimizer might select for a query. They also deal with execution plans. Mainly, it is used to tell it which index to use. Join Hint: Is used when more than one table is used in a query. T Query Hint: Is used when certain kind of logic has to be applied to a whole query. Any hint used in the query is applied to the complete query as opposed to a part of it. Table Hint: Is used when certain kind of locking mechanism of tables has to be controlled. SQL Server query optimizer always puts the appropriate kind of lock on tables, when any of the Transact SQL operations SELECT, UPDATE, DELETE, INSERT or MERGE is used. Day12

How to find the list schema name and table name for the database?

I would query information_schema.table Day11

How to get @@ERROR and @@ROWCOUNT at the same time?

Include both in the same statement and store them in a local variable. SELECT @RC = @@ROWCOUNT, @ER = @@ERROR Day09

What are the different types of joins?

Inner join returns rows when there is at least one match in both tables. Left outer join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values. Right outer join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values. Full outer join combines left outer join and right outer join and returns rows from either table when the conditions are met and returns NULL values when there is no match. Cross join is a Cartesian join and the result set is the number of rows in the first table multiplied by the number of rows in the second table. Self join is when one table joins to itself with one or two aliases to avoid confusion. A self join is unique in that it involves a relationship with only one table. Day04

What is a standby server? Explain the types of standby servers.

It is a computer that is located in close proximity to the production server(s) and can be used to temporarily replace a production server if it experiences a hardware failure Hot Standby: A redundant method in which one system runs simultaneously with an identical primary system. Upon failure of the primary system, the hot standby system immediately takes over, replacing the primary system. The data is mirrored in real time. Warm Standby: Data is mirrored to the secondary server at regular intervals. There are times when both servers do not contain the exact same data. Cold Standby: The secondary (i.e., backup) system is only called upon when the primary system fails. It receives scheduled data backups, but less frequently than a warm standby. Cold standby systems are used for non-critical applications or in cases where data is changed infrequently. It has to be turned on manually and physically replaces the previous server. Day05

What is de-normalization?

It is the process of adding redundant data to increase performance on a previously normalized database. Day02

What is an execution plan? When would you use it? How would you view it?

It tells you how a query will be executed, or how a query was executed. It is also used for performance tuning and troubleshooting. Within the Query Analyzer, there is an option called "Show Execution Plan" (in the Query drop-down menu), if this option is turned on, it will display query execution plan in a separate window when the query is ran again. Day10

What is CHECKPOINT process in the SQL Server? DBA question

It writes all dirty pages for the current database to disk. Day11

Why does a trigger fire multiple times in a single login? DBA question

Multiple SQL Server services are running and IntelliSense is turned on. Day12

What is the difference between VARCHAR and NVARCHAR datatypes?

NVARCHAR supports Unicode characters, allowing you to use multiple languages in the database. It's takes twice as much space when compared to VARCHAR. Use NVARCHAR only if you are using foreign languages. Day11

Is it possible to have clustered index on separate drive from original table location?

No, it is not possible because the data pages are stored at the leaf level of the clustered index. Day12

Is there a performance difference between IF EXISTS (Select null from table) and IF EXISTS (Select 1 from table)?

No, there is no difference in performance between IF EXISTS (Select null from table) and IF EXISTS (Select 1 from table). Day13

Can you insert data if a clustered index is disabled?

No, we cannot insert data if clustered index is disabled because clustered indexes are in fact original tables which are physically ordered according to one or more keys (Columns). It is physically stored there. Day13

What is a stored procedure?

One or more SQL statements that are saved and stored as an object. Day03

How to implement One-to-One, One-to-Many and Many-to-Many relationships while designing tables?

One-to-one can be implemented with one table or two tables where one table references the other table and it references that one-to-one relationship. One-to-Many are implemented by splitting the data into two tables with primary key and foreign key relationships. Many-to-Many are implemented using junction table with the keys from both the tables forming the composite primary key of the junction table. Day10

What are pessimistic and optimistic locks?

Pessimistic Locking: The record is locked for exclusive use until you have finished with it. It has much better integrity than optimistic locking but requires you to be careful with your application design to avoid Deadlocks. Optimistic Locking: A strategy where you read a record, take note of the version number and check that the version number hasn't changed before you write the record back. If the record is dirty, abort the transaction and the user can re-start it. Day07

Which TCP/IP port does SQL server run on? How can it be changed?

Port 1433 and it can be changed from the Network Utility TCP/IP properties - Port number, both on client and the server. Day06

Why can't I use outer join in an indexed view?

Rows can logically disappear from an indexed view based on an outer join when you insert data into a base table. This makes incrementally updating outer join views relatively complex to implement, and the performance of the implementation would be slower than for views based on standard (inner) join. Day05

Can SQL Servers link to other servers like Oracle?

SQL Server can be linked to any server provided it has an OLE-DB provider from Microsoft to allow a link. Oracle has an OLE-DB provider that Microsoft provides to add it as a linked server to the SQL Server group. Day09

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

Scheduled tasks are automated processes that run on regular or predictable cycles. The user can determine the order in which tasks run by creating job steps within a SQL Server Agent job. Job steps give the user control over flow of execution. If one job fails, the user can configure SQL Server Agent to continue to run the remaining tasks or to stop execution. Day09

What is a SQL Server Agent?

Schedules jobs and handles other automated tasks. Day08

What are sp_configure and SET commands?

Sp_configure commands are used to display or change server-level settings. To change the database-level settings, use ALTER DATABASE. SET commands change the settings that affect only the current user session. Day10

What is a RDBMS?

Supports a tabular structure of the data with enforced relationships between tables through the use of primary and foreign keys. Day02

How are ACID properties related to the database?

The ACID properties ensure data integrity during a transaction. Atomicity the entire transaction takes place at once or it doesn't happen at all. Consistency the transaction never leaves the database in a half-finished state. Isolation the transaction will be carried out and executed as if it is the only transaction in the system. Durability once a transaction has been committed, it will remain committed even if there is a system failure. Day02

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

The HAVING clause can be used only with SELECT statement and is typically used in a GROUP BY clause. It behaves like a WHERE clause when GROUP BY is not used. Basically, the HAVING clause is used only with the GROUP BY function in a query. The WHERE clause is applied to each row before they are part of the GROUP BY function in a query. Day07

What is the STUFF function and how does it differ from the REPLACE function?

The STUFF() function deletes a part of a string and then inserts another part into the string, starting at a specified position. Syntax: STUFF(string, start, length, new_string) Example: SELECT ('SQL Tutorial', 1, 3, 'HTML'); Delete 3 characters from a string, starting in position 1, and then insert "HTML" in position 1: returns HTML Tutorial. REPLACE function() replaces all occurrences of a substring within a string, with a new substring. Syntax: REPLACE(string, old_string, new_string) Example: SELECT REPLACE('ABC ABC ABC', 'a', 'c'); returns cBC cBC cBC. Day08

What is the difference between UNION and UNION ALL?

The UNION command is used to select related information from two tables, much like the JOIN command. When using the UNION command, all selected columns need to be of the same data type and only distinct values are selected. Union does not return duplicates. UNION ALL command selects all values. The UNION ALL command will not eliminate duplicate rows, instead it pulls all rows from all the tables fitting your query specifics and combines them into a table. Day09

When would you use the UPDATE_STATISTICS command?

The command is used when a large amount of data is processed. If a large amount of deletions, modifications or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. Day07

What is B-Tree?

The database server uses a B-tree structure to organize index information and generally, has the following types of index pages or nodes: • Root node: A root node contains node pointers to only one branch node. • Branch node: A branch node contains pointers to leaf nodes or other branch nodes, which can be two or more. • Leaf nodes: A leaf node contains index items and horizontal pointers to other leaf nodes, which can be many. Day09

What is SQL Injection? What methods can be used to protect against a SQL Injection attack?

The placement of malicious code in SQL statements. • Filtering input parameters • Use the escape character in LIKE clause • Use parameterized input with stored procedures • Use the parameters collection with Dynamic SQL • Use type-safe SQL parameters • Wrapping parameters with QUOTENAME() and REPLACE() Day11

What is log shipping?

The process of automating the backup of database and transaction log files on a production SQL server and then restoring them onto a standby server. Day08

What is normalization?

The process of organizing data to avoid duplication and redundancy. Day02

What are the disadvantages of indexes?

They decrease performance on inserts, updates, and deletes. Information from a previous student (asked in an interview).

What are aggregate functions a.k.a. Group Functions?

They perform a calculation on a set of values and return a single value. Aggregate functions ignore NULL values except COUNT function. HAVING clause is used along with GROUP BY for filtering query using aggregate values. The following are aggregate functions: AVG, MIN, CHECKSUM_AGG, SUM, COUNT, STDEV, COUNT_BIG, STDEVP, GROUPING, VAR, MAX, VARP. Math computations over a particular column. Day13

What is a dirty read?

Uncommitted data. Day05

How to recompile stored procedure at run time?

Use WITH RECOMPILE during stored procedure creation or execution. Day13

What is the difference between table aliases and column aliases and do they affect performance?

Usually, when the name of the table or column is very long or complicated to write, aliases are used to refer them. Aliases do not affect performance. Day11

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

VARCHAR stores variable-length character data whose range varies up to 8000 bytes. VARCHAR(MAX) stores variable-length character data whose range may vary up to 2 GB. Day11

What does it mean to have QUOTED_INDENTIFIER on? What are the implications of having it off?

When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all T-SQL rules for identifiers. Day08

What is referential integrity?

Whenever a foreign key value is used, it must reference a valid existing primary key in the parent table in order to maintain the accuracy and consistency of the data within a relationship.

Can a stored procedure call itself or a recursive stored procedure? How many levels of SP nesting is possible?

Yes, a stored procedure can call itself because T-SQL supports recursion. Stored procedures can be nested up to 32 levels. Day08

What is data-warehousing?

the process of collecting, storing, and retrieving data in electronic files. • The data in the database is organized so that all the data elements relating to the same real-world event or object are linked together. • Changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time (time-variant). • Non-volatile - the database is never over-written or deleted, once committed, the data is static, read-only, but retained for future reporting. • Integrated - the database contains data from most or all of an organization's operational applications, and that this data is made consistent. Day05

What are the different normalization forms?

• 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, then remove it to a separate table. • 3NF: Eliminate Columns Not Dependent On Primary 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 is a model limited to only simple (elemental) facts, as expressed in Object Role Model notation • DKNF: Domain-Key Normal Form is a model free from all modification anomalies is said to be in DKNF Day02

What are the differences between a primary key and a unique key?

• Both enforce uniqueness of the column on which they are defined • Primary key by default creates a clustered index on the column • Primary key does not allow NULL values • Unique key allows allows one NULL value • Unique key by default creates a non-clustered index Day06

What are the different types of locks?

• Bulk Update Lock is used when bulk-copying data into a table and the TABLOCK hint is specified. • Exclusive Lock is used for data-modification operations, such as INSERT, UPDATE, or DELETE. It ensures that multiple updates cannot be made to the same resource at the same time. • Intent Lock is used to establish a lock hierarchy. The types of intent locks are as follows: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX). • Schema Lock is used when an operation dependent on the schema of a table is executing. The types of schema locks are schema modification (Sch-M) and schema stability (Sch-S). • Shared Lock is used for operations that do not change or update data (read-only operations), such as a SELECT statement. • Update Lock is used on resources that can be updated. It prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later. Day07

What are the advantages of using stored procedures?

• Can reduce network traffic and latency, boosting application performance • Execution plans can be reused because they are cached in SQL Server's memory, reducing server overhead • Help promote code reuse • Encapsulate logic. Can change stored procedure code without affecting clients • Provide better security to data Day09

What are the different types of collation sensitivity?

• Case Sensitivity: A and a, B and b, etc. • Accent Sensitivity: a and á, o and ó, etc. • Kana Sensitivity: When Japanese Kana characters. Hiragana and Katakana are treated differently, it is called Kana sensitive. • Width Sensitivity: A single-byte character (half-width) and the same character represented as a double-byte character (full-width) are treated differently, it is width sensitive. Day06

What are the differences between a clustered and a non-clustered index?

• Clustered index reorders the way records in the table are physically stored • Table can have only one clustered index • The leaf nodes of a clustered index contain the data pages • A non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk • The leaf node of a non-clustered index does not consist of the data pages • The leaf nodes contain index rows Day06

What are the differences between DELETE and TRUNCATE commands?

• DELETE supports a WHERE clause and TRUNCATE does not support a WHERE clause. • DELETE removes rows one at a time and records an entry in the transaction log for each deleted row and TRUNCATE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log. • DELETE removes data based on conditions specified in the WHERE clause and TRUNCATE removes all the data. • DELETE is slower because it takes row level locks and TRUNCATE is faster because it locks the entire table. • DELETE is a DML command and TRUNCATE is a DDL command. • DELETE does not reset table identity column and TRUNCATE resets table identity column to seed value. • DELETE activates triggers and TRUNCATE does not activate triggers. • The DELETE command removes the rows from a table on the basis of the condition that we provide with a WHERE clause. TRUNCATE will remove all the rows from a table, and there will be no data in the table after we run the TRUNCATE command. * Note: DELETE and TRUNCATE both are logged operations and both can be rolled back when they are within transactions. Using T-SQL - TRUNCATE cannot be rolled back unless it is used in TRANSACTION. OR TRUNCATE can be rolled back when used with BEGIN ... END TRANSACTION using T-SQL. Day06

How to optimize stored procedure optimization?

• Don't use the prefix "sp_" in the stored procedure name • Include SET NOCOUNT ON statement • Keep the transactions as short as possible • Try to avoid using SQL Server cursors whenever possible • Use IF EXISTS (SELECT 1) instead of (SELECT *) • Use schema name with object name • Use the sp_executesql stored procedure instead of the EXECUTE statement • Use TRY-Catch for error handling Day11

What is NOT NULL constraint?

• Enforces that the column will not accept NULL values • Used to enforce domain integrity Day09

What is a unique key constraint?

• Enforces the uniqueness of the values in a set of columns (no duplicate values are entered) • Is used to enforce entity integrity as the primary key constraints Day08

What are the differences between a function and a stored procedure?

• Function must return a value but in stored procedures it is optional (Procedure can return zero or n values). • Functions can have only input parameters for it whereas procedures can have input/output parameters. • Functions can be called from procedures whereas procedures cannot be called from function. Day04

What languages does BI use to achieve the goal?

• MDX - Multidimensional Expressions retrieves data from SSAS cubes. • DMX - Data Mining Extensions used for data mining structures. • XMLA - XML for Analysis commonly used in administration tasks such as backup or restore database, copy and move database, or for learning Meta data information. Day05

What are the properties and different types of sub-queries?

• Must be enclosed in the parenthesis • Must be put on the right hand of the comparison operator • Cannot contain an ORDER BY clause, however sub-query can use ORDER BY when used with TOP clause • Can contain more than one sub-query Types of sub-queries: • Single-row: the sub-query returns only one row • Multiple-row sub-query: the sub-query returns multiple rows • Multiple column: the sub-query returns multiple columns Day07

What are the different index configurations a table can have?

• No indexes (HEAP) • A clustered index • Many non-clustered indexes Day06

What is a foreign key?

• Prevents any actions that would destroy links between tables with the corresponding data values • A foreign key in one table points to a primary key in another table • Prevents actions that would leave rows with foreign key values when there are no primary keys with that value • Used to enforce referential integrity Day08

What are primary and foreign keys?

• Primary keys is used to uniquely indentify a row • Contain unique values • Cannot be a NULL value • There can only be one per table Foreign keys ensure referential integrity and are used to link tables together. Day04

What are the commands to rename a database, a table, and a column?

• To rename a database use sp_renamedb 'oldname', 'newname' • To rename a table use sp_RENAME 'Table_First', 'Table_Last' GO • To rename column use sp_rename 'TableName. [OldcolumnName]', 'NewColumnName', 'Column' Day10

What is a primary key?

• Unique identifier for a row • Every table should have a primary key constraint to uniquely identify each row • Only one primary key constraint per table • Used to enforce entity integrity • CANNOT HAVE A NULL Day08

How to Delete Duplicate Rows?

• Use a CTE with a ROWNUMBER() function PARTITION BY and delete WHERE count > 1 • SELECT DISTINCT into a temp table and then TRUNCATE the original table +++++++++++++++++++++++++++++++++++++++++++++++ WITH CTE (Col1, Col2, DuplicateCount) AS ( SELECT Col1, Col2, ROW_NUMBER() OVER(PARTITION BY Col1, Col2 ORDER BY Col1) AS DuplicateCount FROM DuplicateRecordTable ) DELETE FROM CTE WHERE DuplicateCount>1 Day12

What is CHECK constraint?

• Used to limit the values that can be placed in a column • used to enforce domain integrity. Day09

What are the properties of relational tables?

• Values are atomic (cannot be broken down into smaller pieces) • Each row is unique • The sequence of rows are insignificant • The sequence of columns are insignificant • Column values are of the same kind • Each column must have a unique name Day02

What are the two authentication modes in SQL Server? How can it be changed?

• Window Mode • Mixed Mode - SQL and Windows Go to start - Programs - Microsoft SQL Server and select SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group. Select the server. Then from the "Tools" menu, select "SQL Server Configuration Properties" and choose the "Security" page. Day07