SQL server partitioning interview questions.
What is partitioning in SQL Server?
Partitioning is one of the new functionality in SQL Server 2005. Partitioning enables you to split the table across the multiple storage units called file groups based on user specification.
What are the advantages of partitioning?
- Partition allows you to place subset table or index on designated file group.
- Partition helps to us segregate the data within table based on age, gender etc
- This gives advantage to take a back up of subset of table based on specific category.
What are the different steps to partition table or index?
- Create a partition function.
- Create partition scheme mapped to a partition function.
- Create the table or index on the partition scheme.
What is Partition function?
Partition function is a database object that defines the boundary points to the partitioning the data.
Partition function will be first step to create partitioned table, index and view.
What is the syntax to create partition function?
CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )
AS RANGE [ LEFT | RIGHT ]
FOR VALUES ( [ boundary_value [ ,…n ] ] ) [ ; ]
What is partition scheme?
Partition scheme defines file groups, which will be used with specific partition function.
Partition scheme will be second step to create partitioned table, index and view.
What are the requirements to create partition scheme?
- Partition function would required to create partition scheme
- Files groups mentioned in partition scheme must already be part of the database.
- File groups must not be marked as read only.
What are the steps to partition a non partitioned or existing table?
- Create a partition function.
- Create a partition scheme.
- Drop the existing clustered index.
- Recreate the clustered index on the partition scheme.
SQL Server create table Interview question.
What is table and what are the items to consider while creating table in SQL Server?
Tables are basic building blocks of Database. Tables will be used to store data in to database.
Points to remember while creating table in SQL Server
• You should be able to separate data in to different columns in table and assign corresponding data types to those columns.
• The amount of space consumed on disk
• The amount of memory consumed when processing data
• The queries required to manipulate the data for application use
What are the different data types available in SQL Server?
Numeric: Stores numeric values.
Monetary: It stores numeric values with decimal places. It is used specially for currency values.
Data and Time: It stores date and time information.
Character: It supports character based values of varying lengths.
Binary: It stores data in strict binary (0 or 1) Representation.
Special purpose: SQL Server contains Complex data types to handle the XML Documents, Globally unique identifiers etc.
What are the specialized data types in SQL Server?
bit :Stores a 0, 1, or null. Used for basic “flag” values.
TRUE is converted to 1, and FALSE is converted to 0.
Timestamp: An automatically generated value. Each database contains an internal counter that designates a relative time counter not associated with an actual clock. A table can have only one time stamp column, which is set to the database timestamp when the row is inserted or modified.
Uniqueidentifier: A 16-bit GUID used to globally identify a row across databases, instances, and servers.
sql_variant: It can change the data type based on the data that is stored within it.
Cursor: Cursor is used by applications that declare cursors. It contains a reference to the cursor that can be used for operations. This data type cannot be used in a table.
Table: It is used to hold a result set for subsequent processing. This data type cannot be used for a column. The only time you use this data type is when declaring table variables in triggers, stored procedures, and functions.
Xml: It stores an XML document of up to 2 GB in size. You can specify options to force only well-formed documents to be stored in the column.
SQL Server data integrity constraints.
What is primary key?
- A Primary Key in a table identifies each and every row uniquely.
- It should not allow null values.
- We could assign primary key on only column or more than one column also.
What is the difference between primary key and unique key?
- Primary should not allow null; where as unique key will allow nulls.
- By default Primary key is created as clustered index; whereas unique key is created as non clustered index.
What are the different levels of data integrity in SQL Server?
- Entity Integrity
- Domain Integrity
- Referential integrity
Entity Integrity ensures that there are no duplicate rows in a table.
Domain Integrity enforces valid entries for a given column by restricting the type, the format, or the range of possible values.
Referential integrity ensures that rows cannot be deleted, which are used by other records.
Explain different constraints to maintain data integrity in SQL Server?
Check constraints will be useful to limit the range of possible values in a column. We could create check constraints at two different levels
a) Column-level check constraints are applied only to the column and cannot reference data in another other column
b) Table-level check constraints can reference any column within a table but cannot reference columns in other tables
Default constraints enable the SQL Server to write default value to a column when user doesn’t specify a value.
A unique constraint restricts a column or combination of columns from allowing duplicate values.
Primary key constraints:
Primary key constraints will allow a row to be uniquely identified. This will perform by primary key on the table.
Foreign key constraints:
Foreign keys constraints will ensure that the values that can be entered in a particular column exist in a specified table.
SQL Server indexes interview questions.
What is Index?
Indexes are database objects designed to improve query performance.
By applying indexes to one or more columns in table or views, we could see faster data retrieval from these tables.
Explain the structure of Index in SQL server?
An index is structured by the SQL Server Index Manager as a balanced tree (or Btree). A B-tree is similar to an upside-down tree, means with the root of the tree at the top, the leaf levels at the bottom, and intermediate levels in between.
Each object in the tree structure is a group of sorted index keys called an index page.
All search requests begin at the root of a B-tree and then move through the tree to the appropriate leaf level.
What are the different types of indexes in SQL Server?
There are two types of indexes
• Clustered index
• Non Clustered index
Both types of indexes are indexes are structured as B-Trees.
Explain the difference between clustered index and non clustered index?
- A clustered index contains table records in the leaf level of the B-tree.
- There can be only one clustered index on a table or view, because the clustered index key physically sorts the table or view.
(Continue to next page)