|
|
|
|
|
|
|
|
Question of the Day |
Today's question (by Steve Jones - SSC Editor): | |
Changing the PK - I | |
I've got this table:
CREATE TABLE Invoice ( InvoiceID INT NOT NULL IDENTITY(1,1) CONSTRAINT InvoicePK PRIMARY KEY , InvoiceDate DATE , CustomerID INT); GOI want to change the data type of the PK to a character type. I run this code: BEGIN TRAN DECLARE @e INT = 0 ALTER TABLE dbo.Invoice DROP CONSTRAINT InvoicePK IF @@ERROR<> 0 SELECT @e = 1 ALTER TABLE dbo.Invoice ALTER COLUMN InvoiceID VARCHAR(20) NOT NULL IF @@ERROR<> 0 SELECT @e = 1 ALTER TABLE dbo.Invoice ADD CONSTRAINT InvoicePK PRIMARY KEY (InvoiceID) IF @@ERROR<> 0 SELECT @e = 1 IF @e = 0 COMMIT ELSE ROLLBACKDoes this work? | |
Think you know the answer? Click here, and find out if you are right. |
Yesterday's Question of the Day (by Steve Jones - SSC Editor) |
The Temporary Synonym What happens when I run this code? CREATE SYNONYM TempSyn FOR tempdb.#mytable Answer: This works Explanation: This works fine. Synonyms don't check for existence of the object. Ref: CREATE SYNONYM - https://learn.microsoft.com/en-us/sql/t-sql/statements/create-synonym-transact-sql?view=sql-server-ver16 |
Database Pros Who Need Your Help |
Here's a few of the new posts today on the forums. To see more, visit the forums. |
SQL Server 2017 - Administration |
Execute AS and Impersonate - I am trying to write up a stored procedure to allow users to execute jobs in MSDB that are owned by another user. The job-owning user is a service account (set up as non-interactive) has elevated permissions which I do not want the business users to have themselves. The problem is that I can't seem […] |
SQL Server 2016 - Development and T-SQL |
Find the active Group - Hello All, I have a requirement where I am trying to get the active group number. A groupnum can have multiple membid's and any of them can be active. I looking to find the groupnum's with only one "Active" memberid. If the memberid is active can be determined by not having any 'Cancelled' transtype. I […] |
SQL 2012 - General |
Can I still get a SQL 2012 eval for my home lab? - Just for testing upgrades. All the links are dead. I understand it's not in support but like to test the full upgrade properly. |
SQL Server 2019 - Administration |
Strange SQL Login failure issue. - I have 2 x servers. The same 3rd party app on each. The same config screen for SQL login details. Listener details, windows auth, domain\user, password. All details are correct & the same. Server 1 - connection into SQL is made, all good. Server 2 - Login failed for user 'domain\user' ... Attempting […] |
Moved to Development topic - Moved to Development topic |
SQL Server 2019 - Development |
Importing a MySQL database - I've used the Import Wizard in SSMS a few times. Mostly to get data from one server to another. Occasionally making some transformations. I may be involved in a new project to migrate an old PHP app to .NET. The app uses an old version of MySQL, version 5.1.41-community. I don't know, yet, if there […] |
Query to find latest full backup with all database except tempDB - Hi All, I am looking for query to get latest full backup with all database except tempDB. I have got query from internet and trying to make changes looks like some databases are missing from sys.databases. I wanted to do daily check of last full, Differential and Log backup. if I get query for full […] |
Replacing last few characters with another value. - I'm hoping for a bit of assistance, I know this is probably easy but just having a hard time solving it. What I'm hoping to achieve is the string of '000-0000-0000-0000-000000000000' gets changed to the values of '000-0000-0000-0000-000000000001' '000-0000-0000-0000-000000000099' '000-0000-0000-0000-000000000888' '000-0000-0000-0000-0000000005555' Any help would be appreciated DROP TABLE IF EXISTS #test CREATE TABLE #test ([ValueID] […] |
Avoiding Conversion errors - Hello All, Couple of questions on conversion errors. First I have to convert since my reference table stores numeric and character data. Is it bad practice to allow implicit conversions? Why does Convert to INT intermittently fail with a conversion error? I can't replicate this with my limited data but it is happening on my […] |
Table scan shows too many records - Looking at the query plan for a rather complicated select, which draws from views, I see the very beginning of the plan shows a scan of one of the root tables, but it shows that it is returning well over six million records, from a table that contains only around 67,000. It even shows those […] |
Need help with the logic - I have some date variables which are used in the SP but one of the main table is going away and I am trying to come up with a solution so I can get the correct date. I haven't been successful so I thought I'd ask experts. Below is the original code declare @asofdate datetime […] |
Reporting Services |
Subtotals in a filtered matrix - I've been trying to get this to work for months, so I come pleading for help. I have the following matrix. The column and row I will reference are artistically shown below. There is a field which outputs either a 1 or a 0 based on whether it is a "supplementary event" or not. For […] |
Integration Services |
convert to date from datetime - I created the following variable to return the date 25 months ago - all fine. DATEADD("m", -25, DATEADD("d", -DAY(GETDATE()) + 1, GETDATE())) I am having difficulty casting to date datatype, when I add DT_DBDATE (DT_DBDATE) DATEADD("m", -25, DATEADD("d", -DAY(GETDATE()) + 1, GETDATE())) it throws an error - expression cannot be evaluated. with the message of: […] |
SQL Server 2022 - Administration |
time for stats to get updated when auto update statistics async = true - hi, I'm using the following database version of MS SQL SERVER Microsoft SQL Server 2022 (RTM-GDR) (KB5029379) - 16.0.1105.1 (X64) I was experimenting with the Auto Update Statistics Async = TRUE in my test database and noticed that the time it took for my table column stats to get updated took anywhere from immediate to […] |
SQL Server 2022 - Development |
Trigger to track changes - I need to track changes when a column is updated on two tables. Is there a way to do it. please send code for that. |
| |
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com |