Specifying Schema Names - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Specifying Schema Names

Author

Topic

hismightiness
Posting Yak Master

164 Posts

Posted - 2004-09-20 : 12:47:33

I have just installed the SQLBPA, and I am in the process of getting everything that I want in compliance done. However, one of the items it checks for you is "Use of Schema Qualified Table/Views". It tells me, "One or more objects are referencing tables/views without specifying a schema! Performance and predictability of the application may be improved by specifying schema names."

I have 3 SQL books here, and I have to figure out what the schema name is that I put in front of the table name in my SPs, or anything. Perhaps I have been looking in the wrong places. Can someone help me here?

- - - -
- Will -
- - - -
http://www.servicerank.com/

Kristen
Test

22859 Posts

Posted - 2004-09-20 : 13:34:40

Prefix them with "dbo." - or are you doing that already?

Kristen

hismightiness
Posting Yak Master

164 Posts

Posted - 2004-09-20 : 13:52:27

Is that all??? I should've thought of that! No, I am not already doing that. :)

- - - -
- Will -
- - - -
http://www.servicerank.com/

hismightiness
Posting Yak Master

164 Posts

Posted - 2004-09-20 : 13:58:59

Wait! Nevermind... The very first one specified as "failed" has the dbo prefix. D'oh!

- - - -
- Will -
- - - -
http://www.servicerank.com/

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-20 : 14:01:34

It's a warning right?

Have you found any performance problems?

It may speed it up...but it also may limit you to do other things...like making it portable....

Brett

8-)

hismightiness
Posting Yak Master

164 Posts

Posted - 2004-09-20 : 14:03:30

Yes, it is a warning. However, I was wanting to make this as streamlined as possible. What do you mean by portable?

- - - -
- Will -
- - - -
http://www.servicerank.com/

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-20 : 14:09:41

Let's say you'd like to make another copy of the database on the same server...

If you dump and restore it, it'll be reference the original...not your copy....

Brett

8-)

hismightiness
Posting Yak Master

164 Posts

Posted - 2004-09-20 : 14:13:33

Ahhh... So the performance gain isn't necessarily worth it. Hmmm... I do have two databases where it would be worth it though. Other than using "dbo.procedurename", how would I get rid of this "warning"?

- - - -
- Will -
- - - -
http://www.servicerank.com/

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-20 : 14:17:33

Well how do you know that...I would say if there were a lot of databases on the machine, then yes.

Otherwise no.

Now I could be (and have been) wrong....

What else doe sit tell you? And where does it tell you this info?

I never used the best practices "wizard"....

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2004-09-20 : 14:22:49

I thought YOU were the best practices wizard ....

Kristen

hismightiness
Posting Yak Master

164 Posts

Posted - 2004-09-20 : 14:24:20

Well, two of my 10 databases are quite large and accessed very frequently through a handful of Apps (mostly web). Anything I can do now to improve performance going forward is essential. Other than what I pasted in my first post, for each SP, it states "Object has unqualified references."

- - - -
- Will -
- - - -
http://www.servicerank.com/

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-20 : 14:40:14

quote:
Originally posted by Kristen

I thought YOU were the best practices wizard ....

Kristen

And on Monday....Thanks...the sound you here are my associates cracking up....

Anyway....

I would say...that even if your databases are HUGE...the reason the unqualified message comes up is that it would have to determine where to "look" for the object.

This is a problem if you have the same object names across databases, or even with in the same database...yes it happens...I'm workong with a piece of dog sh-t legacy system, where the developers where "brilliant"

Let me ask you.

How many total table and view objects do ALL of the datbases on your server have?

Volume of data is a different issue.

For example what's the largest table you have?


CREATE table #TEMP (TBNAME sysname,ColCount int)
go

DECLARE @cmd varchar(255) 

DECLARE Space_csr CURSOR READ_ONLY FOR 
	SELECT 'INSERT INTO #TEMP SELECT '
		+''''+'['+TABLE_SCHEMA+']'
		+ '.'
		+'['+TABLE_NAME+']'+''''
		+',COUNT(*) FROM '
		+'['+TABLE_SCHEMA+']'
		+'.'
		+'['+TABLE_NAME+']'
	  FROM INFORMATION_SCHEMA.Tables
	 WHERE TABLE_TYPE = 'BASE TABLE'

SET NOCOUNT ON

OPEN Space_csr

FETCH NEXT FROM Space_csr
	INTO	@cmd

WHILE @@FETCH_STATUS = 0

	BEGIN
		EXEC(@cmd)
		FETCH NEXT FROM Space_csr
			INTO	@cmd

	END

SELECT * FROM #TEMP WHERE ColCount <> 0 Order by ColCount DESC

SELECT * FROM #TEMP WHERE ColCount = 0 Order by TBNAME

CLOSE Space_csr
DEALLOCATE Space_csr

DROP TABLE #TEMP
GO

Brett

8-)

hismightiness
Posting Yak Master

164 Posts

Posted - 2004-09-20 : 15:06:13

Sorry, I am the only true Web Developer here and as with most companies my size, I have also become the unofficial/untrained SQL DBA. :)

The largest table I have has 661724 records. I have a total of 304 tables in 13 databases. 5 of the databases are test/sample data, but contain minimal information. The largest table I have is for our intranet web site (holds above table), the second largest runs our IT's "hidden" support site (including web call/ticket/field tech management apps).

P.S. - Nice SQL Statement! I am easily impressed when it comes to this I guess.

- - - -
- Will -
- - - -
http://www.servicerank.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources