Common SELECT Statements (SQL Server) Monday, Jul 18 2011 

Followings are some examples of using SELECT statement. My table name tblTest. Followings are columns. (TestID, TestName, TestDate, TestCity )

1) Select * from tblTest (Returns all columns/Rows)

2) Select * from tblTest Where TestID=2 (Returns the row/s which TestID has value 2)

3) Select * from tblTest where TestID Between 10 and 20 (Return all rows between 10 and 20, this result includes 10 and 20)

4) Select * from tblTest Where TestCity in (‘New York’,’Washington’,’California’) (Returns all rows which city is NewYork, Washington, california)

5) Select * from tblTest Where TestName Like ‘A%’ (Return all rows where the name starts letter A)

6) Select * from tblTest Where TestName Like ‘%A’ (Return all rows where the name ends letter A)

7) Select * from tblTest Where TestName Like ‘[ABC]%’ (Return all rows of name start with A / B / C)

8) Select * from tblTest Where TestName Like ‘[^ABC]%’ (Return all rows of name not start with A and B and C)

9) Select (TestName+space(1)+TestCity) as Address from tblTest (Returns single column address, name and city added together with a space)

10) Select * from tblTest Where TestName IS NULL (Return all rows which TestNane has null values)

11) Select * from tblTest Where TestName IS NOT NULL (Return all rows which TestNane has not null values)

12) Select * from tblTest Order By TestID Desc (Sort the result set descending order, Asc or not using any sort Ascending order)

13) Select ‘Visual Studio’ as IDE, ‘2010’ as Version (Creating memory resident result set with two columns[IDE and Version])

14) Select Distinct TestID from tblTest (Returns unique rows based on TestID)

15) Select Top 10 * from tblTest  (Return 10 customers randomly)

16) Select getdate() (Shows the current date)

17) Select db_name() (shows the database name which you are working on)

18) Select @@Servername (Shows name of the server)

19) Select serverproperty (‘Edition’) (You can pass following ServerName, Edition, EngineEdition, ProductLevel to get current information about the server)

20) Select user_name() (Get current user)

21)  Select *  into #test from tblTest  (Create temporary table #test and insert all records from tblTest)

22)  Select Max(TestID) from tblTest (Returns Maximum TestID from tblTest)

23)  Select * from tblTest Compute Max(TestID) (Returns two result sets – getting all rows and maximum value of TestID)

24) Select FirstName, LastName,  Salary, DOB,

Case Gender
When ‘M’ Then ‘Male’
When ‘F’ Then ‘Female’
End
From Employees

(This Change Gender fields as if M then prints Male and if F then prints Female)  

Advertisements

SQL SERVER – Delete Duplicate Records – Rows Wednesday, Jun 15 2011 

Following code is useful to delete duplicate records. The table must have identity column, which will be used to identify the duplicate records. Table in example is has ID as Identity Column and Columns which have duplicate data are DuplicateColumn1, DuplicateColumn2 and DuplicateColumn3.

DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

Using SQL State for storing session in asp.net Wednesday, Dec 1 2010 

HTTP is called a stateless protocol because each command is executed independently, without any knowledge of the commands that came before it. This makes website development a quite difficult task. To make it resolve “State Management” comes into play and here I am going to give you the step by step procedure to deploy “SQL State session” in you asp.net application.

Just to get a quick reference before we get into the tutorial, I would like you to pay some attention to the session states modes,

1.    Off :  Which disable session state management

2.    InProc: This is the by default mode in the asp.net application. Stores session in the web server.

3.    Out of process: Also know as state server mode. In the memory of a machine dedicated to storing session variables

4.    SQL Server : Session stored in the SQL Server.

Now, let’s get back to the soul of this article and check out the steps you need to follow to implement SQL Server

Step 1: Go to Visual Studio command prompt:

Open VS command prompt

Step 2: Traverse to this path:

…….\Microsoft.NET\Framework\v2.0.50727

Command Prompt

 

Step 3: Now run this command

aspnet_regsql.exe -S servername-E -ssadd -sstype p

or

aspnet_regsql.exe -S SERVERNAME -U sa -P password -ssadd -sstype c -d yourdatabase

Times to demystify this command:

Here,

 

S IP address or the name of the Sql Server in which you want to store the session.
E Indicate that you want use integrated security at the time of making connection.
-ssad It will add support to the SQL server mode session state.
-sstype Type of support for session state, available options are

 

t Temporary storage
p Permanent storage
C Custom Storage

 

 

-u User Id while connecting to the SQL server.
-p Password while connecting to the SQL server.
-ssremove To remove support for the SQL server mode session state.
-d Name of the database when –sstype is on “c” [Custom storage].

 

Note: In our case we are using the integrated security so the so we are using this command

aspnet_regsql.exe -S servername-E -ssadd -sstype p

Step 4:

Now when you run the command, your database will be installed successfully, you can check the same by verify the tables installed

ASPStateTempApplications

ASPStateTempSessions

 

Step5:

If everything is done properly, then come to your ASP.NET application and make the appropriate setting for the SQL server session state:

<sessionState mode=”SQLServer” allowCustomSqlDatabase=”true”

sqlConnectionString=”Data Source=MyDataSource;Initial Catalog=ASPState;Integrated Security=SSPI”

cookieless=”false”

timeout=”10″>

sessionState>

 

Step 6:

Now we have to see that it’s working correctly or not:

On Page_Load,

Session[“TestSession”] = “Some Data”;

On button click

string testVariable = Session[“TestSession”].ToString();

 

Run your application and check your database to verify that everything is working fine.

 

Verify Database

If you find difficulty at the any step, please feel free to contact me.

Hope to see your sessions in SQL Server. 🙂

Reference: http://www.anujtripathi.net/BlogListing.aspx?Id=5

The Attributes of a Connection String in ASP.Net with C# Thursday, Nov 11 2010 

To use a SqlConnection object, you must provide various pieces of information joined into a string but are separated from each other with a semi-colon “;”. Each piece appears as a Key=Value:

Key1=Value1;Key2=Value2;Key_n=Value_n

It can be passed as follows:

SqlConnection connection = new SqlConnection("Key1=Value1;Key2=Value2;Key_n=Value_n");

or assigned as a string to the SqlConnection.ConnectionString property:

string strConnection = "Key1=Value1;Key2=Value2;Key_n=Value_n";
SqlConnection connection = new SqlConnection();

connection.ConnectionString = strConnection;

The Source of Data

To establish a connection, you must specify the computer you are connecting to, that has Microsoft SQL  Server installed. If you are creating your application on the same computer on which SQL Server is  installed, you can use (local). Here is an example:
SqlConnection connection = new SqlConnection("Server=(local); ");

If you know the name of the computer, you can assign it to the computer attribute. Here is an example:

SqlConnection connection = new SqlConnection("Server=central; ");

In the same way, if you are connecting to a specific computer, you must provide its name. Here is an

example:

SqlConnection connection = new SqlConnection("Data Source=central; ")

Security

An important aspect of establishing a connection to a computer is security. To support security, the

connection string of the SqlConnection class includes an attribute called Trusted_Connection orIntegrated

Security that can have a value of truefalseyesno, or SSPI with the SSPI having the same indication

as true.

If you are establishing a trusted or simple connection that doesn't need to be verified, you can assign

a value of true or SSPI.  Here is an example:

SqlConnection connection =
        new SqlConnection("Server=(local);Trusted_Connection=SSPI");

If you are programmatically establishing the connection, to apply authentication, you can assignfalse or

no to the security attribute you selected.

The Username

To specify the user name, after assigning false or no to the security attribute, you must use theUser ID

attribute and assign it a valid username. Here is an example:

string strConnection = "Server=(local);" +
                           "Integrated Security=no;" +
                           "User ID=wmessmann";
    SqlConnection connection = new SqlConnection(strConnection);

The Password

Besides the username, to create a secured connection, you must also provide a password. To specify the

password, you can user either the PASSWORD or the PWD. Here is an example:

string strConnection = "Server=(local);" +
                           "Integrated Security=no;" +
                           "User ID=wmessmann;PWD=$outh~@kotA";
    SqlConnection connection = new SqlConnection(strConnection);

The Database

To specify the database, the connection string includes an attribute named Database. TheDatabase keyword

can also be substituted for the Initial Catalog value. Here is an example:

SqlConnection connection = new SqlConnection(Server=(local);Database=;);

Another alternative is to assign an empty, single-quoted, string to this attribute. Here is an example:

void InitializeComponent()
{
    string strConnection = "Server=(local);Initial Catalog='exercise1';";
    SqlConnection connection = new SqlConnection(strConnection);
}

As mentioned above, the Database attribute is optional, especially if you are only connecting to the

computer and not to a specific database.

Additional Attributes

There are various other attributes used in the connection string. They include Network Library(also

called Net), Application NameWorkstation IDEncryptConnection TimeoutData SourcePacket Size,

AttachDBFilenameCurrent LanguagePersist Security Info.

After creating the connection string, when the application executes, the compiler would "scan" the string

to validate each key=value section. If it finds an unknown Key, an unknown value, or an invalid

combination of key=value, it would throw an ArgumentException exception and the connection cannot be

established.
Reference: http://www.functionx.com/aspnet/sqlserver/Lesson01.htm
 

SQL Server Interview Questions and Answers ebook free download Wednesday, Nov 10 2010 

SQL Server Interview Questions and Answers ebook free download click here.

ACID Properties of Database Tuesday, Oct 19 2010 

In a perfect transaction world, a transaction must contain a series of properties known as ACID. These properties are:
Atomicity
A transaction is an atomic unit of work or collection of separate operations. So, a transaction succeeds and is committed to the database only when all the separate operations succeed. On the other hand, if any single operations fail during the transaction, everything will be considered as failed and must be rolled back if it is already taken place. Thus, Atomicity helps to avoid data inconsistencies in database by eliminating the chance of processing a part of operations only.
Consistency
A transaction must leave the database into a consistent state whether or not it is completed successfully. The data modified by the transaction must comply with all the constraints in order to maintain integrity.
Isolation
Every transaction has a well defined boundary. One transaction will never affect another transaction running at the same time. Data modifications made by one transaction must be isolated from the data modification made by all other transactions. A transaction sees data in the state as it was before the second transaction modification takes place or in the state as the second transaction completed, but under any circumstance a transaction can not be in any intermediate state.
Durability
If a transaction succeeds, the updates are stored in permanent media even if the database crashes immediately after the application performs a commit operation. Transaction logs are maintained so that the database can be restored to its original position before failure takes place.

Reference: http://www.dotnetfunda.com/codes/code21-acid-properties-of-database-.aspx

An Introduction to Database Normalization Tuesday, Oct 19 2010 

n this article, we’ll introduce the concept of normalization and take a brief look at the most common normal forms. Future articles will provide in-depth explorations of the normalization process.

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 1NF2NF, 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 row with 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.

Reference: http://databases.about.com/od/specificproducts/a/normalization.htm

Sql server User Defined Function (UDF) VS Stored Procedure (SP) Wednesday, Oct 13 2010 

UDFs vs. Stored Procedures
UDFs and stored procedures are both SQL Server objects that store one or more T-SQL statements in a single named, executable routine. Although you can often implement the same or similar functionality using either a UDF or a stored procedure, the code will look significantly different depending on which technique you choose. Here are the main differences between UDFs and stored procedures:

  • A UDF must return a value-a single result set. A stored procedure can return a value-or even multiple result sets-but doesn’t have to.
  • You can use a UDF directly in a SELECT statement as well as in ORDER BY, WHERE, and FROM clauses, but you can’t use a stored procedure in a SELECT statement.
  • A UDF can’t use a nondeterministic function such as GETDATE(), NEWID(), or RAND(), whereas a stored procedure can use such functions. A nondeterministic function is one that can return a different result given the same input parameters.
  • A UDF can’t change server environment variables; a stored procedure can.
  • A UDF always stops execution of T-SQL code when an error occurs, whereas a stored procedure continues to the next instruction if you’ve used proper error handling code.

