SQL Best Practices Analyzer Rule: Use Of Schema Qualified Tables/Views

Dec 17, 2005

SQL BPA says the following:

"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."

"When SQL Server looks up a table/view without a schema
qualification, it first searches the default schema and then the
'dbo' schema. The default schema corresponds to the current
user for ad-hoc batches, and corresponds to the schema of a
stored procedure when inside one. In either case, SQL Server
incurs an additional runtime cost to verify schema binding of
unqualified objects. Applications are more maintainable and
may observe a slight performance improvement if object
references are schema qualified."

How important is to specify the schame (dbo. in my case) in
stored procedures? Will it really improve performance if I go
and fix each object that is missing "dbo."?

The problem is I have thousands and thousands of them
with no schemas. Before I invest a lot of time fixing them
I am trying to determine if it's really worth it or not?

Thank you

View 1 Replies


ADVERTISEMENT

Switching Schema Ownership Of Views, Stored Procedures, Tables

Feb 20, 2007

Hi all--I used the following type of script to migrate tables, views and stored procedures from 'dbo' to 'hqmsuser':
alter authorization on dbo.PK_T_PQ_MASTER to HQMSUSER
alter schema HQMSUSER transfer dbo.PK_T_PQ_MASTER
go

This was per recommendations from the BOL to use instead of the 'sp_changeobjectowner' procedure. I returned the following error:

Msg 15346, Level 16, State 1, Line 3
Cannot change owner for an object that is owned by a parent object. Change the owner of the parent object instead.
Msg 15347, Level 16, State 1, Line 4
Cannot transfer an object that is owned by a parent object.

How do I find and change the parent object on this from 'dbo' to a non-dbo schema ownership?

View 1 Replies View Related

SELECT Of A Table - WITHOUT SCHEMA QUALIFIED

Sep 12, 2005

Posted - 09/12/2005 : 15:16:05
--------------------------------------------------------------------------------

Hi

I have a schema XXX. This schema owns a set of tables say XXX.A, XXX.B and XXX.C.

I have a login XXX mapped to the user XXX.

When I connect to the SQL Server using login XXX and execute the query.

Select * from A, it throws be an error saying that"Invalid Object Name A".

Now when I modify the select statement specifying the schema it works. i.e. Select * from XXX.A

In the former case, is it not that SQL Server tries to search for the table in the current user's account and then if it not available it look's in the dbo's account.

HOw can I make a select without specifying the schema ?

I am using SQL Server 2005.

Thanks & Regards
Imtiaz

View 4 Replies View Related

Schema, Owner And Fully Qualified Name

Mar 27, 2008

I have a database that has been upgraded from SQL 200 to 2005. The database was owned by 'Joe' and all objects were also owned by 'Joe'. In SQL 2000 when Joe logged in he could simply issue 'select * from table' and would get results.

The upgraded db now has a user Joe and a schema Joe. All objects now belong to the schema Joe. Joe the user has his default schema set to Joe and he is the owner of the schema. When Joe logs in and tries to 'select * from table' he gets an invalid object. He can 'select * from joe.table'.

How can I set it up so that the objects are still owned by the schema Joe and a simple 'select * from table' works when user Joe logs in? I was convinced that is he owned the schema and this was his default schema it should work.

Thanks
scott

View 1 Replies View Related

When To Use Fully Qualified Names ([database].[schema].object)

Dec 29, 2006

I wihsh to discuss whether to use fully qualified names:[database].[schema].objectof objects to operate (create, query..) on is good or not?If someone change order of sql code blocks in my script - this may causelose of it's context (like: use master / use <mydb>..). I wish to have mysript independed on changes like this and always produce correct result.Does using full name make use of 'use <db>' statement unnecessary?

View 8 Replies View Related

Microsoft SQL Server Best Practices Analyzer ?

Apr 25, 2004

Hi,
I wanted know the Microsoft SQL Server Best Practices Analyzer tool feedback?.
Thanks,
Ravi

View 3 Replies View Related

