1. What are the new data types in SQL Server?
Ans.SQL Server 2000 introduces three new data types. bigint is an 8-byte integer type.
sql_variant is a type that allows the storage of data values of different data types. table is a type that allows applications to store results temporarily for later use. It is supported for variables, and as the return type for user defined functions.
2. How to get the version of SQL Server?
Ans.The following 3 commands can be used to get the version of SQL Server
3. What are system Databases?
Ans.With the installation of SQL Server 4 databases will be created automatically, called as System Databases. They are:
4. What happens if we delete any one of either Master or Model databases?
Ans.SQL Server won’t work properly if we delete any one of Master or Model databases.
5. What are data files?
Ans.Microsoft® SQL Server™ 2000 maps a database over a set of operating-system files.There are the locations where the original database data will be stored. Data and log
information are never mixed on the same file, and individual files are used only by one database. SQL Server 2000 databases have three types of files:
Primary data files
Secondary data files
6. What is the Maximum number of databases that can be created for an instance of SQL Sever?
7. What is the maximum size of data that can be entered in a single row of a table?
8. What is identity property?
Auto number generated columns can be implemented using the IDENTITY property, which allows the application developer to specify both an identity number for the first row inserted into the table (Seed property) and an increment (Increment property) to be added to the seed to determine successive identity numbers. When inserting values into a table with an identifier column, Microsoft® SQL Server™ 2000 automatically generates the next identity value by adding the increment to the seed.
9. How to insert explicit values into identity column?
Ans.In general we can’t enter values into an identity column directly. To do this we have to follow the two steps:
Set the IDENTITY_INSERT to ON for that table
Specify the column list in the INSERT statement
EX: Suppose there is a table called STD with two columns SID and SNAME.SID is anidentity column.
set IDENTITY_INSERT std ON
Insert into std(Sid,sname) values (5,’Anil’);
10. How to get the table names in the database?
Ans.To get the name of all the tables in a database you can use one of the following 3 methods.
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=’BASE
SELECT NAME FROM SYSOBJECTS WHERE TYPE=’U’ EXEC SP_TABLES returns all tables (system, user)
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=’BASE TABLE’
SELECT NAME FROM SYSOBJECTS WHERE TYPE=’U’ EXEC SP_TABLES returns all tables (system, user)
11. How to insert multiple rows using insert statement?
Ans.sample table: create table tName(firstName varchar(20))
INSERT INTO TNAME
SELECT ‘RAJ’ UNION ALL
SELECT ‘GOPI’ UNION ALL
12. How many columns can be given in a composite primary key?
13. Difference between DELETE and TRUNCATE?
Ans.Both TRUNCATE and DELETE commands are used to delete the data from the table. The difference between them is :
With TRUNCATE entire table data will be deleted. Where as with DELETE command we can delete entire table data as well as certain row data, which is not possible with TRUNCATE. Even though, we are deleting entire table data, the deletion will be done row by row. Where as, with TRUNCATE, the deletion will be done at once. DELETE maintains
log information about the deleted records. Where as, TRUNCATE does not maintain log details. Instead of this, it maintains deallocation of Data Pages
14. Difference between WHERE and HAVING clauses?
Ans.Both WHERE and HAVING clauses are used to provide a restriction to the result set. Only,those records, which satisfy the criteria, will be returned. The difference between them is:With WHERE clause the condition can be provided based on any column of table. Where as, by using HAVING clause, the condition can be specified either by using an Aggregate Function or a GROUP BY column.Suppose, if the SELECT statement contains WHERE and GROUP BY clauses, the WHERE clause should precede GROUP BY clause. Where as, if the SELECT statement contains GROUP BY and HAVING clauses, the HAVING clause should come after GROUP BY clause.
15. What are the types of Integrity Constraints?
Ans.Integrity Constraints are used to maintain Data Integrity in the database. It is a restriction that can be applied on a column or a table. 3 types of Integrity constraints are available
16. What is composite primary key?
Ans. In general a PRIMARY KEY can be given on a column by which we can able to identity different rows of the table. Suppose, it is not possible to identity different rows of the table based on a single column which can be done with combination of columns, then we will make that combination as the primary key which is called as COMPOSITE PRIMARYKEY.
17. Difference between UNIQUE and PRIMARY KEY?
Ans.The differences between UNIQUE and PRIMARY KEY are:
UNIQUE allows NULL values where as, a PRIMARY KEY does not allow a NULL value.A table can contain more than one UNIQUE constraint where as, a table can have only one PRIMARY KEY. An UNIQUE constraint on a column by default creates a NONCLUSTERED INDEX.Where as, a PRIMARY KEY on a column by default creates a CLUSTERED INDEX
18. What is ON DELETE CASCADE?
Ans.In general if the parent table record is having a reference In the child table, we can’t delete the record directly. With the use of “ON DELETE CASCADE”, when we delete the parent table record, that record and all its child references will be deleted at once.
19. What are Joins? Types of Joins?
Ans.A JOIN is a select statement used to get the data distributed among different tables.
Types of Joins:
20. What is a correlated sub query?
Ans.Correlated Sub Query: In it passes the resultant values back to the parent query.The parent query uses those values to finish the processing.The above 3 steps should be performed for each row of the parent query
21. What is the difference between IN and ANY Operators?
Ans.IN operator can be used to provide the list of values to checked, either by using explicit list of values and also with the resultant values of a SELECT statement, which is not possible with ANY operator, where we can use only the SELECT statement to specify the list of values.
22. What is a View?
Ans.A view can be thought of as either a virtual table or a stored query. The data accessible through a view is not stored in the database as a distinct object. What is stored in the
database is a SELECT statement. The result set of the SELECT statement forms the virtual table returned by the view. A user can use this virtual table by referencing the view name in Transact-SQL statements the same way a table is referenced. A view is used to do any or all of these functions
23. What is an Index? What are the types of indexes?
Ans.Indexes: In a database, an index allows the database program to find data in a table without scanning the entire table. An index in a database is a list of values in a table with
the storage locations of rows in the table that contain each value. Indexes can be created on either a single column or a combination of columns in a table and are implemented in the form of B-trees. SQL Server provides two types of indexes.Clustered,NonClustered
24. How many Clustered Indexes can be created on a table?
25. How may NonClustered Indexes be able to be created on a table?
26. What is Normalization?
Ans.Normalization is the process of breaking up data into a logical non-repetitive format that can easily be reassembled as a whole.
27. What are the Normal Forms?
1NF: A table is said to be in 1NF if it satisfies the following rules.
The table must not contain any redundant groups of data
The table must contain atomic data (at the intersection of row and column only one value should be specified
2NF: A table is said to be in 2NF if it satisfies the following rules.
The table must be in 1NF
All the non-key column must depend on Whole key (primary key)
3NF: A table is said to be in 3NF if it satisfies the following rules.The table must be in 2NF
All the non-key columns must depend only on the whole key. They should not depend on other non-key columns. (There should be no TRANSITIVE DEPENDENCY)The data should not be derived further.
28. What is Demoralization?
Ans.De-Normalization: It is the reverse process of Normalization. Sometimes, by including just one de-normalized column in a table, you can eliminate or significantly cut down the
number of joins necessary to retrieve information
29. What is a cursor?
Ans.A cursor is a pointer to the result of a SELECT statement.
Allowing positioning at specific rows of the result set. Retrieving one row or block of rows from the current position in the result set.Supporting data modifications to the rows at the current position in the result set.
Supporting different levels of visibility to changes made by other users to the database data that is presented in the result set. Providing Transact-SQL statements in scripts, stored procedures, and triggers access to the data in a result set.