SQL Server2008

SQL Injection Attack                   
In this article I'll explain you what SQL injection attack is and then I'll show you ways how to prevent it on your own website. SQL injection is very common form of an attack and it's relatively easy to perform. There are many actual examples in history where attack using this technique was successful and caused considerable losses. That is why it's important that you learn what it is and how to prevent it.
SQL injection takes advantage of incorrect user input filtering or lack of strong typing. Basically all you need to perform this attack is a simple web browser. Many web pages take user input as parameter for SQL query and if this input is not checked before executing query on SQL database it's very easy to use it in a way that was not indented. The most common example of injecting SQL code is through log-in form.

Basic examples of SQL injection

Suppose that we have on a website log-in form and that after logging in we show our users some information that is only for them. The way we construct SQL query on such a website could look like this:
view plaincopy to clipboardprint?
  1. query = "SELECT * FROM users WHERE login = '" + login +"' AND pass = '" + pass + "';";
Then if we type login like john and password like pass123 our query will look like this:
view plaincopy to clipboardprint?
  1. SELECT * FROM users WHERE login = 'john' AND pass = 'pass123';
So far so good, the website works just the way it was intended - user john provides valid password and gets requested information. But consider the situation when another user wants to get confidential information from user with login "john". In a system that is not well designed and vulnerable to SQL injection all he has to do is to type in log-in form password like:
view plaincopy to clipboardprint?
  1. ' OR '1' = '1

Yeah, I know that this password is very strange but let's take a look at SQL query that is created:
view plaincopy to clipboardprint?
  1. SELECT * FROM users WHERE login = 'john' AND pass = '' OR '1' = '1';
Thus we have created completely valid SQL query that will allow us to view confidential content meant for user with login "john" without knowing his password.
There's also another, equally simple way to obtain the same result. If you type the following user name then the password is even not important and it can be whatever you like (it will be omitted while executing SQL query):
view plaincopy to clipboardprint?
  1. john' --

In SQL two dashes mean commentary so the text that follows these two dashes will be ignored. Let's take a look at the SQL query that will be produced:
view plaincopy to clipboardprint?
  1. SELECT * FROM users WHERE login = 'john' --' AND pass = 'whatever';
Because of the commentary this query can be reduced to the one like this:
view plaincopy to clipboardprint?
  1. SELECT * FROM users WHERE login = 'john'
Thus we are able again to gain unauthorized access to information that was meant only for user with login "john".

Destroying unprotected database

In previous examples I presented the most basic ways of getting access to confidential data but the attacker might also have other intentions. Using SQL injection you can destroy some data from database or even delete whole database.
If database is not protected good enough it could be possible to input as many sql queries as you like just by using semicolon separator - ";". Let's take again our previous website with log-in form as an example. You could input password like the following one:
view plaincopy to clipboardprint?
  1. aaa'; DROP TABLE users; --
Then whole SQL query will look like:
view plaincopy to clipboardprint?
  1. SELECT * FROM users WHERE login = 'john' AND pass = 'aaa'; DROP TABLE users; --';
The first query will probably return nothing unless the password of user john is aaa, but that doesn't matter. What's important is second query that will delete whole users data table from database.
As you can see altering unprotected databases can be very easy using simple SQL injection techniques and this above is just an example, using it you can do a whole bunch of other ugly things.

Weak typing

If data that is submitted by the user is not validated before executing SQL query it's very easy to perform attack using non-string fields like for example numeric field.
Suppose that we have query like this one:
view plaincopy to clipboardprint?
  1. query = "SELECT * FROM products WHERE id = " + productId + ";";
If productId isn't validated before it is used in SQL query you can type there a text like for instance this one:
view plaincopy to clipboardprint?
  1. 0; DROP TABLE products
The whole SQL query will look like this:
view plaincopy to clipboardprint?
  1. SELECT * FROM products WHERE id = 0; DROP TABLE products;
And the result will be of course deleting whole products table. As you can see validating input is very important, because thus you can prevent performing SQL injection attack on your database.
There are many more various techniques to use SQL injection attack. I covered in this article only the most common and simple ones so you could just get an idea how does it work. The purpose of this article is not to learn you how to attack a website but how to protect one. So in the next part of this article I'll describe how you can protect your web site against this kind of attacks. However please note that you can never be sure if your website or any other product is fully protected because there can always be a glitch that somebody can exploit.

