Index

SQL SERVER tagged Questions - Page 1

What is SQL?


SQL is an acronym of Structure Query Language.
Structured Query Language (SQL) is the set of statements with which all programs and users access data in an Oracle/MySql/SQL Server database.
SQL is a language which provides us an efficient way to access the data from the database.

What are the features of SQL?


- SQL can be used by a range of users.
- It is a non procedural language.
- It is an English-like language.
- Functionally complete.

What new indexes are introduced in SQL Server 2005 in comparison to 2000?


- Spatial
- XML

What are the tools that could be used for SQL Query performance tuning?


Query Analyzer, Profiler, Index Wizard, Performance Monitor

Difference between Data and Information


Data: Known facts that can be recorded and that have implicit meaning .
Information: Processed Data.

What are CODD rules?


There are 12 rules that every DBMS should adhere in order to be true RDBMS. These rules were laid by E.F.CODD in 1969.
1. Information Rule
2. Guaranteed access rule
3. Systematic treatment of null values
4. Dynamic on-line catalog based on the relational model
5. Comprehensive data sub-language Rule
6. View updating Rule
7. High-level insert, update and delete
8. Physical data independence
9. Logical data independence
10.Integrity independence
11.Distribution independence
12.Non-subversion Rule

What are the functions of DBMS?


- Data Definition
- Data Manipulation
- Data Security and Integrity
- Data Recovery and Concurrency
- Data Dictionary
- Performance

Difference between DBMS and RDBMS


A DBMS has to be persistent, that is it should be accessible when the program created the data ceases to exist or even the application that created the data restarted. A DBMS also has to provide some uniform methods independent of a specific application for accessing the information that is stored. RDBMS is a Relational Data Base Management System Relational DBMS. This adds the additional condition that the system supports a tabular structure for the data, with enforced relationships between the tables. This excludes the databases that don't support a tabular structure or don't enforce relationships between tables. Many DBA's think that RDBMS is a Client Server Database system but thats not the case with RDBMS.

How many types of relationship exists in Database designing?


1. One to One
2. One to Many
3. Many to Many

What is the purpose of using SET ANSI NULLS ON?


The purpose of using SET ANSI_NULLS ON to follow the ISO Standard. According to this = and <> should not be used for null comparison. Instead we use null and is not null how ever if you will use = null it will return zero rows. If we want to use = or <> for null comparison use SET ANSI_NULLS OFF it means do not follow ISO Standard. SET ANSI_NULLS should be set to ON for executing distributed queries for maintaining compatibility of queries across Servers.

What is the use of SET NOCOUNT ON?


When we use SELECT and DML statement in SQL. SQL server return a message which specify the number of rows effected by these statements. This information helps coder when they are debugging the code other wise this is not useful we can disable this by typing SET NOCOUNT ON. It is very helpful when we are doing on store procedure contains lots of statements, loops its also increase in performance and boost network traffic.

What are the differences between SQL and PL/SQL?


We can get modify, Retrieve by single command or statement in SQL but PL/SQL process all SQL statements one at a time. With PL/SQL, an entire block of statement process in a single command line. SQL is structured query language, various queries are used to handle the database in a simplified manner. While PL/SQL is procedural language contains various types of variables, functions and procedures and other major diffrence is Sql as the name suggest it is just structured query language wheareas PLSQL is a combination of Programming language & SQL.

What are the differences between SQL and PL/SQL?


We can get modify, Retrieve by single command or statement in SQL but PL/SQL process all SQL statements one at a time. With PL/SQL, an entire block of statement process in a single command line. SQL is structured query language, various queries are used to handle the database in a simplified manner. While PL/SQL is procedural language contains various types of variables, functions and procedures and other major diffrence is Sql as the name suggest it is just structured query language wheareas PLSQL is a combination of Programming language & SQL.

How to get number of Maximum connection can be establish to SQL?


select @@MAX_Connections

What is Extent and Page in SQL?


Extent is a basic unit of storage to provide space for tables. Every extent has number of data pages. As new records are inserted new data pages are allocated. There are eight data pages in an extent. So as soon as the eight pages are consumed it allocates new extent with data pages.
While extent is basic unit storage from database point of view, page is a unit of allocation within extent.

What are SQL Statements?


Types:
1. Data Retrieval
Example : SELECT
2. Data Manipulation Language (DML)
Example : INSERT, UPDATE, DELETE
3. Data Definition Language (DDL)
Example : CREATE, ALTER, DROP, RENAME, TRUNCATE
4. Transaction Control Language (TCL)
Example : COMMIT, ROLLBACK, SAVEPOINT
5. Data Control Language (DCL)
Example : GRANT, REVOKE

What is a DDL, DML and DCL concept?


DDL (Data definition language) defines your database structure. CREATE and ALTER are DDL statements as they affect the way your database structure is organized.
DML (Data Manipulation Language) lets you do basic functionalities like INSERT, UPDATE, DELETE and MODIFY data in database.
DCL (Data Control Language) controls you DML and DDL statements so that your data is protected and has consistency. COMITT and ROLLBACK are DCL control statements. DCL guarantees ACID fundamentals of a transaction.

Difference between DELETE and TRUNCATE.


- DELETE table can be rolled back while TRUNCATE can not be.
- DELETE TABLE syntax logs the deletes thus making the delete operations low. TRUNCATE table does not log any information but it logs information about deallocation of data page of the table. So TRUNCATE table is faster as compared to delete table.
- DELETE table can have criteria while TRUNCATE can not.
- TRUNCATE table can not have triggers.

Difference between subquery and corelated subquery?


In a subquery first the the inner query will be executed then the outer query will be executed.
In corelated subquery the inner query refers to the outer query.

What are the differences between “UNION” and “UNION ALL”?


UNION SQL syntax is used to select information from two tables. But it selects only distinct records from both the table, while UNION ALL selects all records from both the tables.

Similar Tags