Best Practices Analyzer Contradicts BOL For SQLSERVERAGENT

Jul 5, 2007

The 2005 Best Practices Analyzer (July 2007) contradicts Books Online about what account to run SQLSERVERAGENT under.



BPA says use Network Service Account, BOL explicitly says not to use Network Service Account:



BPA gives a warning saying "SQL Server Service [MY-PCSQLSERVERAGENT] In Unrecommended Account On Host [MY-PC]. We recommend that the service [SQLSERVERAGENT] on host [MY-PC] be run under Network Service Account. Currently it is designated to run under the account [SOMEDOMAINsomeaccount]."



BooksOnline says "Security Note: Because multiple services can use the Network Service account, it is difficult to control which services have access to network resources, including SQL Server databases. We do not recommend using the Network Service account for the SQL Server Agent service."



So who's right ? BPA or BOL ?

View 1 Replies View Related

SQL 2005 Schema Best Practices

Jan 3, 2008

Hello,

In general, with the introduction of schemas in 2005, is it considered "bad practice" to tell people to create new tables in the "dbo" schema?

Our product documentation contains a "quick start" guide for users who just want to get the product up and runing. We suggest that the customer creates a database for our application. This database is configured with a user that is assigned to the 'db_owner' role (we want to keep things as simple as possible) that we use to connect to the database. This database will only be used by our application. In this situtation is it okay to use the "dbo" schema, or should we consider creating another schmea for all our tables?

Are the any "best practices" for using schemas in SQL 2005?

Thanks,
Brenden.

View 7 Replies View Related

Schema Binding ? For Procs And Views

Apr 19, 2001

Am I correct in understanding that if you create a view or proc with schemabinding that this would prevent a user from droppping a table/column associated with the view or procedure you have created

View 1 Replies View Related

Better Practices Wanted For Cascading Inserts Of Hierarchical Data From Staging Tables

Aug 28, 2007

I apologize if this has been asked, but I can't find a complete answer.

We have a situation with parent/child tables which have an identity column as their PK. We need to be able to insert into the live tables from staging tables. The data in the staging tables are related via a surrogate key.

I have found the OUTPUT clause, but that can only refer to columns of the actual table (since there is no FROM clause in an INSERT). Our current best solution to this problem involves adding bogus "staging" columns to the destination tables, and removing them after we've inserted everything from staging. This is an unattractive solution to say the least.

I'll give an example that mirrors our actual solution, and ask if anyone has a better solution?
----------




Code Snippet
CREATE TABLE [dbo].[TABLE_A](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DATA] [nchar](10) NOT NULL,
[STAGING_COLUMN] [bigint] NULL,
CONSTRAINT [PK_TABLE_A] PRIMARY KEY ([ID] ASC)
)
GO
CREATE TABLE [dbo].[TABLE_B](
[ID] [int] IDENTITY(1,1) NOT NULL,
[A_ID] [int] NOT NULL,
[DATA] [nchar](10) NOT NULL,
[STAGING_COLUMN] [bigint] NULL,
CONSTRAINT [PK_TABLE_B] PRIMARY KEY ([ID] ASC)
)
GO
ALTER TABLE [dbo].[TABLE_B]
ADD CONSTRAINT [FK_TABLE_A_TABLE_B] FOREIGN KEY([A_ID]) REFERENCES [dbo].[TABLE_A] ([ID])
GO
CREATE TABLE [dbo].[STAGE_TABLE_A](
[A_Key] [bigint] NOT NULL,
[DATA] [nchar](10) NOT NULL
)
GO
CREATE TABLE [dbo].[STAGE_TABLE_B](
[B_Key] [bigint] NOT NULL,
[DATA] [nchar](10) NOT NULL,
[A_Key] [bigint] NOT NULL
)
GO


The STAGING_COLUMN columns are the ones that will be added before, and dropped after.






Code Snippet
DECLARE @TABLE_A_MAP TABLE (
A_ID INT,
A_Key BIGINT
)
INSERT INTO TABLE_A (DATA, STAGING_COLUMN)
OUTPUT INSERTED.ID, INSERTED.STAGING_COLUMN INTO @TABLE_A_MAP
SELECT DATA, A_Key FROM STAGE_TABLE_A
INSERT INTO TABLE_B (A_ID, DATA)
SELECT TAM.A_ID, STB.DATA
FROM STAGE_TABLE_B STB INNER JOIN @TABLE_A_MAP TAM ON TAM.A_Key = STB.A_Key






This seems to work, but I'd really like another alternative. Even though this is happening when nobody else is using the database, I cringe at the thought of adding and removing columns just to make this work.

Here are a few of my constraints:



The above is a simplification of the actual problem. The actual problem goes about five levels deep (hence the B_Key in STAGE_TABLE_B). At the top level, our larger customer will have 100,000 rows to insert. Each level will average 3 times as many rows as the next higher level, so we're talking about real volumes here.

This has to finish over the course of a weekend.

This has to be delivered to QA this Friday
Thanks for any help or insight.

View 3 Replies View Related

Analyzer And Created Tables

Nov 24, 2000

Can Query Analyzer deal with this or not???

Select FirstName into FirstNameTable From NameTable
Select Count(*) from FirstNameTable

Fails with FirstNameTable as invalid object.
TIA

View 5 Replies View Related

Query Analyzer --Temp Tables

Nov 16, 2004

Is there a quick way to create a temp table that is the same as an existing table in the schema? I am used to Informix where "select * from <table A> into temp B" would create the temp table B with the same schema is table A. Is this same functionality available with MS SQL server using query analyzer?

View 2 Replies View Related

Creating Tables In SQL Server Via Query Analyzer With Relationships

Jun 7, 2005

I've been searching around for some info on how to set this up, but with no luck.I need to have a .sql file that will set up a few tables and these tables will have relationships and contraints.I can do this by hand in enterprise manager, but need to set up some procedures that will do the same thing.For instance, I can create the tables just fine.....CREATE TABLE students ( sId int NOT NULL PRIMARY KEY,        studentId varchar(50) NOT NULL,               course varchar(50)              )
CREATE TABLE courses  ( cId int NOT NULL PRIMARY KEY,        course varchar(50) NOT NULL,               sco varchar(50)              )But, I need to set up relationships in there somehow.Once student may have many courses (one to many) and one course may have many sco's (one to many) SCO would be another table.Can someone point me to a good link that would show how to complete these procedures?Thanks all,Zath

View 4 Replies View Related

Delete Duplicate Entries From Tables In My Database Using Query Analyzer

Jun 25, 2004

Hello,

How can I delete duplicate entries from tables in my database using Query Analyzer, as there are many duplicate entries in my tables, I want to delete them.

Thanks in advance,
Uday.

View 4 Replies View Related

Tables And Schema

May 14, 2008

hello,
I have list of tables in a database where schema1 is attached. when ever i login thru windows authentication or any other user and if i run select * from schema1.table i get proper values.

what i need is even if i login thru windows authentication i should be able to run the query without attaching the schema
i.e., select * from table should work. i am not sure what permissions has to be given.
pls advise.

View 6 Replies View Related

Compare Schema Between Tables

Oct 6, 2006

In the process of purging data to history tables,
we wanted to make sure that no schema changes have been done
to the main or the history table.
So to ensure identical schemas, we use this function:


ALTER FUNCTION dbo.fnCompareTableSchema
(
@t1Name NVARCHAR(257)
,@t2Name NVARCHAR(257)
)
RETURNS BIT
AS
/*
Compares the schema of 2 tables
If the schema is different RETURNS 0
If the schema is identical between the two table, RETURNS 1
NOTE: system tables or non-existant tables that are NOT in INFORMATION_SCHEMA views will compare equal (RETURNS 1)
==================================================================================================================
SAMPLE USAGE:
DECLARE @schemaOK BIT
SELECT @schemaOK = dbo.fnCompareTableSchema('dbo.table1','dbo.table2')

IF @schemaOK = 1
PRINT 'TABLE SCHEMA IDENTICAL'
ELSE
PRINT 'TABLE SCHEMA DIFFERENT'
==================================================================================================================
*/
BEGIN
IF @t1Name = @t2Name
RETURN 1

