Question: What is Primary and
foreign key in SQL server?
Answer: The primary key ensures the
row has a unique identity. The primary key has unique values and but can’t be null.
A table can have only one primary key.
The foreign key ensures data integrity
of the relationship tables.
Question: What is an identity in
SQL Server?
Answer: If we want to set the unique
value of a column, then we have to use Identity. Identity automatically
generates numeric value; we can set start and increment value.
Question: Can you please tell me the default port of TCP/IP of SQL Server? Can we change it if yes then how?
Answer: The default port of SQL-server is 1433, yes it can be changed. Network Utility TCP/IP propertiesà Port Number.
Question: What is the difference between the clustered and non-clustered index in SQL Server?
Answer: Best answer by Microsoft
MSDN.
Clustered
- Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table because the data rows themselves can be sorted in only one order.
- The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If the table has no clustered index, its data rows are stored in an unordered structure called a heap.
Non-clustered
- Non-clustered indexes have a structure separate from the data rows. A non-clustered index contains the non-clustered index key values, and each key-value entry has a pointer to the data row that contains the key value.
- The pointer from an index row in a non-clustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.
- You can add non-key columns to the leaf level of the non-clustered index to by-pass existing index key limits, 900 Bytes and 16 key columns, and execute fully covered, indexed queries. For more info https://msdn.microsoft.com/en-IN/library/ms190457.aspx
Question: How many clustered index
we can create in a table- SQL Server?
Answer: We can create only one the clustered index in a table.
Question: How many non-clustered the index we can create in a table- SQL Server?
Answer: We can more than one
clustered index in a table. Max number of the non-clustered index in SQL Server 5 is
249, and the max number of the non-clustered index in SQL Server 8 is 999.
Question: Any disadvantage of index
in SQL Server?
Answer: Frequently changes of the table may increase the fragmentation of Index it may cause of slow query performance,
so time to time index should be rebuilt.
Question: What is the fill factor in
SQL Server?
Answer: Fill-Factor works in the performance tuning area. For the index, the most crucial
property is Fill Factor. Fill Factor responsible for determining the percentage of
space on each leaf-level page to be filled with data. As we know, the page is the smallest unit of SQL server which size is 8k. Every page cans one or more than
one row, which is depending on the size of the row.