How to protect your website against SQL injection

As you probably noticed in the previous part of this article SQL injection attack can be very harmful, you can even destroy whole database just by inserting some SQL code into user input fields on a website. Generally there is no one foolproof way of avoiding SQL injection and you can never be 100% sure that your website is secure but if you follow few basic rules and strategies that I'll describe you can minimize risk of being successfully attacked to minimum.

Filter and validate user input

One of the most important things to remember is never to trust user input. That's why you should always and I mean every single time validate input. Sometimes it's relatively easy when you have only a very limited set of characters that are allowed in a given input. Good example of such an input is phone number. Generally there can be allowed only numbers and depending on the convention maybe dashes, white spaces or plus sign. All other characters should be discarded and an error should be displayed to users informing that the format of a phone number is incorrect.
There are situations however when validating an input is not as straightforward as in the previous example. In input fields where the set of allowed characters is not very limited you should at least try if it's possible not to accept characters such as:
  • ; that is used as query separator in SQL
  • -- that indicates beginning of the commentary in SQL
  • ' that is used for strings in SQL
You can also use escape functions such as mysql_real_escape_string from PHP. It translates unwanted characters that have special meaning in SQL to its proper form. For example ' character has to be replaced with two quote characters '' in order to work properly. In C# you can use code like this to escape quote ' character:
view plaincopy to clipboardprint?
  1. input.Replace("'", "''");
Think about a form of possible input strings and try to restrict it as much as possible. If you know that an input can't have more than a certain number of characters (like postal code) than validate also length of input and discard it if it's bigger than a certain value. Always try to validate and filter as much as possible.

Use bound parameters

This technique can significantly improve security of your web application. Generally you create a SQL query with special placeholders like ? for parameters used in your query so SQL code can look for instance like this one:
view plaincopy to clipboardprint?
  1. SELECT * FROM products WHERE id = ?;
Then to use it all you have to do is just bind parameters. In our example there is only one parameter - id but of course there can be more parameters.
The advantage of binding parameters is very simple - even if you use characters that are potentially harmful like semicolons, dashes or quotes it doesn't matter because it's only a parameter and it can't be treated as SQL code even if you use SQL injection techniques described in this article.
Binding parameters depends of course on language and database that you use but general scheme is similar. Take a look how to do this in pseudocode:
view plaincopy to clipboardprint?
  1. query = SELECT * FROM products WHERE id = ?
  2. query.bind(1, 20)
  3. query.execute
It's even better if in some languages you have also option to specify type of parameter that you're binding.
Remember that using bound parameters is one of the best techniques of preventing SQL injection attack so I strongly advise using it.

Use stored procedures

In some cases you can even completely get rid of SQL code from you program and just use stored procedures instead. Stored procedure is simply a piece of code that will be executed after calling this procedure. Take a look at this sample to get the general idea how it works:
view plaincopy to clipboardprint?
  1. CREATE PROCEDURE myStoredProcedure @id int
  2. AS
  3. SELECT name, age FROM staff
  4. WHERE id = @id
  5. GO
Now to execute it all you have to do is call it with id parameter:
view plaincopy to clipboardprint?
  1. myStoredProcedure 6
Another advantage of using stored procedures is performance, in many cases depending on the database you're using it can be faster than a standard query.

User permissions

The most important thing is that you remember never to use admin rights for web based application. Give your user as little rights as possible in other words user rights should allow him to do only what's necessary and nothing more.
You can create a few levels of users in your application - for example one level for all users who entered your web site and another level for users who are already logged-in adding certain right steadily if there's need for it.

Securing web server

There is also an option of securing web server that hosts your application like for example installing special modules such as mod_security for Apache. These modules can filter for instance potentially harmful requests on server. There are however some drawbacks of this strategy like possible decrease in performance or filtration of requests that are not harmful.

Closing remarks

SQL injection attack is a threat that cannot be disregarded because results of such an attack can be devastating. What's more as you can see in the first part of this article SQL injection attack is relatively easy to perform. If you follow security guidelines described in the second part of this article you can significantly reduce risk of being successfully attacked.


ADO.NET
--------------------------------------------------------------------------------------------
          Ado.net is new data access technology it is disconnected architecture i.e there is not necessary to connect database always whenever it requires data that time only it connects .basically library of ado.net is system.data.
   ADO.NET provides access to all kind of data sources such as Microsoft SQL Server, OLEDB, Oracle, XML.ADO.NET includes some providers from the .NET Framework to connect to the database, to execute commands, and finally to retrieve results. Those results are either directly used or can be put in dataset and manipulate it.
Some of the data provider objects are:
Command : Command objects are used to execute the queries, procedures. Sql statements etc. It can execute stored procedures or queries that use parameters as well.
Data Adapter Data adapters are the Bridge between database and dataset. It allows activities like reading data, updating data.
Datareader which reads data from data store in forward only mode.
A dataset object is not in directly connected to any data store. It represents disconnected and cached data. The dataset communicates with Data adapter that fills up the dataset. Dataset can have one or more Datatable and relations.  
DataView object is used to sort and filter data in Datatable.

What is the difference between ADO and ADO.NET?
ADO
ADO.NET
Connected Architecture
disconnected architecture.
in ado we cant retrieve the more than one table at a time
in ado.net we can retrieve many tables as per user
requirement
n ado we have recordset for the retrieving data
in ado.net we have Dataset.
in ado xml integration is not possible
but in ado.net we can use xml

Define connected and disconnected data access in ADO.NET

Data reader is based on the connected architecture for data access. Does not allow data manipulation
Dataset supports disconnected data access architecture. This gives better performance results.

SQL Connection:
Connection String Parameter
Description
Data Source
Identifies the server. Could be local machine, machine domain name, IP Addre
Initial Catalog
Database name.
Integrated Security
Set to SSPI to make connection with user's Windows login
User ID
Name of user configured in SQL Server.
Password
Password matching SQL Server User ID.

Command objects uses, purposes and their methods.
Command objects are used to execute the queries, procedures. Sql statements etc. It can execute stored procedures or queries that use parameters as well.
It works on the basis of certain properties like ActiveConnection, CommandText, CommandType, Name etc.
ExecuteNonQuery: Use for data manipulation,such as Insert,update,delete.
ExecuteReader: Use for accessing data.it provides a forword-only,read-only ,connected recordset.
ExecuteScalar: Use for retriving 1 row 1 col value,.i.e single value,.eg:for retriving aggregate function.it is faster than other ways of retriving a single value from DB.

What are basic methods of Data adapter?

The most commonly used methods of the DataAdapter are:
Fill:This method executes the SelectCommand to fill the DataSet object with data from the data source.Depending on whether there is a primary key in the DataSet, the ‘fill’ can also be used to update an existing table in a DataSet with changes made to the data in the original datasource.
FillSchema:This method executes the SelectCommand to extract the schema of a table from the data source.
It creates an empty table in the DataSet object with all the corresponding constraints.
Update:This method executes the InsertCommand, UpdateCommand, or DeleteCommand to update the original data source with the changes made to the content of the DataSet.

How do you create an instance of SqlDataReader class?

To create an instance of SqlDataReader class, you must call the ExecuteReader method of the SqlCommand object, instead of directly using a constructor.
//Error! Cannot use SqlDataReader () constructor
//Call the ExecuteReader method of the SqlCommand object
SqlCommand CommandObject = new SqlCommand ();
SqlDataReader ReaderObject = CommandObject.ExecuteReader();
Note:--Creating an instance of SqlDataReader class using SqlDataReader () constructor generates a compile time error – The type ‘System.Data.SqlClient.SqlDataReader’ has no constructors defined.

How do you programatically check if a specified SqlDataReader instance has been closed?

Use the IsClosed property of SqlDataReader to check if a specified SqlDataReader instance has been closed. If IsClosed property returns true, the SqlDataReader instance has been closed else not closed.

How do you get the total number of columns in the current row of a SqlDataReader instance? 

FieldCount property can be used to get the total number of columns in the current row of a SqlDataReader instance.

What is the use of SqlParameter.Direction Property?

SqlParameter.Direction Property is used to specify the Sql Parameter type – input-only, output-only, bidirectional, or a stored procedure return value parameter. The default is Input.

How do you retrieve two tables of data at the same time by using data reader? 

Include 2 select statements either in a stored procedure or in a select command and call the ExecuteReader() method on the command object. This will automatically fill the DataReader with 2 Tables of data.
The datareader will always return the data from first table only. If you want to get the second table then you need to use ReaderObject.NextResult() method. The NextResult() method will return true if there is another table. The following code shows you how do it.
//Create the SQL Query with 2 Select statements
string SQLQuery = “Select * from Customers;Select * from Employees;”;
//Create the Connection Object
SqlConnection ConnectionObject = new SqlConnection(ConnectionString);
//Create the Command Object
SqlCommand CommandObject = new SqlCommand(SQLQuery, ConnectionObject);
//Open the connection
ConnectionObject.Open();
//Execute the command. Now reader object will have 2 tables of data.
SqlDataReader ReaderObject = CommandObject.ExecuteReader();
//Loop thru the tables in the DataReader object
while (ReaderObject.NextResult())
{
while (ReaderObject.Read())
{
//Do Something
}
}
//Close the Reader
ReaderObject.Close();
//Close the Connection
ConnectionObject.Close();

What are the advantages of using SQL stored procedures instead of adhoc SQL queries in an ASP.NET web application? 

Better Performance: As stored procedures are precompiled objects they execute faster than SQL queries. Every time we run a SQL query, the query has to be first compiled and then executed where as a stored procedure is already compiled. Hence executing stored procedures is much faster than executing SQL queries.
Better Security: For a given stored procedure you can specify who has the rights to execute. You cannot do the same for an SQL query. Writing the SQL statements inside our code is usually not a good idea. In this way you expose your database schema (design) in the code which may be changed. Hence most of the time programmers use stored procedures instead of plain SQL statements.
Reduced Network Traffic: Stored Procedures reside on the database server. If you have to execute a Stored Procedure from your ASP.NET web application, you just specify the name of the Stored Procedure. So over the network you just send the name of the Stored Procedure. With an SQL query you have to send all the SQL statements over the network to the database server which could lead to increased network traffic.

Can you update the database using DataReader object?

No, you cannot update the database using DataReader object. DataReader is read-only, forward only. It reads one record at a time. After DataReader finishes reading the current record, it moves to the next record. There is no way you can go back to the previous record.

Features and DisAdvantages of dataset..?
 Advantages:
           it’s a disconnected apporch.
           Is Serialized Objects. It contain more than one table and we can done any operations like relationship set, primary key, retrive any column,
           No network traffic while communicating DBServer.
    Disadvantage:
            It is slow than datareader.
            It is Heavy objects. so it take more space. Some times it will not satisfy the ACID Property properly.
 
How can we check that some changes have been made to dataset since it was loaded?
a. GetChanges: gives the dataset that has changed since newly loaded or since Accept changes has been executed.
b. HasChanges: this returns a status that tells if any changes have been made to the dataset since accept changes was executed.

Explain the basic use of “DataView” and explain its methods.

A DataView is a representation of a full table or a small section of rows.
It is used to sort and find data within Datatable.
Following are the methods of a DataView:
Find : Parameter: An array of values; Value Returned: Index of the row
FindRow : Parameter: An array of values; Value Returned: Collection of DataRow
AddNew : Adds a new row to the DataView object.
Delete : Deletes the specified row from DataView object

ADO.NET Code showing Dataset storing multiple tables.

DataSet ds = new DataSet();
ds.Tables.Add(dt1);
ds.Tables.Add(dt2);
ds.Tables.Add(dtn);

Can you explain how to enable and disable connection pooling?

To enable connection pooling:
SqlConnection myConnection = new SqlConnection(@"Data  Source=(local)\SQLEXPRESS;Initial Catalog=TEST;Integrated Security=SSPI;");
This has connection pooling on by default
To disable connection pooling:
SqlConnection myConnection = new SqlConnection(@"Data Source=(local)\SQLEXPRESS;Initial Catalog=TEST;Integrated Security=SSPI;Pooling=false;");

What’s difference between “Optimistic” and “Pessimistic” locking?

In pessimistic locking, when a user opens a data to update it, a lock is granted. Other users can only view the data until the whole transaction of the data update is completed.
In optimistic locking, a data is opened for updating by multiple users. A lock is granted only during the update transaction and not for the entire session. Due to this concurrency is increased and is a practical approach of updating the data.

What is the use of CommandBuilder?

