SQL SERVER ( CURSORS ) REAL TIME INTERVIEW QUESTIONS AND ANSWERS

1. What are cursors? Explain different types of cursors.?
2. What are the disadvantages of cursors? How can you avoid cursors?
3. Difference between fast-forward and forward cursor?

SQL SERVER ( TRIGGERS ) REAL TIME INTERVIEW QUESTIONS AND ANSWERS

1. What is Trigger?
2. What is its user?
3. What are the types of Triggers?
4. What are the new kinds of triggers in sql 2000?
5. When should one use " insted of Trigger"?
6.  Difference between trigger and stored procedure?
7. What are triggers? How many triggers you can have on a table? How to invoke a trigger on demand?
8. There is trigger defined for INSERT operations on a table. in an OLTP system. The trigger is written to instantiate a COM object and pass the newly inserted rows  to it for some custom processing. What do you think of this implementation? Can this be implemented better?
9. Which virtual table does a trigger use?
10. What are the types of Triggers?
11. What will be query used to get the list of triggers in a database?
12. What are magic tables in sql server?
13. Can i invoke/instantiate COM objects from within stored procedures or triggers using T-SQL?
14. What are the pros and cons of using triggers?
15. What is CLR?
16. What is the order of execution of multiple triggers?
17. The difference between instead of trigger and after trigger?
18. Can i change the sequence of firing a trigger, if yes how?
19. What is direct a regression triggers, how to disable this feature in SQL Server - 2008?
20. What is indirect regression?

SQL SERVER ( FUNCTIONS ) REAL TIME INTERVIEW QUESTIONS AND ANSWERS

1. What is User Defined Functions?
2. What kind of User-Defined Functions can be created?
3. What are user defined datatypes and when you should go for them?
4. What is a Function & What are the different user defined functions?
5. What are the difference between a functions and a stored procedure?
6. What is the limitation of user defined function?
7. Can a user defined function return table?

SQL SERVER ( STORED PROCEDURES ) REAL TIME INTERVIEW QUESTIONS AND ANSWERS

1. How can you debug a stored procedure?
2. What are temporary store procedures, how to create them?
3. What is the need of the store procedure?
4. What is Estimated execution plan and Actual execution plan?
5. I wrote sp but it is taking 15 min to executive how do you do sp optimization?
6. Well sometime sp rename db may not work you know because if some one is using the db it will not accept this command so what do you think you can do in such cases?
7. What is an extended stored procedure? Can you instantiate a COM object by using T-SQL?
8. What is an execution plan? When would you use it? How would you view the execution plan?
9. What is sp_configure commands adn set commands?
10. What does it mean to have quoted_identifier on ? What are the implications of having it off?
11. Can a stored procedure call itself or recursive stored procedure? How many level SP nesting possible?
12. What is the advantage of SET NOCOUNT ON?
13. What is Stored Procedure?
14. What command do we use to rename a db, atable and a column?
15. What is recursive stored procedure?
16. How exception can be handled in SQL Server Programming?
17. What are the differences between Stored Procedure and the dynamic SQL ?
18. What is the use of SET NOCOUNT ON/OFF statement?
19. Which SQL Server table is used to hold the Stored Procedure scripts?
20. What is the use of @@SPID?
21. What is the command used to Recompile the stored procedure at run time?
22. Which command is used for user defined error messages?
23. What is an extended stored procedure? Can you instantiate a COM object by using T-SQL?
24. What is T-SQL equivalent of IIF (immediate if/ternary operator) function of other programming languages?
25. How to run an SQL script file that is located on the disk, using T-SQL?
26. How to get the complete erroe message from t-SQL while erroe handling?
27. How to pass a table name, column name ets. to the stored procedure so that i can dynamically select from a table?
28. Error inside a stored procedure is not being raised to my front-end applications using SDO. But i get the error when i run the procedure from Query Analyzer?
29. How to suppress error messages i stored procedures/triggers etc. using T-SQL?
30. How to save the output of a query/stored procedure to a text file using t-SQL?
31. Is there a way to find out when a stored procedure was last upsdated?
32. What is an advantage to using a stored procedure as opposed to passing an SQL query from an application?
33. What are the different types of Storage Procedure?
34. How do i mark the stored procedure to automatic execution?
35. How can you optimize a stored procedure?
36. How will know whether the SQL Statements are executed?
37. Why one should not prefix user stored procedures with sp_?
38. What can cause a stored procedure execution plan to become invalidated and/or fall out of cache?
39. When do one need to recomplile stored procedure?
40. SQL Server provides how many ways to recompile a stored procedure?
41. How to find out which stored procedure is recompiling? How to stop stored procedures from recompiling?
42. I have two stored procedures SP1 and SP2 as given below. How the transaction works, whether SP2 transaction succeeds or fails?
43. How will you handle Errors in SQL Stored Procedure?
44. I have a stored procedure like
      commit tran
      create table a()
      insert into table b
      ------
      ------
      rollback tran
     What will be the result? Is table created? data will be inserted in table b?
