Sql Server Interview Questions and Answers (1758) - Page 2

Coalesce returns the first non-null expression among its arguments.

Lets say we have to return a non-null from more than one column, then we can use COALESCE function.

SELECT COALESCE(hourly_wage, salary, commission) AS 'Total Salary' FROM wages

In this case,

If hourly_wage is not null and other two columns are null then hourly_wage will be returned.
If hourly_wage, commission are null and salary is not null then salary will be returned.
If commission is non-null and other two columns are null then commission will be returned.

Using joins, we can get the data from two or more tables based on logical condition between the tables.

Use following article to know about all types of joins

http://www.techbubbles.com/sql-server/joins-in-sql-server/

NOTE: This is objective type question, Please click question title for correct answer.

Triggers can be classified as Key Triggers, Mouse Triggers ,Navigational Triggers.
Key Triggers :: Key Triggers are fired as a result of Key action.e.g :: Key-next-field, Key-up,Key-Down
Mouse Triggers :: Mouse Triggers are fired as a result of the mouse navigation.e.g. When-mouse-button-presed,when-mouse-doubleclicked,etc
Navigational Triggers :: These Triggers are fired as a result of Navigation. E.g : Post-Text-item,Pre-text-item.
We also have event triggers like when ?Vnew-form-instance and when-new-block-instance.
We cannot call restricted procedures like go_to(??my_block.first_item??) in the Navigational triggers
But can use them in the Key-next-item.
The Difference between Key-next and Post-Text is an very important question. The key-next is fired as a result of the key action while the post text is fired as a result of the mouse movement. Key next will not fire unless there is a key event.
The sequence of firing in a text item are as follows ::
a) pre - text
b) when new item
c) key-next
d) when validate
e) post text

In TRUNCATE we can not rollback..
In DELETE we can rollback

1) Delete keep the lock over each row where Truncate keps the lock on table not on all the row

2) Counter of the Identity column is reset in Truncate where it is not reset in Delete.

3) Trigger is not fired in Truncate where as trigger is fired in Delete.

NOTE: This is objective type question, Please click question title for correct answer.

Relational tables have six properties
1. Values are atomic.
2. Column values are of the same kind.
3. Each row is unique.
4. The sequence of columns is insignificant.
5. The sequence of rows is insignificant.
6. Each column must have a unique name.


Values Are Atomic
Columns in a relational table are not repeating group or arrays. Such tables are referred to as being in the "first normal form" (1NF). The atomic value property of relational tables is important because it is one of the cornerstones of the relational model.
The key benefit of the one value property is that it simplifies data manipulation logic.


Column Values Are of the Same Kind
All values in a column come from the same set of values or domain. For example, a Product_Price column contains only specific to product price. It never contains other information such as comments, status flags, or even weekly salary.
It simplifies data access because developers and users can be certain of the type of data contained in a given column. It also simplifies data validation. Because all values are from the same domain, the domain can be defined and enforced with the Data Definition Language (DDL) of the database software.


Each Row is Unique
Each unique row ensures that no two rows in a relational table are identical; there is at least one column, or set of columns, the values of which uniquely identify each row in the table. Such columns are called primary keys.
This property guarantees that every row in a relational table is meaningful and that a specific row can be identified by specifying the primary key value.


The Sequence of Columns is Insignificant
Ordering of the columns in the relational table has no meaning. Columns can be retrieved in any order and in various sequences. The benefit of this property is that it enables many users to share the same table without concern of how the table is organized. It also permits the physical structure of the database to change without affecting the relational tables.


The Sequence of Rows is Insignificant
This property is analogous the one above but applies to rows instead of columns. The main benefit is that the rows of a relational table can be retrieved in different order and sequences. Adding information to a relational table is simplified and does not affect existing queries.


Each Column Has a Unique Name
Because the sequence of columns is insignificant, columns must be referenced by name and not by position. In general, a column name need not be unique within an entire database but only within the table to which it belongs.

1. Use as much as possible WHERE clause filters. Where Clause is the most important part for optimization
2. Select only those fields which really require.
3. Joins are expensive in terms of time. Make sure that use all the keys that relate the two tables together and don't join to unused tables, always try to join on indexed fields. The join type is important as well (INNER, OUTER).

DESCRIBE is used to see table structure. In SQL server 2005 we can use sp_columns, sp_tables or sp_help.

sp_columns
will show list of columns and its details in table.
sp_tables will show list of tables in the database

1024

When you are creating or changing a stored procedure,please keep in mind that
The name of the procedure is a standard Transact-SQL identifier. The maximum length of any identifier is 128 characters.
Stored procedures may contain up to 1,024 input and output parameters.
The body of the stored procedure consists of one or more
Transact-SQL statements. The maximum size of the body of the stored procedure is 128MB.

SQL Server 2000 has a System Variable @@IDENTITY which gives the last identity element value inserted in any table

The maximum size for SQL Server 2000 databases is 1,048,516 TB. Each database can contain up to 32,767 files, and the maximum size of each file is 32TB

NOTE: This is objective type question, Please click question title for correct answer.

Primary data files --->> .mdf.
Secondary data files --->> .ndf.
log files --->> .ldf

o rename a database first get the database into Single user mode . by using the query below ( run these query in query analyzer)

ALTER DATABASE DBMydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE

then rename the database using sp_rename

sp_renamedb 'DBMydb', 'DBmydb_new'

then change the access mode of the database to multiuser

ALTER DATABASE DBmydb_new SET MULTI_USER

Returns the number of active transactions for the current connection.

PRINT @@TRANCOUNT

--  The BEGIN TRAN statement will increment the transaction count by 1.

BEGIN TRAN

    PRINT @@TRANCOUNT

--  The COMMIT statement will decrement the transaction count by 1.

    COMMIT

    PRINT @@TRANCOUNT

--Results

--0

--1

--0

10 fields in MS Acces
900 Bytes in SQL Server

Its nothing but a set of T-SQL statements combined to perform a single task of several tasks. Its basically like a Macro so when you invoke the Stored procedure, you actually run a set of statements.