1. What is the difference between Delete and Truncate command in SQL?
Delete command and truncate command both will delete the data; however the truncate command can not be rolled back as delete can be. The delete command can be used for selected records using the where clause but with the truncate command we have to loose data. DELETE statement is a logged operation and hence takes more time then truncate.
2. What is Magic Table in SQL?
The insert and Delete commands are known as magic tables in SQL.
3. Can Primary key is a Foreign Key on the same table?
Yes, consider a category table in an e-commerce web site. Category_Id, Category_Name, Parent_Category_ID. In this table all the parent categories are also categories. When we create a self join category id will be treated as foreign key to the same table.
4. What is Normalization? What are its rules?
Normalization is the technique in the database design where
The idea is to reduce the redundancy of non key data items across the table.
Rule 1: There should be a one-to-one relationship between the instances of an entity and the rows of the table.
Rule 2: A field should have the same meaning in each row of the table.
Rule 3: Each table should represent at most one entity.
Rule 4: Multiple instances of an entity should be represented by multiple rows in a table.
Rule 5: Joins should be based only on primary and foreign-key equality.
Rule 6: Make sure keys are linked correctly.
5. What are the advantages and disadvantages of Normalization?
There are several advantages of normalization as under:
1) Faster sorting and index creation.
2) A larger number of clustered indexes.
3) Narrower and more compact indexes.
4) Fewer indexes per table, which improves the performance of INSERT, UPDATE, and DELETE statements
5) Fewer null values and less opportunity for inconsistency, which increase database compactness. Beside the above benefits there are few disadvantages as well:
6) Increased amount of Normalization increases the amount of complexity of joins between tables and that hinders the performance.
6. What are the conditions to achieve the normalization?
There are few conditions to achieve the normalization:
There should be a unique row identifier.
A table should store only data for a single type of entity. For e.g. details for book's publisher and book's author should be saved under different table.
A table should avoid columns, which can be null-able.
A table should avoid duplication of data and columns.
7. What is a Stored Procedure? State its advantage.
A stored procedure is a set of pre-compiled SQL commands (query statements), which are stored in the server. It is faster then the loose SQL statements processed on client, as it is pre-compiled. It can execute more then one SQL commands once as they are bundled in a single entity. We can use control statements within the stored procedure, which will allow us to repeat some SQL command. It can send return values depending upon the result. Stored procedures are used to reduce network traffic.
8. What is a Trigger?
Triggers are a special type of stored procedure, which gets invoked upon a certain event. They can be performed upon an INSERT, UPDATE and DELETE.
9. What is a Clustered Index?
The data rows are stored in order based on the clustered index key. Data stored is in a sequence of the index. In a clustered index, the physical order of the rows in the table is the same as the logical (indexed) order of the key values. A table can contain only one clustered index. A clustered index usually provides faster access to data than does a non-clustered index
10. What is a Non-Clustered Index?
The data rows are not stored in any particular order, and there is no particular order to the sequence of the data pages. In a clustered index, the physical order of the rows in the table is not same as the logical (indexed) order of the key values.
11. Describe the three levels of data abstraction?
The are three levels of abstraction:
Physical level: The lowest level of abstraction describes how data are stored.
Logical level: The next higher level of abstraction, describes what data are stored in database and what relationship among those data.
View level: The highest level of abstraction describes only part of entire database
12. What is DDL (Data Definition Language)?
A data base schema, which is specified by a set of definitions expressed by a special language, is called DDL. Data Definition Language (DDL) is used to define and manage all the objects in an SQL database.
13. What is DML?
It is a special language used to manipulate the Data. Data Manipulation Language (DML), which is used to select, insert, update, and delete data in the objects defined using DDL.
14. What is a PRIMARY KEY?
The PRIMARY KEY is the column(s) used to uniquely identify each row of a table.
15. What is a FOREIGN KEY?
A FOREIGN KEY is a one or more column whose values are based on the PRIMARY or CANDITATE KEY values from the database.
16. What is a UNIQUE KEY?
A UNIQUE KEY is a one or more column that must be unique for each row of the table.
17. What is the difference between UNIQUE and PRIMARY KEY?
The UNIQUE KEY column restricts entry of duplicate values but entry of NULL value is allowed. In case of PRIMARY KEY columns entry of duplicate as well as <NULL> value is also restricted.
18. What is a VIEW?
A View is a database object that is a logical representation of a table. It is derived from a table but has no storage space of its own and often may be used in the same manner as a table.
19. What is a ROWID?
ROWID is the logical address of a row, and it is unique within the database.
20. What is INDEXING?
INDEX is a general term for an SQL feature used primarily to speed up execution and impose UNIQUENESS upon data. You can use an index to gain fast access to specific information in a database table. An index is a structure that orders the values of one or more columns in a database table. The index provides pointers to the data values stored in specified columns of the table, and then orders those pointers according to the sort order you specify.
21. What is a cursor?
An entity that maps over a result set and establishes a position on a single row within the result set. After the cursor is positioned on a row, operations can be performed on that row, or on a block of rows starting at that position. The most common operation is to fetch (retrieve) the current row or block of rows.
22. The Difference between 'Count' and 'Count (*)'?
'Count': Counts the number of non-null values. 'Count (*)': Counts the number of rows in the table, including null values and duplicates.
23. Explain Types of Join?
There are three types of join
1). Inner join
2). Outer join
i.Left outer join
ii.Right outer join
iii.Full outer join
3). Self join
4). Cross join
24. What's the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a no clustered index by default. Another major difference is that, primary key doesn't allow Nulls, but unique key allows one NULL only.
25. What is sorting and what is the difference between sorting and clustered indexes?
The ORDER BY clause sorts query results by one or more columns up to 8,060 bytes. This will happen by the time when we retrieve data from database. Clustered indexes physically sorting data, while inserting/updating the table.
26. What are the differences between UNION and JOINS?
A join selects columns from 2 or more tables. A union selects rows.
27. What is the Referential Integrity?
Referential integrity refers to the consistency that must be maintained between primary and foreign keys, i.e. every foreign key value must have a corresponding primary key value
28. What is the use of SCOPE_IDENTITY () function?
Returns the most recently created identity value for the tables in the current execution scope.
29. What is a deadlock?
Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other's piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user's process.
30. What is a Live Lock?
A live lock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A live lock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.
31. What are defaults? Is there a column to which a default can't be bound?
A default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can't have defaults bound to them.
32. What are the constraints?
Table Constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing integrity. SQL Server 2000 supports five classes of constraints. NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY.
33. What is Transaction?
A transaction is a sequence of operations performed as a single logical unit of work. A logical unit of work must exhibit four properties, called the ACID (Atomicity, Consistency, Isolation, and Durability) properties, to qualify as a transaction.
34. Does SQL Server 2000 clustering support load balancing?
SQL Server 2000 clustering does not provide load balancing; it provides fail over support. To achieve load balancing, you need software that balances the load between clusters, not between servers within a cluster
35. Explain local and Global temp table?
Ø Local Temporary Tables
CREATE TABLE #people
(
id INT,
name VARCHAR (32)
)
A temporary table is created and populated on disk, in the system database tempdb — with a session-specific identifier packed onto the name, to differentiate between similarly-named #temp tables created from other sessions. The data in this #temp table (in fact, the table itself) is visible only to the current scope (usually a stored procedure, or a set of nested stored procedures). The table gets cleared up automatically when the current procedure goes out of scope, but you should manually clean up the data when you're done with it:
DROP TABLE #people
Ø Global Temporary Tables
CREATE TABLE ##people
(
id INT,
name VARCHAR (32)
)
Global temporary tables operate much like local temporary tables; they are created in tempdb and cause less locking and logging than permanent tables. However, they are visible to all sessions, until the creating session goes out of scope (and the global ##temp table is no longer being referenced by other sessions
36. Explain string function?
· ASCII: return the ASCII numeric value of the leftmost character of a string.
Ex: SELECT ASCII ('A') –65
· CHAR: the reverse of the ASCII function as it changes a numeric value in to an ASCII character Ex: SELECT CHAR (65) –A
· NCHAR: Returns a Unicode character representing the number passed as a parameter SELECT NCHAR (65)-A
· CHARINDEX: returns the starting point of the first occurrence of one string of characters within another string. A value of 0 is returned if the string is not found.
Ex: SELECT CHARINDEX ('Mars', 'The stars near Mars are far from ours')-16
· DATALENGTH: returns the number of bytes used to manage a value.
· LEN: - returns the length of a string as an integer
Ex:DECLARE @Va1 Int, @Val2 Int
SET @Value1 = 2 SET @Value2 = 2000000000SELECT DATALENGTH (@Va1), LEN (@Val1), DATALENGTH (@Val2), LEN (@Val2)
----------- ----------- ----------- ----------- 4 1 4 10
· DIFFERENCE: work out when one string sounds similar to another string.
'0' means that the two strings aren't even close,'4' means a perfect matchDECLARE @s1 varchar(6), @s2 varchar(6)
SET @s1 = "Dewson" SET @s2 = "Joosun"SELECT DIFFERENCE(@s1,@s2)- 3
· PATINDEX: search for a pattern of characters within a string.
Return 0 if no match is found.
Ex: SELECT PATINDEX ("%SQL%","SQL server sql server")
· QUOTENAME: turns a character string in to a valid SQL Server identifier
Ex: SELECT QUOTENAME ("[bad] table name")
· REPLICATE: replicate the same string several times.
Ex: SELECT REPLICATE("Ramesh",2) -RameshRamesh
· SOUNDEX: check how similarly sounding two tested strings can be.
Ex: SELECT SOUNDEX ("Robin Dewson"), SOUNDEX("Robyn Jewshoon")-R150 R150
· STUFF: replace a portion of a string with another string.
Ex: SELECT STUFF('Please submit your payment for 99.95 immediately.', 32, 5, '109.95') -Please submit your payment for 109.95 immediately.
· UNICODE: Returns the integer Unicode value of a single leftmost character in a string. Ex: SELECT UNICODE("Bedford Blues")-66
No comments:
Post a Comment