CommandBuilder is used to build complex queries. It can even build commands that are based on the returned results. CommandBuilder is less error prone and more readable than the command object.
Difference between datareader and dataset?

                       DataSet
                       Datareader
DataSet object can contain multiple rowsets from the same data source as well as from the relationships between them
DataReader provides forward-only and read-only access to data
Dataset is a disconnected architecture
Datareader is connected architecture
Dataset can persist(manipulate) data.
Datareader can not persist data.
Can traverse data in any order front, back.
. Can traverse only forward
More expensive than datareader as it stores multiple rows at the same time.
. It is less costly because it stores one row at a time

How to check null values in dataset ? Answer

if dataset.tables(0).rows.count = 0

How To Store Dataset in viewstate..?
 
  dataset can be stored in the viewstate. any object that can be serializable can be stored in the viewstate. we can serialize the dataset and then can be saved in the viewsate.
store a dataset in a view state is not Recommend.
//da(sqldataadapter);dataset dSet
da.Fill(dSet);
System.IO.StringWriter sw = new System.IO.StringWriter();
// Write the DataSet to the ViewState property.
dSet.WriteXml(sw);
ViewState["dSet"] = sw.ToString();
 

SQL Server 2005

                              SQL commands are instructions used to communicate with the database to perform specific task that work with data. SQL commands can be used not only for searching the database but also to perform various other functions like, for example, you can create tables, add data to tables, or modify data, drop the table, set permissions for users.
 SQL commands are grouped into four major categories depending on their functionality:
Data Definition Language (DDL) - These SQL commands are used for creating, modifying, and dropping the structure of database objects. The commands are CREATE, ALTER, DROP, RENAME, and TRUNCATE.
Data Manipulation Language (DML) - These SQL commands are used for storing, retrieving, modifying, and deleting data. These commands are SELECT, INSERT, UPDATE, and DELETE.
Transaction Control Language (TCL) - These SQL commands are used for managing changes affecting the data. These commands are COMMIT, ROLLBACK, and SAVEPOINT.
Data Control Language (DCL) - These SQL commands are used for providing security to database objects. These commands are GRANT and REVOKE.

What is Normalization?

Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminating redundant data (for example, storing the same data in more than one table) and ensuring 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.

The Normal Forms

The database community has developed a series of guidelines for ensuring that databases are normalized. These are referred to as normal forms and are numbered from one (the lowest form of normalization, referred to as first normal form or 1NF) through five (fifth normal form or 5NF). In practical applications, you’ll often see 1NF, 2NF, and 3NF along with the occasional 4NF. Fifth normal form is very rarely seen and won’t be discussed in this article.
Before we begin our discussion of the normal forms, it’s important to point out that they are guidelines and guidelines only. Occasionally, it becomes necessary to stray from them to meet practical business requirements. However, when variations take place, it’s extremely important to evaluate any possible ramifications they could have on your system and account for possible inconsistencies. That said, let’s explore the normal forms.

First Normal Form (1NF)

First normal form (1NF) sets the very basic rules for an organized database:
·         Eliminate duplicative columns from the same table.
·         Create separate tables for each group of related data and identify each rowwith a unique column or set of columns (the primary key).

Second Normal Form (2NF)

Second normal form (2NF) further addresses the concept of removing duplicative data:
·         Meet all the requirements of the first normal form.
·         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)

Third normal form (3NF) goes one large step further:
·         Meet all the requirements of the second normal form.
·         Remove columns that are not dependent upon the primary key.

Fourth Normal Form (4NF)

Finally, fourth normal form (4NF) has one additional requirement:
·         Meet all the requirements of the third normal form.
·         A relation is in 4NF if it has no multi-valued dependencies.
Remember, these normalization guidelines are cumulative. For a database to be in 2NF, it must first fulfill all the criteria of a 1NF database.
Select Statement:-
SELECT column_list FROM table-name [WHERE Clause] [GROUP BY clause] [HAVING clause] [ORDER BY clause];
SELECT first_name || ' ' || last_name FROM employee;

SQL Alias

SELECT first_name AS Name FROM student_details;
or
SELECT first_name Name FROM student_details;

SQL WHERE Clause

SELECT first_name, last_name FROM student_details WHERE id = 100;

SQL LIKE Operator

SELECT first_name, last_name FROM student_details 
WHERE first_name LIKE 'S%';

SQL BETWEEN ... AND Operator

SELECT first_name, last_name, age FROM student_details WHERE age BETWEEN 10 AND 15;

SQL IN Operator:

SELECT first_name, last_name, subject FROM student_details WHERE subject IN ('Maths', Science'); 

SQL IS NULL Operator

SELECT first_name, last_name FROM student_details WHERE games IS NULL 

Syntax for using SQL ORDER BY clause to sort data is:

SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1
[, column2, .. columnN] [DESC]];

SQL GROUP Functions

SELECT COUNT (*) FROM employee WHERE dept = 'Electronics';
SELECT COUNT (*) FROM employee;
SELECT DISTINCT dept FROM employee;
SELECT COUNT (DISTINCT name) FROM employee;
SELECT MAX (salary) FROM employee;
SELECT MIN (salary) FROM employee;
SELECT AVG (salary) FROM employee;
SELECT SUM (salary) FROM employee;

SQL GROUP BY Clause

SELECT dept, SUM (salary) FROM employee GROUP BY dept; 

SQL HAVING Clause

SELECT dept, SUM (salary) FROM employee GROUP BY dept 
HAVING SUM (salary) > 25000 

SQL ALTER TABLE Statement

ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name DROP column_name;
ALTER TABLE table_name MODIFY column_name datatype;
RENAME old_table_name To new_table_name;
Unique and primary Key

Primary key:
Unique Key:
Primary key is nothing but it is uniqly identified each roe in Table.
Unique Key is nothing but it is uniqly identified each roe in Table.
Primary key Does not Allows Duplicate values and Null values.
Unique Key Does not Allows Duplicate values but allows only one Null value.
Primary key is default Clustered indexes
Primary key is default Non- Clustered indexes
One table can have only one Primary key.
A Table may Contain  one or more UNIQUE  constraints

Differences between Functions and stored procedure:
storedprocedures & functions are collection of some sql statements to perform a particular task.Functions can be called from procedure whereas procedures cannot be called from function.

Functions
Stored Procedure
Functions are compiled and excuted runtime
Procedures are parsed and compiled. They are stored in compiled format in the database
a function is better when returning a single scalar value.
a stored procedure is better when returning one or more rows.
functions can have only input parameters
Procedures can have input,output parameters for it
function allow only select statement in it.
Procedure allow select as well as DML statement in it
try-catch block cannot be used in a function.
Exception can be handled by try-catch block in a procedure
whereas function can return one value which is mandatory.
Procedure can return zero or n values
Cannot affect the state of database.
Can affect the state of database using commit etc.


About writting a query and SP which is better ?

sp is the better one... Its compile and inside that we can write any number of Query Statement , finally for the whole thing it will create only one object as a reference .
But in Query Statement Each query be an Objects ...

Explain store procedure and trigger?

A stored procedure is a set of SQL commands that has been compiled and stored on the database server. sp means to execute set of quries in single sp.and we can call manually.
A trigger is a special kind of stored procedure that is invoked whenever an attempt is made to modify the data in the table it protects. Modifications to the table are made
Using INSERT, UPDATE, OR DELETE statements. We can not call manually.
 
Using try-catch block in stored procedure  sql server 2005
 
 BEGIN TRY
   -- This will generate an error, as ProductID is an IDENTITY column
   -- Ergo, we can't specify a value for this column...
   INSERT INTO Products(ProductID, ProductName) VALUES(1, 'Test')
 END TRY
 BEGIN CATCH
   SELECT 'There was an error! ' + ERROR_MESSAGE()
 END CATCH
Function
Description
ERROR_NUMBER()
Returns the number of the error
ERROR_SEVERITY()
Returns the severity
ERROR_STATE()
Returns the error state number
ERROR_PROCEDURE()
Returns the name of the stored procedure or trigger where the error occurred
ERROR_LINE()
Returns the line number inside the routine that caused the error
ERROR_MESSAGE()
Returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times
static query vs dynamic query
             Static Query
           Dynamic Query
