Term
|
Definition
| Relational Data Base Management Systems (RDBMS) |
|
|
Term
|
Definition
Database normalization is a data design and organization process applied to data structures based on
rules that help build relational databases. In relational database design, the process of organizing data
to minimize redundancy. |
|
|
Term
| What is Stored Procedure? |
|
Definition
A stored procedure is a named group of SQL statements that have been previously created and stored
in the server database. |
|
|
Term
|
Definition
A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE)
occurs. |
|
|
Term
|
Definition
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. |
|
|
Term
|
Definition
An index is a physical structure containing pointers to the data. Indices are created in an existing table
to locate rows more quickly and efficiently. |
|
|
Term
| What is the difference between clustered and a non-clustered index? |
|
Definition
Take the example of a phone book. The actual data - that is, the name, address and phone number records - is ordered by the name. If you want to look up Joe Bloggs's phone number, you open the book somewhere near the middle, maybe see the names there start with "M", but "Bloggs" is before "M", so you go a bit earlier in the book. You keep narrowing it down until you find the entry labelled Bloggs, and that's it - all the data for that record is right there. That's a bit like a clustered index.
On the other hand, a book might have a table of contents, sorted alphabetically. If you want to find out about llamas, you search the contents for llamas, which probably then gives you a page number, at which point you go to the page, and there's the data about llamas. The difference here is that you've had to do an extra bit of indirection - following the page number pointer - in order to get to the data. You can probably now see that while you can have as many tables of contents, ordered in any way you like, one set of data can only be physically arranged in one way. This means you can have many non-clustered indexes, but only one clustered index on a table. |
|
|
Term
|
Definition
| Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis |
|
|
Term
| What's the difference between a primary key and a unique key? |
|
Definition
Both primary key and unique enforce uniqueness of the column on which they are defined. But by
default primary key creates a clustered index on the column, where are unique creates a nonclustered
index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key
allows one NULL only. |
|
|
Term
How to implement one-to-one, one-to-many and many-to-many relationships while
designing tables? |
|
Definition
One-to-One relationship can be implemented as a single table and rarely as two tables with primary
and foreign key relationships.
One-to-Many relationships are implemented by splitting the data into two tables with primary key and
foreign key relationships.
Many-to-Many relationships are implemented using a junction table with the keys from both the tables
forming the composite primary key of the junction table. |
|
|
Term
| What is difference between DELETE & TRUNCATE commands? |
|
Definition
Delete command removes the rows from a table based on the condition that we provide with a WHERE
clause. Truncate will actually remove all the rows from a table and there will be no data in the table
after we run the truncate command. |
|
|
Term
| What is the difference between a HAVING CLAUSE and a WHERE CLAUSE? |
|
Definition
| Having specifies a search condition for a group or an aggregate. Where is much more specific and works for select, delete, update etc. Having only works with select. |
|
|
Term
| What is sub-query? Explain properties of sub-query. |
|
Definition
|
|
Term
|
Definition
The @@ERROR automatic variable returns the error code of the last Transact-SQL statement. If there
was no error, @@ERROR returns zero. |
|
|
Term
| What are primary keys and foreign keys? |
|
Definition
Primary keys are the unique identifiers for each row. They must contain unique values and cannot be
null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys
and constraints. A table can have only one Primary key.
Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship
between tables. |
|
|
Term
| What is data integrity? Explain constraints? |
|
Definition
| Data integrity is an important feature in SQL Server. When used properly, it ensures that data is accurate, correct, and valid. It also acts as a trap for otherwise undetectable bugs within applications. |
|
|
Term
| How to get @@error and @@rowcount at the same time? |
|
Definition
If @@Rowcount is checked after Error checking statement then it will have 0 as the value of
@@Recordcount as it would have been reset. |
|
|
Term
|
Definition
Identity (or AutoNumber) is a column that automatically generates numeric values. A start and
increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers,
the value of this cannot be controled. Identity/GUID columns do not need to be indexed. |
|
|
Term
| How do you load large data to the SQL server database? |
|
Definition
BulkCopy is a tool used to copy huge amount of data from tables. BULK INSERT command helps to
Imports a data file into a database table or view in a user-specified format. |
|
|
Term
|
Definition
| Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate. |
|
|
Term
|
Definition
| It returns all of the table on the left for a left join, and then all of the right table that match the conditional clause. Vice versa for right outer join. |
|
|
Term
|
Definition
| It will combine the two tables and create rows for each permutation of the two rows. |
|
|
