1)What are the different types of Indexes available in SQL Server?
2)What is the difference between Clustered and Non-Clustered Index?
3)What is the default Port No on which SQL Server listens?
4)CTE in sql server?
5)What are the different types of constraints in SQL Server?
Notes:
2.An index speeds retrieval of rows from a table or view. An index contains keys built from one or more columns in the table or view. These keys are stored in a B-tree structure that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.
In a clustered index, the leaf level pages are the actual data pages of the table. When a clustered index is created on a table, the data pages are arranged accordingly based on the clustered index key. There can only be one Clustered index on a table.
In a Non-Clustered index, the leaf level pages does not contain data pages instead it contains pointers to the data pages. There can multiple non-clustered indexes on a single table.
3.1433
4.A common table expression (CTE) is a similar concept to a derived table it’s
a named table expression that is visible only to the statement that defines it. Like derived table, a query against a CTE involves three main parts:
Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
A CTE can be used to:
5.A constraint is a property assigned to a column or the set of columns in a table that prevents certain types of inconsistent data values from being placed in the column(s).
Constraints are used to enforce the data integrity. This ensures the accuracy and reliability of the data in the database.
2)What is the difference between Clustered and Non-Clustered Index?
3)What is the default Port No on which SQL Server listens?
4)CTE in sql server?
5)What are the different types of constraints in SQL Server?
Notes:
2.An index speeds retrieval of rows from a table or view. An index contains keys built from one or more columns in the table or view. These keys are stored in a B-tree structure that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.
In a clustered index, the leaf level pages are the actual data pages of the table. When a clustered index is created on a table, the data pages are arranged accordingly based on the clustered index key. There can only be one Clustered index on a table.
In a Non-Clustered index, the leaf level pages does not contain data pages instead it contains pointers to the data pages. There can multiple non-clustered indexes on a single table.
3.1433
4.A common table expression (CTE) is a similar concept to a derived table it’s
a named table expression that is visible only to the statement that defines it. Like derived table, a query against a CTE involves three main parts:
- The inner query
- The name you assign to the query and its columns
- The outer query
Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
A CTE can be used to:
- Create a recursive query.
- Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
- Reference the resulting table multiple times in the same statement.
5.A constraint is a property assigned to a column or the set of columns in a table that prevents certain types of inconsistent data values from being placed in the column(s).
Constraints are used to enforce the data integrity. This ensures the accuracy and reliability of the data in the database.
- NOT NULL
- CHECK constraints
- UNIQUE constraints
- PRIMARY KEY constraints
- FOREIGN KEY constraints
