sql-Questions

1 What is BCP? When do we use it? - BulkCopy is a tool used to copy huge amount of data from tables and views. But it won’t copy the structures of the same.
2 What should we do to copy the tables, schema and views from one SQL Server to another?
We have to write some DTS packages for it.
3 What are the different types of joins and what dies each do?
4 What are the four main query statements?
5 What is a sub-query? When would you use one?
6 What is a NOLOCK?
7 What are three SQL keywords used to change or set someone’s permissions?
8 What is the difference between HAVING clause and the WHERE clause?
9 What is referential integrity? What are the advantages of it?
10 What is database normalization?
11 Which command using Query Analyzer will give you the version of SQL server and operating system?
12 Using query analyzer, name 3 ways you can get an accurate count of the number of records in a table?
13 What is the purpose of using COLLATE in a query?
14 What is a trigger?
15 What is one of the first things you would do to increase performance of a query? For example, a boss tells you that “a query that ran yesterday took 30 seconds, but today it takes 6 minutes”
16 What is an execution plan? When would you use it? How would you view the execution plan?
17 What is the STUFF function and how does it differ from the REPLACE function?
18 What does it mean to have quoted_identifier on? What are the implications of having it off?
19 What are the different types of replication? How are they used?
20 What is the difference between a local and a global variable?
21 What is the difference between a Local temporary table and a Global temporary table? How
is each one used?
22 What are cursors? Name four types of cursors and when each one would be applied?
23 What is the purpose of UPDATE STATISTICS?
24 How do you use DBCC statements to monitor various aspects of a SQL server installation?
25 How do you load large data to the SQL server database?
26 How do you check the performance of a query and how do you optimize it?
27 How do SQL server 2000 and XML linked? Can XML be used to access data?
28 What is SQL server agent?
29 What is referential integrity and how is it achieved?
30 What is indexing?
31 What is normalization and what are the different forms of normalizations?
32 Difference between server.transfer and server.execute method?
33 What id de-normalization and when do you do it?
34 What is better - 2nd Normal form or 3rd normal form? Why?
35 Can we rewrite subqueries into simple select statements or with joins? Example?
36 What is a function? Give some example?
37 What is a stored procedure?
38 Difference between Function and Procedure-in general?
39 Difference between Function and Stored Procedure?
40 Can a stored procedure call another stored procedure. If yes what level and can it be
controlled?
41 Can a stored procedure call itself(recursive). If yes what level and can it be controlled.?
42 How do you find the number of rows in a table?
43 Difference between Cluster and Non-cluster index?
44 What is a table called, if it does not have neither Cluster nor Non-cluster Index?
45 Explain DBMS, RDBMS?
46 Explain basic SQL queries with SELECT from where Order By, Group By-Having?
47 Explain the basic concepts of SQL server architecture?
48 Explain couple pf features of SQL server
49 Scalability, Availability, Integration with internet, etc.)?
50 Explain fundamentals of Data ware housing & OLAP?
51 Explain the new features of SQL server 2000?
52 How do we upgrade from SQL Server 6.5 to 7.0 and 7.0 to 2000?
53 What is data integrity? Explain constraints?
54 Explain some DBCC commands?
55 Explain sp_configure commands, set commands?
56 Explain what are db_options used for?
57 What is the basic functions for master, msdb, tempdb databases?
58 What is a job?
59 What are tasks?
60 What are primary keys and foreign keys?
61 How would you Update the rows which are divisible by 10, given a set of numbers in
column?
62 If a stored procedure is taking a table data type, how it looks?
63 How m-m relationships are implemented?
64 How do you know which index a table is using?
65 How will oyu test the stored procedure taking two parameters namely first name and last
name returning full name?
66 How do you find the error, how can you know the number of rows effected by last SQL
statement?
67 How can you get @@error and @@rowcount at the same time?
68 What are sub-queries? Give example? In which case sub-queries are not feasible?
69 What are the type of joins? When do we use Outer and Self joins?
70 Which virtual table does a trigger use?
71 How do you measure the performance of a stored procedure?
72 Questions regarding Raiseerror?
73 Questions on identity?
74 If there is failure during updation of certain rows, what will be the state?
75 SQL Server interview questions
76 How do you read transaction logs?
77 How do you reset or reseed the IDENTITY column?
78 How do you persist objects, permissions in tempdb?
79 How do you simulate a deadlock for testing purposes?
80 How do you rename an SQL Server computer?
81 How do you run jobs from T-SQL?
82 How do you restore single tables from backup in SQL Server 7.0/2000? In SQL Server 6.5?
83 Where to get the latest MDAC from?
84 I forgot/lost the sa password. What do I do?
85 I have only the .mdf file backup and no SQL Server database backups. Can I get my
database back into SQL Server?
86 How do you add a new column at a specific position (say at the beginning of the table or
after the second column) using ALTER TABLE command?
87 How do you change or alter a user defined data type?
88 How do you rename an SQL Server 2000 instance?
89 How do you capture/redirect detailed deadlock information into the error logs?
90 How do you remotely administer SQL Server?
91 What are the effects of switching SQL Server from ‘Mixed mode’ to ‘Windows only’
authentication mode? What are the steps required, to not break existing applications?
92 Is there a command to list all the tables and their associated filegroups?
93 How do you ship the stored procedures, user defined functions (UDFs), triggers, views of
my application, in an encrypted form to my clients/customers? How do you protect intellectual property?
94 How do you archive data from my tables? Is there a built-in command or tool for this?
95 How do you troubleshoot ODBC timeout expired errors experienced by applications
accessing SQL Server databases?
96 How do you restart SQL Server service automatically at regular intervals?
97 What is the T-SQL equivalent of IIF (immediate if/ternary operator) function of other
programming languages?
98 How do you programmatically find out when the SQL Server service started? How do you get rid of the time part from the date returned by GETDATE function