How to attach(add) .mdf file to sql server 2005/2008 Tuesday, Oct 12 2010 

By default  ASPNETDB will be automatically created under App_data directory in your application if personalization is use for the first time.If you wanted to locate ASPNETDB file in your Own Database (SQL Sevrer 2005) then just simply do the following

-Copy the ASPNETDB that is located at the App_data Folder in your Apps and paste it anywhere in your drives.. the after copying
-Remove the ASPNETDB in your App_Data folder
-Open SqlServer 2005 or Studio Management Express

<connectionStrings>

<remove name=”LocalSqlServer” />

<add name=”LocalSqlServer” connectionString=”Data Source= YOUR SERVER NAME;Initial Catalog=ASPNET;Integrated Security=True;MultipleActiveResultSets=False;Packet Size=4096;Application Name=&quot;Microsoft SQL Server Management Studio Express&quot;”

providerName=”System.Data.SqlClient” />

</connectionStrings>


-Right Click on Database Folder then select Attach
-Click Add then Browse the ASPNETDB that you have just copied earlier
-You are done 🙂

In your WebConfig File point the database “ASPNETDB” that was added in your SQL Sever to create a connection.. See below set up:

Source: http://geekswithblogs.net/dotNETvinz/archive/2009/02/23/adding-aspnetdb.mdf-file-to-sql-server-2005.aspx

How To Convert Database of SQL Server 2008 to SQL Server 2005? Tuesday, Oct 12 2010 

Requirements

If you are trying to restore database backup of SQL Server 2008 to SQL Server 2005, you are bound to fail. Database backup of SQL Server 2008 is not compatible backward, you cannot restore it to SQL Server 2005. The following is a solution to convert databases of SQL Server 2008 to 2005

Step by Step Guide

1) Start convert wizard

Open SQL Server Management Studio2008. in ‘Object Explorer’, right click the database that you want to convert. Select ‘Tasks’ > ‘Generate Scripts…’.

Change Hyper-V Default Folders Step 1

2) Next

Click ‘Next’.

Change Hyper-V Default Folders Step 1

3) Select database and objects

Select the database that you want to convert, and check on ‘Scripts all objects in the selected databases

Change Hyper-V Default Folders Step 2

4) Convert Options

Set options:

'Script for Server Version' = 'SQL Server 2005'
'Script Data' = 'True'
'Scirpt Database Create' = 'True'

 

Change Hyper-V Default Folders Step 2

5) Output Option

Select option ‘Script to file’, ‘Single file’ and ‘Unicode text’.

Change Hyper-V Default Folders Step 1

6) ‘Finish’

View summary and click ‘Finish’.

Change Hyper-V Default Folders Step 1

7) Result

Now you got a complete database creation script with data. It can be executed on target database server.

 

Change Hyper-V Default Folders Step 1

8) Amend Script

Open the generated script in SQL Server Management Studio 2005. Find the following section and amend the path to proper data folder

    CREATE DATABASE [StockTraderDB] ON  PRIMARY 
( NAME = N'StockTraderDB', 
FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\StockTraderDB.mdf ,
 SIZE = 4352KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'StockTraderDB_log', 
FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\StockTraderDB_log.LDF',
 SIZE = 6272KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

9) Execute the Script

When finished, You should get converted database of SQL Server 2005

Source:  http://www.hyper-v-mart.com/HowTo/Convert_SQL_Server_2008_to_SQL_Server_2005.aspx

Next Page »