Is There Any Performance Loss If I Have All My Columns Preceeded With Dbo.Tablename?
Jan 6, 2006
I am doing a mass update of our SQL script files by adding dbo.
to all references to the tables.
The code is also adding dbo. in front of existing lines of code
that are like this:
SELECT CLIENT.FIRSTNAME,
CLIENT.LASTNAME
FROM CLIENT
....
The mass update is causing this to become:
SELECT dbo.CLIENT.FIRSTNAME,
dbo.CLIENT.LASTNAME
FROM dbo.CLIENT
....
My goal is to see FROM dbo.CLIENT
but even the column names are getting the "dbo."
added, so my question is if there's any performance
loss or any other side effect if I end up all my columns
with a preceeding "dbo."?
I use SQL2005 EE to capture the "In Progress Data" from the Devices and have connected it to the Main server-SQL2005 SE thro linked Server. I Run a Job that updates the Main server and Clears the "in Process Data". Off late I have been seeing that the systems with SQL2005 EE have gone slow ? I have not investigated the reason. But wanted to find out if using this method ,should I need to take additional precautions ?
was trying to performance tune a query and came up on this weird issue(Weird to me atleast.. possibly because I am a newbie:-))).
When I run the query mentioned below, I get the results in 1 second, while the same query, if I declare a variable for the value I am comparing, it takes 40 seconds. I am on SQL Server 2000. Both queries are being run in the same DB on the same tables and the only difference between the two is that the date has been declared as a variable. I am able to consistantly reproduce this. Tried casting the variable, declaring it as varchar , replacing the date by getdate() at both places, but the moment I use the variable, the performance goes for a toss. Has anyone came across any similar issues? TIA Callista
Query 1 select ae.Col1, ae.Col2, ae.Col3, ae.Col4, ge.Col4, c.Col5, 0 from table1 ae WITH (INDEX=ind_Col3 NOLOCK), table2 ge with (nolock), table3 c with (nolock) where ae.Col1 = ge.Col1 AND ae.Col4 = c.Col1 AND ae.Col3 > '2007-10-25 14:18:12.380' and ae.Col6 is not null
Query 2 declare @MyVariable datetimeselect @MyVariable = '2007-10-25 14:18:12.380' select ae.Col1, ae.Col2, ae.Col3, ae.Col4, ge.Col4, c.Col5, 0 from table1 ae WITH (INDEX=ind_Col3 NOLOCK), table2 ge with (nolock), table3 c with (nolock) where ae.Col1 = ge.Col1 AND ae.Col4 = c.Col1 AND ae.Col3 > @MyVariable and ae.Col6 is not null
Hi,I'm designing a new database and I have a doubt in which surely youcan help me.I'm storing in this database historical data of some measurements andthe system in constantly growing, new measurements are added everyday.So, I have to set some extra columns in advance, so space is availablewhenever is needed and the client doesn't have to modify the structurein SQL server.The question is: the more columns I add "just in case", the slower theSQL reads the table?Of course the "empty" columns are not included in any query until theyhave some valid data inside.Will I have better performance if I configure only the columns beingused at the moment, without any empty columns?Thanks in advance.Ignacio
Hi, I have a denormalized table (done so with reason) with around 40 columns. I would never have to retrieve data for all of those columns together. I haven't done any performance measurements yet but just wondering if anyone has ready answer to this: Will there be a performance degradation if I retrieve data from a table with many columns, even if not all columns are referred in the query? (for making it simple, lets assume that all or varchar type of columns, I just want to find out if performance degrades if there are too many columns in table)
I have a db which I have little control over most of it's makeup because of the vendor supplied tools. We currently have over 700 tables and 19000 columns. Has anyone seen a problem or saturation pont with these kinds of numbers? The database delivered to the clients will be from 2-50 gig depending on the site. I can probably through hardware at problems, but if anyone has been down this road any suggestions are appreciated.
i need to develop a stored procedue, in ehich i have to use variable table name.. as Select * from @tableName but i m unable to do so.. it says u need to define @tablename
heres da code CREATE PROCEDURE validateChildId ( @childId int, @tableName varchar(50), @fid int output ) AS ( SELECT @fid=fid FROM @tableName where @childId= childId )
Hi, i just migrated an database from oracle to sql server 2005 with the migration tool from microsoft (v3). the migration tool works only with uppercase table and column names, but i need them in lower case. is there a way to modify the names of tables and columns with t-sql to lower case? Thx Frank
Does anyone know the syntax to reference a table in a stored procedure using a parameter? I'm trying to make a stored procedure that will "SELECT INTO" a table as the passed parameter. I keep getting an syntax error when I use this:
CREATE PROCEDURE make_table @tablename varchar(20) AS SELECT * FROM old_table INTO @tablename
I've tried it several ways, but I can't get it to work, and I haven't found any examples of this anywhere. Thanks in advance for any tips.
Hi, What is the difference between <Tablename> . . <ColumnName> and <Tablename> . <ColumnName> this syntax we are using in Storedprocedures. In SQL 2000, <Tablename> . . <ColumnName> is not working
Eg: Tablename =a Column Name = b Difference between a..b and a.b
I know there has already been a thread on this, but I want to push some about it.
In SQL Server, there is a command "SET IDENTITY_INSERT tablename ON".
That allows you to update IDENTITY columns, or do INSERTs that include IDENTITY columns. Although a work-around was given for this in the other thread (reset the IDENTITY seed to the desired value before each INSERT), that is a major pain.
In my database, I have a table that tracks charitable donors. They have a donornum, that is an IDENTITY column, and a year. Together, the donornum and the year form the primary key. Each year end, a copy is made of all donor records in one year, to form the next year's donors. They have to keep the same donornum, but they get a new year value of course. Adding new donors uses the donornum normally, incrementing the IDENTITY donornum value.
The advantage of this is that you can match up one year's donors with the previous year's, by joining on donornum. But I need there to be separate records, so they can have separately updated addresses, annual pledge amounts, etc.
Is there any way the SET IDENTITY_INSERT feature can be added to SQL Everywhere, or some other approach can be found that is less laborious than the existing work-around? (The problem with it is that if you have hundreds of donors to copy, you have to do one ALTER TABLE to reset the identity seed for each donor insert for the new year.)
Hi to all , I write the query like belwodeclare @userid nvarchar(20)set @userid ='6,8'print @useridselect * from user_master where user_id in (@userid)Here user_id is int datatype, when i execute this query then meet the error as 6,8Msg 245, Level 16, State 1, Line 4Syntax error converting the nvarchar value '6,8' to a column of data type int. How to do this.? Anybody answer me,...
hi all how are you today i am not good because i can't set this variable in this query please read this thanks
create table aaa1 (id_no int, name varchar(20)) go create table aaa2 (id_no int, name varchar(20)) go
insert into aaa1 values (1,'rahmi') insert into aaa1 values (2,'atilganer') insert into aaa1 values (3,'hasan')
insert into aaa2 values (4,'rahmi') insert into aaa2 values (5,'atilganer') insert into aaa2 values (6,'hasan')
/* declaring any numeric variable*/ declare @id_no_var int /* and set variable to max table name's (aaa2 table in this example) id_ no column
note :insqlhelp and insqlhelp2 is an alias for tablename query (recommended from sql help */
set @id_no_var = (select max(id_no) from (select max(name) insqlhelp from sysobjects where name like 'aa%') insqlhelp2 )
this query return:
Server: Msg 207, Level 16, State 3, Line 3 Invalid column name 'id_no'.
this error returned because max(id_no) column is absent
if you are run select * from (select max(name) insqlhelp from sysobjects where name like 'aa%') insqlhelp2
this query return that
insqlhelp --------------- aaa2
(1 row(s) affected)
this mean that my table name query returned table name but i cant use this name in any other query (i think because of daclaring alias "insqlhelp, insqlhelp2")
other way is
set to any other text variable to table name, and concetanate to query, and execute with exec
I have written the Query in which i am getting TableName,Columns,Precision but how can i get Table related Foreign key and Constraints
SELECT DISTINCT QUOTENAME(SCHEMA_NAME(tb.[schema_id])) AS 'Schema', QUOTENAME(OBJECT_NAME(tb.[OBJECT_ID])) AS 'Table', C.NAME AS 'Column', T.NAME AS 'DataType', C.max_length, C.is_nullable, c.precision, c.scale
Can someone help me with the following function? I would like to use a table name as a variable.
Thanks in advance!
CREATE FUNCTION FAC_user.Overzicht_DTe (@tabel1 as nvarchar, @proces as nvarchar, @categorie as nvarchar) RETURNS numeric AS BEGIN declare @aantal numeric
if @proces = 'Inhuizen' begin if @categorie = 'open_op_tijd' begin SET @aantal =(SELECT Count(@tabel1 + '.Contractnummer') FROM @tabel1, Rapportageweek WHERE@tabel1.Verwerkingsdatum is null AND @tabel1.UiterlijkeVerwDatum >= Rapportageweek.Rapportagedatum AND @tabel1.ItemType = 'ZVHG' AND @tabel1.ItemType = 'ZVHN' AND @tabel1.ItemType = 'ZVIG' AND @tabel1.ItemType = 'ZVIN' GROUP BY@tabel1.Maand, @tabel1.Jaar) end
if @categorie = 'open_te_laat' begin SET @aantal =(SELECT Count(@tabel1 + '.Contractnummer') FROM @tabel1, Rapportageweek WHERE@tabel1.Verwerkingsdatum is null AND @tabel1.UiterlijkeVerwDatum < Rapportageweek.Rapportagedatum AND @tabel1.ItemType = 'ZVHG' AND @tabel1.ItemType = 'ZVHN' AND @tabel1.ItemType = 'ZVIG' AND @tabel1.ItemType = 'ZVIN' GROUP BY@tabel1.Maand, @tabel1.Jaar) end
HiCan someone please shed some light as to how this can be done. With the below sql statement as you can see I don't want to declare the table name but would rather the table name be passed in via a parameter. I've tried declaring it as a varchar parameter but it doesnt seem to like it if I don't add a valid table name. Any ideas how this can be done. Thanks.select * from @tableName where condition = condition
I have a stored procedure that accepts the table name as a parameter. Is there anyway I can use this variable in my select statement after the 'from' clause. ie "select count(*) from @Table_Name"? When I try that is says "Must declare the table variable @Table_Name". Thanks!
Hi Experts,I m using a stored procedure:set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[SP_Table_SELECT]( @TableName VarChar)ASset nocount onset ansi_warnings offDECLARE @sql varchar(2000)SET @sql = 'SELECT* FROM (' +@TableName + ')'EXEC (@sql)when I run It it shows return value = o and Query Completed with ErrorsThere are data in My Table. Help Plzzzz!!!!!!
Hi all. I want something like this : create proc myProc ( @num int ,@name varchar(80)) as select @num = max(field1) from @name
but there is a problem that the @name is a varchar and the error is : Server: Msg 170, Level 15, State 1, Procedure sp_myProc, Line 3 Line 3: Incorrect syntax near '@name'.
I tried select @num = max(field1) from object_id(@name) and the error was the same
is ther another way than : exec ("declare @num int select @num = max(field1) insert into anotherTable values( @num) " ) ? thx Eyal Peleg
I am trying to create a stored procedure for automating Bulk inserting into tables for one database to another. Is there any way i can pass the table name as variable to insert and select stmt
I am facing a unique problem, In my DB all tables which has nvarchar datatype columns. When i see any table in EM design mode it shows length of navrchar datatype column correct.
But if i see same table through QA using sp_help tablename then it will show length of my nvarchar column just double.
when i see all my nvarchar columns in syscolumns it will display the length of my nvarchar columns just double then actual.
i dont know where exactly the problem. Because of that my tester are getting wrong table information through my data dictionary whic i created using sysobects,syscolumns,sysproperties.
I want to fire a trigger which calls a stored proc, passing the name of the table the trigger is defined on to the proc without having to hardcode.
Can it be done ?
trigger x on table y for update as declare @table_name select @table_name = get underlying table_name 'y' from somewhere exec stored proc (@table_name)
where do I find the name of the table the current trigger is defined on ?
I would like to pass into a stored procedure the name of a table. Following code does not work. USE pubs GO CREATE PROC test1 @tableName varchar(40) AS SELECT * FROM @tableName EXEC test1 @tableName='authors'
I would appreciate it very much if someone could help me with this.
I have read through BOL but am still confused by the above sql. I think it rebuilds all indexes on a table. Am I correct? (If so, if would seem to be a good thing to run it nightly on all tables in all databases. Or maybe that is too extreme)
Hi,I have a big problem with a database in MS SQL SERVER 2000.the rows into the some tables, for the second time, have been mixed betweenthey without appearing reason.the application that uses the db is totally TRANSACTIONAL and they do notexist query that they do not have clause WHERE.The database is on a computer with NAS architecture.I have this problem for the first time in 5 years of use of MS SQL SERVERCan someone help me?TIA
its meant to be so simple .. yet i just can not get this to work. i have a Sequence container which has a execute SQL task in it. The SQL query is simple . 'Select companyname from clientadmin where Licence = '217514'
Connection is Native OLE DBSQL Native Client. When i test connectio it succeeds
I used the build query option to build and test the SQL Query and it returned the company name
but when i execute the task i get the following error
[Execute SQL Task] Error: Executing the query "" failed with the following error: "Retrieving the COM class factory for component with CLSID {7816B7A3-CD60-4539-BD38-C35AFC61F200} failed due to the following error: 80040154.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Once that hurdle is over how can i get the result into a variable called 'company' , type string ??
