|
SQL Server 2008R2.
Do all defaults need a constraint name? Why would you or would you not use a constraint name?
Example of a SSMS created table script:
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_MyTable_MyDateColumn] DEFAULT (getdate()) FOR [MyDateColumn]
GO
ALTER TABLE [dbo].[MyTable] ADD DEFAULT ((0)) FOR [MyBitColumn]
GO
Thanks
|
|
|
|
|
From what I read on table constraints[^]
It's to maintain backwards compatibility.
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
Thank you! As I have a tendensy to skim the definitions I miss things.
|
|
|
|
|
It's always a good idea to use constraint names. Makes it easy to disable/drop a constraint.
|
|
|
|
|
Hello,
I am executing the following script:
use[SomeDatabase]
DECLARE @TEST_XML TABLE (Column_XML XML)
DECLARE @VAR VARCHAR(MAX) = 'EXECUTE dbo.sp_GetData 3229';
INSERT @TEST_XML EXEC (@Var)
SELECT * FROM @TEST_XML
any clues as to why I am receiving this error message, "The FOR XML clause is not allowed in a INSERT statement"? How can I get around this?
I really just need to get this data into a temp table.
|
|
|
|
|
iCanDivideByZero wrote: any clues as to why I am receiving this error message
Because "dbo.sp_GetData" uses a FOR XML clause.
iCanDivideByZero wrote: How can I get around this?
Remove the XML clause.
iCanDivideByZero wrote: I really just need to get this data into a temp table.
Than what's the use of using XML?
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
I have one column table with negative and positive values and want to display positive and negative values in different columns using SQL Query.
Column
-10000
-17000
16000
25000
output should be like
A B
-----------------
-10000 16000
-17000 25000
|
|
|
|
|
And how do you determine that 16000 and -10000 belong into the same column, but not 16000 and -25000? What's the rule behind that?
|
|
|
|
|
One of the problems you will have with creating a query to do this is that the column of numbers will most likely not have an equal number of positive and negative values. Even if it does, I don't know of a way to create a single query to create the output you illustrate. The best I could do in this situation is the following which will output the same number of rows as in the original table but simply put a NULL value in the column that does not apply:
SELECT
CASE
WHEN NumberField > 0 THEN NumberField
ELSE NULL
END AS PositiveNumbers,
CASE
WHEN NumberField < 0 THEN NumberField
ELSE NULL
END AS NegativeNumbers
FROM NumbersTable
Of course, you also have to decide what exactly to do with values of zero.
-NP
Never underestimate the creativity of the end-user.
|
|
|
|
|
thanks! that is useful for me.
|
|
|
|
|
With SQL Server 2012?
SELECT A.[Column] A
, B.[Column] B
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY [Column] DESC) RN , [Column] FROM [Table] WHERE [Column]<0)A
FULL OUTER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY [Column]) RN , [Column] FROM [Table] WHERE [Column]>0)B
ON A.RN=B.RN
|
|
|
|
|
A good example of CAN be done versus SHOULD be done...
But, well done...
|
|
|
|
|
What? I didn't use CTEs. 
|
|
|
|
|
Erk - I mean I like ROW_NUMBER but that is just... I agree with Tim!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am doing some experimenting with using SQL Azure as the data store for a Access 2010 Application. I have created a test database in Access, migrated it to SQL Azure using Microsoft's Server Migration Assistant, and can connect to the migrated Tables both using the links created by the Migration Assistant and using ODBC connections which I have created manually, using the master credentials for the Azure Server. I have created a new login on the Azure server, associated it with a user name for the test database, and given that user read and write privileges on that database. However, when I try to create an ODBC connection with the new login information, the Create New DataSource wizard fails when I 'Finish', saying that "Access to selected database has been denied". Does the limited login/user need some additional privileges, either on the SQL master database or on the test database, or am I missing something else? Almost all the material on the web seems to assume that the developer wants to use the master credentials in this situation (which does, indeed, work), but I don't!
|
|
|
|
|
Hi,
I have a design question that I was hoping a professional could answer. Lets say I have the following one-to-many table relationship between an Account and its Addresses.
create table account (
acctkey int,
acctname varchar(100),
active bit
)
create table acctaddress (
acctkey int,
addkey int,
street1 varchar(100),
isprimary bit
)
An account can have many addresses but only one can be the primary. Does it make sense to include a soft link in the main table to hold the key of the address that is the primary? ie.
create table account (
acctkey int,
acctname varchar(100),
active bit,
primaryaddkey int <
)
This is purely to return the primary address of account (with the best performance in mind). Is this a bad design? Does the need to sync the account and acctaddress tables outweigh the performance advantages?
I would appreciate any thoughts on this.
Thanks
Ryan
|
|
|
|
|
I would go on the side of storing information regarding addresses in the address table rather than the account table.
So have a column on the address table showing it to be the primary address - something like addressContext nvarchar(max).
In the end it's not a deal-breaker but it does mean that you are de-coupling your data(not sure that is the correct term) by only storing account information in the account table and address information in the address table.
Also don't use varchar(100) as you will run out of room sooner than you think - use nvarchar(max) instead, there will not be much of a performance hit and you are allowing for large addresses.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
I'd disagree with the nvarchar(max), I would only ever use nvarchar if it was a requirement to store unicode and I would place a size limit on the address, make it large but don't use max unless you are intending to use it which an address will never do.
If some pillock has a couple of thousand characters in his address then truncate the idiot, not the data, the pillock!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I would never do the PrimaryAddKey flag on the Account table, I don't even care about the performance difference (unless you have mega addresses it would be minuscule) it introduces a vulnerability into your schema and is therefore wrong.
I would however create an Account view that included the primary address but then I'm a great beleiver in views 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I actually raised this question because I'm at odds with the design. Thanks for the sanity check
|
|
|
|
|
As others have said, I'd leave the primary indicator in the address table, not the account table.
Conceivably, the primary address for an account could change, in which case the address table is updated. The account hasn't changed - the primary address has.
Again, as others have noted, create an acount view that includes the primary address.
To that extent, create an account view that shows all addresses and denotes which one is primary.
Tim
|
|
|
|
|
Using an account view is a good idea. Thank you!
|
|
|
|
|
can i access the oracle(scott user) entire table in sql server2012 ? already i tried with SSMA but i got a error i.e, oracle not connecting and shows the sometimes "no listener",objest is not found.? how can i fix it., any one plz help me.
|
|
|
|
|
Please, see this: Oracle Provider for OLE DB[^] to create a linked server to access an Oracle database instance.
I hope it will be helpful.
|
|
|
|
|
Hi i have this function Val(Right([ContractNo],6) and its in Access format how do i change it to SQL Server
Thanx
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
