|
If I understand you just need to delete all rows in table C related to the rows being deleted in A or B. C uses a sort of composite key - Primary + TableLetter to reference either table. So why can't your trigger do something like:
DELETE FROM C WHERE EXISTS (SELECT * FROM DELETED WHERE C.ID = DELETED.ID) AND C.Letter = 'A'
or
DELETE C FROM DELETED
INNER JOIN C ON C.ID = DELETED.ID
WHERE C.Letter = 'A'
|
|
|
|
|
Thank you! It worked perfectly!
modified on Monday, March 10, 2008 4:58 AM
|
|
|
|
|
By default SQL Server 2005 creates new database files in the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA folder. What I would like to do is set the default folder to D:\SQLDatabases.
Can anyone tell me if this is possible and if so, how?
Thanks
Steve Jowett
-------------------------
Sometimes a man who deserves to be looked down upon because he is a fool, is only despised only because he is an 'I.T. Consultant'
|
|
|
|
|
|
I have a project done in ASP.NET with SQL Server 2005. It's ecommerce application, and product id's using GUID column. What would be the advantage for this GUID over identity columns ? I think both are unique, then what would be the difference ?
|
|
|
|
|
N a v a n e e t h wrote: I think both are unique, then what would be the difference ?
The difference is that GUID's are assured to be unique across all space and time. And unlike an Identity int column, GUID's wont run out of range. But the disadvantage being that they're large in terms of storage and consequently, any indexes on them will be large and slow.
If you think that your application will need an unique key across all machines, use GUID. It's mostly used when you're using multiple instances of SQL Server and data needs to be consolidated in the end.
Regards.
|
|
|
|
|
There is another reason for using a GUID - unlike an identity, you can control the assignment, so you don't need to retrieve it and pass it back out to the calling application.
|
|
|
|
|
Pete O'Hanlon wrote: unlike an identity, you can control the assignment
Thanks pete. Can you please make it more clear ? What do you meant by "controlling assignment" ?
|
|
|
|
|
It means that you can actually assign the value yourself. For instance, if your calling code was in C# you will do this using:
Guid myGuid = Guid.NewGuid();
|
|
|
|
|
GUIDs are nice for replication. You can create a new GUID and know that its not in use - which can be easier than asking the database for the next identity value.
You can also assign a new GUID in the field, bring the record back and not have to change the primary key.
Apart from this minor inconvenience they are big, ugly, and a pain to debug.
MyBusinessObjects.PurchaseOrder#849 is a lot easier to look at than MyBusinessObjects.PurchaseOrder#aaA-aaaAAr-RRRR-GH834989-3849.
Don't use them unless you can forsee a need to.
Edit: You said ecommerce - accountancy folks and auditors love sequential numbers on things. If you can't give them a sequential invoice number, they'll probably make you stick another column on anyway.
|
|
|
|
|
Thanks mark. Thank you very much
|
|
|
|
|
I'm trying my hand at using LINQ to SQL as a data source for a little pet project. My main requirement is to capture an invoice, and for this I have a simple master-detail screen with an InvoiceBindingSource and an InvoiceLineBindingSource. The invoice source binds to several textbox edit fields etc. and lines source to a DataGridView. Now when I try and insert a second invoice record, I get a primary key violation because the invoice id 0 already exists. How do I get LINQ to recognise the identity column?
|
|
|
|
|
Sorry, that was a brain fart. I hadn't made the field IDENTITY in the DB, so the O/R designer didn't set the Auto Generated Value flag.
|
|
|
|
|
Hi, I have this error when doing 'sum' on table,
whereas I convert it fist before doing 'sum'.
here is my code:
sum(convert(decimal(5,5),value)).
After I look the data 'value', I found that there is
char '%', here is my table:
'value'
2.131 %
2.412 %
2.612 %
So any suggestion? or is there a code for mid(string,start,length) in sql
Thanks for your help.
|
|
|
|
|
Try
select sum(convert(decimal, replace(value, '%', ''))) from table1
Good Luck.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Hi,
Thanks, it's work.
Then I adding char '%' again to my 'value'
convert(varchar,sum(convert(decimal, replace(value, '%', ''))))+' %',
Regards,
|
|
|
|
|
Hi,
I was advised to stay away from using identity seeds where the number increments inteself by everytime but to use a varchar(10) for example. And I was also told that having a varchar ID slows down insert/update/ But inserting/updating 1 record shouldn't be that bad? But I'm more worried about sting searches on the ID. Will it slow down searches??
Can I please hear your thoughts on this? I am looking for best practices please. I am using SQL Server 2005.
Regards,
Brendan
|
|
|
|
|
.NET Enthusiast wrote: I was advised to stay away from using identity seeds where the number increments inteself by everytime but to use a varchar(10) for example. And I was also told that having a varchar ID slows down insert/update/ But inserting/updating 1 record shouldn't be that bad? But I'm more worried about sting searches on the ID. Will it slow down searches??
If you must use string based IDs then you should go for fixed length columns, e.g. CHAR(10). However, it is better to use an INT with an identity on it. It will be much more efficient.
|
|
|
|
|
Thanks for both replies Colin.
|
|
|
|
|
Hi,
I have a customer ID of type varchar(10) and I want to use it like a identity seed. I want the first customer to start with 1000 and then incremented each time a new customer is added. I could probably do this with C#, but I think a safer option will maybe be to do it with SQL Server 2005??
Please can someone advise me on this? I need to check first if a record exists, if not then the ID is 1000, other get the number and increment it with 1.
I hope to hear from someone soon.
Regards
Brendan
|
|
|
|
|
.NET Enthusiast wrote: I have a customer ID of type varchar(10) and I want to use it like a identity seed. I want the first customer to start with 1000 and then incremented each time a new customer is added. I could probably do this with C#, but I think a safer option will maybe be to do it with SQL Server 2005??
Please can someone advise me on this? I need to check first if a record exists, if not then the ID is 1000, other get the number and increment it with 1.
Why use a varchar? Why not declare the column as INT IDENTITY(1000,1)? That would do everything you described that you want and be a lot safer than writing your own mechanism.
|
|
|
|
|
If I have an int identity starting at 1000 then the ids will obviously be higher. It won't affect performance when searches are being done?
|
|
|
|
|
.NET Enthusiast wrote: If I have an int identity starting at 1000 then the ids will obviously be higher. It won't affect performance when searches are being done?
They are all stored as 32bits whether is it 1, 1000, or 2147483647. The value of the number is not going to affect performance.
|
|
|
|
|
Hi,
I am developing a web page for searching user records from DB. In advance search option there are 5 fields like firstname, lastname, years of experience,experience type, location.
I want to write a storedprocedure which take all these 5 parameter as input and return me record from a table.All these fields are in single table.
User can search a record by providing all the 5 fields are by providing any of these.
Plz help me in writing this query.
I am very week in T-Sql.
Thanks in Advance
Alok....
Alok...
|
|
|
|
|
Write a stored procedure that takes the values you want. Pass the value the user is searching for, or null if no value on that column
Then your query can look like:
select * from sometable
where
(ColumnA = @SearchFieldA or @SearchFieldA is null)
and (ColumnB = @SearchFieldB or @SearchFieldB is null)
etc
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
