Sql Server Questions

  • What is RDBMS?
  • What are the Properties of the Relational Tables?
  • What is Normalization?
  • What is De-normalization?
  • How is ACID property related to Database?
  • What are the Different Normalization Forms?
  • What is a Stored Procedure?

    A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.
    e.g. sp_helpdb, sp_renamedb, sp_depends etc.

  • What is a Trigger?
  • What are the Different Types of Triggers?

    There are two types of Triggers.
    1)      DML Trigger
    There are two types of DML Triggers
    1.Instead of Trigger
    Instead of Triggers are fired in place of the triggering action such as an insert, update, or delete.
    2. After Trigger
    After triggers execute following the triggering action, such as an insert, update, or delete.
    2)      DDL Trigger
    This type of trigger is fired against Drop Table, Create Table, Alter Table or Login events. DDL Triggers are always After Triggers.

    • What is a View?                                                                                                                                      A simple view can be thought of as a subset of a table. It can be used for retrieving data as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does the data in the view as views are the way to look at parts of the original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.
  • What is an Index?
  • What is a Linked Server?
  • What is a Cursor?A cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.In order to work with a cursor, we need to perform some steps in the following order:
    • Declare cursor
    • Open cursor
    • Fetch row from the cursor
    • Process fetched row
    • Close cursor
    • Deallocate cursor
  • What is Collation?
  • Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence with options for specifying case sensitivity, accent marks, Kana character types, and character width.
  • What is the Difference between a Function and a Stored Procedure?
  • What is subquery? Explain the Properties of a Subquery?
  • What are Different Types of Join?
  • What are Primary Keys and Foreign Keys?
  • What is User-defined Functions? What are the types of User-defined Functions that can be created?
  • What is an Identity?
  • What is DataWarehousing?
  • What languages BI uses to achieve the goal?
  • What is Standby Servers? Explain Types of Standby Servers.
  • What is Dirty Read?
  • Why can’t I use Outer Join in an Indexed View?
  • What is the Correct Order of the Logical Query Processing Phases?
  • Which TCP/IP port does the SQL Server run on? How can it be Changed?
  • What are the Difference between Clustered and a Non-clustered Index?
  • What are the Different Index Configurations a Table can have?
  • What are Different Types of Collation Sensitivity?
  • What is OLTP (Online Transaction Processing)?
  • What’s the Difference between a Primary Key and a Unique Key?
  • What is Difference between DELETE  and TRUNCATE Commands?
  • What are Different Types of Locks?
  • What are Pessimistic Lock and Optimistic Lock?
  • When is the use of UPDATE_STATISTICS command?
  • What is the Difference between a HAVING clause and a WHERE clause?
  • What is Connection Pooling and why it is Used?
  • What are the Properties and Different Types of Sub-Queries?
  • What are the Authentication Modes in SQL Server? How can it be Changed?
  • Which Command using Query Analyzer will give you the Version of SQL Server and Operating System?
  • What is an SQL Server Agent?
  • Can a Stored Procedure call itself or a Recursive Stored Procedure? How many levels of SP nesting is possible?
  • What is Log Shipping?
  • Name 3 ways to get an Accurate Count of the Number of Records in a Table?
  • What does it mean to have QUOTED_IDENTIFIER ON? What are the Implications of having it OFF?
  • What is the Difference between a Local and a Global Temporary Table?
  • What is the STUFF Function and How Does it Differ from the REPLACE Function?
  • What is PRIMARY KEY?
  • What is UNIQUE KEY Constraint?
  • What is FOREIGN KEY?
  • What is CHECK Constraint?
  • What is NOT NULL Constraint?
  • What is the difference between UNION and UNION ALL?
  • What is B-Tree?
  • How to get @@ERROR and @@ROWCOUNT at the Same Time?
  • What is a Scheduled Job or What is a Scheduled Task?
  • What are the Advantages of Using Stored Procedures?
  • What is a Table Called, if it has neither Cluster nor Non-cluster Index? What is it Used for?
  • Can SQL Servers Linked to other Servers like Oracle?
  • What is BCP? When is it Used?
  • What Command do we Use to Rename a db, a Table and a Column?
  • What are sp_configure Commands and SET Commands?
  • How to Implement One-to-One, One-to-Many and Many-to-Many Relationships while Designing Tables?
  • What is Difference between Commit and Rollback when Used in Transactions?
  • What is an Execution Plan? When would you Use it? How would you View the Execution Plan?
  • What is Difference between Table Aliases and Column Aliases? Do they Affect Performance?
  • What is the difference between CHAR and VARCHAR Datatypes?
  • What is the Difference between VARCHAR and VARCHAR(MAX) Datatypes?
  • What is the Difference between VARCHAR and NVARCHAR datatypes?
  • Which are the Important Points to Note when Multilanguage Data is Stored in a Table?
  • How to Optimize Stored Procedure Optimization?
  • What is SQL Injection? How to Protect Against SQL Injection Attack?
  • How to Find Out the List Schema Name and Table Name for the Database?
  • What is CHECKPOINT Process in the SQL Server?
  • How does Using a Separate Hard Drive for Several Database Objects Improves Performance Right Away?
  • How to Find the List of Fixed Hard Drive and Free Space on Server?
  • Why can there be only one Clustered Index and not more than one?
  • What is Difference between Line Feed (\n) and Carriage Return (\r)?
  • Is It Possible to have Clustered Index on Separate Drive From Original Table Location?
  • What is a Hint?
  • How to Delete Duplicate Rows?
  • Why the Trigger Fires Multiple Times in Single Login?
  • What is Aggregate Functions?
  • What is Use of @@ SPID in SQL Server?
  • What is the Difference between Index Seek vs. Index Scan?
  • What is the Maximum Size per Database for SQL Server Express?
  • How do We Know if Any Query is Retrieving a Large Amount of Data or very little data?
  • What is the Difference between GRANT and WITH GRANT while Giving Permissions to the User?
  • How to Create Primary Key with Specific Name while Creating a Table?
  • What is T-SQL Script to Take Database Offline – Take Database Online
  • How to Enable/Disable Indexes?
  • Can we Insert Data if Clustered Index is Disabled?
  • How to Recompile Stored Procedure at Run Time?
  • Is there any Performance Difference between IF EXISTS (Select null from table) and IF EXISTS (Select 1 from table)?
  • What is Difference in Performance between INSERT TOP (N) INTO Table and Using Top with INSERT?
  • Does the Order of Columns in UPDATE statements Matter?
  • What are the basic functions for master, msdb, model, tempdb and resource databases?
  • What is the Maximum Number of Index per Table?
  • Explain Few of the New Features of SQL Server 2008 Management Studio
  • Explain IntelliSense for Query Editing
  • Explain MultiServer Query
  • Explain Query Editor Regions
  • Explain Object Explorer Enhancements
  • Explain Activity Monitors
  • What is Service Broker?
  • Where are SQL server Usernames and Passwords Stored in the SQL server?
  • What is Policy Management?
  • What is Database Mirroring?
  • What are Sparse Columns?
  • What does TOP Operator Do?
  • What is CTE?
  • What is MERGE Statement?
  • What is Filtered Index?
  • Which are the New Data Types Introduced in SQL SERVER 2008?
  • What are the Advantages of Using CTE?
  • How can we Rewrite Sub-Queries into Simple Select Statements or with Joins?
  • What is CLR?
  • What are Synonyms?
  • What is LINQ?
  • What are Isolation Levels?
  • What is Use of EXCEPT Clause?
  • What is XPath?
  • What is NOLOCK?
  • What is the Difference between Update Lock and Exclusive Lock?
  • How will you Handle Error in SQL SERVER 2008?
  • How to Rebuild the Master Database?
  • What is the XML Datatype?
  • What is Data Compression?
  • What is Use of DBCC Commands?
  • How to Copy the Tables, Schema and Views from one SQL Server to Another?
  • How to Find Tables without Indexes?
  • How to Copy Data from One Table to Another Table?
  • What is Catalog Views?
  • What is PIVOT and UNPIVOT?
  • What is a Filestream?
  • What is SQLCMD?
  • What do you mean by TABLESAMPLE?
  • What is ROW_NUMBER()?
  • What are Ranking Functions?
  • What is Change Data Capture (CDC) in SQL Server 2008?
  • How can I Track the Changes or Identify the Latest Insert-Update-Delete from a Table?
  • What is the CPU Pressure?
  • How can I Get Data from a Database on Another Server?
  • What is the Bookmark Lookup and RID Lookup?
  • What is Difference between ROLLBACK IMMEDIATE and WITH NO_WAIT during ALTER DATABASE?
  • What is Difference between GETDATE and SYSDATETIME in SQL Server 2008?
  • How can I Check that whether Automatic Statistic Update is Enabled or not?
  • How to Find Index Size for Each Index on Table?
  • What is the Difference between Seek Predicate and Predicate?
  • What are Basics of Policy Management?
  • What are the Advantages of Policy Management?
  • What are Policy Management Terms?
  • What is the ‘FILLFACTOR’?
  • Where in MS SQL Server is ’100’ equal to ‘0’?
  • What are Points to Remember while Using the FILLFACTOR Argument?
  • What is a ROLLUP Clause?
  • What are Various Limitations of the Views?
  • What is a Covered index?
  • When I Delete any Data from a Table, does the SQL Server reduce the size of that table?
  • What are Wait Types?
  • How to Stop Log File Growing too Big?
  • If any Stored Procedure is Encrypted, then can we see its definition in Activity Monitor?
  • What is Data Warehousing?
  • What is Business Intelligence (BI)?
  • What is a Dimension Table?
  • What is Dimensional Modeling?
  • What is a Fact Table?
  • What are the Fundamental Stages of Data Warehousing?
  • What are the Different Methods of Loading Dimension tables?
  • Describes the Foreign Key Columns in Fact Table and Dimension Table?
  • What is Data Mining?
  • What is the Difference between a View and a Materialized View?
  • What is OLTP?
  • What is OLAP?
  • What is the Difference between OLTP and OLAP?
  • What is ODS?
  • What is ER Diagram?
  • What is ETL?
  • What is VLDB?
  • Is OLTP Database is Design Optimal for Data Warehouse?
  • If denormalizing improves Data Warehouse Processes, then why is the Fact Table is in the Normal Form?
  • What are Lookup Tables?
  • What are Aggregate Tables?
  • What is Real-Time Data-Warehousing?
  • What are Conformed Dimensions?
  • What is a Conformed Fact?
  • How do you Load the Time Dimension?
  • What is a Level of Granularity of a Fact Table?
  • What are Non-Additive Facts?
  • What is a Factless Facts Table?
  • What are Slowly Changing Dimensions (SCD)?
  • What is Hybrid Slowly Changing Dimension?
  • What is BUS Schema?
  • What is a Star Schema?
  • What Snow Flake Schema?
  • Differences between the Star and Snowflake Schema?
  • What is Difference between ER Modeling and Dimensional Modeling?
  • What is Degenerate Dimension Table?
  • Why is Data Modeling Important?
  • What is a Surrogate Key?
  • What is Junk Dimension?
  • What is a Data Mart?
  • What is the Difference between OLAP and Data Warehouse?
  • What is a Cube and Linked Cube with Reference to Data Warehouse?
  • What is Snapshot with Reference to Data Warehouse?
  • What is Active Data Warehousing?
  • What is the Difference between Data Warehousing and Business Intelligence?
  • What is MDS?
  • Explain the Paradigm of Bill Inmon and Ralph Kimball.

