Auto Incrementing Primary Keys in SQL Server 2005

Question

Post reply

Auto Incrementing Primary Keys in SQL Server 2005

mmgorman

Valued Member

Points: 58
More actions
April 30, 2008 at 9:58 pm

#95919

Hi everybody:
I have a database built through Mimer. It's a DBMS from Sweden. A user asked how difficult would it be to have that database run under SQL Server 2005. I told him that there are three requirements.
1. Automatically enforced Referential Integrity... NOT through triggers.
2. Auto-incrementing Primary Keys via SQL DDL syntax, NOT through triggers.
3. Schema Information Tables where there is one Schema Information Table that contains the ODBC data set names.
Are all these in SQL Server 2005?
Regards,
Mike Gorman

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

1 & 2, definately.

I'm not sure I understand what #3 is.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

mmgorman Valued Member Points: 58 More actions · Answer 2

Thanks for the reply. I hope to be creating a MS/SQL version of our Metadata Management System, Metabase by the end of May... I have a lead on the Schema Information Tables stuff. Basically it's the set of tables that MS/SQL 2005 would use to store information about the database (not DBMS) itself such as the names of the tables, columns, etc. In short, not the "real data" but the database's metadata.

Regards, and thanks so much for your quick response....

Mike Gorman

P.S.

If anybody wants to learn more about the Metabase System, go to http://www.wiscorp.com.

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

Oh, then yes, the data describing the databases is stored in a series of views called dynamic management views. So that's yes to all three.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

mmgorman Valued Member Points: 58 More actions · Answer 4

Those therefor must be their Schema Information Tables.

Thanks,

Mike G

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 5

Yes, but starting with SQL Server 2005, the actual tables were hidden and all access to them was controlled through dynamic management views. Some of the views look exactly like the tables in SQL Server 2000 and earlier so some people might not be spotting the difference.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

You can also get this information through the ANSI-standard INFORMATION_SCHEMA views provided by SQL Server. All of these are read-only, of course.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

GSquared SSC Guru Points: 260824 More actions · Answer 7

mmgorman (4/30/2008)
Hi everybody:
I have a database built through Mimer. It's a DBMS from Sweden. A user asked how difficult would it be to have that database run under SQL Server 2005. I told him that there are three requirements.
1. Automatically enforced Referential Integrity... NOT through triggers.
2. Auto-incrementing Primary Keys via SQL DDL syntax, NOT through triggers.
3. Schema Information Tables where there is one Schema Information Table that contains the ODBC data set names.
Are all these in SQL Server 2005?
Regards,
Mike Gorman

Definitely yes on all three. And the first two are dead easy to use. (Last one, ease of use depends on what you intend to do with the data. Some easy, some more complex.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon