btaylor@sswug.org
SSWUG on Facebook
Got a question on databases or development platforms? Ask on our Facebook Wall & we’ll answer on our SelecTViews program:
Question and Answer – Group By
Julie Asks:
I have 4 records that are written into [a temp] table. Of the 4 records all columns contain identical data with the exception of 5 of them.
I am running a group by query excluding 4 of the unique columns. The fifth is included using a MAX aggregate. I expect to get 1 record. My query returns 2 records instead. What could cause this?
Julie:
Most likely one of the other columns (not the 5 not included in the group by) is actually different, resulting in the second record.
I’m making this assumption because the question you are describing would mean that SQL Server doesn’t do a group by correctly, which we know to not be the case.
I have experienced this in the past when using fixed length character data, binary data, or variable length character data. Here are some ways this problem expresses itself:
- There can be differences in the data that is not visible to the end user, such as a different number of leading or trailing spaces.
- There are sometimes special, un-printable, characters embedded in the data
- Binary data converted to string data may look the same, but may not be, because binary data can include non-printable characters
- You are using case specific columns, table, or SQL configuration. In this case the character B is not the same as the character b
Top Ten Voting
Sam and his team hear at SSWUG have completed the Online survey for the TOP (10) Worst Things to Do in a Database. Now is your chance to choose what you believe to be the worst Three. To give you plenty of time to make your selections, voting will continue until 10:00 PM, Pacific Time, Tuesday, May 10th. So, get your votes in by going to our Online Survey. Results will be published later on this week.
Trivia Question Answer – How Many Databases Can a Single Instance of SQL Server Mount
The answer from Microsoft is 32,767 (ish). They never used to publish a number. I had to know at one time how many databases could be mounted concurrently.
32,767 comes from the fact that the original SYSDATABASES table, the container for all databases for a SQL Server configuration, has an ID column for each database. The ID column is a SmallInt, max size of 32,767. Interesting that SYS.DATABASES, which replaces SYSDATABASES, has an INT datatype.
But the real answer, as many of you already know, is that mounting a database requires resources. I tested with SQL Server 2000. I actually created a test about 6 years ago to see how many databases I could mount concurrently. I did find an esoteric article on TechNet telling me how to configure SQL Server with a startup flag, allowing me to mount the maximum number of databases. Even then, there was no real guidance for planning.
I ran my test on a server with 10 gig of ram, not doing anything in the databases other than mounting them, I was surprised I was able to mount on an average 3,000 databases, and that not consistently. That means I was simply mounting the databases, no queries were being exercised against the databases.
Another trivial note, the test databases were bare bones. There were no tables or user accounts. Just an empty MDF and LDF file, copied to a new file name, and then attached. So we were not requiring resources to update statistics, or other such background tasks.
The true answer is once again, "It Depends." It depends on your hardware, version of SQL Server, the contents of the database, how much is required per database for different caches, and how many concurrent users.
With the movement toward virtual servers this is a good thing to keep in mind. A server does have constraints in total capacity. Once it starts swapping memory to disk, performance takes a nose dive.
Note that I have emphasized the requirement to have the databases mounted. You can have a lot more databases managed by a single instance of SQL Server if they are not actively mounted. The default on many versions of SQL Server is to keep a database open…not close it after the last connection is released. A closed database does not take up the same resources as an opened/mounted database. With Connection Pooling, it is possible that the last connection won’t be closed anyway.
Do you have any trivia questions you’d like to try out on our readers? Send your questions to me at btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
A Basic Introduction to Coding DB2 SQL for Performance (Part 1)
This article series is intended to give the basics of good SQL programming to application developers. Before delving into the specifics of coding SQL for performance, take a few moments to review SQL basics by reading this article.
Featured White Paper(s)
Upgrading to Microsoft SQL Server 2008 R2 from Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000
More than ever, organizations rely on data storage and analysis for business operations. Companies need the ability to deploy… (read more)