-- check if schema is different
IF EXISTS
(
SELECT*
FROM
(
SELECTCOLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
, COLUMN_DEFAULT, IS_NULLABLE
, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
, COLLATION_NAME
FROMINFORMATION_SCHEMA.COLUMNS
WHERETABLE_SCHEMA = COALESCE(PARSENAME(@t1Name,2),'dbo') AND TABLE_NAME = PARSENAME(@t1Name,1)
UNION ALL
SELECTCOLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
, COLUMN_DEFAULT, IS_NULLABLE
, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
, COLLATION_NAME
FROMINFORMATION_SCHEMA.COLUMNS
WHERETABLE_SCHEMA = COALESCE(PARSENAME(@t2Name,2),'dbo') AND TABLE_NAME = PARSENAME(@t2Name,1)
) U
GROUP BY
COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
HAVING COUNT(*) <> 2
)
RETURN 0

-- schema identical
RETURN 1
END

View 6 Replies View Related

Access Tables Without Schema

Oct 11, 2006

Hello

in my database I have schema "x". I have user "x" who is database owner, The user has schema "x" as default schema and is owner of this schema.

Now there are tables "x.mytable"

If I connect to the db as "x" and try

select * from mytable

I get an error "Invalid object name", I have to write it this way

select * from x.mytable

Why?



Thank you

Eckard



View 5 Replies View Related

Views Vs Tables

Oct 16, 2007

I am an SQL beginner / intermediate. 
My question is assuming everything is equal is a view of a 4 table join slower than the SQL query of the 4 table join when running reports?
 

View 3 Replies View Related

Views Vs Tables

Jun 29, 2001

My client has a three tier system.
and they are using only views to assess the database and doing about 5 joints what are the advantages and disavantages of using only view vs hiting the table dirrectly

View 3 Replies View Related

Views Vs. Tables

Aug 24, 2000

Are views used for securiry reasons, such as restricting access to certain columns/rows or is there performance advantages to using views. There is disagreement in my dept.

I created the following view PAYACTIVE:

Select * FROM PAYMENTS WHERE STATUS = 'ACTIVE'

The PAYACTIVE view contains 260 rows. The PAYMENTS table contains over 2 million rows. I executed the following commands:

Select * FROM PAYACTIVE

Select * FROM PAYMENTS WHERE STATUS = 'ACTIVE'

They seem to take the exact same amount of time. In other words, selecting from the view seemed to not help performance. Am I conducting a valid test?

View 3 Replies View Related

Views Vs Tables

Jun 29, 2001

My client has a three tier system.
and they are using only views to assess the database and doing about 5 joints what are the advantages and disavantages of using only view vs hiting the table dirrectly>

Emily

View 1 Replies View Related

256 Tables In Views

Aug 30, 2005

I have a SQL script that I attempted to write into a view. If I run the script in SQL Query Analyzer it works perfectly. When I attempt to run execute as a view I receive the following error message: "[MS][ODBC SQL Server Driver][SQL Server] The query and the views or function in it exceeded the limit of 256 tables."

My objective is to use the recordset from the view to create a crystal report but have been unable to replicate the "query" effect directly in crystal. (unfortunately I am also a new crystal user)

All my searches have been fruitless. I remember seeing something about SQL Server 2000 supporting 1000 tables and older versions supporting 256 in an Access forum but have not been able to find that thread a second time.

Any provided solution would be greatly appreciated. (I am getting tired of hearing the "gears" in my head grinding. Is smoke from the ears normal?)

SELECT SubID, SubMenuDesc, ItemID, ItemDesc, 0 AS ModID, 'n/a' AS ModDesc, round(ItemPrice/100,2) as ItemPrice
FROM vwSubMenuNorm
WHERE (ItemPrice <> 0)

UNION
SELECT vwSubMenuNorm.SubID, vwSubMenuNorm.SubMenuDesc, vwSubMenuNorm.ItemID, vwSubMenuNorm.ItemDesc, vwModifiersNorm.ItemID AS ModID,
vwModifiersNorm.ItemDesc AS ModDesc, round(vwModifiersNorm.ItemPrice/100,2) as ItemPrice
FROM vwSubMenuNorm INNER JOIN
vwItemNorm ON vwSubMenuNorm.ItemID = vwItemNorm.ID INNER JOIN
vwModifiersNorm ON vwItemNorm.MOD1 = vwModifiersNorm.ModID
WHERE (vwSubMenuNorm.ItemPrice = 0) AND (vwModifiersNorm.ItemPrice <> 0)

UNION
SELECT vwSubMenuNorm.SubID, vwSubMenuNorm.SubMenuDesc, vwSubMenuNorm.ItemID, vwSubMenuNorm.ItemDesc, vwModifiersNorm.ItemID AS ModID,
vwModifiersNorm.ItemDesc AS ModDesc, ROUND((vwSubMenuNorm.ItemPrice + vwModifiersNorm.ItemPrice)/100, 2) AS ItemPrice
FROM vwSubMenuNorm INNER JOIN
vwItemNorm ON vwSubMenuNorm.ItemID = vwItemNorm.ID INNER JOIN
vwModifiersNorm ON vwItemNorm.MOD1 = vwModifiersNorm.ModID
WHERE (vwSubMenuNorm.ItemPrice <> 0) AND (vwModifiersNorm.ItemPrice <> 0)

View 13 Replies View Related

How To Omit Schema Name When Referrring To Tables?

Jan 9, 2006

Hi guys, please help.What I did is :Step1. create a Database named [Test], it's default owner is [sa]Step2. create a User in Database [Test] named [kimliuTest], which mapsto the Login [kkhadkimliu] on this Database Server, it's a windowsdomain user.Step3. create a Schema in Database [Test] named [schTest], whose SchemaOwner is User [kimliuTest]Step4. created two tables: [dbo].[t1] and [schTest].[t2]Step5. run the scriptALTER USER kimliuTest WITH DEFAULT_SCHEMA = schTestto set the default schema.Now my problem is, when I try to runselect * from schTest.t2, everything is OK, but if I runselect * from t2the error message says:Msg 208, Level 16, State 1, Line 1Invalid object name 't2'.why? I'm loggin in with my [kkhadkimliu] windows domain id , ActiveMonitor also shows that. but the command does not work?Yeah, it's a good habit to write the full name of the Table with Schemaname as prefix, however, we did our development in SQL 2000 and tryingto upgrade to SQL2005, so trying to avoid scanning codes and insertingin thousand of places.How could we omit the Schema name when referrring to Tables? Thanks.ps. I posted in another topic days ago, but nobody is replying, when Itried to bring it forward by replying myself, it failed. please help me, thanks!

View 3 Replies View Related

Querying Tables Without Schema Prefix

Dec 27, 2006

Hi all,

I'm using SQL Server 2005 Express Edition, and I have restored a database that was using SQL Server 2000 previous. All of that went fine - sprocs and tables/data all made it.

However, when viewing the tables in the object explorer in the management studio, all the tables are prefixed with the schema name (testuser - so the table names are like: testuser.pn_user). Now, all of the code I have queries the tablename only without prefixing testuser to it - but this doesn't work anymore (invalid object name.)

I've tested, and if I prefix the table names in my code with "testuser.", it starts working again, but would rather avoid doing this. Is there something I can do within Sql Server? I've made the "testuser" account "mapped" to the database I'm using (not sure what that means - I'm more of a software developer than a database guy.) Also, when I do "Properties" for the "testuser," the default database is set to the one I'm trying to query w/o the schema.

Any ideas?

View 4 Replies View Related

Permissions On Views And Tables

Aug 12, 2005

