Can Anyone Explain To Me Why This Is Not A Good Idea

Jan 28, 2008

I have a complex select statement that is used in several stored procedures. I decided that instead of having x number of T-SQL scripts with the same exact select statement that I would to put this query into a view and then do a select * from View.  Recently an instructor told me that this was a bad idea and that anyone who uses a select * from anything should be fired.  When I asked for his reasoning his response was to say the least abnoxious.  I can understand why a Select * from Table might be a bad idea as the table definition can change, but the chances of a view changing seems much less likely.

Is a view a good idea in this case?  Is the Select * from View really a bad idea?

 Thanks

 

 

View 6 Replies


ADVERTISEMENT

Need Good Idea

Jul 23, 2005

Hi guysWe have a following problem. For security reasons in each table in ourDB we have addition field which is calculated as hash value of allcolumns in particular row.Every time when some field in particular row is changed we create andcall select query from our application to obtain all fields for thisrow and then re-calculate and update the hash value again.Obviously such approach is very ineffective, the alternative is tocreate trigger on update event and then execute stored procedure whichwill re-calculate and update the hash value. The problem with thisapproach is that end user could then change the date in the tables andthen run this store procedure to adjust hash value.We are looking for some solution that could speed up the hash valueupdating without allowing authorized user to do itThanks in advance,Leon

View 6 Replies View Related

AUTO_UPDATE_STATISTICS A Good Idea?

May 20, 2003

Can anyone tell me if turning on the AUTO_UPDATE_STATISTICS option will have any adverse effects on performance? I've read that it is possible for it to adversely affect performance, because SQL Server “takes a quick break to update database statistics in the middle of the day”. The book does not explain why and I'm always leery of simply accepting something, simply because I’ve read it from a book. This is the first time that I’ve ever heard this and cannot find anything from SQL BOL or any other source.

View 2 Replies View Related

Many To One Relation - Is It A Good Idea?

Jun 11, 2008

Hi

I have about six different entities that can have zero or more note entities associated with them. The easy way to do this is obviously to have a different "note" table for each of the entities i.e. WorkItemNote, CustomerNote etc.. But I would much rather have a single "note" table since they would all be identical, so I came up with this design:


CREATE TABLE WorkItem
(
WorkItemGuid uniqueidentifier PRIMARY KEY DEFAULT (newid()),
-- rest of table declaration removed for bravity
)

CREATE TABLE Customer
(
CustomerGuid uniqueidentifier PRIMARY KEY DEFAULT (newid()),
-- rest of table declaration removed for bravity
)

CREATE TABLE Note
(
NoteId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
ReferenceGuid uniqueidentifier NOT NULL,
Text ntext NOT NULL,
-- rest of table declaration removed for bravity
)


This way I can get notes associated with a given entity, either Customer or WorkItem, by just selecting from the Note table with its WorkItemGuid or CustomerGuid.

My question is: Is this the best approach to what I am trying to accomplish?

(ps: Apologies if "many to one" is not the right terminology)

Regards, Egil.

View 12 Replies View Related

IDENTITY_INSERT, Is It A Good Idea?

Sep 14, 2006

Hi,

Today I discovered this command completely by accident and thought that ther are several places which we could use it in our apps.

Talking with a colleague, he is not to sure as it new to him too.

By using this to recover lost identity values, would this have any possible adverse effects on the table, indexes etc.

I can see potential problems when constraints are set between tables/keys. Anyone with any experience using this good and bad would be useful to hear.

Thanks

Adam

View 4 Replies View Related

Is Dropping Distribution Db A Good Idea?

Jan 29, 2008

What would happen if I dropped the distribution db?

I'm having problems configuring distribution and after searching, I found someone that claims they solved the same problem by dropping the distribution db. The resolution is the last post on this thread: http://www.ureader.com/msg/11563430.aspx

Is this safe to do? There are currently no publications and the distribution server is not configured.

Thanks

View 4 Replies View Related

Is It Good Idea To Replicate Sql Server Db Files?

Jul 20, 2005

Hi.I am wondering if it is a good idea to replicate sql server db filesusing frs.I don't really know how the frs works, sodoes frs replicates the whole database from time to time or just theportion that is changed?Also if the db is expected to change very often, and wouldn't it makethe whole system down?I wonder if it's a good idea just to make a backup of the database andcopy it.What's the usual practice?

View 3 Replies View Related

Is It A Good Idea To Install SQL On Exchange Server?

Nov 16, 2007



I am out of my element here but I have someone who is working on a project for me that entails him migrating our Access database to sql. He wants to load the sql software on our exchange server but our IT guy is saying this is not a good idea. Any thoughts???

Thanks,

Beth

View 5 Replies View Related

Is It A Good Idea To Use SQL2005 MDF For Audit, Exception Tracking

Feb 24, 2007

I am writing a web application that uses a Teradata database as the primary data source.  While Teradata is great as a data warehouse and managing Terabytes of information it doesn't do as well when update or inserting.  I was thinking of using a local SQL2005 MDF file to hold a few reference tables and an audit table to collect usage information and exception database to capture any errors.
There could be a few thousand users of the web application but no more than a couple hundred at a time.
I just trying to get some opinions on these technique.  I am open to all comments and suggestions.
Thank You 
 
 
 

View 2 Replies View Related

Stored Procs And Source Safe Good Idea?

Sep 25, 2000

Hi,
Any pros and cons of putting sprocs into Source Safe?

Thanks,
Judith

View 2 Replies View Related

Need Advice - A Delay Timer In Retrieving Remote Data, A Good Idea?

May 21, 2008

I have an application that automatically reads a lot of data from a third-party application into my database, via XML. For example, I might read a couple thousand rows-worth of XML data, one row at a time in a foreach loop.
To reduce the load on their server and database, I thought about putting a 2 second delay in between each of my automatic requests. Would this really help much, or is there enough overhead (setting up/tearing down connections, etc) with each request that it wouldn't reduce server load much anyway?
Is 2 seconds enough? Too little or too much? 

View 3 Replies View Related

Can Someone Please Explain This To Me...

Jun 26, 2007

why do the following return the same datasets?
 select * from myTable where myData = ''
 select * from myTable where myData = '     '
in the first I'm specifically searching empty strings, in the second a sequence of five spaces. Yet both return any and all white character matches? Is this a "feature" of SQL...
P.S. I'm using T-SQL

View 2 Replies View Related

How Do I Run 'explain' On A Sql??

Jun 27, 2001

Hi, does anyone know how to run an 'explain' on a sql query on SQL Server 2000?

Thanks.

View 1 Replies View Related

Could Someone Please Explain The Following...

Dec 1, 2005

As previous threads have indicated I've been trying to figure out what's slowing down our 3rd party ETL tool.

My research has figured out the culprit, but now I need some advice on the best way to handle it.

Here's what's going on...
SPID 1 = dynamic sql passed from Crystal
SPID 2 = ETL tool
SPID 3 = report in stored proc

Until a few days ago we didn't even know SPID 1 existed. These are different guys, using old version of Crystal passing crappy queries.

Here's the scenario
SPID 1 is running (inefficient select)
SPID 2 is waiting
30 minutes pass and SPID 2 is still waiting PAGEIOLATCH_SH
SPID 3 fires off

Now SPID 2 is blocking SPID 3, but SPID 2 is still waiting for SPID 1

Finally when SPID 1 finishes, everything catches up. But it appears that there is a period of at least 45 minutes when all SPID's are just sleeping.

My questions:
1. If SPID 1 is just running an inefficient select query, why is it holding up SPID 2 (Which reads and inserts). SPID 1 should have a shared lock on table, this would prevent SPID 2 from insert/update?
2. Is there any steps I can take to give SPID 2 precendence? or is it first come first serve?

Thanks in advance

View 10 Replies View Related

Explain

May 2, 2008

Any one explain me about this query exactly wat it will do

backup log iiswebappauditdb with truncate_only

View 3 Replies View Related

Explain Me

May 8, 2008

set @tb1='rptmain.Month01.DBO.LCDB' in

View 1 Replies View Related

Can U Just Explain....

Nov 3, 2007

Query:
insert into aaaa values(',Â?'&',',Â?'&');

BUJJI.

View 6 Replies View Related

Can Some Explain This To Me?

Oct 25, 2007

Hello,I run the following query on a database (SQL 2005):delete from person where pers_companyid in (select pers_companyid fromcompany where comp_expiry is not null)I wanted to delete all people associated with a company which had anexpiry date. The mistake I made was that the column pers_companyiddoes not exist. It should have been comp_companyid.However, the query ran anyway and deleted all records from my persontable? If I run the subquery on its own then it doesn't run as thecolumn is missing.Shouldn't I have got an error running this query?Thanks,David

View 2 Replies View Related

Please Explain Transaction For Me

Sep 29, 2007

 I found this on http://msdn2.microsoft.com/en-us/library/86773566.aspx which i am hoping may be able to resolve my problem but I am not sure I actually understand it. Where it shows // Start a local transaction. transaction = connection.BeginTransaction("SampleTransaction");
is the "SampleTransaction" nothing more then just a name of the transaction that is being created through this code or is it actually pointing to something?I believe that I understand the rest. Oh and in case you have a better way of doing what I am needing here is the SQL Query that is pulling info for me to display on a pageSELECT     p.product_ID, p.class_ID, p.category_ID, p.product_name, p.product_desc, p.product_image, p.product_dimension, p.product_o1, p.product_o2,                       p.product_o3, p.product_ac, p.product_ph, p.product_photo, pcl.class_name, pca.category_nameFROM         products AS p INNER JOIN                      productClass AS pcl ON p.class_ID = pcl.class_ID INNER JOIN                      productCategories AS pca ON p.category_ID = pca.category_IDBasically I am using the transaction to perform multiple updates seeing as how I will need to update multiple tables.  private static void ExecuteSqlTransaction(string connectionString){ using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); SqlCommand command = connection.CreateCommand(); SqlTransaction transaction; // Start a local transaction. transaction = connection.BeginTransaction("SampleTransaction"); // Must assign both transaction object and connection // to Command object for a pending local transaction command.Connection = connection; command.Transaction = transaction; try { command.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')"; command.ExecuteNonQuery(); command.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')"; command.ExecuteNonQuery(); // Attempt to commit the transaction. transaction.Commit(); Console.WriteLine("Both records are written to database."); } catch (Exception ex) { Console.WriteLine("Commit Exception Type: {0}", ex.GetType()); Console.WriteLine(" Message: {0}", ex.Message); // Attempt to roll back the transaction. try { transaction.Rollback(); } catch (Exception ex2) { // This catch block will handle any errors that may have occurred // on the server that would cause the rollback to fail, such as // a closed connection. Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType()); Console.WriteLine(" Message: {0}", ex2.Message); } } }} 

View 1 Replies View Related

Please Explain This SQL Trigger

Sep 25, 2005

I am new to triggers.I am unsure why on the SELECT statement the ROLLBACK is enforced. I thought this would be only for when an attempt is made to insert the data. Also what is the pupose of IF @counter = 1 When does this counter change value? USE Northwind GO CREATE TRIGGER PriceCheck     ON [Order Details]     FOR INSERT     AS DECLARE @counter int     SET @counter= @@ROWCOUNT     DECLARE @unitprice money     DECLARE order_details_insert_cursor CURSOR FOR         SELECT Unitprice             FROM inserted     IF @counter = 1         BEGIN             IF(SELECT UnitPrice FROM inserted) > 300             -- If the price entered is greater than 300             BEGIN                 --print a warning                 PRINT 'Cannot enter price greater than 300'                 --Take back the command                 ROLLBACK TRANSACTION             END         END     ELSE         BEGIN             OPEN order_details_insert_cursor             FETCH NEXT FROM order_details_insert_cursor INTO @unitprice             WHILE @@FETCH_STATUS = 0                 BEGIN                     if @unitprice > 300                     -- If the price entered is greater than 300                     BEGIN                     --print a warning                         PRINT 'Cannot enter price greater than 300'                         --Take back the command                         ROLLBACK TRANSACTION                         RETURN                         --Exit trigger immediately                     END                     FETCH NEXT FROM order_details_insert_cursor INTO @unitprice                 END                                  CLOSE order_details_insert_cursor             END                          DEALLOCATE order_details_insert_cursor

View 3 Replies View Related

Explain Xp_cmdshell

Feb 2, 2006

I've got some questions about xp_cmdshell and was hoping someone could explain some things to me.  I've written a trigger for a table that uses 'xp_cmdshell' to launch a VB.Net application.  The syntax is correct and it "seems" to work but in the way I intended.  When the app launches there is no GUI present but when I check the task list I see that the exe has been launched.  This intrigued me so I started playing with it in Query Analyzer.  When I run the commandexec master.dbo.xp_cmdshell 'C:WINDOWSsystem32otepad.exe'I seen a process for notepad show up on my processes list started by 'system' as opposed to myself and notepad still doesn't open up for me to see.  I also noticed that the query executes until I kill that particular process.  I'm guessing cause it's waiting on the tranaction to complete.  What I want to know though is why can't I see the program when I open like this?  Is it because 'system' is the user behind it or is there some extra parameter to throw in the xp_cmdshell?  

View 3 Replies View Related

EXPLAIN Function

May 19, 2008

Is there some equivalent to MySQL's EXPLAIN function? (or some utility that does the same thing?)

Basically the function takes a query and will return a list of the tables in the query and the number of rows that would need to be checked. It's used to check how well the indexes are set. (If a table returns a very high number, that could be a bottleneck for the query).

Any help is appreciated. Thanks.

-D. Israel

View 1 Replies View Related

Explain Plan

Mar 17, 2008

Dear All,
what exactly is ExplainPlan and maintenance plan? and where would we use these?


i've found some artilce in the NET, but i'm not satisfied


please provide me good articles and your words for me

Vinod
Even you learn 1%, Learn it with 100% confidence.

View 1 Replies View Related

Can Someone Explain This Behaviour?

Jul 23, 2005

Hello All,The following script is reproducing the problem assuming you haveNorthwind database on the server.Please note it gives you the error message on line 12.USE tempdbGOsp_addlinkedserver 'Test17'GOsp_setnetname 'Test17', @@SERVERNAMEGOIF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id =object_id(N'[dbo].[This_works]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)DROP PROCEDURE [dbo].[This_works]GOCREATE PROCEDURE This_works@UseLinkedServer bit = 0-- WITH RECOMPILE -- Does not helpASSET NOCOUNT ONIF @UseLinkedServer = 1 -- Linked ServerBEGINIF EXISTS (SELECT 1 FROM dbo.sysobjects where id =object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)DROP TABLE dbo.Orders_TMPSELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.OrdersENDELSE -- LocalBEGINIF EXISTS (SELECT 1 FROM dbo.sysobjects where id =object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)DROP TABLE dbo.Orders_TMPSELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.OrdersSELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?ENDBEGIN TRANSACTIONSelect 'Line 25'SELECT COUNT(*) FROM dbo.Orders_TMPCOMMITgoIF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id =object_id(N'[dbo].[This_does_not]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)DROP PROCEDURE [dbo].[This_does_not]GOCREATE PROCEDURE This_does_not@UseLinkedServer bit = 0-- WITH RECOMPILE -- Does not helpASSET NOCOUNT ONIF @UseLinkedServer = 1 -- Linked ServerBEGINIF EXISTS (SELECT 1 FROM dbo.sysobjects where id =object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)DROP TABLE dbo.Orders_TMPSELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.OrdersENDELSE -- LocalBEGINIF EXISTS (SELECT 1 FROM dbo.sysobjects where id =object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)DROP TABLE dbo.Orders_TMPSELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.Orders--SELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?ENDBEGIN TRANSACTIONSelect 'Line 25'SELECT COUNT(*) FROM dbo.Orders_TMPCOMMITGOPRINT 'This_works'EXECUTE This_works 0PRINT ' 'PRINT 'This_does_not'EXECUTE This_does_not 0Thanks for any help or hint,Igor Raytsin

View 8 Replies View Related

Explain Plan

Jul 20, 2005

Hello.Does someone of you know if is there a 'explain plan' like function in SQLServer (similar to explain plan in Oracle or DB2)If so then how it works, where stores data and how it can be retrieved?Best regardsBagieta=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=dbDeveloper - Multiple databases editorhttp://prominentus.com=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

View 5 Replies View Related

Could Someone Explain This Script To Me

Jan 29, 2008

Hi,

Could someone help me understand this script:

DECLARE @TempValue tinyint

SELECT @TempValue = 1
FROM dbo.TABLE_NAME (tablockx updlock holdlock)
WHERE 1 = 2

To my understanding, it would lock all the row in the table at once.

View 14 Replies View Related

Can Someone Explain This Error Please

Oct 9, 2007



Upon executing the following:


create function test()

returns @tmp table

(

somevalue varchar(max)

)

as

begin

select '' as ttt

into tmp


insert into @tmp(somevalue)

select ttt

from tmp


return

end



I get this error:

Msg 443, Level 16, State 15, Procedure test, Line 8

Invalid use of side-effecting or time-dependent operator in 'SELECT INTO' within a function.



I've searched around and honestly cannot find a definitive description of what this means. Can anyone explain in simple terms for a simpleton like me?

