SQLServerPedia

Q:  How can I connect SQL server 2005 to DB2 in As400?

Jason Hall says:  On your SQL Server machine you should create an ODBC data source that connects to your DB2 instance. Once the datasource exists you should create a linked server inside of SQL Server. This linked server will allow you to run queries on your DB2 instance from within the SQL Server query engine. For more information you should look up “Linked Servers” in SQL Server 2005 books online.

Technorati Tags:
linked servers
SQL Server BOL

Add to: | Technorati | Digg | del.icio.us | Yahoo | reddit |

Q: Learning SQL Server Stan, I am fairly new to SQL Server. Do you have any recommendations on books that will help me learn this platform and can i get this books, because have tried so many ways for me to get some text book but all prove abortive?

Jason Hall says:  I’ve always liked the Solid Quality Learning books. They have a good beginners book on SQL Server 2005 called Microsoft SQL Server 2005 Database Essentials Step by Step that I’ve heard good things about. Here is a link to the Barnes and Noble page below.

http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?z=y&EAN=978073562

Technorati Tags:
SQL Server, database essentials, Solid Quality Learning

Add to: | Technorati | Digg | del.icio.us | Yahoo | reddit |

Q:  What actually goes on behind the scenes for the different types of native SQL Backups? Ie If for example my backup drive and t-log drive are physically separate to the data drive will there be any adverse effect to data access times for the database data files.

Jason Hall says: In a perfect world you would want all three files on separate drives. For example you would have your data file on drive D:, log file on drive E:, and backup files on drive F:,

Even in this scenario, data access times might be slowed slightly to your data files during the backup as the backup will be causing read IO’s to the data files in addition to the IO’s that are occuring during normal database operation. In this separated scenario you are provided the least amount of IO contention during the backup time and both your database performance and backup performance will be optimized.

A backup is very straightforward, it simply reads blocks of data from your data/log files and writes those blocks out to whichever backup device you have specified.

Technorati Tags:
SQL Backups, T-Logs, database contention, backup performance

Add to: | Technorati | Digg | del.icio.us | Yahoo | reddit |

Q: How i can execute dynamic sql statement?

Kevin Kline says: Dynamic SQL, in and of itself, isn’t very hard. But doing it well is hard.

Rather than try to tell you all of the nuances of it myself, I encourage you to look at the website of MVP Erland Sommarskog. He’s done a lot of great work on the topic at http://www.sommarskog.se/dynamic_sql.html.

Technorati Tags:
Dynamic SQL, Erland Sommarskog

Add to: | Technorati | Digg | del.icio.us | Yahoo | reddit |

Q: how do i store an image to SQL server 2000 using Visual Basic 6 as the programming lanuage

Kevin Kline says: You should first ask youself “Should I even store image files inside of SQL Server 2000?” It’s not always as straight of a question as you might think. Check out the article at to help you decide whether to store images inside of the http://databases.aspfaq.com/database/should-i-store-images-in-the-database-or-the-filesystem.html database or on the file system. You’ll find some very nice programmatic examples at http://www.sqlteam.com/article/images-and-sql-server.

Technorati Tags:
Visual Basic, application programming, BLOB datatypes, GetImage, StoreImage, GetImage

Add to: | Technorati | Digg | del.icio.us | Yahoo | reddit |

Q: Our’s is a microsoft shop. Our products need database and we work with Sql server 2000 database.The prodcut is shipped to clients and clients need to have sql server database. Our products are data and memory intensive products. We do analysis on millions of records, with mutiple application processing engines. When multiple engines are running, they load data from database_A and process and then dump the data into database_B and run reports fron database_A and database_B. This process depending on data takes any where from 1hr to 15 days.During retrieving huge data sets from database, one or more analysis engines fails and we loose lot of time. Based on where the analysis is, we cannot recover the work and have to redo. Lot of time is spent on client performance and scalabilty issues. Generally database_A can grow upto 100G and database_B can grow upto 700G. All the databases are well normalized. How can we improve scalability and memory usage with Sql server?

Kevin Kline says: You haven’t given a full description of your application or environment, but the fact that you have even one report that can take 15 days to run on a 100GB database means that there are some starting performance problems in your application which, I would bet money, are due to poorly performing SQL statements and possibly poor database design.

Here are some broad tuning tips:

1. You mention that the database is well normalized. Normalization is especially important for OLTP applications with lots of inserts, updates, and deletes happening. If your database is used for lots of reporting and aggregations, then DENORMALIZE. Do this by pre-performing any summations or aggregations before running the reports. If your normalized table has a AMOUNT column, and you frequently pull AVG and SUM from that column, calculate and save those numbers in the table ahead of time, rather than in the reporting phase.

2. You are almost certainly getting a lot of thrash on TEMPDB, and possibly in your user databases. Be sure that they are each on their own disk subsystem and that transaction logs and database files are not sharing the same physical drive or RAID array.

3. Avoid cursors! They consume lots of additional memory and locking resources on SQL Server.

4. When using procedural code like Transact-SQL, include SET NOCOUNT ON into all of your procedures, functions and triggers. You’ll get at least a 30% boost in performance speed.

5. You don’t describe how your “analysis engine” interfaces with SQL Server. If possible, encapsulate as much of the analysis engine’s logic into stored procedures so that you get the advantage of caching.

After considering these tips, spend a few hours looking around http://www.SQL-Server-Performance.com. There are many, many more tips there.

Technorati Tags:
normalization, denormalization, T-SQL, cursors, SQL Performance, database tuning

Add to: | Technorati | Digg | del.icio.us | Yahoo | reddit |

Q: how to do the query “select * from tbl1 where tbl1.fname like ‘John’ and lname not in (select lname from tbl1 where ybirth>1975 and city=’London’)” without the subquery?

Kevin Kline says: This is a very simple SQL problem. The following query can be rewritten:

Select *
From tbl1
WHERE TBL1.fname LIKE ‘John’
AND lname NOT IN (SELECT lname
FROM tbl1
WHERE ybirth>1975 AND city=’London’)

As:

Select*
FROM tbl1
WHERE tble1.fname=’JOHN’
AND (ybirth <> 1975
AND city <> ‘LONDON’)

Note that you don’t need the LIKE operator since you didn’t use a wildcard operator in the name ‘JOHN’. Also note that ybirth must not be a datetime datatype or else the query would need to say”…AND ybirth <> ‘JAN-01-1975…’ and otherwise follow a proper datetime format.

Technorati Tags:
SQL, T-SQL

Add to: | Technorati | Digg | del.icio.us | Yahoo | reddit |

Q: I am actually .Net professional. I want to get around some Lakhs of records from SQL server 2005 by using ADO.Net.For this I want to use the Disconnected Data Access techniqe (i.e. Data set). My actual problem is using DataSet it is not possible to get that much amount of data, So please provide me a code of Stored procedure with which i want to do the Paging (each on of 500 Records).

Kevin Kline says: Hello, I’m assuming you mean “locks” here. 500 records is not in the least a large amount of records, so I’m surprised that ADO.Net won’t allow you to get and manipulate this amount of records. Since I’m not a ADO.Net programmer, I can’t give you any help with your ADO code. On the other hand, you should be able to ge the records you want without locking issues by issuing your SELECT statement using the NOLOCKS hint. Read about NOLOCKS in the SQL Server Books On-Line. I also like the article www.sql-server-performance.com/lock_contention_tamed_article.asp.

Technorati Tags:
.NET, ADO.NET programming, database locks, database contention

Add to: | Technorati | Digg | del.icio.us | reddit |

Q: My application generates log files rapidly – how do i truncate the size of the log in SQL server without affecting the data files. this will help me to space in the hard disk

SQL Stan says: If the application is not mission critical, then run the database in SIMPLE recovery mode. This will automatically truncate the log every minute or so. If the application is critical and you need to be able to recover from a disaster, then use BACKUP to clear the log at regular intervals, say every 15 or 30 minutes.

Technorati Tags:
database recovery, disaster planning, archive log

Add to: | Technorati | Digg | del.icio.us | Yahoo | reddit |

Q: How can you view SQL Server through the Internet?

SQL Stan says: Unfortunately, you didn’t tell me if you needed to expose the database to developers, end-users, or administrators. Their needs differ as do the means of exposing SQL Server over the internet to each different type of user. However, I’ll assume you’re a developer and you want to program an application that is exposed to end-users over the internet. In a situation like this, most people use ASP code. Read all about it at: http://support.microsoft.com/kb/169377.

Technorati Tags:
T-SQL, ASP code, browser-based application

Add to: | Technorati | Digg | del.icio.us | Yahoo | reddit |