I am using a Microsoft Access ADP to get to data on SQL Server 2000. I would like to protect the base tables from being edited directly, but allow the views and SPs to handle all the work of getting data in and out. From what I have read in numerous articles and boods, I should be able to grant permissions just on the Views and NOT on the tables. However, the ONLY way I can make my views 'updatable' is by graniting UPDATE permissions on the TABLE! Worse yet, if I DENY permissions to UPDATE, INSERT, and DELETE in the view, but allow them in the table, the view allows the updates anyway, apparently not looking at the fact that it should be DENIED.
:confused:

View 2 Replies View Related

Create Views Not Tables

Jun 9, 2004

I want to allow a group of users to create views but not be able to create new tables or stored procedures... how can I do this ??

Thanks, John :eek:

View 1 Replies View Related

Tables/Views In DTS Package

Jan 5, 2005

Is there an easy way to find all instances of a table/view name in packages?

View 1 Replies View Related

Linking Tables With Views

May 12, 2008

I want to link in a table from another sql server in a view, how is this done?

View 1 Replies View Related

Updating Tables Using Views

May 18, 2007

Is there any gud topic on "updating tables using Views".Plz let me know

View 3 Replies View Related

Permissions With Views And Tables

Jul 23, 2005

We are attempting to implement security on top of a shrink-wrapped softwarepackage and are trying to get row-level security. Here's the scenario:1. Table dbo.BOOK contains all the information about books in everydepartment.2. There are a large number of developed reports that run queries like"select * from BOOK..."3. We wish to have each Department only be able to see their books - withoutchanging the existing reports.Our thought was to create a series of views:create view Dept1.BOOK asselect * from BOOK where Dept=1....and then create Roles for each Dept. We'd then remove rights to dbo.BOOKand grant rights to DeptN.BOOK as appropriate for each role. We startedtesting this and seemed to get it working, but are now having problems. Isthis possible? Is there another, better solution?Thanks!

View 5 Replies View Related

Need Some Help With Design Of Tables/views

Jul 23, 2005

I have an applicaton in which I collect data for different parametersfor a set of devices. The data are entered into a single table, eachset of name, value pairs time-stamped and associated with a device.The definition of the table is as follows:CREATE TABLE devicedata(device_idintNOT NULL REFERENCES devices(id),-- id in the devicetabledatetimedatetimePRIMARY KEY CLUSTERED,-- date creatednamenvarchar(256)NOT NULL,-- name of the attributevaluesql_variantNOT NULL-- value)For example, I have 3 devices, and each is monitored for two attributes-- temperature and pressure. Data for these are gathered at say every20 minute and every 15 minute intervals.The table is filled with records over a period of time, and I canperform a variety of SQL queries.I have another requirement which requires me to retrieve the *latest*values of temperature and pressure for each device.Ideally, I'd like to use the data I have collected to get thisinformation, and I suppose I can.What I need is the SELECT statement to do this.I'd appreciate it very much, if someone can help provide that.Conceivably, I could use a SQL server View for making this easier forsome of my users.One alternate technique I thought was to create another table which I*update* with the latest value, each time I *insert* into the abovetable. But it seems like a waste to do so, and introduces needlessreferential integrity issues (minor). Maybe for fast access, that isthe best thing to do.I have requirements to maintain this data for several months/year ortwo, so I am dealing with a large number of samples.Any help would be appreciated.(I apologize if this post appears twice)

View 9 Replies View Related

About Security Between Views And Tables

Nov 13, 2006

Hi,

I've got a table T1 and a view V1 based on T1's rows (but not all rows).


I've granted users to do update and select on my View V1.
But i don't want users directly update my table T1.

then, i log as a standard user in Enterprise mgr, and open my view V1. I modify a value in a field, and when i validate, an error occurs saying i'm not allowed to make Select neither Update on table T1.

Is there a way of doing this ? (sql 2000).







View 7 Replies View Related

Change Schema Name On Tables And Stored Procedures

Sep 12, 2006

Hi,Is there a way I can change schema name on tables and stored procedures? How do I do this?I´m very news to SQL and .netThanks 

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved