SQL
1. How do you go for database design?
First identify the entities and each entity’s attributes….Then define the relations ships among the entities and prepare the entity
relationship diagrams…Then apply the normalization rules to achieve the normalized database.
2. Normalization gives consistency and ensures data integrity. Then why we go for de- normalization and what is the situation in which you will go for de-normalization?
In most of the scenarios, the database in 3rd normal form would perform very well…
But if your tables have thousands of records and if you need to frequently join number of tables then in that case the query
performance will be bit lowered due to many joins
In such a case, you should go for denormalizing the database i.e. changing your database design such that it is not in the 3rd normal form. This can be done in 3 ways.
1. adding a redundant column
2. horizontal partitioning
3. vertical partitioning
3. How to order by in a view?
Cannot include ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement
4. Write a query to give the 3rd maximum of marks obtained by students in a class.
SELECT TOP 1 * FROM Employee
WHERE Marks < (SELECT MAX(Marks) FROM Employee WHERE Marks < (SELECT MAX(Marks) FROM Employee))
ORDER BY Marks DESC
SELECT TOP 1 A.* FROM EMPLOYEE A
WHERE 3 > (SELECT COUNT(Marks) FROM Employee WHERE Marks > A.Marks)
ORDER BY A.Marks
5. What is the scenario in which you should go for self join?
Where there is a self referencing table.
6. Cursors. Is it useful? What are the different ways to avoid cursors?
o Cursors allow row-by-row processing of the result sets.
o Types of cursors:
Static, Dynamic, Forward-only, Keyset-driven.
o Disadvantages of cursors:
Each time you fetch a row from the cursor, it results in a network round trip; where as a normal SELECT query makes only one roundtrip, however large the result set is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Further, there are restrictions on the SELECT statements that can be used with some types of cursors.
o Most of the times, set based operations can be used instead of cursors. Here is an example: If you have to give a flat hike to your employees using the following criteria: Salary between 30000 and 40000 – 5000 hike Salary between 40000 and 55000 – 7000 hike Salary between 55000 and 65000 – 9000 hike. In this situation many developers tend to use a cursor, determine each employee’s salary and update his salary according to the above formula. But the same can be achieved by multiple update statements or can be combined in a single UPDATE statement as shown below:
o UPDATE tbl_emp SET salary = CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000 WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000 WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000 END
o Another situation in which developers tend to use cursors:
You need to call a stored procedure when a column in a particular row meets certain condition. You don’t have to use cursors for this. This can be achieved using WHILE loop, as long as there is a unique key to identify each row. For examples of using WHILE loop for row by row processing, check out the ‘My code library’ section of my site or search for WHILE. Write down the general syntax for a SELECT statement covering all the options. Here’s the basic syntax: (Also checkout SELECT in books online for advanced syntax).
o SELECT select_list [INTO new_table_] FROM table_source [WHERE search_condition] [GROUP BY group_by_expression] [HAVING search_condition] [ORDER BY order_expression [ASC | DESC] ]
7. What are jobs? How does u maintain jobs?
You can schedule tasks in SQL Server.
8. What is meant by distributed transaction?
It involved more than one database servers that can be located on remote locations.
9. How do you connect servers?
First define the liked server using the sp_addLinkedServer and then using the four part query.
10. What is deadlock and how do you remove that? What is the difference between deadlock and blocking?
Use the sp_lock and the sp_WHO system stored procedures to find out locked resources and to identify processes that are holding locks.
Use this information to identify and redesign the transactions that are causing the locks.
11. How many types of triggers are available and explain how you use after triggers ?
Insert/Delete/Update
12. Do you know what OPENQUERY function is?
Executes the specified pass-through query on the given linked server, which is an OLE DB data source. The OPENQUERY function can be referenced in the FROM clause of a query as though it is a table name. The OPENQUERY function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.
Syntax
OPENQUERY ( linked_server , 'query' )
13. Why fill factor is important and where it finds the place?
The fill factor percentage is used only at the time the index is created. The pages are not maintained at any particular level of fullness.
The default for fill factor is 0; valid values range from 0 through 100. A fill factor value of 0 does not mean that pages are 0 percent full. It is treated similarly to a fill factor value of 100 in that SQL Server creates clustered indexes with full data pages and nonclustered indexes with full leaf pages. It is different from 100 in that SQL Server leaves some space within the upper level of the index tree. There is seldom a reason to change the default fill factor value because you can override it with the CREATE INDEX statement.
Small fill factor values cause SQL Server to create new indexes with pages that are not full. For example, a fill factor value of 10 is a reasonable choice if you are creating an index on a table that you know contains only a small portion of the data that it will eventually hold. Smaller fill factor values cause each index to take more storage space, allowing room for subsequent insertions without requiring page splits.
If you set fill factor to 100, SQL Server creates both clustered and nonclustered indexes with each page 100 percent full. Setting fill factor to 100 is suitable only for read-only tables, to which additional data is never added.
fill factor is an advanced option. If you will be using the sp_configure system stored procedure to change the setting, you can change fill factor only when show advanced options is set to 1. The setting takes effect after stopping and restarting the server.
14. In which type of relationship you go for an intermediate table?
Clustered Indexes
15. What is the advantage of RAISEERROR over PRINT statement?
Returns a user-defined error message and sets a system flag to record that an error has occurred. Using RAISERROR, the client can either retrieve an entry from the sysmessages table or build a message dynamically with user-specified severity and state information. After the message is defined it is sent back to the client as a server error message.
16. What is a FileGroup and what is the advantage of that?
17. What is a distributed portioned view?
A partitioned view joins horizontally partitioned data from a set of member tables across one or more servers, making the data appear as if from one table. Microsoft® SQL Server™ 2000 distinguishes between local and distributed partitioned views. In a local partitioned view, all participating tables and the view reside on the same instance of SQL Server. In a distributed partitioned view, at least one of the participating tables resides on a different (remote) server. In addition, SQL Server 2000 differentiates between partitioned views that are updatable and views that are read-only copies of the underlying tables.
18. What is failover clustering?
Use the Failover Clustering screen to define the virtual server for a new cluster, or to maintain the virtual server definition for an existing cluster. You can add and remove IP addresses; multiple IP addresses are allowed for each virtual server.
Options
Virtual Server Name
Displays the network name of the virtual server. This is the name users will see when they connect to the virtual server.
When upgrading to a cluster, this name is entered in the Virtual Server Name dialog box.
IP address
Enter the IP address or addresses used to connect to the virtual server.
SubNet
Displays the Subnet, which is supplied by MSCS.
Network
Displays the Network name you assigned each subnet during setup of MSCS.
Add
Adds the specified IP address and SubNet to the named virtual server.
Remove
Removes the specified IP address and SubNet from the named virtual server.
19. What is RAID? Explain the concept behind it.
(Redundant array of independent disks)
20. If you have a batch having only one SQL statement and a procedure with that SQL statement which will be more
efficient and why?
Procedure
21. What is indexed scan and how different it is from table scan?
22. How many types of isolation levels are there?
When locking is used as the concurrency control mechanism, it solves concurrency problems. This allows all transactions to run in complete isolation of one another, although there can be more than one transaction running at any time.
Serializability is the database state achieved by running a set of concurrent transactions equivalent to the database state that would be achieved if the set of transactions were executed serially in order.
23. What is fragmentation and how do you go for avoiding that?
24. What are hints? Where you go for that?
25. How you identify which index is being used? (Query execution plan)
26. Use of constraints?
29. Difference between clustered and non-clustered index?
30. Why only one clustered index can be there per table?
31. What internally happens when an Update query fired?
32. What is the use of triggers?
33. Can you call your COM component in a stored procedure? If yes then what is the requirement for that COM component? Who all can create object of COM component?
34. Can you have two tables or stored procedure or any other database object with the same name in the same database?
35. How will u get a table structure of an existing table using a t-Sql stmt without the data in it?
36. Different types of joins.
37. What do you know about normalization? Definitions of all 3 normal forms?
38. Given the below table. Normalize it Up to 3rd normal form.
Employee
Employee_ID, Employee_Name, Email_Type, Email_Address, Email_Text, Email_Date
| Employee | Employee_Email_Type_MST | Employee_Email_Dtl | |
| Employee_ID | Employee_ Type_ID | Employee_ID | |
| Employee_Name | Email_Type_Desc | Employee_ Type_ID | |
| | | Email_Address | |
| | | Email_Text | |
| | | Email_Date | |
39. What is @@TRANCOUNT? How the value of @@TRANCOUNT affected during a transaction?
40. Explain Nested transactions
41. How do you do error handling in stored procedures?
42. Given two tables
There are 2 types of phones.
1.
2. Landline phones.
Employee Employee_Phone
Employee_ID Employee_ID
Employee_Name Phone_Type
Phone_Number
a. Write a select query that will return all employees who does not have a single phone.
b. Write a select query that will return all those employees who have more than one phone.
c. Write an update query that will interchange the Phone_Type i.e.
a. SELECT * FROM Employee WHERE Employee_ID NOT IN (SELECT Employee_ID FROM Employee_Phone)
b. SELECT A.* FROM Employee WHERE 1 < (SELECT COUNT(Employee_ID) FROM Employee_Phone WHERE Employee_ID = A.Employee_ID)
c. UPDATE Employee_Phone A SET A.Phone_Type=(SELECT Phone_Type FROM Employee_Phone WHERE Phone_Type != A.Phone_Type)
43. What does SET NO COUNT ON statement will do?
It will prevent SQL server from sending messages to the client for each SQL statement fired.
44. What is the use of INTERSECT function?
INTERSECT is same as
45. How can you find out how many concurrent connections are opened with SQL Server at a give point of
time?
Using @@CONNECTION Directive
46. What is function and its types?
47. When to use and not to use functions?
48. What is distributed transaction and what are the different ways to use distributed
transactions?
49. How to execute dynamic queries?
50. What are the things to be considered while using transactions?
51. What is dead lock and how to handle it?
52. What is collation?
53. Some queries related to insert into …select from clauses.
54. What are the types of temporary tables and how to declare it?
55. What is the output of the below given code?
If ‘’= 0
Print ‘Yes’
Else
Print ‘No’
56. In which table the contents of the stored procedure are stored?
SysObjects : The stored procedure object ID is created
SysComments: The stored procedure text is stored in this table
57. Write a Stored Procedure to get the Product details for a given customer.
Open Northwind database.
Look at Customers, Orders, Order Details and Products tables.
I will pass Customer Name as Input. Give me the input as below.
Customer Name Product Name Final Price
--------------------------------------------------------------------------
XXXX Aniseed Syrup 340.78
XXXX Ikura 234.56
….
….
Assume that you sit in the production environment and write a Stored Procedure to achieve this requirement. Try to complete it with in 15 Minutes.
My Approach:
- Written few lines of Comments before starting the Stored Proc
- Taken Customer Name varchar(40) as Input parameter.
- Declared a variable for Customer ID inside a stored procedure and set it to empty char. Follow the naming convention for variables. If it is Char data type then declare it as ChrCustID.
- Get the Customer ID from Customer Table for a given Customer Name.
- Write Inner Join on three tables Orders, Order Details and Products. Use SUM for Price Group By Product.
- Deduct discount from Price and multiply it with Quantity to get final price. Apply Round to get two digits after decimal point. Write this logic in the Query it self.
- Error Situations: Customer Name may not exist in the database. Customer exists but he may not have orders. In these two situations our stored procedure returns 0 results. But he expects ERROR HANDLING in the SP.
- Put message in SysMessage table using sp_AddMessage. Check the Customer ID and Row Count. Then use RAISE ERROR statement.
- Write comments where ever necessary. If necessary use SET statements also. SET NOCOUNT, ANSINULL and QUOTED IDENTIFIER. If not useful eliminate them.
- Try to finish out before/on time.
- Discount column data type is REAL. Careful while rounding it off.
58. What factors you think before writing Stored Procedure?
a. Logic or task to be accomplished in SP.
b. What are Input Parameters and their data types?
c. What are the tables required for writing SP.
d. Confirm whether required tables are exist or to be created.
e. Check the Database if any other SP has the same name of my SP.
f. After Completion of SP, I think of Error situations and invalid inputs then I handle
them in SP.
59. Difference between Function and a stored Procedure? I can use OutPut parameter in a Procedure to get return value.
Why should I go for Function?
a. Using functions we can return single Value, Multiple value or Table.
b. Function is a single entity which can be commonly used anywhere in the database.
c. After my answer, what do you mean by Single Entity?
d. If I use output param in SP, I have to declare a temporary variable to get that return value. I can not use the SPs resulted row set as a table. But using functions I simply use them directly in query. Suppose, I want to have First letter in the name Capital and remaining letters small. Simply I write my own function FormatName(VarCharName). Once I have this function I can use select statement as following one. Select EmpId,FormatName(EmpName) from EMP. Across the my database I can use this function where ever I want to format the name.
e. We can only write a logic in Functions. We cannot put DML statements in Functions where as in SP we can put them.
60. What Constraints we have in SQL Server 2000?
f. Primary Key , Foreign Key, Unique, Not Null , Check
g. Questioned followed, what is diff between Primary Key and Unique key?
h. Primary Key is Unique + Not Null. This does not allow null value. Where as Unique allow null one time and ensures Uniqueness. SQL Server 2000 puts Clustered Index by default on Primary key column.
i. Questioned followed, why we put Clustered index on Table?
j. Physical order of the rows data in a table is same as logical order of indexes. So Searching , Sorting are faster when we query the DB based on the Clustered column
k. Questioned followed, putting constraints increases correctness of data and retrieving also fast. Can I use Check Constraints on all columns?
l. In my real experience most of the times I used Primary Key and Foreign Key. Even we need checking of the data, we don’t prefer to put check constraints. Because each time we are updating/Inserting the data or querying the data, SQL engine does all constraint checks. Index occupies space of the DB and effects DB size. While SQL engine preparing estimated plan, it trails on all indexes. This plan and Statistics will be stored by the SQL server for reuse. Conclusion is if we go for unnecessary indices, they cause poor performance and increases DB size also.
m. Question followed, what is Domain Table?
n. I heard about Domain Columns and I never heard about Domain Tables. I think, like system tables, SQL server has some other tables to maintain its internal details. I am sorry if I am wrong.
o. He said, it is not at all related to what u said.
61. What r distributed Queries? Have u worked on them in your real experience?
p. Querying from the databases which are located at two different places.
q. I never had a chance to work with distributed databases. But I know the concepts of Linked Server and ad hoc queries.
r. We can maintain a link to the remote machine. After than we can write the query using Server.User.Database.Table. Similarly we can connect to the database and run the queries using OpenQuery, OpenRowSet statements.
s. Question followed, Forget about the two machines. Think of two databases residing in the same SQL server. How do you write a join query which uses one table from DB1 and other from DB2?
t. If my connection has permission to both the databases, I can specify user name and Database name in the query. I don’t know if the user does not have the permission to the other DB.
62. How many characters we can put in a Varchar Column? I told 8000. Okay I have text box in front end GUI which
accepts unlimited characters. Data entered in that text box is going to the database. How do you handle it?
1. Most of the times I put limit on the number of characters userenters in the GUI. If I have not validated it, then I handle it in the front end coding. I give a message to the user stating that his info been terminated and I pass the limited length string to the database. Otherwise if I directly pass it, SQL server itself terminates excess string.
2. Question followed, what ever user entered is valuable to us. We have to store it. If he enters 18000 chars. I want to keep it in my DB some way. Think and give a solution.
3. I will implement Encoding Mechanism which can shrink 8000 ANSI characters to some thing around 4000 chars. I encode the user input and put it in table. While retrieving I will decode and use it front end. But this encoding and decoding is performance hazard.
4. Question followed, Good Idea! But if I pass 30000 characters to the database, as u said U can shrink it to 15000 length encoded string. Again, how can you a store 15000 string in a column? Think of some thing else.
5. I know this is the worst idea, but we can do like this, we know which columns probably exceed 8000 char. If any row data exceeds 8000 then add one more column to the database and out excess string in second column named COL2. If it exceed further maintain COL3, COL4 like that. While retrieving we can get the concatenated text. But all other rows have NULL data in these columns. So Again this is poor database design. No one allows me to modify the db design. I can give you one more solution. We have Primary Key for each row in the table. If any row exceeds 8000. Simply note that Primary key. Take one more table and store the exceeded string against the noted KEY. We can get the full text by joining these two tables. These two tables have relation like Primary and Foreign Key.
6. Good! But why you are thinking about another table. Why don’t you put it in the same table? Think and give me a solution.
7. I assumed that U have Primary key on the table. So I cannot store the exceeded data in another row with the same ID. That’s the reason for suggesting a Child table. Even now also we can do one thing. Let us say, Column in 3rd row exceeded 8000 char. Then I will store exceeded string in the same table with ID 3E1. Similarly if it has more characters I would like to go for 3E2, 3E3 like that. So Rows, which have 3E suffix, are related to row 3. But It is always better to have Numeric in Primary Column. If we think in this direction, put the one more column in the TABLE. This Column name is PARENT ROW. Now, new row will be stored with id 4 but it has parent row as 3. I mean to say, it has self-reference column. We can get the all text by joining the related rows based on parent row information.
8. Question followed, well. Do you know where Stored Procedure is stored in Database?
9. SP text is stored in system table SysComments.
10. Question followed, I can write 2000 lines of code in a stored procedure. Did u ever think of it? How can SQL server store 2000 lines of code in SysComments?
11. I don’t know exactly. It may break the SP text into pieces of 8000 characters each. Each SP has one ID. Against that ID all these pieces will be stored in SysComments. Is it right?
12. Go and check in the system!!!
63. I have CountryId and Country Name columns in the table.
For me,
a. I asked him about Priority/Flag of each country. He said that he doesn’t have any other columns except CountryID and CountryName.
b. I questioned him, suppose tomorrow you may think
c. What you said is correct. As a manager I know what will happen in future. I just had given this task to you. How you will do it?
d. I had given few solutions. Put a new column in the table with Column name priority. For US give priority 1. For
e. Put * before Important countries. ‘*’ is always before ‘A’ in order. While writing the query Replace the ‘*’ with empty string. So that we can get Priority countries first. But, here we are changing the country name it self. If some one wants to use our country table, he will get Countries names with ‘*’. So think for one more solution.
f. I asked him, “Can I use sequence of statements?” He agreed. I created a temporary table with county name and Priority. After that updated the priorities for US and
Select CountryName, 0 Priority into #temp from Country
Update #Temp set Priority=1 where CountryName=’US’
Update #Temp set Priority=1 where CountryName=’
g. Finally, I tried with Union clause. This is just like hard coded thing. In the query itself I mention Country names. I used IN and NOT IN operators. But When I was putting Order By clause for last statement, it was ordering all the countries in an alphabetical order. So my idea of using
64. What is an extended stored procedure? Can you instantiate a COM object by using SQL?
a. An extended stored procedure is a function within a DLL (written in a programming language like C, C++ using Open Data Services (ODS) API) that can be called from T-SQL, just the way we call normal stored procedures using the EXEC statement. See books online to learn how to create extended stored procedures and how to add them to SQL Server. Yes, you can instantiate a COM (written in languages like VB, VC++) object from T-SQL by using sp_OACreate stored procedure. Also see books online for sp_OAMethod, sp_OAGetProperty, sp_OASetProperty, sp_OADestroy. For an example of creating a COM object in VB and calling it from T-SQL, see ‘My code library’ section of this site.
65. What is the system function to get the current user’s user id?
a. USER_ID(). Also check out other system functions like USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME().
66. What are statistics, under what circumstances they go out of date, how do you update them?
o Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. Query optimizer uses these indexes in determining whether to choose an index or not while executing a query. Some situations under which you should update statistics: 1) If there is significant change in the key values in the index 2) If a large amount of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated 3) Database is upgraded from a previous version. Look up SQL Server books online for the following commands: UPDATE STATISTICS, STATS_DATE, DBCC SHOW_STATISTICS, CREATE STATISTICS, DROP STATISTICS, sp_autostats, sp_createstats, sp_updatestats
67. What are the different ways of moving data/databases between servers and databases in SQL Server?
a. There are lots of options available, you have to choose your option depending upon your requirements. Some of the options you have are: BACKUP/RESTORE, dettaching and attaching databases, replication, DTS, BCP, logshipping, INSERT…SELECT, SELECT…INTO, creating INSERT scripts to generate data.
68. Explain different types of BACKUPs available in SQL Server? Given a particular scenario, how would you go about choosing a backup plan?
a. Types of backups you can create in SQL Sever 7.0+ are Full database backup, differential database backup, transaction log backup, filegroup backup. Check out the BACKUP and RESTORE commands in SQL Server books online. Be prepared to write the commands in your interview. Books online also has information on detailed backup/restore architecture and when one should go for a particular kind of backup.
69. What is database replication? What are the different types of replication you can set up in SQL Server?
a. Replication is the process of copying/moving data between databases on the same or different servers. SQL Server supports the following types of replication scenarios: · Snapshot replication · Transactional replication (with immediate updating subscribers, with queued updating subscribers) · Merge replication See SQL Server books online for indepth coverage on replication. Be prepared to explain how different replication agents function, what are the main system tables used in replication etc.
70. What is RAID and what are different types of RAID configurations?
a. RAID stands for Redundant Array of Inexpensive Disks, used to provide fault tolerance to database servers. There are six RAID levels 0 through 5 offering different levels of performance, fault tolerance. MSDN has some information about RAID levels and for detailed information, check out the RAID advisory board’s homepage
71. What are the steps you will take to improve performance of a poor performing query?
a. This is a very open ended question and there could be a lot of reasons behind the poor performance of a query. But some general issues that you could talk about would be: No indexes, table scans, missing or out of date statistics, blocking, excess recompilations of stored procedures, procedures and triggers without SET NOCOUNT ON, poorly written query with unnecessarily complicated joins, too much normalization, excess usage of cursors and temporary tables. Some of the tools/ways that help you troubleshooting performance problems are: SET SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SET STATISTICS IO ON, SQL Server Profiler, Windows NT /2000 Performance monitor, Graphical execution plan in Query Analyzer. Download the white paper on performance tuning SQL Server from Microsoft web site. Don’t forget to check out sql-server-performance.com
72. What’s the maximum size of a row?
a. 8060 bytes. Don’t be surprised with questions like ‘what is the maximum number of columns per table’. 1024 columns per table. Check out SQL Server books online for the page titled: "Maximum Capacity Specifications". Explain Active/Active and Active/Passive cluster configurations Hopefully you have experience setting up cluster servers. But if you don’t, at least be familiar with the way clustering works and the two clusterning configurations Active/Active and Active/Passive. SQL Server books online has enough information on this topic and there is a good white paper available on Microsoft site. Explain the architecture of SQL Server This is a very important question and you better be able to answer it if consider yourself a DBA. SQL Server books online is the best place to read about SQL Server architecture. Read up the chapter dedicated to SQL Server Architecture.
73. What is lock escalation?
a. Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks). Every lock is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer coarse-grain locks. Lock escalation threshold was definable in SQL Server 6.5, but from SQL Server 7.0 onwards it’s dynamically managed by SQL Server.
74. What’s the difference between DELETE TABLE and TRUNCATE TABLE commands?
a. DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won’t log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back. TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause: both remove all rows in the table. But TRUNCATE TABLE is faster and uses fewer system and transaction log resources than DELETE. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log. TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column. If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement. You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger. TRUNCATE TABLE may not be used on tables participating in an indexed view
75. What are user defined datatypes and when you should go for them?
a. User defined datatypes let you extend the base SQL Server datatypes by providing a descriptive name, and format to the database. Take for example, in your database, there is a column called Flight_Num which appears in many tables. In all these tables it should be varchar(8). In this case you could create a user defined datatype called Flight_num_type of varchar(8) and use it across all your tables. See sp_addtype, sp_droptype in books online.
76. Define candidate key, alternate key, composite key.
a. A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys. A key formed by combining at least two or more columns is called composite key.
77. What can I do so that nobody should able to drop a table?
78. Can I create CHECK constraints on user defined data types?
79. How many types of User Defined Function SQL server support?
80. NOLOCK is used for what purpose?
81. What is the difference between EXEC and sp_executesql stored procedure?
82. Which one is faster and why? Sp_executesql is faster why is it so?
83. One Non-Clustered index is based on Clustered index, what will happen if I drop clustered index, will it re-create new non-clustered index or will drop it?
0 Comments:
Post a Comment