Source: Books Online from Microsoft
1. What is SQL Server?
SQL Server is a vast, easy, powerful Relational Database Management (also Datawarehouse Management) application from Microsoft. It offers Database Development, Database Management and Business Intelligence capabilities. This wonderful technology is very easy to master and manage. This offers following advantages:
- Easy To Use
- Support for Small, Medium and Large Database Storage
- Cheaper, compared to other RDBMS
- Datawarehouse Support
- Enhanced Security Features
- Enterprise Feature Support
- In-Memory Processing
- Business Intelligence Support
- Highly Scalable and Powerful
- Low Cost of Ownership
It is positioned Number 2 in the RDBMS market for FY 2013 as per Microsoft Latest Press News. Next version is SQL Server 2014 (currently in CTP - Community Technology Preview)
2. What are the System Requirements in SQL server?
SQL Server from Microsoft is mainly intended to be used on Windows Systems. Prior versions of SQL Server are compatible on Linux (Redhat Linux) as well. SQL Server comes with different Editions (Flavors) in each Version (Product Release). SQL Server is available in 32 bit and 64 bit compilers. Generic (Minimal)Requirements include:
3. What are Editions of SQL Server 2012
OS System requirements depend on the Version and Edition you are using. Enterprise and Datacenter Editions require Windows Server 2003 SP2 or Windows Server 2008 for complete features support.
|Core Server Editions of SQL Server 2012||Specialized Editions of SQL Server 2012|
4. Difference between DELETE & TRUNCATE statement?
DELETE is used for conditional removal of data records from Tables (Also applicable for Views). These operations are logged.
TRUNCATE is used for unconditional removal of data records from Tables. Truncate Operations are not logged.
5. What are System Stored Procedures?
6. What are extended stored procedures?
Extended stored procedures are used to execute external / non SQL Server routines. They are actually DDLs that are executed dynamically on demand.The extended stored procedures are similar to regular stored procedures where Parameters can be passed to extended stored procedures, and can return results and return status. They have the naming prefix: XP_ and executed like any other generic Stored Procedure using EXEC / EXECUTE commands.
7. How to insert or load the result of a Query or a Procedure into an existing Table?
INSERT INTO TABLE_NAME(
INSERT INTO TABLE_NAME( ) EXECUTE PROCEDURE_NAME
8. What is Left Anti Semi Join Operator?
The Left Anti Semi Join operator returns the result set for each row from the first (top) input when there is no matching row in the second (bottom) input. If no join predicate exists in the Argument column, each row is a matching row. Left Anti Semi Join is a logical operator.
9. What are the precautions for Cursor Usage?
As per the Memory Usage of Cursor Variable, Dynamic Cursors requires less RAM and presents update, dynamic data compared to Static Cursors that requires More RAM and offer better performance.
10. Why you should not use a cursor? What are its alternatives?
Microsoft requests us to avoid using cursors due to Memory and RAM overhead including comparatively more DISK IO.
Alternatives: WHILE LOOP / CASE / Temprary Tables / Derived Tables / Sub Queries can be used in place of Cursors
11. What are Dynamic Queries in T-SQL?
Dynamic T-SQL Queries are those queries designed on the fly / at run time using Variables or using CTE or other sources. We use SP_EXECUTESQL Stored Procedure or EXECUTE function to execute such queries.
12. When to use COALESCE() & ISNULL() Functions?
The NULLability of result expression is different for ISNULL and COALESCE. ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one) whereas COALESCE is not. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1) although equivalent have different NULLability values. This makes a difference if you are using these expressions in computed columns and creating key constraints or making return value of a scalar UDF deterministic so that it can be indexed.
13. What are limitations of IDENTITY Column?
IDENTITY column can be used as a surrogate key. This column values cannot be updated once generated (automatically or manually). As we have possibility to duplicate these values within a table we may require to define this column explicitly as a PRIMARY KEY. This Identity property is applicable for integer based columns only. /p>
14. What are generic guidelines to define a Primary Key
Primary Key is usually defined for unique identification of rows within a Table. As creation of Clustered Index creates a Index ( Clustered Index by default but can also be defined as Non-Clustered Index) we generally consider the selectivity of column before deciding the Index Column. If possible, better to have this on a Integer Based Column for efficient Indexing and Reference Operations.
15. What is CROSSAPPLY Operator?
We use INNER / OUTER / CROSS JOINS for joining Tables and/or Views. CROSSAPPLY operator in T-SQL is used to join a Table or a View with a Table Value returning Function
16. What are CUBE & ROLLUP in T-SQL?
CUBE & ROLLUP are the grouping sets used in conjunction with GROUP BY clause to generate summarized aggregations. These are mainly used for Report Generation and Data Audits.
17. What are types of XML indexes in SQL Server?
Microsoft SQL Server supports different types of XML indexes. An XML index is different than a relational index. There are basically TWO types of XML Indexes viz., Primary XML Indexes and Secondary XML indexes. The Primary XML index is a clustered index on an internal table known as the node table that users cannot use directly from their T-SQL statements. To enhance search performance, we create secondary XML indexes. These create secondary links (RID) at leaf level for existing clustered index based KEY pages. A primary XML index should be created prior to creating the Secondary XML Indexes.
18. How you can load large data in SQL Server?
IMPORT and EXPORT Wizard, BULK INSERT and BCP are the possible options. We need to set the RECOVERY MODEL to BULKLOGGED before we perform such activities.
19. What are the various Isolation levels in SQL Server?
a) Read Uncommitted Isolation Level
b) Read Committed Isolation Level
c) Repeatable Read Isolation Level
d) Serializable Isolation Level
e) Snapshot Isolation Level
f) Read Committed Snapshot Isolation Level
20. What are advantages of SQL Server latest release?
a) delivers an ROI of up to 189% with a 1 year payback period.
b) Microsoft DBAs run more mission critical databases, when compared to Oracle DBAs
c) SQL Server delivers six nines (99.9999%) uptime availability.
d) SQL Server is the most secure of any of the major database platforms.
e) SQL Server delivers a 460% savings in annual cost of administration per database over Oracle.
f) SQL Server reduces downtime by over 20% by migrating an SAP ERP environment to SQL Server. Source