Paras Doshi has submitted 21 interesting question and answers for SQL Azure.

1.What is SQL Azure?
2.What is cloud computing?
3.How is SQL Azure different than SQL server?
4.How many replicas are maintained for each SQL Azure database?
5.How can we migrate from SQL server to SQL Azure?
6.Which tools are available to manage SQL Azure databases and servers?
7.Tell me something about security and SQL Azure.
8.What is SQL Azure Firewall?
9.What is the difference between web edition and business edition?
10.How do we synchronize On Premise SQL server with SQL Azure?
11.How do we Backup SQL Azure Data?
12.What is the current pricing model of SQL Azure?
13.What is the current limitation of the size of SQL Azure DB?
14.How do you handle datasets larger than 50 GB?
15.What happens when the SQL Azure database reaches Max Size?
16.How many databases can we create in a single server?
17.How many servers can we create in a single subscription?
18.How do you improve the performance of a SQL Azure Database?
19.What is code near application topology?
20.What were the latest updates to SQL Azure service?
21.When does a workload on SQL Azure get throttled?


How Many Foreign Key Can You Have on A Single Table?

Answer: SQL Server 2014 and earlier versions, supports 253 as a maximum foreign key table references per table. However, this limitation, changes in SQL Server 2016.

To understand the limitations in SQL Server 2016 we need to understand two important concepts

