TOP 300+ SQL SERVER INTERVIEW QUESTIONS AND ANSWERS PDF | FRESHERS AND EXPERIENCED
Now all SQL Server Interview Questions and answers at one place. Here we come with all Sql server questions asked during SQL Interview. Thi...
So now you don't need to visit here and there for Sql Server Interview Interview/Telephonic Interview Questions.
PART 1 : SQL SERVER INTERVIEW QUESTIONS ANSWERS
1). What is RDBMS?
2). What are the properties of the relational tables?
3). What is Normalization?
4). What is De-Normalization?
5). What is Self Join?
6). What is Cross Join?
7). What are the different types of cursor?
8). What is a default constraint?
9). How to strip all non-alphabetic characters from string in SQL Server?
10). How do I find a value anywhere in a SQL Server Database?
11). Suppose that you have a table with AreaID but for that column you forgot set Identity. later you got it. Now you want to set identity without effecting the current records, and you only enabled Identity with seed 1. Then what would be the next value for AreaID in our below table example?
12). When should you use "with (nolock)"?
13). Is it possible to insert into two tables at the same time?
14). Preferred Method of Storing Passwords In Database?
15). Can a view be updated/inserted/deleted? If Yes – under what conditions?
16). Where the integrity constraints are stored in data dictionary?
17). How many LONG columns are allowed in a table? Is it possible to use LONG columns in WHERE clause or ORDER BY?
18). What is Collation?
19). What is SQL Server Agent?
20). How to convert Rows to Columns in SQL Server?
21). What is the difference between ROW_NUMBER and Ranking function in SQL SERVER?
22). Difference between Where clause and Having clause in SQL Server?
23). What is data dictionary?
24). Explain the use of SSIS in BI?
25). In Linq Query why from clause come first as select statement ?
26). What is collation?
27). How to convert a table data in XML format in sql server?
28). What are the different types of locks?
29). What are Pessimistic and optimistic Locks?
30). What is the difference between an Update and Exclusive lock?
31). What is the difference between table and view in sql server?
32). What is NOLOCK hint?
33). What is NOT NULL Constraint?
34). What is the difference between DATETIME2 and DATETIME?
35). Explain Geography datatype in SQL Server?
36). What is nolock hint in sql server 2008?
37). What are the different types of SQL Commands?
38). What is the difference between a DDL trigger and a DML trigger?
39). What are the types of transaction levels in SQL SERVER?
40). Which TCP/IP port does SQL Server run on? How can it be changed?
41). What is @@ERROR?
42). what is the difference between count(*) and count(1)?
43). What are the difference between clustered and a non-clustered index?
44). What's the maximum size of a row?
45). What is HINT?
46). How do you delete duplicate records?
47). How do you delete all tables?
48). What is Aggregate function?
49). What is the difference between a query and stored procedure?
50). What will be the result of this query. select * from TableName order by 1 .Will this query throw an error?
50.1). How to get specific string/Date from a string in SQL Server using TSQL?
51). What are the different index configurations a table can have?
52). What are the different types of BACKUPs avaialabe in SQL Server 2005?
53). What are the different locks in Sql Server?
54). What is OLTP (Online Transaction Processing)?
55). What's the difference between a primary key and a unique key?
56). What is the difference between DELETE and TRUNCATE statement?
57). What is SQL Profiler?
58). What are Data files?
59). What is difference between DELETE and TRUNCATE commands?
60). When is the use of UPDATE_STATISTICS command?
61). What is the default port of Microsoft SQl Server?
62). What is Data Compression in sql server 2012?
63). What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
64). What is a linked server?
65). What database does SQL Server use for temporary tables?
66). What are different Types of Sub-Queries?
67). What are the authentication modes in SQL Server? How can it be changed?
68). Which command using Query Analyzer will give you the version of SQL server and operating system?
69). What is service Broker?
70). What is the difference between HAVING and WHERE clause?
71). What is a B-Tree?
72). What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
73). What is an active database?
75). Can a stored procedure call itself or recursive stored procedure? How much level SP nesting is possible?
76). What is Log Shipping?
77). Name 3 ways to get an accurate count of the number of records in a table?
78). What does it mean to have QUOTED_IDENTIFIER ON? What are the implications of having it OFF?
79). What is the difference between a Local and a Global temporary table?
80). What is the STUFF function and how does it differ from the REPLACE function?
81). What is the difference between TEMP tables and Variable Tables?
82). What are the different String Functions in SQL Server?
83). What are the different Index configurations a table can have?
84). What is a Filtered Index?
85). What are indexed views?
86). What is the restriction on Indexed view?
87). What are Statistics? How can you find it in SQL Server?
88). How you can remove time part of datetime in SQL Server?
89). NOT IN vs NOT EXISTS?
90). What are the different Mathematical Functions in SQL Server?
91). What is the difference between COUNT and COUNT_BIG?
92). What is WITH CHECK OPTION on view?
93). What is the difference between INSTEAD OF TRIGGER and AFTER Trigger?
94). How is the ACID property related to the database?
95). What are the different normalization forms?
96). What are different part of a SQL Page?
97). What is the difference between a local and a global variable?
98). What is PRIMARY KEY?
99). What is UNIQUE KEY constraint?
100). What is FOREIGN KEY?
101). What is CHECK Constraint?
102). What is the real time example of RIGHT Outer Join?
103). What are the types of database recovery models?
104). What is NOT NULL Constraint?
105). What are the different data types in SQL Server?
106). What is blocking?
107). What are the different Date functions in SQL Server?
108). How to get @@ERROR and @@ROWCOUNT at the same time?
109). What is a Scheduled Jobs or what is a Scheduled Tasks?
110). What are the advantages of using Stored Procedures?
111). What is a table called, if it has neither Cluster nor Non-cluster Index? What is it used for?
112). Can SQL Servers linked to other servers like Oracle?
113). What is BCP? When does it used?
114). How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
115). What is an execution plan? When would you use it? How would you view the execution plan?
116). What is the difference between CHAR and VARCHAR?
117). What is the difference between VARCHAR and VARCHAR (max)?
119). What are different types of Collation Sensitivity?
120). What is a Stored Procedure?
121). What are the different types of collation sensitivity?
122). What is dirty read?
123). How do you check collation and compatibility level for a database?
124). What is a covered Index?
125). What is the maximum row size for a table?
126). When I delete data from a table, Does SQL Server reduce the size of table?
127). How do you rebuild master database?
128). What is PIVOT and UN-PIVOT?
129). What is EXCEPT operation?
130). What are GROUPING Sets?
131). What are the row constructors inside SQL Server?
132). What are table valued parameters?
133). How do you identify and resolve deadlock?
134). What are Spare columns?
135). How to get last inserted id?
136). Which is database shrinking?
137). What is the maximum number of columns a table can have?
138). What are included columns?
139). What is INTERSECT operation?
140). What is RAID?
141). What are the limitations of Select Into clause?
142). What is FileStream in SQL Server?
143). What do you mean by TableSample?
144). What are the disadvantages of using Stored Procedure?
145). What is the difference between COMMIT and ROLLBACK?
146). What is Transaction?
147). How do you do Error Handling in SQL server?
148). What is RAISEERROR?
149). What is SQL INJECTION?
150). How do you find list of schema names and table names from the database?
151). Why can be there only one clustered index?
152). Can we create view on TEMP table?
153). Can we use constraints on TEMP Table?
154). How to recompile a stored procedure at run time?
155). Does the order of columns in UPDATE statement matters?
156). What are the different types of System database inside SQL Server?
157). What is the use of Transaction Log file?
158). What is the difference between view and Materialized view?
159). What is database mirroring?
160). How does SQL Server database engine work?
161). What is the maximum number of indices per table?
162). What are the purpose of Normalisation?
163). What does the Merge Statement do?
164). What are the new data types introduced in SQL Server 2012?
165). Define Hierarchyid data type?
166). What are synonyms?
167). What is CTE?
168). What are the advantages of CTE?
169). Can we write Sub Queries into Simple Select Using join and CTE?
170). Can CTE be recursive? Till what level it can be nested?
171). What is LINQ?
172). What is XML?
173). How can you find tables without Indexes?
174). How do you find the size of index?
175). How do you copy data from one table to another table?
176). What is ROW_NUMBER()?
177). What is ROLLUP clause?
178). What are ranking functions?
179). How do you stop a log file from growing too big?
180). How do we use DBCC commands?
181). What is CDC?
182). What are the main performance differences between varchar and nvarchar SQL Server data types?
183). What is the difference between varchar and nvarchar?
184). How do I insert multiple rows WITHOUT repeating the “INSERT INTO dbo.Blah” part of the statement?
185). What is the difference between Index Seek and Index Scan?
186). Can we insert data if the clustered index is disabled?
187). What is standby server?
188). How do you disable Index?
189). How do you enable index?
190). What are the steps to create a Table Partition?
191). What are the basics of Table Partitioning in SQL Server?
192). How do you copy tables, schemas, and views from one sql server to another SQL Server?
193). Where are SQL Server user names and passwords stored in?
194). Can we have Triggers on Temp table and Variable Table?
195). What is the syntax for encrypting a column in SQL Server?
196). How do you send email on SQL Server?
197). What is query optimization ?
198). How many maximum Identity columns we can have in a single table?
199). How to find all Triggers in database?
200). Can we add constraint on Variable Table?
201). What is Schema?
202). Can we create multiple constraints on a single column?
203). Can we rename table and column using ALTER command?
204). How to rename Table and Column?
205). How to rename Database?
206). What is the disadvantage of Index?
207). How can we find the table size?
208). How to find N highest salary?
209). What is the difference between data mirroring and log shipping?
210). What are the different backup options within SQL Server?
211). How to add DEFAULT constraint on existing column? Write query
212). How to add NOT NULL constraint on existing column? Write query
213). How do you find why query is running slow?
214). How to create foreign key constraints on temporary table?
215). What is Dynamic SQL?
216). Can we create a table name as Table?
217). What is the difference between CAST and CONVERT Function?
218). What Are Binary String Data Types?
219). How to execute Dynamic SQL?
220). Can we use Variable table in Dynamic SQL?
221). How to execute queries stored in a table?
222). How can you capture the length of column when it is text, image and ntext data type?
223). Is it possible to import data using TSQL?
224). How can you prevent TSQL code from running on a Production server?
225). Define Unique Key?
226). What is the use of SP_Helptext , SP_HelpIndex stored procedure?
227). Can we change order of triggers?
228). What do you understand by Joins in SQL Server?
229). How to disable Auto Commit in SQL Server?
230). Can we recover deleted data?
231). How to delete Top 100 records from a table?
232). How to delete two tables using one Drop command?
233). Can I create CTE in Trigger?
234). Can we create Variable table in Trigger?
235). Can we use cursors in Trigger?
236). Can we call Stored Procedure in Trigger?
236). Can we call Stored Procedure in Trigger?
236.1). Can we call Stored Procedure in a View?
237). Can I create Triggers on TEMP table?
238). Can we use PRINT Command in Triggers?
239). How Triggers are fired?
240). Why do we use DECLARE for cursor and Variable table?
241). How to take database online –offline?
242). How to copy data using Bulk copy when columns data type doesn’t match?
243). What is SP_Configure commands and SET commands?
244). Can Inserted table have multiple records?
245). Can we perform DML & DDL operation on Inserted and Deleted tables?
246). What is the advantage of Index?
247). Which is fast UNION or UNION ALL?
248). Can we create clustered index on view?
249). Can we create computed columns?
250). Can we change the Column Sequence order inside table?
251). Truncate is DDL or DML?
252). Can we create view from view?
253). What the difference between UNION and UNIONALL?
254). How to join two tables from different database?
255). Can we use ORDER BY Clause in UNION?
256). What is difference between Deterministic and Non Deterministic Functions?
257). What is Synchronous and asynchronous function?
258). Can we add Identity column after creating the table?
259). Can we drop Identity column?
260). Can we store Image, MP3 and binary data in SQL Server?
261). How can we disable Identity column?
262). Can Foreign key column have NULL?
263). How to find column description of a table?
264). How to delete Duplicate records?
265). How to find employees hired in last month?
266). How to find all rows that contains only numeric data?
267). How to find primary key name if not given by the user for a particular column?
268). Can we add two columns using ALTER command?
269). How to get row number without ROW_NUMBER function?
270). What is Partitioned View?
271). What is the difference between UNIQUE Key and Primary Key?
272). How to find who deleted/ dropped from Transaction log?
273). Can we ALTER two columns using ALTER command?
274). How to clean Buffer in SQL Server?
275). How to clear Execution Plan cache?
276). How can we check for existence of any object in database?
277). What is meant by differed name resolution in SQL Server?
278). How to find Organization Employee Hierarchy using SQL?
279). How does a recursive CTE works?
280). What is Auditing inside SQL Server?
281). What is the difference between GETDATE() and SYSDATETIME()?
282). How do you check if Automatic Statistic Update is enabled for a database?
283). What are the limitations of view?
284). How to find department with highest number of employees?
285). What are different operation available on ONDELETE and ONUPDATE?
286). What are the uses of System tables?
287). What are WAIT Types?
288). What is Data Page?
289). What is FILL Factor?
290). Sql server difference between view and stored procedure?
291). Sql server difference between unique and nonunique index?
292). Sql server difference between update lock and exclusive lock?
293). Sql server difference between windows authentication and sql server authentication?
294). Sql server difference between sysobjects and sys.objects?
295). Sql server difference between session and connection?
296). Sql server difference between set and select variable?
297). Sql server difference between shrink database and shrink files?
298). Sql server difference between revoke and deny?
299). Sql server difference between rank and dense_rank?
300). Sql server difference between gdr and qfe?
301). Sql server difference between db_owner and dbo?
302). Sql server difference between log shipping and mirroring?
303). Sql server difference between isnull and coalesce?
304). Differences between ISNULL and IS NULL?
305). Difference between hadoop and sql server?
306). Difference between PatIndex and CharIndex function in SQL SERVER?
307). What is the difference between SQL Server standard and web edition?
308). Difference between numeric,float and decimal in sql server?
309). Difference between binary and varbinary datatype in Sql server?
310). Sql server difference between count(*) and count(1)
311). Sql server difference between exec and sp_executesql?
312). Difference between blocking and deadlock sql server?
313). Difference between detach and take offline in sql server?
314). Difference between Index Scan / Index Seek in sql server?
315). What is the difference between TRY_CONVERT and Convert?
316). Write down the query to get the list of tables changed with in the database in last 5 days?
317). Write a query to insert your name 1000 times without Using While Loop in SQL Server?
318). SQL Server 2016 came with new way to drop object if exist, Explain it.(DROP IF EXIST)
320). Write down the T-SQL Script to print then number from 1 to 10?
321). What is the difference between Wide and Nonwide tables in SQL Server?
323). What are NDF files in SQL Server?
324). What is the difference between DateTime and DateTimeOffset data types in SQL Server?
325). What is Slot Array in Sql Server? How it is related to Database Page?
326). What is the extension for trace file in SQL Server Profiler?
327). How to pass an array(or a table variable) into a SQL Server stored procedure?
328). What is forwarding pointer in SQL Server? How it is helpful in performance optimization?
329). How you will trace all event only from a selected Database or Procedure using SQL Server Profiler?
330). What is the difference between SQL, PL-SQL and T-SQL?
331). Name any three standard trace templates provided by SQL Server Profiler?
332). Can you edit Sql Profiler existing/default templates?
333). How to strip all non-alphabetic characters from string in SQL Server?
334). Database dot dot TableName(Database..TableName) will point to which schema in SQL Server, Is this way to select data will fail or not?
335). Write down the query to get list of all supported language by SQL Server?
336). Name the SQL Server functions used to encrypt and decrypt password?
337). What would be the output of the following script?
339). How to split a comma-separated value to columns?
340). What would be the output of following SQL Script.
341). What is the difference between Master, MSDB,TempDB and Model Databases?
342). What is the main rule of first normal form? second form ? third form?
343). What is the difference between Data files and Log Files in Sql Server?
344). How you will select all records using TOP keyword in SQL Statement, even you don't know the count of table?
345). How you will change the font size/style in SQL Server?
346). What will be the output of the following query?
What would be the output of following query?
UPDATE Testupdate SET ID = ID + (SELECT MAX(ID) FROM Testupdate)
SELECT * FROM Testupdate
348). Suppose you have above table"TestUpdate". then what would be the output of following query?
349). Will Non-Clustered Index used every time by SQL Server Engine? Asked in HCL/Unitedhealth Group
350). What do you understand by Column Density in SQL Server?
351). How column Density is effect to Index?
352). What Execute in SQL Server First?
353). What do you understand by "sql_variant" datatype in SQL Server?
354). What do you understand by "Sp_MSforeachtable", Please explain?
355). What will execute first tell the order of every keyword used in below query?(Wipro/Sapient)
Write query to show marks as below
357). What do you understand by sp_MSForEachTable?
358). How you will replace "A" with "B" and "B" with "A" in following string "ABAB"?(HCL)
359). How you will print table using SQL query?(HCL)
360). What would be the output of following script. (asked in TCS with more nested question)
361). How to Generate Sequence without using Ranking functions in SQL Server?
362). Repeat Rows N Times According to Column Value in SQL Server?
363). Which built-in function returns the number of active transactions?
What Do you think this is ending of SQL Server Interview Questions?
PART 2: SQL SERVER INTERVIEW QUERIES QUESTIONS ANSWERS
CLICK HERE FOR SQL SERVER INTERVIEW QUERIES (MORE THAN 100 INTERVIEW QUERIES)