45. What do you do when one procedure is blocking the other?
46. How you will return XML from stored procedure?
47. Difference between view and stored procedure?
48. sp_configure commands?
49. What is the nesting limit of SQL Server stored procedure?
50. In how many ways you can recieve information fro stored procedure?
51. List few advantages of stored procedure?
52. What is the use of shell commands? xp_cmdshell?
53. What is Raise error?
54. What are the steps you will take to improve performance of a poor performing query?
55. sp_who?
56. Have you worked on Dynamic SQL? How will you handled "(Double Quotes)" in Dynamic SQL?
57. How to call a COM dll from SQL Server 2000?
58. You have a SP names YourSP and have the a Select Statement inside the SP. You also have a user named YourUser. What permissions you will give him for accessing the SP.?
59. Where does system store procedures located?
60. What happens If i create a procedure with SP_ ?

SQL SERVER ( VIEWS ) REAL TIME INTERVIEW QUESTIONS AND ANSWERS

1. What is a SQL View?
2. What is View? Use  ? Syntax of View ?
3. Does the View occupy memory space?
4. Can u drop a table if it has a view?
5. Why doesn't SQL Server permit an ORDER BY clause in the definition of a view?
6. How do you create view and limit of columns?
7. What do you understand by a view? What does the WITH CHECK OPTION clause for a view do?
8. What is difference between View and Materialized view?
9. Write a CTE to display department hierarchy?
10. Views accepts encryptions?
11. Can you write the query of view using encryption?
12. Can view create any new table while after creation of view? Or get data from original table?
13. We have 40 tables in a view and used join in query can we update 38 table in view?
14. How do you differentiate Local and Global Temporary table?
15. What is the difference between a local and a global variable?
16. Difference between temp table and table variable?
17. What is the difference between # tables and declare table(temp tables)?
18. What is the difference between # tables and ##tables?
19. How Global temporary tables are representated and its scope?
20. Define some facts about Temporary table and there types?
21. What is CTE?
22. What are the Advantages of using CTE?

SQL SERVER ( SUB QUERIES ) REAL TIME INTERVIEW QUESTIONS AND ANSWERS

1. What is sub-query? Explain properties of sub-query?
2. What are the Properties of Sub-Query?
3. What are types of sub-queries?
4. What is a correlated sub-query? How can these queries be useful?
5. How the Exists keyword works in SQL Server?
6. Can we rewrite sub queries into simple select statements or with joins?
7. What is derived table?

SQL SERVER ( INDEXES ) REAL TIME INTERVIEW QUESTIONS AND ANSWERS

1. How can i enforce to use particular index?
2. What is sorting and what is the difference between sorting and clustered indexes?
3. What is a table called, if it does not have neither Cluster nor Non-cluster Index? What is it used for?
4.  How to know which index a table is using?
5. Size of Clustered and Non-Clustered in 2005 and 2008?
6. What do you mean by Filtered indexes?
7. What is heap table in SQL SERVER?
8. What are the difference between clustered and a non-clustered index?
9. What are the different index configurations a table can have?
10. What are the trade offs with having indexes?
11. What types of index data structures can you have?
12. What is "index covering" of a query?
13. What is Index?
14. Can we create primary key without clustered index?
15. What is UPDATE_STATISTICS command?
16. What will be the maximum number of index per table?
17. What is an index? What are the types of indexes? How many clustered indexes can be created on a table?I create a separate index on each column of a table. What are the advantages and disadvantages of this approach?
18. What are statistics, under what circumstances they go out of date, how do you update them?
19. When a query is sent to the database and an index is not being used, what type of execution is taking place?
20. What are Index Optimization tips?
21. What is Index? It's purpose?
22. Explain about Clustered and non clustered index? How to choose between a Xlustered Index and a Non-Clustered Index?
23. Disadvantage of Index?
24. Give a scenario that i have a 10 clustered index in a table to all their 10 columns. What are the advantages and disadvantages?
25. How can i enforce to use particular index?
26. What is index tuning?
27. Differences between index defrag and index rebuild?
28. What is sorting and what is the difference between sorting & clustered indexes?
29. What are statistics, under what circumstances they go out of date, how do you update them?
30. What is fill factor? What is the use of it? What happens when we ignore it ? When you should use low fill factor?
31. How many type of indexes are there?
32. How many index can be created on a table?
33. What is Filtered Index in SQL?
34. Create INDEX mu Index ON myTable ( myColumn )
What type of Index will get created after executing the above statement?

SQL SERVER ( JOINS ) REAL TIME INTERVIEW QUESTIONS AND ANSWERS

1. What is a join and List different types of joins?
 Joins are used in queries to explain how different tables are related.Joins are also let you select data from a table depending upon data from another  table.
Types of JOINS :
--INNER JOIN
--OUTER JOIN
   LEFT OUTER JOIN,RIGHT OUTER JOIN,FULL OUTER JOIN
--CROSS JOIN
2. What are the differences between UNION and JOINS?
A join selects columns from two or tables.
A union selects rows.
3. What is the Referential Integrity?

4. What is a self-join? Explain it with an example?
5. How to join tables from different databases?
6. How to join tables from different servers?
7. What is a Cartesian product? What causes it?
8. T1 table having 7 rows and T2 table having 3 rows, How to find out missing rows from table T1?
9. What is the difference of a LEFT JOIN and an INNER JOIN statement?
10. Can i improve performance by using the ANSI-style joins instead of the old-style joins?

SQL SERVER ( TRANSACTIONS ) REAL TIME INTERVIEW QUESTIONS AND ANSWERS

