1.What is a Database ?
Ans: A database is a structured and organized collection of data in computer using one of the database models.
2. What is a Database Management System ?
Ans: A Database Management System (DBMS) is a software which allow to modify,store and extract the information from the database.
3. What is a Relational Database Management System ?
Ans: A Relational Database Management System (RDBMS) is a DBMS which uses relational database model. this is one that organizes data in the form of Table and Rows.
4. What is normalization ?
Ans: Normalization is the process of reducing the redundancy in data in the database.it also separate the database into one or two table and defining the relationship between those tables.
5. What is de-normalization ?
Ans: De-normalization is the process of introducing controlled redundancy of data in the RDBMS. It is the reverse process of normalization.
6. How to create a table and insert the data, from another table in the same database, in one query ?
Ans: Select * into tabA from tabB
7. How to create a table structure and not data, from another table in the same database, in one query ?
Ans: Select * into tabA from tabB where 1 = 2
8. What is are properties of database transaction ?
Ans: A database transaction always comply with ACID properties
A=Atomicity
C=Consistency
I=Isolation
D=Durability
9. What are the different Isolation levels ?
Ans: Read uncommitted, Read committed, Repeatable Read and Serializable.
10. What is difference between DELETE and TRUNCATE ?
11. What is the difference between clustered and non-clustered index ?
Ans:Clustered index is a special kind of index in which the node of the B tree has the actual value. And it reorders the way records in the table are physically stored so, clustered index can be created only on table that has unique non null values and this is usually made on the primary key.
12. Maximum number of columns used in GROUP BY, ORDER BY in SQLServer 2008:
25. What is a linked server in SQL Server ?
Ans: From your current SQL server management stuio, if you want to execute commands against OLE DB data sources on remote servers, you use linked server.
26. Write a query to list all the user defined tables in SQL Server 2008 ?
Ans: select * from sys.objects where type = 'U'
27. What are the default databases in SQL Server 2008 ?
Ans: tempdb, model, msdb, master
28. What is a Common Table Expression (CTE) ?
Ans:Common Table Expression can be thought of as temporary result set. But,CTE is very uch useful for recursive usage.A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
Example:
WITH CTEtest (ID, numofprod)
AS
(
SELECT ID, COUNT(*)
FROM product
GROUP BY ID
)
select * from CTEtest
29. What is a temporary table in SQL Server ?
31. What are the difference between local and global temporary tables ?
Ans: Local tables can be created as #temp and global tables ones are represented as ##temp. Local temporary tables can been accessed by only the user who creates it and are deleted when the user disconnects from the current instance of SQL Server. Global temporary tables can be seen by any user and are deleted only when all users referencing the table disconnect from the instance of SQL Server.
What are the different types of Ranking Functions in SQL SERVER
1-ROW_NUMBER(): Returns the serial number of the row order by specified column.
2-RANK(): Returns the rank of each row in the result set of partitioned column
Ans: A database is a structured and organized collection of data in computer using one of the database models.
2. What is a Database Management System ?
Ans: A Database Management System (DBMS) is a software which allow to modify,store and extract the information from the database.
3. What is a Relational Database Management System ?
Ans: A Relational Database Management System (RDBMS) is a DBMS which uses relational database model. this is one that organizes data in the form of Table and Rows.
4. What is normalization ?
Ans: Normalization is the process of reducing the redundancy in data in the database.it also separate the database into one or two table and defining the relationship between those tables.
5. What is de-normalization ?
Ans: De-normalization is the process of introducing controlled redundancy of data in the RDBMS. It is the reverse process of normalization.
6. How to create a table and insert the data, from another table in the same database, in one query ?
Ans: Select * into tabA from tabB
7. How to create a table structure and not data, from another table in the same database, in one query ?
Ans: Select * into tabA from tabB where 1 = 2
8. What is are properties of database transaction ?
Ans: A database transaction always comply with ACID properties
A=Atomicity
C=Consistency
I=Isolation
D=Durability
9. What are the different Isolation levels ?
Ans: Read uncommitted, Read committed, Repeatable Read and Serializable.
10. What is difference between DELETE and TRUNCATE ?
| Delete | Truncate |
| DELETE is a logged operation | TRUNCATE is not a logged operation. |
| So, each row deleted is logged in the transaction log | So, each row is not entered in transaction log |
| Once data deleted, can be roll back | can be roll back |
| we can delete the specific record | it delete all the record of table |
11. What is the difference between clustered and non-clustered index ?
Ans:Clustered index is a special kind of index in which the node of the B tree has the actual value. And it reorders the way records in the table are physically stored so, clustered index can be created only on table that has unique non null values and this is usually made on the primary key.
Non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.There can be as many as 249 non-clustered index per table
12. Maximum number of columns used in GROUP BY, ORDER BY in SQLServer 2008:
Ans: It is not limited by number of columns but it is limited by number of bytes. The maximum bytes per GROUP BY, ORDER BY is 8060.
13. What is the maximum number of clustered index per tables inSQL Server 2008 ?
Ans: 1
14. What is the maximum number of columns per select or insert statement in SQL Server 2008 ?
Ans: 4096
15. What is the maximum Database size in SQL Server 2008 ?
Ans: 524,272 terabytes
16. What is the maximum number of databases per instance of SQLServer 2008 ?
Ans: 32,767
17. What is maximum number of non-clustered index per table in SQLServer 2008 ?
Ans: 999
18. What is the maximum number of parameters in stored procedures or user defined functions in SQL Server 2008 ?
Ans: 2,100
19. What is the maximum number of rows a table can have in SQLServer 2008 ?
Ans: It is limited only by the storage.
20. What is the nesting maximum for stored procedures/triggers/subqueries in SQL Server 2008.
Ans: 32
21. What is the maximum bytes per row in SQL Server 2008 ?
Ans: 8,060
13. What is the maximum number of clustered index per tables inSQL Server 2008 ?
Ans: 1
14. What is the maximum number of columns per select or insert statement in SQL Server 2008 ?
Ans: 4096
15. What is the maximum Database size in SQL Server 2008 ?
Ans: 524,272 terabytes
16. What is the maximum number of databases per instance of SQLServer 2008 ?
Ans: 32,767
17. What is maximum number of non-clustered index per table in SQLServer 2008 ?
Ans: 999
18. What is the maximum number of parameters in stored procedures or user defined functions in SQL Server 2008 ?
Ans: 2,100
19. What is the maximum number of rows a table can have in SQLServer 2008 ?
Ans: It is limited only by the storage.
20. What is the nesting maximum for stored procedures/triggers/subqueries in SQL Server 2008.
Ans: 32
21. What is the maximum bytes per row in SQL Server 2008 ?
Ans: 8,060
22. How do you check the version number of the SQL Server you are currently running ?
Ans: select @@version
23. What is use of sp_who ?
Ans:We can check all Currently running users and process in SQL Server can be found by running sp_who.
24. What is sp_who2 ?
Ans: It shows little more that sp_who with locking, blocking, what the users logged in are doing and so on.
Ans: select @@version
23. What is use of sp_who ?
Ans:We can check all Currently running users and process in SQL Server can be found by running sp_who.
24. What is sp_who2 ?
Ans: It shows little more that sp_who with locking, blocking, what the users logged in are doing and so on.
25. What is a linked server in SQL Server ?
Ans: From your current SQL server management stuio, if you want to execute commands against OLE DB data sources on remote servers, you use linked server.
26. Write a query to list all the user defined tables in SQL Server 2008 ?
Ans: select * from sys.objects where type = 'U'
27. What are the default databases in SQL Server 2008 ?
Ans: tempdb, model, msdb, master
28. What is a Common Table Expression (CTE) ?
Ans:Common Table Expression can be thought of as temporary result set. But,CTE is very uch useful for recursive usage.A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
Example:
WITH CTEtest (ID, numofprod)
AS
(
SELECT ID, COUNT(*)
FROM product
GROUP BY ID
)
select * from CTEtest
29. What is a temporary table in SQL Server ?
Ans: As the name suggest Temporary tables is very similar to regular table but are temporary and are stored in tempdb database.Temp table is a session specific table.
30. What are the two types of temporary tables in SQL Server ?
Ans: Local temporary table and Global temporary table.
30. What are the two types of temporary tables in SQL Server ?
Ans: Local temporary table and Global temporary table.
31. What are the difference between local and global temporary tables ?
Ans: Local tables can be created as #temp and global tables ones are represented as ##temp. Local temporary tables can been accessed by only the user who creates it and are deleted when the user disconnects from the current instance of SQL Server. Global temporary tables can be seen by any user and are deleted only when all users referencing the table disconnect from the instance of SQL Server.
1-ROW_NUMBER(): Returns the serial number of the row order by specified column.
2-RANK(): Returns the rank of each row in the result set of partitioned column
3-DENSE_RANK() This is same as RANK() function. Only differencec is returns rank with out gaps
No comments:
Post a Comment