Thanks
Jamie

View 18 Replies View Related

Could Anybody Explain To Me Why Sqldatadpater Does Not Allow Subqueries?

Aug 17, 2007

Dear experts,
Recently i got an error msg looks like this: you cannot use subqueries within a sqldatadpter except the subquery is introduced with EXISTS.
Well, actually i was using IN.
I know I can revise my query sting to use INNER JOIN or such stuff just to remove the nested queries. But i'm realllllly curious why it's not allowed??
Really appreciate it if some expert can tell me.
Thanks in advance

View 1 Replies View Related

You Can't Explain This! ARRRRG! SQL Ghost!

Dec 23, 2007

Hello all,
Where do I start?  My son and I wrote a small ASP.net 2.0 website on XP IIS 5.  Works ok with the standard login controls.
I FTP'ed the files up to a Windows server2003.  I installed the SQL publishing wizard.  I created a .sql file of the data base.
I ran the file in SQL2005 manager to build the data base on SQL express running on the Windows 2003 server.  It installed,  no errors.  great!
The website would not run with Data base connection errors ,  like files or data base was read only,  duplicate database ,  can not run mydatabase.create.  something like that.
I created a new app pool and made sure network service was the account.  Gave network server permissions to the folders,  it started working great.
Now sit down.  I created a few accounts using the website  ok,  no errors, Great!   Then check the database to see if the passwords were encrypted.  The new accounts were not there. 
But the accounts went somewhere????   Must be two databases????  One hidden?
I detached the database I created with the .sql script file....  to see if I would get an error on the browser.  nope still worked. Hmmmm other hidden database still working.... I stopped database server
in the management tool,    It still worked....Ok, I'll get the house check for poltergiests.
I went to SERVICES.  The service was stopped(SQLEXPRESS)..  I changed auto start to manual.  Browsed back to the site again,  finally an error.
I restarted the service,  website worked again,  but the database,  the only user database,  is still detached.  Created more user accounts with no error....
Seached the hard drives for .MDF files.  Found the file I detached and also the ASPNETDB.MDF that I orginally FTP'ed to the server earlier in the APP_DATA folder of the website.  I renamed the  
MDF file to see if then I would get an error,  yep I did.    It then automatcly re-created the MDF file exactly the same size.  Two files in the directory now and now a new error in the browser
about a database mismatch.  Ok  that makes sense.   But it will not allow me to rename the new file so I can rename the original. 
Question is how does SQLEXPRESS run the ASPNETDB.MDF file in the web folder which is not attached to the database engine? 
Why use a SQL pub wizard if ASP.NET creates the database automaticly? Or just "Runs" it?    AM I HIGH?
Thanks in advance,  before I take drugs and sit in a dark closet.
-Wade
 
 
 
 

View 4 Replies View Related

Explain Exists() Function

Apr 8, 2008

Can any one explain to me how to use the exists() function and what exactly it is used for?

View 1 Replies View Related

Can Someone Explain The Precision Of An Integer In A Sql Db Pls

Nov 3, 2003

Hi I am in the process of creating a new db in sql. In my users table I wish to set the UserIds as Integer datatype. It defualts on precision 4. Does this mean that when the column auto increments as its my primary key with a seed of one, my highest number allowed in the table would be row 9999. ???

Also if you where to store a phone number in your db, what column type would you give it. I have used varChar but its all numbers i want to store. Would this suffice.

Thanks

View 1 Replies View Related

Mysql EXPLAIN = What In MS SQL 2000

Oct 29, 2004

If you have a table named Users then using mysql
you can run EXPLAIN Users; to get all the fields & types, etc.
How would you do this same thing in SQL 2000?

View 1 Replies View Related

Can Someone Explain This Strange Value Of @@ROWCOUNT?

May 31, 2006

I have noticed the following strange situation, but cannot find any explanation.  The @rows variable is 1 , even though the @@ROWCOUNT value just after the select statement is 0. (I have checked this by stepping through the SP).
update Products set ParentId = @parent where ParentId = @lastParentIdset @err = @@ERRORset @rows = @@ROWCOUNT
However, if I set @rows before @err then the @rows is correctly set to 0. (as in script below).
update Products set ParentId = @parent where ParentId = @lastParentIdset @rows = @@ROWCOUNTset @err = @@ERROR
Can someone please explain why this is happenning?

View 3 Replies View Related







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