 |
What is the difference between char and varchar data types?
|
| |
The char is a fixed-length character data type, the varchar is
a variable-length character data type.
You can use char when the data entries in a column are expected to be the same
size.
You can use varchar when the data entries in a column are expected to vary
considerably in size.
|
|
What is the difference between UNION and UNION ALL Sql syntax?
|
| |
UNION - is used to select information from two tables, it selects only distinct records
UNION ALL - selects all the records from both the tables.
|
|
How can you raise custom errors from stored procedure?
|
| |
The RAISERROR statement is used to produce the error message or retrieve a custom message that is stored in the sysmessages table.
The Syntax is follows
RAISERROR ({msg_id|msg_str}{, severity, state}
[argument [,…n]])
[WITH option [, …n]]
Custom error messages must have identifier numbers greater than or equal to 50000, and the message itself is limited to 255 characters or less. As an example, to create a new error message, assign it an identifier number of 55555, and assign it a severity code of 10, you would call sp_addmessage like this:
sp_addmessage 55555, 10, 'New error message.'
You could then raise that new error in a procedure like this:
RAISERROR 55555, 10
The @@ERROR function only indicates the error number that occurred in the last T-SQL statement that was executed.
If @@ERROR returns zero, then no error occurred.
|
|
What is the difference between clustered and nonclustered indexes?
|
| |
A clustered index is a special type of index that reorders the
way records in the table are physically stored. Therefore table can have only
one clustered index. The leaf nodes of a clustered index contain the data
pages.
A nonclustered index is a special type of index in which the logical order of
the index does not match the physical stored order of the rows on disk. The
leaf nodes of a non clustered index contain index rows. A table can have 249
non clustered indexes
|
|
What is the difference between PRIMARY KEY and UNIQUE constraints?
|
| |
| Primary Key |
Unique Key |
| 1.It Wont allow Null values.
|
1.It allows only one Null value |
| 2.Primary key will create clustered index by default. |
2.Unique key will create non-clustered index by default. |
| 3.Only one Primary Key constraints can be added to a table |
3.Any number of Unique Key constraints can be added to a table.
|
|
|
What is Database Normalization?
|
| |
Its a process of efficiently organizing data in a database
There are two goals of the normalization process:
1.eliminate redundant data (for example, storing the same data in more than one
table) and
2.ensure data dependencies make sense (only storing related data in a table).
Both of these are worthy goals as they reduce the amount of space a database
consumes and ensure that data is logically stored. There are five normalizaion
forms, Which are..
First normal form (1NF)
Eliminate duplicative columns from the same table. Create separate tables for
each group of related data and identify each row with a unique column or set of
columns (the primary key).
Second normal form (2NF)
Remove subsets of data that apply to multiple rows of a table and place them in
separate tables. Create relationships between these new tables and their
predecessors through the use of foreign keys.
Third normal form (3NF)
Remove columns that are not dependent upon the primary key.
fourth normal form (4NF)
A relation is in 4NF if it has no multi-valued dependencies.
Fifth normal form is very rarely seen
|
|
What is transaction?
|
| |
Transactions group a set of tasks into a single execution
unit. Each transaction begins with a specific task and ends when all the tasks
in the group successfully complete. If any of the tasks fails, the transaction
fails. Therefore, a transaction has only two results: success or failure.
Incomplete steps result in the failure of the transaction.
Users can group two or more Transact-SQL statements into a single transaction
using the following statements:
---Begin Transaction
---Rollback Transaction
---Commit Transaction
If anything goes wrong with any of the grouped statements, all changes need to
be aborted. The process of reversing changes is called rollback in SQL Server
terminology. If everything is in order with all statements within a single
transaction, all changes are recorded together in the database. In SQL Server
terminology, we say that these changes are committed to the database.
|
|
Delete Vs Truncate
|
| |
| Truncate |
Delete |
| 1.Removes all rows from a table without logging the individual row
deletes.
|
1.Removes rows one at a time and records an entry in the
transaction log for each deleted row |
| 2.Cannot apply where conditions. |
2.Can apply where conditions |
| 3.Auto commit & can't rollback. |
3.no Auto Commit & can rollback. |
| 4.Truncated table's table space can be reused immediatly
|
4.table space cannt be reused immediatly
|
| eg TRUNCATE TABLE authors
|
eg A. Use DELETE with no parameters
This example deletes all rows from the authors table.
USE pubs
DELETE authors
B. Use DELETE on a set of rows
Because au_lname may not be unique, this example deletes all rows in which
au_lname is McBadden.
USE pubs
DELETE FROM authors WHERE au_lname = 'McBadden'
|
|
|
User Defined Functions vs Stored Procedures
|
| |
| User Defined Functions |
Stored Procedures |
| 1.Executable from SQL SELECT and SQL action queries |
1.Use EXECUTE or EXEC to run |
| 2.Doesn't return output parameters |
2.Supports output parameters |
| 3.Returns table variable |
3.Can create a physical table and populate it, but can't return a table
variable |
| 4.Can join to a UDF |
4.Can't join to a stored procedure |
| 5.Can't make a permanent changes to the server environment or an external
source |
5.Stored Procedures can be used to change some of the server environment and
operation environment |
| 6.Can't be used in an XML FOR Clause |
6.Can be used in an XML FOR clause |
| 7.T-SQL errors stop the function when an error occurs |
7.Errors are ignored and T-SQL processes the next statement. You must include
error handling code. |
| 8.Use CREATE FUNCTION to create |
8.Use CREATE PROCEDURE to create |
|
|
What is the use of set NoCount ON in stored procedures?
|
| |
Use the SET NOCOUNT ON statement to prevent SQL Server from
sending the DONE_IN_PROC message for each statement in a stored procedure. For
example, if you have eight operations in a stored procedure and you have not
used this option eight messages are returned to the caller. Each message
contains the number of affected rows for the respective statement.
|