Interview Questions and answers - SQL Server Day 4

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 the 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.

PostgreSQL-Query: Sort result set by specific field values using ORDER BY Clause

Problem: Suppose we have a book_inventory table which has some columns such as id, isbn, title, author, publisher, publish_date, etc.. whe...