1. What is Transaction?
2. What is Dead Lock?
3. What is a Live Lock?
4. What is Isolation Level?
5. What is a NOLOCK?
6. Can we check locks in database? If so, how can we do this lock check?
7. What is lock escalation?
8. What is a deadlock and what is a live lock? How will you go about resolving deadlocks?
9. What is blocking and How would ou troubleshoot it?
10. What are statistics, under what circumstances they go our of date, how do you update them?
11. What are locks?
12. What are the different types of locks?
13. For example, if the transaction isolation level is set to SERIALIZABLE, and the table-level locking hint NOLOCK is used with the SELECT statement , key-range locks typically used to maintain serializable transactions are not taken?
14. After one Begin Transaction a truncate statement and Roll Back statements ar ether. Will it be roll backed? Since the truncate statement does not perform logged operation how does it Rollback?
15. What is the value of @@Row count at each statement levels?
16. What is Uncommitted Dependency (Dirty Read)?
17. What is Inconsistent Analysis (Non Repeatable Read)?
18. What is Phantom Reads?
19. What is @@ Tran count?
20 Can you have a nested transaction?
21.What is Isolation Level?
22. What is the difference between the REPEATABLE READ and SERIALIZE isolation levels?
23. What is the difference between COMMIT and ROLLBACK?

SQL SERVER ( CONSTRAINTS ) REAL TIME INTERVIEW QUESTIONS AND ANSWERS

1. What is Data Integrity?
Data integrity is an important feature in SQL Server. When used properly, it ensures that data is accurate,correct,and valid.

2. 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.  

3. 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.

4. What is PRIMARY KEY?
A Primary Key is constraint is a unique identifier for a row within a data base table.It doesn't accepts duplicates and null values.

5. What is UNIQUE KEY Constraint?
 A Unique constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered.It can accept only one NULL.

6. What is FOREIGN KEY?
 A Foreign Key is a column that references  a column of another table. The purpose of the foreign Key is to ensure referential integrity of the data.

7. What is CHECK Constraint?
A check constraint is used to limit the values that can be placed in a column.The check constraints are used to enforces domain integrity.

8. What is NOT NULL Constraint?
A NOT NULL constraint enforces that the column will not accept null values.The not null constraints are used to enforce domain integrity, as the check constraints.

9. What is different in Rules and Constraints?
Rules and Constraints are similar in functionality but there is a little difference between them.Rules are used for backward compatibility.One the most exclusive difference is that we can bind rules to a data types where as constraints are bound only to columns.So we can create own data types with the help of Rules and get the input according to that.

10. What is the difference between a PRIMARY KEY and 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 as unique creates a non clustered index by default.Another major difference is that ,primary key doesn't allow NULLs,But unique key allows only one NULL.

11. Difference candidate key, alternate key and composite key?
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 rest are called alternate keys.
A key formed by combining at least two or more columns is called composite key.

12. Try to use constraints instead of triggers, whenever possible?

13. What is the use of DBCC Commands?
DBCC stands for database constistency checker.We use these commands to check the consistency of the database.
i.e.,maintenance,validation task and status checks.
Ex: DBCC CHECKDB - Ensures that tables in the DB and indexes are correctly linked.
DBCC CHECALLOC-To check that all pages in a DB are correctly allocated.

14. Have you ever used DBCC Command? Give an example for it?

15. How do you use DBCC Statements to monitor various aspects of a SQL Server  installation?

16. What is a Surrogate Key? What is the difference between Primary and Unique Key? What is the difference Primary and Surrogate Key?

17. Difference between column level check constraint and  table level check constraint?

18. How many check constraints for a table?

19. What is the difference between check constraints and trigger?

20. I want create check constraint on salary column as salary>0 but existing data already constain negative values as shown below. What happen if conflict occurs?

SQL SERVER ( OPERATORS & SYSTEM FUNCTIONS ) REAL TIME INTERVIEW QUESTIONS AND ANSWERS

OPERATORS

1. What is COALESCE in SQL Server?

    COALESCE is used to return first non-null expression within the arguments. This function is used to return a non-null from more than one column in the arguments.
Example:-  [sql]Select COALESCE(empno, empname, salary) from employee[/sql]

2. What is the use of =,==,=== operators?

= is used to assign one value or variable to another variable.
== is used for comparing two strings or numbers.
=== is used to compare only string with the string and number with numbers.

3. What is ISNULL () Operator?

    ISNULL function is used to check whether value given is NULL or not NULL in SQL Server.  This function also provides to replace a value with the NULL.

4. Try to use UNION ALL statement instead of UNION, Whenever possible.

    The UNION ALL Statement is much faster than UNION, because UNION ALL Statement deos not look for duplicate rows, and UNION Statement does look for duplicate rows, whether or not they exist.

5. How can you compare a part of the name rather than the entire name?

    Select * from people where empname like  '%ab%'
Would return a recordset with records consisting empname the sequence 'ab' in empname.

6. What is MERGE Statement?

    MERGE is a new feature that provides an efficient way to perform multiple DML operations. In previous versions of SQL Server, we had to write separate statements to INSERT, UPDATE or DELETE data based on certain conditions, but now using MERGER statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it.
One of the most important advantage of MERGER Statement is all the data is read and processed only one.

7. How do enable and disable identity property?

8. What is better -2nd Normal Form or 3rd Normal Form? why?

9. I want convert lowercase letters into uppercase how do you do?  

10. How do you concatenate first name and last name?

SYSTEM FUNCTIONS

1. Write a SQL Query to find first Week Day of Month?

    SELECT DATENAME(dw, DATEADD(dd, -DATEPART(dd, GETDATE())+1, GETDATE())) AS FirstDay

2. What is the system function to get the current user's user id?

    USER_ID(). Also check out other system functions like USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID90, HOST_NAME().

3. What are the different types of replication ? Explain.

   STUFF function to overwrite existing characters, Using this syntax, STUFF ( string expression, start , length, replacement characters), string expression is the string that will have characters substituted, start is the starting position, length is the number of characters in the string that are substituted, and replacement characters are the new characters interjected into the string.
    REPLACE function to replace existing characters of all occurrence, Using this syntax REPLACE (string expression, search string, replacement string), where every incidence of search string found in the string expression will be replaced with replacement string.

4. What is the STUFF Function and how does it differ from the REPLACE Function?

5. What is the purpose of Floor Function? 

   FLOOR Function is used to round up a non-integer value to the previous least integer.
 Ex :-
[sql]FLOOR(6.7)[/sql]
Returns 6.

6. What is the use of SIGN Function?

     SIGN Function is used to determine whether the number specified is Positive, Negative and Zero, This will return +1,-1 or 0
Ex ;-  [sql]SIGN(-35) returns -1[/sql]

7. What is the SUBSTR?

     The SUBSTR function is used to return specific portion of string in a given string.
EX:- [sql]SUBSTR("Smiley",3)[/sql]

8. What is the difference between GETDATE and SYSDATETIME?

    Both are same but GETDATE can give time till milliseconds and SYSDATETIME can give precision till nanoseconds. SYSDATE TIME is more accurate than GETDATE.

9. What is DATEPART?

   Returns an integer representing the specified date part of the specified date.

10. How to get rid of the tim epart from the date returned by GETDATE Function? (top)

   We have to use the COVERT function to strip the time off the date. Any of the following commands will do this :

SELECT CONVERT (char, GETDATE(), 101)
SELECT CONVERT (char, GETDATE(), 102)
SELECT CONVERT (char, GETDATE(), 103)
SELECT CONVERT (char, GETDATE(), 1)

11. How to get the first day of the week, last day of the week and last day of the month using T-SQL date functions?(top)

       DECLARE @ Date datetime
SET @Date = '2001/08/31'
SELECT DATEADD(dd, -(DATEPART(dw, @Date) - 1), @Date) AS 'First day of the week'
SELECT DATEADD(dd, -(DATEPART(dw, @Date) - 7), @Date) AS 'Last day of the week'
SELECT DAY(DATEADD(d,-DAY(DATEADD(m,1,@Date)),DATEADD(m,!,@Date))) AS 'Last day of the month'

12. What is snapshot replication how is it different from Transactional replication?

13. What is OPENXML in SQL Server?

14. What is RANK Function?
Returns the rank of each row within the partition of a result set.
syntax:RANK() OVER ([PARTION_BY_CLAUSE] ORDER_BY_CLAUSE) 15. Diference between Rank and Dense Rank?
Rank:It calculates the rank with gaps
Dense Rank:It calculates rank without gaps.

SQL SERVER (SQL SERVER INTRODUCTION) REAL TIME INTERVIEW QUESTIONS AND ANSWERS

1. What is SQL?
     SQL stands for 'structured query language'. SQL is used to communicate with database.

2. How to determine the service pack currently installed on SQL Server?
     The global variable @@version stores the build number of sqlserver.exe, which is used to determine service pack installed.

3. What is a scheduled jobs or what is a Scheduled Tasks?(SQL DBA)

4. How do you load large data to the SQL Server database?
    Bulk copy is a tool used to copy the huge amount of data from tables.BULK INSERT command helps to imports a data file into a database table or view or user-specified format.

5. Can you SQL Servers linked to other servers like Oracle?
    Yes.

6. What are the OS Services that the SQL Server installation adds?(SQL DBA)

7. What is BCP? When does it used?
     Bulk copy program is a tool used to copy huge amount of data from tables and views.BCP does not copy structures same as source to destination.

8. What is SQL Profiler?
   

9. Which TCP/IP port does SQL Server run on? How can it be changed?(SQL DBA)
    SQL server runs on port 1433.it can be change from network utility TCP/IP properties--> Number .Both on client and the server.

10.Which command using Query Analyzer will give you the version of SQL Server and operating system?(SQL DBA)

11.What is SQL Server agent?
     SQL server agent plays an important role in the day-to-day tasks of DBA(database  administrator)

12. What are Sparse Columns?
     Sparse column is another tool to reduce the amount of physical storage used in database .

13. Using query analyzer, name 3 ways to get an accurate count of the number of records in a table?
      -->Select * from table
       -->Select count(*) from table
       -->select rows from sysindexes
             where id=object_id(table) and indid <2

14. Can SQL Servers linked to other servers?
       Yes.

15. How to restart SQL Server in single user mode?(SQL DBA)

16. what is DTC?(SQL DBA)

