Forum Replies Created

Viewing 15 posts - 46 through 60 (of 7,612 total)

  • Reply To: Change all occurances of one field in DB

    You could also likely work around changing the db code using views, if changing the code is just too difficult to do quickly by hand.  Typically that doesn't cause performance...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 30, 2025 at 10:08 pm

    #4526402

  • Reply To: Change all occurances of one field in DB

    Yes.

    EXEC sys.sp_rename 'dbo.table_name.column_name', 'new_column_name', 'COLUMN'

    for each table column that you want to rename.

     

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 30, 2025 at 7:08 pm

    #4526331

  • Reply To: Using ROW_NUMBER() to increment every time a value occurs

    OOPS, quite right, I should have used ">=" and "<" rather than "BETWEEN".

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 11, 2025 at 2:43 am

    #4513594

  • Reply To: Using ROW_NUMBER() to increment every time a value occurs

    I have NOT uber-tuned this, just trying to get something that works (so many people here obsess over every microsecond).  If you have a lot of rows, more tuning might...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 10, 2025 at 11:03 pm

    #4513537

  • Reply To: Complex Query (Multiple Tables & Joins) - Need to Restrict to Max Date

    SELECT G.Gage_ID 'ID', G.Model_No 'Model', L.LocationName 'Room', D.GageDescriptionName 'Desc',C.Calibration_DateTime 'Cal', G.Gage_SN 'S/N', S.StatusName 'Status', C.NextDue 'Due'

    FROM GAGETRAK.Gages G

    JOIN GAGETRAK.Locations L ON G.StorageLocation_RID_FK=L.Location_RID

    JOIN GAGETRAK.GageDescriptions D ON G.GageDescription_RID_FK=D.GageDescription_RID

    JOIN GAGETRAK.Status S ON G.Status_RID_FK=S.Status_RID...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 8, 2025 at 11:44 pm

    #4512310

  • Reply To: why no suggested indexes in estimated exec plan?

    stan wrote:

    that lookup went into one of many nested loop join objects in the plan

    (Nested) loop joins are a concern, particularly if SQL doesn't properly estimate the number of...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 8, 2025 at 7:32 pm

    #4512212

  • Reply To: why no suggested indexes in estimated exec plan?

    If you select the query code, right-click on it, and then select "Display Estimated Execution Plan", you should see any recommended index

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 7, 2025 at 8:49 pm

    #4511648

  • Reply To: Join 2 tables 

    SELECT B.*, CASE WHEN A.ServerName IS NULL THEN 'Not present in Table A' 
        ELSE 'Present in Table A' END AS Comment
    FROM TableB B
    LEFT...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 3, 2025 at 8:07 pm

    #4509702

  • Reply To: how to fix this error? 

    ALTER procedure db.ToolReduceLogFileSize
        @db_name nvarchar(128),
        @size_mb int = 1024
    AS
    SET NOCOUNT ON;
    DECLARE @sql nvarchar(max);
    SET @sql = 'USE [' + @db_name + '];...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 2, 2025 at 9:06 pm

    #4509121

  • Reply To: Moving DB to new drive

    More technically, you need permissions for whatever account SQL Server is running under.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 2, 2025 at 6:42 pm

    #4509055

  • Reply To: Using a RETURN

    stevec883 wrote:

    ... How does the @SINGLE_RETURN cause the messages to accumulate? ...

    Steve

    It doesn't.  It's the "+=" that causes the msgs to "accumulate" (concatenate).

    For example, if you do this:

    DECLARE @msg varchar(max);

    DECLARE...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 1, 2025 at 11:16 pm

    #4508577

  • Reply To: need help with assigning a value to a variable

    You can check for NULL as well in either format:

    DECLARE @LATEST_DATE date;

    SELECT @LATEST_DATE = ISNULL(MAX(MY_DATE), ...) FROM DBO.TABLE

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    December 27, 2024 at 3:06 pm

    #4505824

  • Reply To: Testing whether TRY_CONVERT is losing precision

    I think you'll have to check for that yourself, maybe something like this:

    SELECT CASE WHEN TRY_CONVERT(DECIMAL(9,6),'6.125') <> TRY_CONVERT(DECIMAL(5,2),'6.125') THEN 'Loss of precision error' ELSE 'Good' END

    SELECT CASE...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    December 26, 2024 at 10:29 pm

    #4505364

  • Reply To: need help with assigning a value to a variable

    Either below; the first  is more typical:

    DECLARE @LATEST_DATE DATE;

    SELECT @LATEST_DATE = MAX(MY_DATE) FROM DBO.TABLE

    --or:

    SET @LATEST_DATE = (SELECT MAX(MY_DATE) FROM DBO.TABLE);

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    December 25, 2024 at 7:36 pm

    #4504752

  • Reply To: Vendor claims unused table space impacts performance

    It depends.  If the unused space is significant and is embedded in pages that are being read, i.e. contain live data, then it could slow down access (somewhat) because more...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    December 23, 2024 at 7:17 pm

    #4503754

Viewing 15 posts - 46 through 60 (of 7,612 total)