SQL Server Online Test Series 1, SQL Server quiz Series 1

DBCC SHRINKFILE is used to make sql server resize the database file to its smallest possible size. This file shrinks the size of the specified data or log file for the current database, or empties a file by moving the data from the specified file to other files in the same filegroup, allowing the file to be removed from the database. You can shrink a file to a size that is less than the size specified when it was created. This resets the minimum file size to the new value.

UNION selects only distinct values, whereas UNION ALL selects all values and not just distinct ones. UNION returns only unique records from both tables. UNION ALL returns all records from both tables. UNION is used to combine multiple result sets into one result set and will remove any duplicate rows that exist. UNION ALL is used to combine multiple result sets into one result set, but it does not remove any duplicate rows.

Partitioned views allow the data in a large table to be split into smaller member tables. The data is partitioned between the member tables based on ranges of data values in one of the columns. The data ranges for each member table are defined in a CHECK constraint specified on the partitioning column. You can create an indexed view on a partitioned view.

BCP Utility: Bulk Copy Program (BCP) is a command line utility which can be used for exporting SQL server data to text file. It is also used to transfer data from SQL server table to a data file.

DTS Packages: Data Transformation Services (DTS) tool is used to export the data from table to textfile. It is a set of tools that allows you extract, transform, and consolidate data from disparate sources into single or multiple destinations. You can create custom data movement solution using DTS object model. DTS package also notifies if package steps succeed or failed by sending mails to source and destination.

DECLARE CURSOR is used to define a cursor. DECLARE CURSOR accepts both a syntax based on the ISO standard and a syntax using a set of Transact-SQL extensions.

Dynamic cursor affects the changes which is made to database table even after the result set is returned.

RAISERROR is used to return messages back to applications using the same format as a system error or warning message generated by the SQL Server Database Engine. It can be logged in the error log. It prints a message to the application and assigns an error number, state and severity.

The @@TRANCOUNT is used to determine whenever a transaction is still open. It returns the number of active transactions for the current connection. It increments the count value whenever we open a transaction and decrements the count whenever we commit the transaction. Rollback sets the trancount to zero and transaction with save point does to affect the trancount value. It helps application to keep track of the open transactions.

OLE DB and ODBC support application role permissions and password encryption. OLE DB and ODBC are APIs that provide access to a range of data sources. OLE DB and ODBC both provide a relatively universal layer of code that uses the same core API to access different databases.

Read Committed is the default isolation level for all SQL Server databases. The isolation level uses shared locking or row versioning to prevent dirty reads, depending on whether the READ_COMMITTED_SNAPSHOT database option is enabled.

An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. Indexes are created on columns in tables or views. The index provides a fast way to look up data based on the values within those columns.

Fill factor is the value that determines the percentage of space on each leaf-level page to be filled with data. In an SQL Server, the smallest unit is a page, which is made of Page with size 8K. Every page can store one or more rows based on the size of the row. The default value of the Fill Factor is 100, which is same as value 0. The default Fill Factor (100 or 0) will allow the SQL Server to fill the leaf-level pages of an index with the maximum numbers of the rows it can fit. There will be no or very little empty space left in the page, when the fill factor is 100.

DBCC SHOW_STATISTICS displays current query optimization statistics for a table or indexed view. The query optimizer uses statistics to estimate the cardinality or number of rows in the query result, which enables the query optimizer to create a high quality query plan. DBCC SHOW_STATISTICS displays the header, histogram, and density vector based on data stored in the statistics object.

Yes, a clustered index can also be a unique index. You can create clustered indexes on tables by using SQL Server Management Studio or Transact-SQL. With few exceptions, every table should have a clustered index. Besides improving query performance, a clustered index can be rebuilt or reorganized on demand to control table fragmentation. A clustered index can also be created on a view. When you create a UNIQUE constraint, a unique nonclustered index is created to enforce a UNIQUE constraint by default. You can specify a unique clustered index if a clustered index on the table does not already exist.

No, the composite index key is not part of a covering index. A composite index is just an index defined over more than one column. It may or may not be a covering index as well. A covering index is an index that “covers” a query. That is it supplies all information for the query so that SQL Server does not have to touch the base tables.

Referential integrity is a relational database concept, which states that table relationships must always be consistent. Referential integrity means that the foreign key in any referencing table must always refer to a valid row in the referenced table. Referential integrity ensures that the relationship between two tables remains synchronized during updates and deletes.