Recomendations, Tips, Hints etc To consider when createing a SQL Server Database

[Kyle Klaus]

Hey Everybody,

I am working on re-creating a fairly weak Microsoft Access Application using
Visual Basic .NET and SQL server 7.0

basically the Access Database has been a Frankenstein project for a couple
years, so I'm looking to more or less just start over, starting with the
Database. What I'm looking for is any hints and tips that anybody has for
designing the database. I pretty much know all the tables I need. but I do
have a question about the keys for the tables.

reading another post in here from Marina, he mentioned: "I myself prefer
GUID's, because for inserting purposes, etc, they are easier to use since
you can create them on the client, and still know they will be unique." Now
I see that I can create an field in SQL Server that will use the value of
the row GUID.. is that what he meant, or did he mean that the key would
simply be a char (or similar) datatype large enough to hold a GUID and have
the client generate it using some utility?

Is that really beneficial.. I will have multiple people accessing the
database, about 10, but I doubt the concurrent users will ever be more than
2 or 3.

I also have a question about using types. Should I check the data strongly
before putting it into the database (so that the database is typed to
basicly hold anything you thow at it), or should I strongly type the
database and then catch errors if they occur?

Thanks Everybody, I'm just starting out with SQL Server, but I do have a
basicly understanding of it, and SQL in general

 

[William Ryan]

You can default a column to GUID and when an insert is
done, you get a number you know will be unique. You can
also generate these client side if you wish, but if you
do, you can't be guaranteed that the value will be unique
across all servers, particularly if you use replication.
In most instances, it's easier to just use it on the
server, but this depends on the entire situation.

BOTH! The way to get the fastest performance across the
board is strong typing. One the client side, use
parameters and explicitly declare their types whenever
possible ie cmd.Parameters.Add("@ParamName",
SqlDBType.SmallInt) for instance.

You want to use the smallest type possible on the DB b/c
it affects both size and performance. However, the fact
that you type everything rigidly on the DB should stop
you from using the same on the client. Sending a command
that will fail that could have been prevented with client
side typing is not a good practice in general, unless you
have a strong reason to let the db reject values (and
I've not seen any real instances although I've heard many
claims to the contrary).

If you can, used Strongly Typed Datasets b/c they are
much more efficient than non typed ones, and you get
intellisense. And if possible, use numeric indexes when
referencing tables, rows etc particulary with column
names. If you want the clarity, use the getordinal
method beforehand and use it, otherwise, teh compiler
will have to resolve the string name on each row read,
which isn't efficient.

If you are just learning ADO.NET, I HIGHLY recommend
ADO.NET core reference by David Sceppa/MS PRess. It's a
phenomenal book IMHO.

If you have specific questions, I've made just about
every mistake you can make and I'll be glad to help where
I can.

Just take the nospam out of my email address.

Good Luck,

Bill

 

[Kyle Klaus]

Thanks William, I appreciate your help. I'll look into that book, I've
actually been eye-balling it for a couple days now. Sort of off topic for
this newgroup, but can anybody recomend some of the other books in that
group? Like 'Programming Microsoft Visual Basic .NET (Core Refrence)', or
'Coding Techniques for Microsoft Visual Basic .NET'

Just asking because Amazon normally will offer them in pairs for a lower
price (not always by much though)

Thanks again William

 

[Michael Lang]

Correction:
from .NET help
"A GUID is a 128-bit integer (16 bytes) that can be used across all
computers and networks wherever a unique identifier is required. Such an
identifier has a very low probability of being duplicated."

Go research the probability of duplication. It is practically nil.
http://www.devx.com/dbzone/Article/10167
"The GUID datatype can assist with a number of database challenges:
Global databases that must guarantee unique key values across many servers
Replicated databases that must guarantee unique key values across many
servers
Large databases that have outgrown incremental primary key values
Companies faced with combining database files where primary key values are
duplicated "

Of coase GUID this doesn't make GUID the best in all cases... just read more
from the article above...

"In most situations, changing an auto-incrementing primary to a GUID has no
advantage, only the following disadvantages:
GUID values are up to four times larger than the alternatives (Access'
AutoNumber and SQL Server's Identity columns are Long Integer datatypes), so
GUIDs require more overhead.
GUID values take longer to search and sort. Consequently, a GUID primary key
suffers a performance penalty that cascades throughout the database.
The number of characters in and the awkward composition of the actual GUID
values themselves make them hard to remember or work with directly, although
neither is really necessary when used as a primary key value."

The same article summarizes with:

"The GUID datatype is here to stay. Its presence in Access and SQL Server
makes both products viable systems for the largest of projects, even if most
of us never use it. Fortunately, the technology behind the GUID datatype is
simple to understand and implement-just being aware of it and its
functionality opens the door to easier solutions. Not many database
developers hit the 2-billion-record ceiling, but if it could happen to you,
design for success with GUIDs. "

To create a unique GUID in .NET use the "NewGuid()" method of the Guid
structure.