a) Outgoing Foreign Key References – A column in a table referring other table columns.

b) Incoming Foreign Key References – A column in a table which is referenced by other table columns.

Now we know the answer of two important key words, let us see the answer to our original question.

A table can reference a maximum of 253 other tables and columns as foreign keys (Outgoing Foreign Key References). SQL Server 2016 increases the limit for the number of other table and columns that can reference columns in a single table (Incoming Foreign Key References), from 253 to 10,000. However, self referencing FK (where table column is referencing itself in the same table) still is limited to 253 in all the versions of SQL Server.

Here is limitation from Books On Line-

  • Greater than 253 FK references are supported for DELETE and UPDATE DML operations. MERGE operations are not supported.
  • A table with a FK  reference to itself is still limited to 253 foreign key references.
  • Greater than 253 FK  references are not currently available for column store indexes, memory-optimized tables, or Stretch Database.

Best Practices for Better Database Performance

1. Store relevant and necessary information in the database instead of application structure or array.

2. Use normalized tables in the database. Small multiple tables are usually better than one large table.

3. If you use any enumerated field to create look up for it in the database itself to maintain database integrity.

4. Keep primary key of lesser chars or integer. It is easier to process small width keys.

5. Store image paths or URLs in database instead of images. It has less overhead.

6. Use proper database types for the fields. If StartDate is database filed use date time as datatypes instead of VARCHAR (20).

7. Specify column names instead of using * in SELECT statement.

8. Use LIKE clause properly. If you are looking for exact match use “=” instead.

9. Write SQL keyword in capital letters for readability purpose.

10. Using JOIN is better for performance than using sub queries or nested queries.

11. Use stored procedures. They are faster and help in maintainability as well security of the database.

12. User comments for readability as well as guidelines for the next developer who comes to modify the same code. Proper documentation of application will also aid help too.

13. Proper indexing will improve the speed of operations in the database.

14. Make sure to test it any of the database programming as well administrative changes.