17. What is DTS?
       Data Transformation Services (DTS) is a set of graphical tool and programmable objects that lets you extract,Transform,and consolidate data from disparate sources into single or multiple destinations.

18. What is SQL injection?(SQL DBA)

19. What are the methods used to protect against SQL injection attack?(SQL DBA)

20. What is RAID and what are different types of RAID configurations?(SQL DBA)

21. What are the steps you will take, if you are tasked with securing an SQL Server? (SQL DBA)

22. What is the architecture of SQL Servers in terms of its hierarchy?

SERVER
|
DATABASE
|
SCHEMA
|
TABLE
|
COLUMNS
A server has multiple databases and database has multiple schemas and so on......

23. What are the two types of authentication on SQL Server 2008?
      Windows:
    Map with the current use. In other words,you can get on the server as long as you are on your Windows.Uses the credentials for windows.
      SQL Server:
   Also known as mixed mode authentication. You need both windows and the sql server authentication so this type of Authentication is more secure.

24. What are the authentication modes in SQL Server? How can it be changed?(SQL DBA)

25. What is the difference between a connection and session?
    Connection:
  It is the number of instance connected to the database.An instance is model zed soon as the application is open again.49 connection maximum are allowed in SQL Server 2008
   Session:
  A session runs queries.In one connection, SQL Server 2008 Allowed 10 million of session for one connection.

26. What is the row size in SQL Server 2008 R2?
      8KB

27. How to copy the Tables, Schema and Views from one SQL Server to another?
    Data Transformation Server (DTS) is a set of graphical tool and programmable objects that lets user extract,transform,and consolidate data from  disparate sources into single or multiple destinations.

28. What are the basic functions for master, msdb, model, tempdb databases?
    The master database holds information for all databases located on the SQL server instance and is the glue that holds the engine together. Because SQL Server cannot start without a functioning master database, you must administer this database with care.
     The msdb database stores information regarding database backups,SQL Agent information,DTS packages,SQL Server jobs, and some replication information such as for log shipping.
    The tempdb holds temporary objects such as global and local temporary tables and stored procedures.
     The model is essentially a template database used in the creation of any new user database created in the instance.

29. How to rebuild Master Database?(SQL DBA)

30. What is the use of Master Database?

31. What is the use of model db if we did any changes in model db these changes will effect existing databases or not?

32. What is the role of master database?

33. What is the role of tempDB database?

34. What is the role of Model Database?

35. What is the role of Model Database?

36. Where SQL Server user's names and passwords are stored in SQL Server?

37. GUID data size?
  128bit

38. How GUID becoming unique across machines?(SQL DBA)

39. What are the default file groups and file extensions?

40. What is Primary Data File (.mdf)?
    It contains startup information  for the DB and points the other Files in the DB.User  data and objects can be stored in this file or in secondary data file .
   Every DB must have at least one primary DB file(mandatory).
    Recommended file extension (.mdf) 

41. What s Second Data File (.ndf)?
      These are optional, are user defined and store user data. Secondary DB files are used to spread data across multiple Disk Drives .
     Recommended file Extension (.ldf)

42. What is Transaction Log File(.ldf)?
    It holds the log information that is used to recover the DB.
  There must at least one log file (Mandatory), Recommended file extension is (.ldf)

43. Write syntax to take database backup?(SQL DBA)
44. Database backup file extension?
   .bak

45. How many types of database backups, what are they?(SQL DBA)

46. Write syntax to create new file group?
   Create database db_name on (name='db_name_dat',filename='c///...db_name_dat.mdf',size in mb,max size =in mb's,filegrouwth=in mb's)

47. How can I add files to a file group?
File is a place where actual data is stored.File is a part of file group so whenever we create a table the transaction data is stored in a file
 
48. What is the use of file group?
  It is used to separate the data.

49. Write syntax to create new transaction log? And what is the file extension?

50. How do you add users to roles and give permissions to roles?(SQL DBA)

51. What is a default FG? How to change it?(SQL DBA)

52. List what actios can be done at FG level - eg marking it read-only---(SQL DBA)

53. What are the differences between setting a DB in " Restricted" Model VS 'Single User Mode"?(SQL DBA)

54. What is a "Removable Database" - Why do we require this? How to use this?(SQL DBA)

55. How is "Attaching /Detaching" a DB? How does this work?(SQL DBA)

56. How to change the owner of a DB?(SQL DBA)

57. How to view statistics for a table/col?(SQL DBA)

58. What are the data types in SQL?
       Big int,binary,bit,char,cursor,date time,decimal,float,image,int,money,nchar,ntext,nvarchar,real,small int,small money,text,time stamp,tiny int,var binary,varchar,uniqueidentifier.

59. What is user defined data types and when you should go for them?(SQL DBA)
60. How to convert timestamp data to date data (date time data type)?
      By using CAST and CONVERT we can convert timestamp data type date data type
 Cast timestamp as date

61. What is bit data type and what is the information that can be stred inside a bit column?(SQL DBA).

62. Difference between char and nvarchar / char and varchar data-type?
   Char(n):It is a fixed length data type , it stores string values in non-Unicode manner and it occupy space 1byte for each character.
    Varchar(n): It is variable length data type,it is used store non-Unicode characters and it occupies 1 byte of space for each character.
    Nvarchar(n): It is a variable length data type , it is used to store Unicode characters and it occupies 2 bytes of space for each character.

