Maellic (maellic2002@yahoo.fr) writes:
[quoted text, click to view]

The last issue could be address with sql_variant.

There are situations where this kind of solution is the only viable
way to go. As you say, the coding is more difficult, but only
initially. If new items comes each week, all you need to do is to
add some more data to the table. With the other solutions you have
run ALTER TABLE each week.

But if the structure is decently static, then this is a pain, so starting
with the second alternative is probably the best way to go, at least
as the list of objects to support is a closed set.




--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

[quoted text, click to view]

Artificial/surrogate keys are generally recommended when the natural
key contains too many columns. e.g. if a table has 10 columns and the
natural key consists of all 10 cols. If you make the 10 cols the PK,
you effectively doubled the space requirements for the table. As the
clustered index is now almost the same size as the data. Of course,
this is a limitation of the DB software. Future releases of MS Sql
Server might make this a moot point. As surrogate vs. natural keys

[quoted text, click to view]

Beware though, that to get all data from the main table joined with
all subtables, you'd need to use either OUTER JOIN or UNION. If you
try it with INNER JOIN, the result set will be empty.

[quoted text, click to view]

A stored procedure is basically a (usually small) program that is
stored on the server and executed on demand. It may do anything: show
data, change data, access other servers, drop or create database
objects, commit or rollback transactions or even shut down your
server. Some stored procedures return data, others don't.

A view always consists of one select statement. The SQL for that
statement is stored in the server as the view definition. Now, the
view can be used anywhere you'd normally use a table: you can select
all or part of the data from the view and in many cases, you can
insert, update and delete data through the view. Since the view has
it's own authorization settings, it's a useful method to allow people
access to some but not all data in a table. A common example is the
table with personnel data. The manager of the sales department is
entitled to see data of "his" employees, but nothing else. And even
for "his" employees, he is not entitled to see salary and some other
data. This can be done with a view:

CREATE VIEW Sales_Personnel AS
SELECT EmpID, FirstName, LastName, Dept, Address, HireDate
FROM Employees
WHERE Dept = 'Sales'
GO

You can now remove all rights of the Sales manager to the Employees
table but give him access rights to this view instead. This would
allow him to see only the columns mentioned of only the employees in
his own department.

Neither a view nor a stored procedure result in extra actual user data
being stored somewhere. Both are executed when needed to produce the
results from the tables used in either the stored procedure's code or
in the view definition.

[quoted text, click to view]

You can push any database over it's limit if you try hard enough, but
SQL Server really needs a whole lot of pushing before it's limits are
reached.

[quoted text, click to view]

My main argument against unneeded artifical keys is not avoiding joins
(though that can be a useful side effect - as well as improving
performance on lots of other queries as well), but user friendliness.
Why would you introduce a new naming scheme on a user if (s)he's
perfectly happy with the current naming scheme?

Suppose the French mail delivery service introduced artificial keys as
a replacement for city names. How would you like it if you could not
simply jot "Marseille" or "Paris" on an envelope but would have to
look up these names in a long list and write "18" or "1" on the
envelope instead?

[quoted text, click to view]

<soapbox>

Maybe they don't think. Or they think in old-fashioned ways. Or they
think that something that is really useful (sometimes even necessary)
in some cases and doesn't hurt *them* in other cases might as well be
used in all cases. Or -most probably- they do it because everybody
else does it.

It's a bit like starting all table names with "tbl", like you do. Why
do you do it? Are you afraid that when you get back to a working query
later, you might be in doubt whether the name after "from" would be a
table, a column or a database name?

</soapbox>

Best, Hugo
--

<snip>

[quoted text, click to view]

Can't you do the same with a stored procedure though ? Remove rights
from the table but give execute rights to the stored procedure?

If I understand you, there is no difference between a view and a
stored procedure that has a select statement - apart from maybe
different security settings ? Unless I'm getting confused: when I used
access, I could define queries. And a query could access another query
as if it was a table. I thought stored procedures on sql server were
just a more powerful kind of query. I thought you could actually
access a stored procedure as if it was a table, just like in access
you could access a query as if it was a table. Is that where I'm
getting confused ?

[quoted text, click to view]

I guess I'll find out, eh?

[quoted text, click to view]

Makes sense if you let the user see your keys but in my case, in all
databases I've developed, the keys were just an internal thing, the
user didn't need to see them.


[quoted text, click to view]

Good point. But there is a difference between me and these guys
though: I don't go telling people over newsgroups they SHOULD be using
names with "tbl". I know it's just a habit probably left over from uni
days and whenever I think about a table I think tbl*something. And
because it doesn't hurt, I'm not overly worried about using it or not
using it as long as I'm consistent. But I'm certainly not saying
that's it's a better way.

Maellic (maellic2002@yahoo.fr) writes:
[quoted text, click to view]

Well, it *could* happen, but SQL Server is an enterprise DBMS, and you
would have to be really mean with it for something like that to happen.
SQL Server can handle up to 256 tables in a query.

[quoted text, click to view]

There are natural keys and there are natural keys.

Say that you have a database about buying and seeling financial instruments.
Three concepts in such a database is customer, instrument and position.

How do we indentify a customer? Well, in many countries they have some
national registration number that all people have one of, could we use
that? But it happens that people change numbers, and you may foreigners
that don't have any. And some people feel that their integrity is at
stake with their number everywhere. So in the end, an articifical customer
number is better. Probably one that is generated so that it controls a
check digit, as this number is likely to be shown externally.

Instruments then? Well, all financial instruments are supposed to have
an ISINcode, a 12-character code with two letters (country code) and 10
digits. Perfect natural key? Nah, it happens that instruments changes
its code for some reason. More importantly, it turns out that not all
instruments have an ISIN-code. They might be temporary instruments as
part of a corporate action like a rights issue. But it could also be
any sort of goods that we for some reason like to register. So again,
we need an artificial key. But in this case, we can keep that code
internally, because instruments can be decently identified by the users
by their names. (In fact, in our system there has to be a unique name).

Finally a position. If customer 4711 buys 30 MSFT, that gives us a
position. What is the natural key for that position? CustomerNumber and
InstrumentID of course. That is, while artificial keys in itself,
they can still be natural keys when you model some composite object.
Having a PositionID as an artificial key would be bad design.

Occasionally, when the natural key has many columns, and you still
need to refer to this table, by yet a subtable, adding a shortcut
key may be a good idea. But in such case, don't forget to add a
UNIQUE constraint for the real key.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

[quoted text, click to view]

<snip>
[quoted text, click to view]

You can do that with a stored procedure, but you'll lack the
flexibility of a view.


[quoted text, click to view]

The stored procedure canm only be executed. The view can be used in
another query to select only rows meeting specified criteria from the
result set. And a view can (in most cases) be updated as if it were a
regular table.

[quoted text, click to view]

Yes. What Access calls a query is what every other database (some
would write "every database") calls a view. A stored procedure is
roughly equivalent to what Access calls a module.

Best, Hugo
--