when your query pass with value(static).
when your query pass with parameter like(@user_name). is dyanamic query.
select * from TblEmpDetail where user_name='Kapil'.
select * from TblEmpDetail Where user_name=@user_name where @user_name=sqlparameter
Static query will create an execution plan during compilation.
Dynamic query will create execution plan at run time.
A view is a virtual table that represents data from one or more than one database table. You can select data from a single or multiple tables based on the sort and filter criteria (using the WHERE and GROUP BY clauses) and save data as a view. You can also set permissions on views. For example, a manager, an accountant, and a clerk of a company share the same database. The accountant can access partial data from multiple tables, and the clerk can access partial data from a single table. You can create three different views based on these user rights and let the user’s access these views based in their rights.

SQL Index

What is Index? It's purpose?
In a database, an index allows the database program to find data in a table without  scanning the entire table.Index in sql is created on existing tables to retrieve the rows quickly.When there are thousands of records in a table, retrieving information will take a long time. Therefore indexes are created on columns which are accessed frequently, so that the information can be retrieved quickly. Indexes can be created on a single column or a group of columns. When a index is created, it first sorts the data and then it assigns a ROWID for each row.
Syntax to create Index:
CREATE INDEX index_name ON table_name (column_name1,column_name2...); 
Syntax to create SQL unique Index:
CREATE UNIQUE INDEX index_name ON table_name (column_name1,column_name2...); 
CLUSTERED INDEX:
    Clustered index will be created by default when u create primary key on a column. So we can create one clustered index per table. clustered index is stored in serial passion.
1.There can be only one Clustered index for a table
2.usually made on the primary key
3.the logical order of the index matches the physical stored order of the rows on disk
NON CLUSTERED INDEX:
   Non clustered index will be created automatically when u  create unique key on a column. A table can have no.of
unique keys, so we can create no.of non clustered indexes  per table.
1.There can be only 249 Clustered index for a table
2.usually made on the any key
3.the logical order of the index does not match the physical stored order of the rows on disk
Use a nonclustered index for:
·         Queries that do not return large result sets;
·         Columns frequently involved in search conditions of a query (WHERE clause) that return exact matches;
·         Applications for which joins and grouping are frequently required. Create multiple nonclustered indexes on columns involved in join and grouping operations, and a clustered index on any foreign key columns;
·         Covering all columns from one table in a given query.
Use a clustered index for:
·         Queries that return large result sets;
·         Columns that are accessed sequentially;
·         Queries that return a range of values using operators such as BETWEEN, >, >=, <, and <=;
·         Columns that are frequently accessed by queries involving join or GROUP BY clauses (typically these are foreign key columns). An index on the column(s) specified in the ORDER BY or GROUP BY clause eliminates the need for SQL Server to sort the data because the rows are already sorted (this improves query performance);
·         where very fast single row lookup is required, typically by means of the primary key. Create a clustered index on the primary key.

NOTE:
1) Even though sql indexes are created to access the rows in the table quickly, they slow down DML operations like INSERT, UPDATE, DELETE on the table, because the indexes and tables both are updated along when a DML operation is performed. So use indexes only on columns which are used to search the table frequently.
2) Is is not required to create indexes on table which have less data. 
3) In oracle database you can define up to sixteen (16) columns in an INDEX. 


Inner Vs Outer Join in SQLFriday, Aug 6 2010 

SQL Server Sharad 2:31 pm
Assuming you’re joining on columns with no duplicates, which is by far the most common case:
·         An inner join of A and B gives the result of A intersect B, i.e. the inner part of a venn diagram intersection.
·         An outer join of A and B gives the results of A union B, i.e. the outer parts of a venn diagram union.
Examples
Suppose you have two Tables, with a single column each, and data as follows:
A    B
-    -
1    3
2    4
3    5
4    6
Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.
inner join
An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.
select * from a INNER JOIN b on a.a = b.b;
select a.*,b.*  from a,b where a.a = b.b;
 
a | b
--+--
3 | 3
4 | 4
left outer join
A left outer join will give all rows in A, plus any common rows in B.
select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*,b.*  from a,b where a.a = b.b(+);
 
a |  b  
--+-----
1 | null
2 | null
3 |    3
4 |    4
full outer join
A full outer join will give you the union of A and B, i.e. All the rows in A and all the rows in B. If something in A doesn’t have a corresponding datum in B, then the B portion is null, and vice versa.
select * from a FULL OUTER JOIN b on a.a = b.b;
 
 a   |  b  
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5
 
 http://learnsqlserver.in/

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


No comments:

Post a Comment

hi Happy Reading.......................

Subscribe to: Comments (Atom)