63. What is the difference between text and image data type?
     Text:It is same as varchar data type , it is a variable length data type,and it storesthe string value in non-Unicode manner ,it takes 1 char per 1 byte.
     Image:It is same as varbinary , it is a variable length data type , the maximum length of  binary data type is 1 to 8000bytes.

64. What do you mean by XML Data type?
 XML data type is used to store XML documents in the SQL Server  database. Columns and variables  are created and store XML instances in the database.

65. What is Timestamp data type?
      System date datatype is nothing but time-stamp data type

66. How many system data types are in SQL Server?
   27

67. What are the different database objects?
      These are total seven database objects(6 permanent databases object +1 temporary database object)
permanent DB objects
table,Views,Stored procedures,user-Defined Functions,Triggers,Indexes
Temporary DB object
 Cursors

68. Explain CREATE DATABASE syntax?
Create database database_name
Create database creates a database with the given name.

69. What is TABLE SAMPLE?(SQL DBA)

70. What are three SQL Keywords used do change or set someone's permissions?
   GRANT,REVOKE AND DENY.

71. What is Select Statement?
     The SELECT statement lets you select a set of values from a table in database.The values selected from from the database table would depend on the various conditions that are specified in the SQL query.

72. Write down the general syntax for a SELECT Statement covering all the optios?
Select select_list
[INTO new_table]
FROM source_table
WHERE [search_condition]
[GROUP BY group_by_exp]
[HAVING [search_condition]
[ORDER BY order_exp[ASC|DESC]]
     
73. Difference between Set and Select?
Set is a ANSI standard for variable assignment.
Select is a Non-ANSI standard when assigning variables.
SET-We can assign only one variable at a time.
SELECT-We can assign multiple values at a time.
SET-Designed to assign values to local variables.
SELECT-Designed to return data.

74. What is the difference between (Select * from table) and ( Select c1, c2 from table name)?
     Select * from table: Used to retrieve the data from a table.
      Select c1,c2 from table name: Used to retrieve the specified columns from a table.

75. What is the Insert Statement?
The  INSERT  statement lets you insert information into a database

76. What is an IDENTITY Column in insert statements?
 IDENTITY column is used in table columns to make that column as Auto incremental number or a surrogate key.

77. How to retrieve last inserted identity value?
 Use @@IDENTITY function to get the last inserted identity value.

78. How data can be copied from one table to another table?
 INSERT INTO SELECT statement selects data from one table and inserts it into an existing table.Any existing rows in the target table are unaffected.
  Ex; insert into table2 select * from table1.
  The SELECT INTO statement copies data from one table and inserts it into a new table.
Ex; select * into newtable from table1.

79. What is the basic form of a SQL Statement to read data out of a table?
  The basic form to read data out of table is "SELECT * FROM TABLE".

80. What is Identity?
Identity is a column that automatically generates numeric values. A start and increment value can be set,but most DBA leave these at 1.

81. What is the use of SCOPE_IDENTITY() function?
It returns the most recently created identity value for the tables in the current execution scope.

82. What is the Scope_Static command?(SQL DBA)

83. How do I reset the identity column?
 By using DBCC CHECKIDENT we can reset the identity column.
DBCC CHECKIDENT(table,RESEED,idvalue).

84. What are the different ways of moving data/ databases between servers and databases in SQL Server?
 There are lots of options available;you have choose your option depending upon your requirements.
Some of the options are:BACKUP/RESTORE,DTS,BCP,INSERT INTO,SELECT INTO.

85. How do you transfer data from textfile to database (other than DTS)?
    Using BCP (Bulk Copy Program) utility.

86. What structure can you implement for the database to speed up table reads?
 Creating different types of indexes.
Properly locate different DB objects across different table spaces,files,....

87. What is the use of FOR Clause?
 FOR clause is mainly used for XML and browser options.This clause is mainly used to display the query results in XML format or in browser.

88. What is DDL?
 Data definition language is used to, CREATE table,DROP table,ALTER table and TRUNCATE table.

89. What is DML?
 Data Manipulation language , which is used to INSERT,UPDATE and DELETE data in the objects defined using DDL.

90. How do you delete a record from a database?
 By using DELETE statement to remove records or any particular column values from a database.

91. What command do we use to rename a db?
 SP_RENAMEDB 'oldname','newname'.
If someone is  using DB it will not accept sp_renamedb.In that case first bring db to single user using sp_dboptions.
Use sp_renamedb to rename db,use sp_dboptions to bring db to multi user mode.

92. How will I export database?
  Through DTS-Import/Export wizard
  Backup-through complete/differential/transaction log.

93. How to export database at a particular time, every week?
 Backup-Schedule
 DTS-Schedule
Jobs-Create a new job.

94. How to export database at a particular time, every week?

95. Explain precision, Scale and Length?
 Precision:It is the no. of  digits in a number.
Scale:It is the no. of digits to the right of the decimal point in a number
The default precision is 18 and maximum is 38.
ex:3457.7854 precision is 8 and scale is 4.
Length for a numeric data type is the number of bytes that are used to store the number.

96. What is the difference between DELETE TABLE and TRUNCATE TABLE commands?
 TRUNCATE:
It is an DDL command,It clears the data from table and it won't information.
It resets identity field.
We can't apply where clause.
Can't truncate primary key table.
DELETE:
It is DML command, It clears data from table and it can log the information .
It does not reset the identity field.
We can apply where clause.
we can delete primary key table . 97. Which command is used for user defined error messages?
RAISEERROR is the command used to generate and initiates error processing for a given session.
98. How to get the results of a Query sorted in any order?
You can sort the results by using ORDER BY keyword.
99. How could I get distinct entries from a table?
In a table , a columnmay contain many duplicates values and some times you want to list the distinct (different) values.
The DISTINCT keyword can be used to return distinct values. 100. What is a "functional dependency"? How does it relate to database table design?

101. Why can a "group by" or "order by" clause be expensive to process?
 Processing of Group By OR Order By clause often requires creation of temporary tables to process the results of the query.Which depending of the result set can be very expensive. 102. How do you delete a record from a database?
Use DELETE statement to remove records or any particular column values from a database.
107. How can i find the total number of records in a table?
You can use COUNT keyword.
EX: SELECT COUNT(*) FROM TABLE
108. What is GROUP BY?
The GROUP BY clause will gather all of the rows together that contain data in the specified column(s) and will allow aggregate functions to be performedon the one or more columns 109. What is the difference among " dropping a table", " truncating a table" and " deleting all records" from a table?
Dropping:(Table structure +Data are deleted),Invalidates the dependent objects,Drops the indexes.
Truncating:If you truncate table total table records deleted and can't be rollback and it reset identity column, it is faster than delete.
Delete:Data alone deleted, doesn't perform automatic commit and data can be rollback. 110. What does TOP Operator Do?
The TOP operator is used to specify the number of rows to be returned by a query. 111. How can we get count of the number of records in a table?
Following are the queries can be used to get the count of records in a table
--SELECT * FROM TABLE
--SELECT COUNT(*) FROM TABLE
 --SELECT ROWS FROM SYSINDEXES
WHERE ID=OBJECT_ID(TABLE) AND INDID<2 112. How to delete duplicate rows in SQL Server?
Duplicate rows can be deleted using CTE and ROW NUMBER 113. Why are my insert, update statements failing with the following error?

114. How to upload images or binary files into SQL Server tables? (top)

115. Oracle has a rownum to access rows of a table using row number or row id. Is there any equivalent for that in SQL Server? Or how to generate output with row number in SQL Server?

116. If you need to return the total table's row count, you can use alternative way instead of SELECT COUNT(*) statement

117. Try to restrict the queries result set by using the Where clause.

118. Use the select statements with TOP keyword or the SET ROWCOUNT statement, if you need to return only the first n rows.

119. Try to restrict the queries result set by returning only the particular coumns from the table, not all table's columns.

120.  How to list all the tables in a particular database?
USE DATABASE_NAME
GO
sp_help 121. Give a scenario where two operations, Delete Statement and Truncate Statement, Where the Delete Statement was successful and the truncate statement was failed. - Can you judge why?

122. What is use of shrink database?
MS SQL Server 2000 allows each file within a database to be shrunk to remove unused pages.Both data and transaction log files can be shrunk.  
123. What is a pass-through query?

124. How can i randomly sort query results?
To randomly order rows,or to return x number of randomly chosen rows, you can use the RAND function inside the SELECT statement.But the RAND function is resolved only once for the entire query 125. How to find dependents of a table?
Verify dependencies with sp_depends before dropping an object.
126. How can i create a plain-text flat file from SQL Server as input to another application?

127. Different types of Backups?
--A full database backup is a full copy of the database.
--A transaction log backup copies only the transaction log. 
--A differential backup  copies only the database pages modified after the last full database backup.
--A file or file group restore allows the recovery of just the portion of a database that was on the  failed disk.   
128. How to get @@ error and @@rowcount at the same time?
If @@Rowcount is checked after Error checking statement then it will have 0 as the value of @@Recordcount as it would have been reset and if @@Recordcount is checked before the error checking statement then @@error would get reset.To get @@error and @@rowcount at same time do both in same statement and store them in local variable.
SELECT @RC=@@ROWCOUNT,@ER=@@ERROR
 129. What is @@ERROR?
The @@ERROR automatic variable returns the error code of the last Transact-SQL statement.If there was no error,@@ERROR returns zero.
130. What are synonyms?

131. How can i send a message to user from the SQL Server?

132. If a database is normalized by 3 NF then how many number of tables it should contain in minimum? How many minimum if 2 NF and 1 NF?

133. Difference between sysusers and syslogins?

134. What is the purpose of using COLLATE in a query?

135. After removing a table from database, what other related objects have to be dropped explicitly?
VIEW,STORED Procs.
136. If a user does not have permission on a table, but he has permission to a view created on it, will he be able to view the data in table?
Yes.  
137. What a query to convert all the letters in a word to upper case?

138. Write a query to roun up the values of a number. For example even if the user enters 7.1 it should be rounded up to 8.?

139. I entered login ID and Password click for login but it is taking so much of time to login how will you tune for performance?

140. Write the query to retrieve the name column reverse alphanbetic?

141. Execute each and every query in ssms and write different types and compare time to execute ?

142. Describe few system tables in SQL Server?

143. How many columns for a table?
1024  
144. Can i have DML, DDL, Commands inside the procedure?
Yes
145. Can you explain Fourth Normal Form?

146. Can you explain Firth Normal Form?

147. What is the difference between Fourth and Fifth Normal Form?

148. What is Extent and Page?
  

SQL SERVER (DBMS & RDBMS) REAL TIME INTERVIEW QUESTIONS AND ANSWERS

1. What is Data Warehousing?
Ans :  It usually contains historical data derived from transaction data, but it can include data from                other sources.

2. What is OLTP (online Transaction Processing)?
Ans : OLTP - Online Transaction Processing
          On regular basis if you are processing transaction information is nothing but OLTP. It contains            live data(Current Data).

3. What is Data?
Ans : Data is nothing but a valid fact which has some feature reference value.

4. What is Information?
Ans : Processed Data is know as Information
          Information always gives meaning for us.

5. What is Database?
Ans : Collection of information belongs to a particular topic (an organization) written in a                             predetermined manner stored at a particular place so, as per easy retrieval.

6. What is DBMS(Data Base Management System)?
Ans : Relationship,s can't be maintained between the tables. Only one user work on DBMS at the                 same time.
          EX : Excel DB

7. What is RDBMS?
Ans : Relationship's can be maintained between the tables. Multiple users work on RDBMS at the                 same time.
          EX : SQL, ORACLE, TERADATA ... etc.

8. How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
Ans : one-to-one relationship can be implemented as a single table and rarely as two tables with                   primary and foreign key relationships.

          one-to-many relationship can be implemented by splitting the data into two tables with                         primary key and foreign key relationships.

           many-to-many relationship can be implemented using a junction table with the keys from both            the tables forming the composite primary key of the junction table.

9. What is difference between DBMS and RDBMS?
Ans : 
DBMS :

1. DBMS applications store data as file.
2. In DBMS, data is generally stored in either a hierarchical form or a navigational form.
3. Normalization is not present in DBMS.
4. DBMS does not apply any security with regards to data manipulation.
5. DBMS uses file system to store data, so there will be no relation between the tables.
6. DBMS has to provide some uniform methods to access the stored information.
7. DBMS is meant to be fro small organization and deal with small data. it supports single user.
8. DBMS doesn't support distributed database.
9. Examples of DBMS are file systems, xml etc.

RDBMS

1. RDBMS applications store data in a tabular form.
2. In RDBMS, the tables have an identifier called primary key and the data values are stored in the form of tables.
3. Normalization is present in RDBMS.
4. RDBMS defines the integrity constraint for the purpose of ACID (Automocity, Consistency, Isolation and Durability) property.
5. in RDBMS, data balues are stored in the form of tables, so a relationship between these data values will be stored in the form of a table as well.
6. RDBMS system supports a tabular structure of the data and a relationship between them to access the stored information.
7. RDBMS is designed to handle large amount of data. it supports multiple users.
8. RDBMS supports distributed database.
9. Example of RDBMS are mysql, postgre, sql server, oracle etc.

10.What is an ER Diagram?
Ans : 1.A Blueprint or pictorial analysis of a database.
          2.A diagram that analyzes how a database can look on the physical level.
             Components of an ER Diagram would be :
             * Entity
             * Attribute
             * Relationship
          3. ER diagram is platform independent.
          4. English form of an ER diagram is called 'Relational Schema'.
              Eg : Emp Details ( e#, Project#, Role , Number-Of-Shares, Shareworth)

11.What is De-normalization?
Ans : Frequently if you are inserting data into database and very rare cases , if you are retrieving the data then using De-normalization concepts, we merge the data multiple tables into one table.

12.What is "normalization"?"Denormalization"? Why do you sometimes want to denormalize?
Ans : To eliminate data redundancy and improve the performance of DB, we go with normalization whenever retrieving the data more frequently from .

13.How will you show many to many relation in SQL?
Ans : create 3rd table with 2 columns which having one to many relation to these tables.

14.What are the benefits of Normalization?
Ans :  Some of the benefits of normalization include :

• Faster sorting and index creation.
• Narrower and more compact indexes.
• Fewer indexes per table, which improves the performance of insert, update and delete statements.
• Fewer null values and less opportunity for inconsistency, which increase database compactness.

15.What are the disadvantages of Normalization?
Ans : As normalization increases, so do the number and complexity of joins required to retrieve data. Too many complex relational joins between too many tables can hinder performance.

16.What Tool you have used for checking Query Optimization? What is the use of profiler in SQL Server? What is the first thing u look at in a SQL Profiler?
Ans : SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each evebt ti a fuke ir SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures is hampering performance by executing too slowly.

Use SQL Profiler to:
Monitor the performance of an instance of SQL Server.
Debug Transact-SQL statements and stored procedures.
Identify slow-executing queries..
 
            Test SQL statements and stored procedures in the developement phase of a project by single stepping through statements to confirm that the code works as expected.
            Troubleshoot problems in SQL Server by capturing events on a production system and replaying them on a test system. This is useful for testing or debugging purposes and allows users to continue using the production system without interference.
           Audit and review activity that occurred on an instance of SQL Server. This allows a security administrator to review any of the auditing events, including the success and failure of a login attempt and the success and failure of permissions in accessing statements and objects.