Introduction to 3-Tier Architecture

Introduction

As a developer, the .NET framework and Visual Studio present many choices for choosing the right architecture, from placing the data access code directly in the UI through datasets and data source controls, to creating a data access layer that talks to the database, all the way to creating an n-tier architecture approach that consists of multiple layers, and use data-transfer objects to pass data back and forth.

If you’ve ever wondered why you should use layers and what the benefits are, this article is for you. This article delves into the use of layers and how they can benefit any application.

What is a Layer?

A layer is a reusable portion of code that performs a specific function. In the .NET environment, a layer is usually setup as a project that represents this specific function. This specific layer is in charge of working with other layers to perform some specific goal. In an application where the presentation layer needs to extract information from a backend database, the presentation would utilize a series of layers to retrieve the data, rather than having the database calls embedded directly within itself. Let’s briefly look at the latter situation first.

Two-Tier Architecture

When the .NET 2.0 framework became available to the world, there were some neat features that allowed the developer to connect the framework’s GUI controls directly to the database. This approach is very handy when rapidly developing applications. However, it’s not always favorable to embed all of the business logic and data access code directly in the web site, for several reasons:

• Putting all of the code in the web site (business logic and data access) can make the application harder to maintain and understand.
• Reusing database queries in the presentation layer often isn’t done, because of the typical data source control setup in the ASP.NET framework.
• Relying on the data source controls can make debugging more difficult, often due to vague error messages.

So in looking for an alternative, we can separate the data access code and business logic into separate “layers”, which we’ll discuss next.

The Data Layer

The key component to most applications is the data. The data has to be served to the presentation layer somehow. The data layer is a separate component (often setup as a separate single or group of projects in a .NET solution), whose sole purpose is to serve up the data from the database and return it to the caller. Through this approach, data can be logically reused, meaning that a portion of an application reusing the same query can make a call to one data layer method, instead of embedding the query multiple times. This is generally more maintainable.

But the question is how is the data returned? Multiple frameworks employ different techniques, and below is a summary:

• ADO.NET – Built into the .NET framework, ADO.NET contains a mechanism to query data out of the database and return it to the caller in a connected or disconnected fashion. This is the most common approach to working with data, because it’s already readily available. See more at: http://en.wikipedia.org/wiki/ADO.NET.
• Table Adapters/Strongly-Typed Datasets – Strongly-typed datasets and table adapters provide a similar means to querying the data through ADO.NET, but add strong-typing features, meaning custom objects are generated for you to work with. See more here.
• Enterprise Library – Enterprise library Data Access Application Block provides a flexible way to connect to databases of multiple types, without having to know anything about that database, through an abstract approach. See more at: http://msdn2.microsoft.com/en-us/magazine/cc188705.aspx (read part one first).
• LINQ-to-SQL – LINQ to SQL is an ORM tool that uses a DataContext object as the central point to query data from the database. See more here. (read parts one through eight first).
• Auto-Generated Code – Tools like CodeSmith Studio automatically generate the code for you based upon a database schema. Simply writing a script to output the code you want to use and the backend is generated in a short amount of time. See more at: http://community.codesmithtools.c om/blogs/tutorials/archive/2006/02/13/nettiers.aspx.

Most (if not all) options above take advantage of the CRUD (create, read, update, or delete) operations that databases support, so all of that is available as shown above. There are plenty of resources online to help you get started. To see an overview of some of the options, please read this.

Business Layer

Though a web site could talk to the data access layer directly, it usually goes through another layer called the business layer. The business layer is vital in that it validates the input conditions before calling a method from the data layer. This ensures the data input is correct before proceeding, and can often ensure that the outputs are correct as well. This validation of input is called business rules, meaning the rules that the business layer uses to make “judgments” about the data.

However, business rules don’t only apply to data validation; these rules apply to any calculations or any other action that takes place in the business layer. Normally, it’s best to put as much logic as possible in the business layer, which makes this logic reusable across applications.

One of the best reasons for reusing logic is that applications that start off small usually grow in functionality. For instance, a company begins to develop a web site, and as they realize their business needs, they later decide to add a smart client application and windows service to supplement the web site. The business layer helps move logic to a central layer for “maximum reusability.”

Presentation Layer

The ASP.NET web site or windows forms application (the UI for the project) is called the presentation layer. The presentation layer is the most important layer simply because it’s the one that everyone sees and uses. Even with a well structured business and data layer, if the presentation layer is designed poorly, this gives the users a poor view of the system.

It’s best to remove as much business logic out of the UI and into the business layer. This usually involves more code, but in my mind, the excess time (which ranges from minimal to moderate, depending on the size of the application) pays off in the end.

However, a well-architected system leaves another question: how do you display it in an ASP.NET or windows application? This can be more of a problem in ASP.NET, as the controls are more limited to the type of inputs they can receive. If you use certain architectures, like passing datasets from the data to the presentation layer, this isn’t as much of a challenge; however, the challenge can come with business objects that support drill-through business object references.

Why Separating Logic Is Useful

You may wonder why it is important to move as much logic outside the presentation layer and into the business layer. The biggest reason is reuse: logic placed in a business layer increases the reusability of an application. As applications grow, applications often grow into other realms. Applications may start out as a web application, but some of the functionality may later be moved to a smart client application. Portions of an application may be split between a web site and a web or windows service that runs on a server. In addition, keeping logic helps aid in developing a good design (sometimes code can get sloppier in the UI).

However, there are some caveats to this: it takes a little longer to develop applications when most of the logic resides in the business layer. The reason is this often involves creating several sets of objects (data layer and access code, plus business objects) rather than embedding it in the application. The extra time that it takes to do this can be a turnoff for some managers and project leads, especially because it often requires you to be knowledgeable about object-oriented programming, more than most people are comfortable with.

Although embedding code in the UI is easier, in most cases I don’t believe it’s the best approach. A layered approach is often a better approach because it pays dividends down the road. This is because as more and more code is developed, the following happens:

• Code is copied and pasted frequently, or code is reused in classes that could easily be moved to a business layer.
• Code that is very similar is often copied and pasted with slight modification, making duplication harder to track down.
• It’s harder to maintain; even though applications with business objects are larger applications, they usually are structured better.
• Code is harder to unit test, if unit testing is available at all. Web applications and windows forms projects are hard to use unit testing with.

A good architecture is often harder to implement, but is easier to maintain because it often reduces the volume of code. This means that hours spent supporting an application are reduced.

Distributed Applications

Using a separation of layers can aid in development of distributed applications. Because the code is broken up into layers, a layer that facilitates the use of remoting or web services can be added to the project, with a minimal amount of work.

Development Techniques

When developing a business object architecture, it’s good to know about the many design patterns that are out there. There are many websites, blogs, and books related to the subject of design patterns. One of the more well-known books on the subject is titled “Design Patterns,” whom the authors are often referred to as the Gang of Four.

Another useful development technique is called Refactoring, or improving the quality of your code by making small changes to the way it works. This involves moving code into a method, or moving a method from one object to another, in a systematic, logical way. Martin Fowler has written a great book on this subject, called “Refactoring, Improving the Design of Existing Code.” There are plenty of books on the subject; this one is the source that helped me to understand refactoring the most.

There are also tools on the market that can help you refactor in a faster way. One of those tools is Resharper by Jet Brains, which looks for a lot of code patterns and refactors them in a way that is useful. Some of the other refactoring tools that I heard about are Refactor Pro by DevExpress (free for VB.NET and ASP.NET), Visual Assist X by Whole Tomato Software, and Just Code by OmniCore.

Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance

There are three major theoretical differences between temporary tables:

create table #T (…)

And table variables:

declare @T table (…)

The first difference is that transaction logs are not recorded for the table variables. Hence, they are out of scope of the transaction mechanism, as is clearly visible from this example:

create table #T (s varchar(128))
declare @T table (s varchar(128))
insert into #T select ‘old value #’
insert into @T select ‘old value @’
begin transaction
     update #T set s=’new value #’
     update @T set s=’new value @’
rollback transaction
select * from #T
select * from @T

s
—————
old value #

s
—————
new value @

After declaring our temporary table #T and our table-variable @T, we assign each one with the same “old value” string. Then, we begin a transaction that updates their contents. At this point, both will now contain the same “new value” string. But when we rollback the transaction, as you can see, the table-variable @T retained its value instead of reverting back to the “old value” string. This happened because, even though the table-variable was updated within the transaction, it is not a part of the transaction itself.

The second major difference is that any procedure with a temporary table cannot be pre-compiled, while an execution plan of procedures with table variables can be statically compiled in advance. Pre-compiling a script gives a major advantage to its speed of execution. This advantage can be dramatic for long procedures, where recompilation can be too pricy.

Finally, table variables exist only in the same scope as variables. Contrary to the temporary tables, they are not visible in inner stored procedures and in exec(string) statements. Also, they cannot be used in an insert/exec statement.

But let’s compare both in terms of performance.

At first, we prepare a test table with 1 million records:

create table NUM (n int primary key, s varchar(128))
GO
set nocount on
declare @n int
set @n=1000000
while @n>0 begin
     insert into NUM
          select @n,’Value: ‘+convert(varchar,@n)
     set @n=@n-1
     end
GO

Now we prepare our test procedure T1:

create procedure T1
     @total int
as
     create table #T (n int, s varchar(128))
     insert into #T select n,s from NUM
          where n%100>0 and n<=@total
     declare @res varchar(128)
     select @res=max(s) from NUM
          where n<=@total and
               not exists(select * from #T
               where #T.n=NUM.n)
GO

Called with a parameter, which we will vary from 10, 100, 1,000, 10,000, 100,000 up to 1,000,000, it copies the given number of records into a temporary table (with some exceptions, as it skips records where n is divisible by 100), and then finds a max(s) of such missing records. Of course, the more records we give, the longer the execution is.

To measure the execution time precisely, I use the code:

declare @t1 datetime, @n int

set @t1=getdate()
set @n=100 – (**)
while @n>0 begin
     exec T1 1000 – (*)
     set @n=@n-1 end
select datediff(ms,@t1,getdate())
GO

(*) The parameter to our procedure is varied from 10 to 1,000,000.

(**) If an execution time is too short, I repeat the same loop 10 or 100 times.

I run the code several times to get a result of a “warm” execution.

The results can be found in Table 1 below.

Now let’s try to improve our stored procedure by adding a primary key to the temporary table:

create procedure T2

     @total int
as
     create table #T (n int primary key, s varchar(128))
     insert into #T select n,s from NUM
          where n%100>0 and n<=@total
     declare @res varchar(128)
     select @res=max(s) from NUM
          where n<=@total and
               not exists(select * from #T
               where #T.n=NUM.n)
GO

Then, let’s create a third one. With a clustered index, it works much better. But let’s create the index AFTER we insert data into the temporary table—usually, it is better:

create procedure T3
     @total int
as
     create table #T (n int, s varchar(128))
     insert into #T select n,s from NUM
          where n%100>0 and n<=@total
     create clustered index Tind on #T (n)
     declare @res varchar(128)
     select @res=max(s) from NUM
          where n<=@total and
               not exists(select * from #T
               where #T.n=NUM.n)
GO

Surprise! Large amounts of data take longer; merely adding 10 records takes an additional 13 milliseconds. The problem is that “create index” statements force SQL server to recompile stored procedures, and slows down the execution significantly.

Now let’s try the same using table variables:

create procedure V1
     @total int
as
     declare @V table (n int, s varchar(128))
     insert into @V select n,s from NUM
          where n%100>0 and n<=@total
     declare @res varchar(128)
     select @res=max(s) from NUM
          where n<=@total and
               not exists(select * from @V V
               where V.n=NUM.n)
GO

To our surprise, this version is not significantly faster than the version with the temporary table. This is a result of a special optimization SQL server has for the create table #T statements in the very beginning of a stored procedure. For the whole range of values, V1 works better or the same as T1.

Now let’s try the same with a primary key:

create procedure V2
     @total int
as
     declare @V table (n int primary key, s varchar(128))
     insert into @V select n,s from NUM
          where n%100>0 and n<=@total
     declare @res varchar(128)
     select @res=max(s) from NUM
          where n<=@total and
               not exists(select * from @V V
               where V.n=NUM.n)
GO

The result is much better, but T2 outruns this version.

Records

T1

T2

T3

V1

V2

Table 1: Using SQL Server 2000, time in ms.

But the real shock is when you try the same on SQL Server 2005:

N

T1

T2

T3

V1

V2

Table 2: Using SQL Server 2005 (time in ms).

In some cases, SQL 2005 was much faster then SQL 2000 (marked with green). But in many cases, especially with huge amounts of data, procedures that used table variables took much longer (highlighted with red). In four cases, I even gave up waiting.

Conclusion

1. There is no universal rule for when and where to use temporary tables or table variables. Try them both and experiment.
2. In your tests, verify both sides of the spectrum—small numbers of records and huge data sets.
3. Be careful with migrating to SQL 2005 when you use complicated logic in your stored procedures. The same code can run 10-100 times slower on SQL server 2005!

SQL Server: Check Whether All Characters In a String Are in Uppercase or Not

When I was developing a small application, I had to write a Microsoft SQL Server script to check whether all the characters in a given string are uppercase alphabets or not.

Here is the Microsoft SQL Server function code which performs the check. This code is compatible with Microsoft SQL Server 2000 and Microsoft SQL Server 2005.

---

/****************************************************************

 * Purpose: Check whether all characters

 *         in a string are capital alphabets or or not

 * Parameter: input string

 * Output: 0 – on success; 1 on failure

 ****************************************************************/

CREATE FUNCTION fnChkAllCaps(@P_String VARCHAR(500))

RETURNS BIT

AS

BEGIN

 

DECLARE @V_RetValue BIT

DECLARE @V_Position INT

 

SET @V_Position = 1

SET @V_RetValue = 0   

 

–Loop through all the characters

WHILE @V_Position <= DATALENGTH(@P_String)

           AND @V_RetValue = 0

BEGIN

 

     –Check if ascii value of the character is between 65 & 90

     –Note: Ascii value of A is 65 and Z is 90

     IF ASCII(SUBSTRING(@P_String, @V_Position, 1))

            BETWEEN 65 AND 90

        SELECT @V_RetValue = 0

    ELSE

       SELECT @V_RetValue = 1      

   –Move to next character       

   SET @V_Position = @V_Position + 1

END

 

–Return the value

RETURN @V_RetValue

 

END

---

Sample code to test the function

---

SELECT dbo.fnChkAllCaps(‘TECHTHOUGHTS’) – Returns 0

GO

SELECT dbo.fnChkAllCaps(‘TechThoughts’) – Returns 1

GO

---

The above function iterates through all the characters of a given input string and checks whether ASCII value of the characters is between 65 and 90 to verify it is an uppercase alphabet or not.

You might be wondering why is ASCII values check is between 65 and 90. It is because the ASCII value of uppercase A is 65 and uppercase Z is 90.

Soon I’ll rewrite the same code using SQL Server 2005 CLR functions and post it. I believe Microsoft SQL Server 2005 CLR functions perform this check very efficiently.

Why is SQL Server So Slow?

I’ve been asked this question a few times when looking into performance problems on a website, and the short answer is, it’s not. SQL Server is a very efficient database.

It’s important to remember that a database is just a runtime for your SQL code. It’s just doing what you ask it to do, nothing more, nothing less.

This applies to any database. MySQL, PostgreSQL, maybe even Oracle are all just agents that do your bidding. But I’m most familiar with SQL Server’s tools for finding and fixing some common problems, so that’s what I’m going to talk about here.Typically what happens is something like this:

• You develop an application.
• You test it. It works okay.
• You add some test data and test it with a “lot��? of rows. It still works okay.
• You deploy it.

Then, maybe a few weeks, maybe a few years later, you, or your successor, notices that the application or website’s response is slow, and it turns out, it’s the database’s fault. The conclusion is that the database is slow, and plans are made to replace it.

The problem with this scenario is that the database probably isn’t the problem. The performance characteristics of any of the major databases are all close enough that switching from one to the other without some optimizing in the process would not help. Do that same optimizing to the existing system and you won’t need to switch.

First off, how was the conclusion reached that the database is slow?

Both Windows and SQL Server give you some excellent tools for diagnosing your problem.

First, look at the CPU usage of the SQL Server process.

Either it will be very low, very high, or somewhere in between.

Ideally it will be somewhere in between, but generally low. If it’s pegged at 100% then you’re probably running a query that’s got all the data it needs in memory, and SQL Server is being forced to do something rather inefficient with it, like stepping through all the rows adding the value of some field for example. This can be a symptom of throwing RAM at the problem without optimizing, and will stop being a solution once the data outgrows memory again.

Often what you see, though, is that SQL Server’s CPU usage is very low, but the system’s paging activity is very high. This signifies that the system is starved for memory, and is swapping. SQL Server shows low CPU usage because it’s busy waiting for the disk to deliver some pages.

This screenshot shows a pretty good situation: Just a bit of paging activity going on. If you’re seeing hundreds of pages per second constantly, you’ve got a problem. Swapping will kill your server’s performance.

If you have a query like “select * from users where lastname = ‘bob’” and you don’t have an index on lastname, then what you’re doing is asking SQL Server to read every single row from table users and build a set of matching rows. This is called a table scan. You want to avoid table scans.

This is the #1 cause of SQL Server slowdown over time: Not having the right indexes.

When executing a table scan, the server needs to read every row of the table. This thrashes SQL Server’s cache, so that now other data cached for other queries has been tossed out and now those other queries have to hit the disk. This means even an occasional table scan is a bad thing.

Here’s how to gain a little visibility into what’s causing your problems. First off, launch the SQL Profiler.

This tool lets you spy on what’s going on inside your server. Create a new trace:

The defaults here are good. Hit Run.

While it’s running, SQL Profiler is gathering data on every query your server executes. Launch your application or visit your website and do some of the things that are causing the performance problems. This gives the profiler, and the next tool, the Index Tuning Wizard, some data to work with. When you’ve got a good data sample, stop the trace.

Before using the Index Tuning Wizard, take a look at the result data. The Reads column and the Duration column are two of the most useful: Reads tells you how many disk pages needed to be read to satisfy this query, and Duration tells you how long it took (in milliseconds).

The highlighted line read 296972 pages, and took 29 seconds. Looks like a problem, doesn’t it?

You can use this data to do some manual tweaking; often the problem is immediately obvious. But your next new friend, the Index Tuning Wizard, will help.

Save your current profile run as a Trace File (*.trc), and then on the Tools menu of the profiler, select the Index Tuning Wizard.

It’s best to select Thorough if you’re working with a fairly small data set.

Unless you’re looking for a very specific problem or have a huge data set, select all the tables. The more the wizard has to look at, the better.

With any luck, the next page will tell you that the Wizard is going to fix all your problems.

No such luck in this case: No recommendations were found. But lets look at another example in a different tool: Say you’ve seen one particular query in the Profiler that you’re suspicious of. Launch the Query Analyzer, enter the query, and bring up the Index Tuning Wizard from there.

When you launch the wizard this way, one of the options is to use the Query Analyzer selection as the query to tune indexes for. I typed in a query that’s going to cause a table scan:

select count(*) from eventlog where logportalname = 'bob'

The logproperties field of the eventlog table doesn’t have an index. Here’s what the tuning wizard gives me for this:

Now there’s a nice projected outcome: A 95% performance improvement! A few clicks and you can have SQL Server apply the changes for you, or create a scheduled job to apply the changes later (during a scheduled maintenance period, for example). You can apply the changes to a live database, but queries on the table being indexed will block until the operation is complete, which could take a while.

But back to the case where the wizard didn’t find anything that could help you. Another thing that often happens is that you’ve got a massive table because you wrote code to put things into it, but never to take anything out of it. You create a log table, or an errors table, or a hits table, write code to put things there, and forget about it.

If you see a query or an insert that’s hitting thousands of rows, count the rows in the table.

select count(*) from eventlog

If you see a few million rows, then that’s your problem.

When you insert into a table, there’s work to do, and the bigger the table, the more work. If your eventlog table has a few indexes on it, those need to be updated when you insert, and the indexes on a table with millions of rows are going to take more IO operations to update than inserting into the same table with only a few thousand rows.

The best way I’ve found to manage this is to use SQL Server’s Jobs to automatically clean things up at regular intervals. Check out the Jobs section of the Enterprise Manager:

A job is work SQL Server will do for you at scheduled times. Create a new job.

This is a sample job. It’s got the 3 steps listed that it’s going to take, one after the other, that will drop old data from a database. A job step is just some SQL to execute:

If you’re going to have multiple steps for a job, don’t forget to go to the Advanced tab and set up the job actions – each job should go to the next job when it’s done, except the last one, which should report success.

Set up the job to run at some regular interval, and that’s it – you’ve guaranteed that your table isn’t going to grow unbounded until it starts causing you problems again.

There you go – Hope all this helps! If you have any other SQL Server optimization tips please post

SQL Optimization Tips

• Use views and stored procedures instead of heavy-duty queries.
This can reduce network traffic, because your client will send to
server only stored procedure or view name (perhaps with some
parameters) instead of large heavy-duty queries text. This can be used
to facilitate permission management also, because you can restrict
user access to table columns they should not see.

• Try to use constraints instead of triggers, whenever possible.
Constraints are much more efficient than triggers and can boost
performance. So, you should use constraints instead of triggers,
whenever possible.

• Use table variables instead of temporary tables.
Table variables require less locking and logging resources than
temporary tables, so table variables should be used whenever possible.
The table variables are available in SQL Server 2000 only.

• Try to use UNION ALL statement instead of UNION, whenever possible.
The UNION ALL statement is much faster than UNION, because UNION ALL
statement does not look for duplicate rows, and UNION statement does
look for duplicate rows, whether or not they exist.

• Try to avoid using the DISTINCT clause, whenever possible.
Because using the DISTINCT clause will result in some performance
degradation, you should use this clause only when it is necessary.

• Try to avoid using SQL Server cursors, whenever possible.
SQL Server cursors can result in some performance degradation in
comparison with select statements. Try to use correlated sub-query or
derived tables, if you need to perform row-by-row operations.

• Try to avoid the HAVING clause, whenever possible.
The HAVING clause is used to restrict the result set returned by the
GROUP BY clause. When you use GROUP BY with the HAVING clause, the
GROUP BY clause divides the rows into sets of grouped rows and
aggregates their values, and then the HAVING clause eliminates
undesired aggregated groups. In many cases, you can write your select
statement so, that it will contain only WHERE and GROUP BY clauses
without HAVING clause. This can improve the performance of your query.

• If you need to return the total table’s row count, you can use
alternative way instead of SELECT COUNT(*) statement.
Because SELECT COUNT(*) statement make a full table scan to return the
total table’s row count, it can take very many time for the large
table. There is another way to determine the total row count in a
table. You can use sysindexes system table, in this case. There is
ROWS column in the sysindexes table. This column contains the total
row count for each table in your database. So, you can use the
following select statement instead of SELECT COUNT(*): SELECT rows
FROM sysindexes WHERE id = OBJECT_ID(‘table_name’) AND indid < 2 So,
you can improve the speed of such queries in several times.

• Include SET NOCOUNT ON statement into your stored procedures to stop
the message indicating the number of rows affected by a T-SQL statement.
This can reduce network traffic, because your client will not receive
the message indicating the number of rows affected by a T-SQL statement.

• Try to restrict the queries result set by using the WHERE clause.
This can results in good performance benefits, because SQL Server will
return to client only particular rows, not all rows from the table(s).
This can reduce network traffic and boost the overall performance of
the query.

• Use the select statements with TOP keyword or the SET ROWCOUNT
statement, if you need to return only the first n rows.
This can improve performance of your queries, because the smaller
result set will be returned. This can also reduce the traffic between
the server and the clients.

• Try to restrict the queries result set by returning only the
particular columns from the table, not all table’s columns.
This can results in good performance benefits, because SQL Server will
return to client only particular columns, not all table’s columns.
This can reduce network traffic and boost the overall performance of
the query.
1.Indexes
2.avoid more number of triggers on the table
3.unnecessary complicated joins
4.correct use of Group by clause with the select list
5 In worst cases Denormalization

Index Optimization tips

• Every index increases the time in takes to perform INSERTS, UPDATES
and DELETES, so the number of indexes should not be very much. Try to
use maximum 4-5 indexes on one table, not more. If you have read-only
table, then the number of indexes may be increased.

• Keep your indexes as narrow as possible. This reduces the size of
the index and reduces the number of reads required to read the index.

• Try to create indexes on columns that have integer values rather
than character values.

• If you create a composite (multi-column) index, the order of the
columns in the key are very important. Try to order the columns in the
key as to enhance selectivity, with the most selective columns to the
leftmost of the key.

• If you want to join several tables, try to create surrogate integer
keys for this purpose and create indexes on their columns.

• Create surrogate integer primary key (identity for example) if your
table will not have many insert operations.

• Clustered indexes are more preferable than nonclustered, if you need
to select by a range of values or you need to sort results set with
GROUP BY or ORDER BY.

• If your application will be performing the same query over and over
on the same table, consider creating a covering index on the table.

• You can use the SQL Server Profiler Create Trace Wizard with
“Identify Scans of Large Tables” trace to determine which tables in
your database may need indexes. This trace will show which tables are
being scanned by queries instead of using an index.

• You can use sp_MSforeachtable undocumented stored procedure to
rebuild all indexes in your database. Try to schedule it to execute
during CPU idle time and slow production periods.
sp_MSforeachtable @command1=”print ‘?’ DBCC DBREINDEX (‘?’)”

Talking about Some SQL Queries

How to Run commands for all tables in current dB:

EXEC sp_MSforeachtable
@command1 = ‘DELETE FROM ? WHERE your_condition’

The ‘?’ will be replaced by the table name

——————————————————————————–

How to find Size of all user tables with the number of rows:

EXEC sp_MSforeachtable
@command1=’sp_spaceuse d “?”‘

——————————————————————————–

How to find Number of rows in a table and the space the table and
index use (in KB):

EXEC sp_MStablespace table_name

——————————————————————————–

How to display Size of the datafile and log separately:

EXEC sp_helpdb database_name

——————————————————————————–

How to List of all hard drives and the amount of free space in MB
for each drive:

EXEC master..xp_fixeddrives

——————————————————————————–

How to return all tables, which contain a specified column:

SELECT a.name ‘Table’
FROM sysobjects a
JOIN syscolumns b
ON a.id = b.id
WHERE a.type = ‘U’ AND
b.name = + ‘your_column’

——————————————————————————–

How to Delete a file from the server in SQL:

xp_cmdshell “cmd /c del <filename_with_path>“

——————————————————————————–

How to Control State Options of the Database:

ALTER DATABASE db_name
SET SINGLE_USER
WITH ROLLBACK AFTER x SECONDS

or

ALTER DATABASE db_name
SET RESTRICTED_USER
(or MULTI_USER)
WITH ROLLBACK IMMEDIATE

(Roll back transactions after the specified number of seconds or
immediately.)