Here I am trying to provide some important SQL Server database engine interview questions and answers.
What is T-SQL?
T-SQL stands for Transact Structured Query Language. It is an extension of SQL functionality supported by Microsoft SQL Server.
How can T-SQL statements be written and submitted to the Database engine?
T-SQL statements can be written and submitted to the Database engine in the following ways:
- By using the SQLcmd Utility
- By using the SQL Server Management Studio
- By connecting from an application that you create
What is the difference between TRUNCATE and DELETE statements?
The difference between TRUNCATE and DELETE statements is as follows:
- TRUNCATE is used for the unconditional removal of data records from Tables. Truncate operations are not logged.
- DELETE is used for conditional removal of data records from Tables. These operations are logged.
- TRUNCATE is faster than DELETE.
- Truncate command resets the identity value.
What does the T-SQL command IDENT_CURRENT do?
The T-SQL command IDENT_CURRENT returns the last identity value produced for a specified table or view. The last identity value created can be for any session and any scope.
What does the T-SQL command IDENT_INCR do?
The T-SQL command IDENT_INCR returns the increment value mentioned during the formation of an identity column in a table or view that has an identity column.
Can we import data directly from T-SQL commands without using SQL Server Integration Services? If yes, what are the commands?
Yes, it is possible to import data directly from T-SQL commands without using SQL Server Integration Services. These commands include:
- BCP
- OpenRowSet
- Bulk Insert
- OPENQUERY
- OPENDATASOURCE
- Linked Servers
What is a sub-query?
A sub-query is a query that can be nested inside the main query, such as Select, Update, Insert, or Delete statements. Properties of sub-queries include:
- A subquery should not have an ORDER BY clause.
- A subquery should be placed in the right-hand side of the comparison operator of the main query.
- A subquery should be enclosed in parentheses because it needs to be executed first before the main query.
- More than one sub-query can be included.
What are dynamic queries in T-SQL?
Dynamic queries in T-SQL are those queries designed on the fly or at runtime using variables or using CTEs (Common Table Expressions) or other sources. We use EXECUTE function or SP_EXECUTESQL stored procedure to execute such queries.
What are ROLLUP and CUBE in T-SQL?
ROLLUP and CUBE are grouping sets used along with the GROUP BY clause to generate summarized aggregations. These are mainly used for Data Audits and Report Generation.
What are the Join Types in T-SQL?
Join Types in T-SQL are as follows:
- Inner join
- Outer join
- Left outer join
- Right outer join
- Left outer join with Exclusions
- Right outer join with Exclusions
- Full outer join
- Full outer joins with Exclusions
- Cross join
What are the String functions available in T-SQL?
String functions available in T-SQL are:
- Left
- Right
- Ltrim
- Rtrim
- Substring
- Replace
- Stuff
What is OFFSET-FETCH filter in T-SQL?
In T-SQL, OFFSET-FETCH filter is designed similar to TOP but with an extra element. It helps to define how many rows you want to skip before specifying how many rows you want to filter.
What is Patindex?
The PATINDEX function returns the starting position of the first occurrence of a pattern in a specified expression or zero if the pattern is not found.
PATINDEX('%pattern%', expression)How To Create a Temporary Table? How Do We Apply Nonclustered Index? What Is Nolock? When And Where Is Nolock Applied Normally?
Two ways of creating a temporary table with a non-clustered index applied to it. Also, an example shows how to apply "nolock." NOLOCK is normally applied while querying on production servers. This would make the records being queried sharable on the table. It allows other queries to query the same record in parallel. However, it might return junk data in certain cases because the select query might query the table while other insertions or updates are being performed on the table.
CREATE TABLE #tmpTable
(
OfficeName varchar(50),
officeid int,
CustID int,
AgentID int,
mlsid varchar(4),
RequestMoreDetails int null,
Emails int null
)
CREATE NONCLUSTERED INDEX #IX_tmpTable ON #tmpTable(AgentID)SELECT
OfficeName,
officeid,
o.CustID,
AgentID,
o.mlsid,
PrintBrochure_Views = null,
RequestMoreDetails = null,
Emails = null
INTO #table1 FROM Offices o
LEFT JOIN dbo.table2 t2 WITH (NOLOCK)
ON t2.officeid = o.RID
CREATE NONCLUSTERED INDEX #IX_DW_Listings ON #table1(AgentID)What Tools Do You Use For Performance Tuning?
Several tools are useful when performance tuning your SQL Server applications, including:
- Query Analyzer
- Profiler
- Index Wizard
- Performance Monitor
What Is SQL Profiler?
SQL Profiler is a tool that allows a system administrator to monitor events in SQL Server. It is mainly used to capture and save data about each event of a file or table for analysis.
What is a Recursive Stored Procedure?
SQL Server supports recursive stored procedures, which call themselves. A recursive stored procedure can be defined as a method of problem-solving wherein the solution is arrived at repetitively. It can nest up to 32 levels.
What are the Differences Between Local and Global Temporary Tables?
Local and global temporary tables differ as follows:
- Local temporary tables are visible when there is a connection, and are deleted when the connection is closed.
CREATE TABLE #- Global temporary tables are visible to all users and are deleted when the connection that created it is closed.
CREATE TABLE ##What is a CHECK Constraint?
A CHECK constraint can be applied to a column in a table to limit the values that can be placed in a column. The CHECK constraint enforces data integrity.
Can SQL Servers Link to Other Servers?
SQL Server can be connected to any database that has an OLE-DB provider. For example, Oracle has an OLE-DB provider that can be used to connect with SQL Server.
No comments:
Post a Comment