Q6. Stu_name Varchar(8). What is output of LEN(Stu_name)?
Q7. Different types of Indexes.
Q8. Maximum number of Indexes we can have in table?
Q9. Difference between clustered index and non-clustered indexes?
Q10. What are magic tables in SQL?
------------------------------------------------------------------------------------------------------------------------
Q6. Stu_name Varchar(8). What is output of LEN(Stu_name)?
Answer:
That will depend on the content available in column Stu_name.
example:
Stu_name Varchar(8) - Represents that table has a column named 'Stu_name'. which can hold maximum varchar of up to 9 charters.
LEN(Stu_name) - Represents the length of string present in Stu_name column. it must be always less than the size of column can hold.
------------------------------------------------------------------------------------------------------------------------
Q7. Different types of Indexes.
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-ver15
------------------------------------------------------------------------------------------------------------------------
Q8. Maximum number of Indexes we can have in table?
Q7. Different types of Indexes.
Q8. Maximum number of Indexes we can have in table?
Q9. Difference between clustered index and non-clustered indexes?
Q10. What are magic tables in SQL?
------------------------------------------------------------------------------------------------------------------------
Q6. Stu_name Varchar(8). What is output of LEN(Stu_name)?
Answer:
That will depend on the content available in column Stu_name.
example:
Stu_name Varchar(8) - Represents that table has a column named 'Stu_name'. which can hold maximum varchar of up to 9 charters.
LEN(Stu_name) - Represents the length of string present in Stu_name column. it must be always less than the size of column can hold.
------------------------------------------------------------------------------------------------------------------------
Q7. Different types of Indexes.
Answer:
There are mainly two types of indexes in MS SQL server.
- Clustered Indexes
- Non Clustered Indexes
------------------------------------------------------------------------------------------------------------------------
Q8. Maximum number of Indexes we can have in table?
Answer:
Clustered - 1
Clustered - 1
Non clustered - 999 was allowed SQL Server 2008 R2 so now with 2016. We do not need to worry around number of non clustered indexs.
------------------------------------------------------------------------------------------------------------------------
Q9. Difference between clustered index and non-clustered indexes?
------------------------------------------------------------------------------------------------------------------------
Q9. Difference between clustered index and non-clustered indexes?
Answer:
clustered index determines how the data is stored in the rows of a table physically. On the other hand, the non-clustered index stores the data at a single place and the indexes are stored at another place. Additionally, each table can have only one clustered index. As against, In case of a non-clustered index, a table can have multiple non-clustered indices
1) One table
can only have only one clustered index.One table can only have many
non-clustered index.
2) A
clustered index requires no separate storage than the table storage. It forces
the rows to be stored sorted on the index key.A non-clustered index requires
separate storage than the table storage to store the index information.
3) The
default index created as part of the primary key column is a clustered index.
------------------------------------------------------------------------------------------------------------------------
Q10. What are magic tables in SQL?
Answer:
Magic tables are tables created for insertion, deletion and update internally by server,. In case of new row , a new entry is made in INSERTED magic table.
In case of deletion of a row, entry is made in DELETED magic table'
In case of update of a row, one new entry is made in DELETED magic table for old data and one new entry is made in INSERTED magic table for new entry.
Before SQL 2008R2. they were used only in case of triggers but now they are available for all tables.