Forum Replies Created

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

  • Reply To: Are the posted questions getting worse?

    Michael L John wrote:

    below86 wrote:

    Is there any circumstances where you would allow code with a  'SELECT * ' to go into production?

    I believe this should NEVER happen.  There is a discussion that...

    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".

    February 21, 2025 at 8:39 pm

    #4543269

  • Reply To: Query Help to show record in single row 

    SELECT
        LEFT(Bname, CHARINDEX(' ', BName) - 1) AS BName,
        MAX(CASE WHEN BName LIKE '% Start%' THEN StartDate ELSE '' END) AS...

    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".

    February 21, 2025 at 6:02 pm

    #4543189

  • Reply To: How to convert run_date and run_time columns to datetime from SQL Agent systable

    I don't know of a super-slick way to do this off the top of my head:

    SELECT DISTINCT run_date, run_time, 
        CAST(CAST(run_date AS varchar(8)) +...

    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".

    February 10, 2025 at 3:32 pm

    #4534296

  • Reply To: Solution to a problem without using a CURSOR

    OOPS, SORRY, I did it back'ards.

    SELECT [Profile ID]

    FROM dbo.table_name

    GROUP BY [Profile ID]

    HAVING COUNT(DISTINCT [Customer ID]) =

    (SELECT COUNT(DISTINCT [Customer ID]) FROM dbo.table_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".

    February 7, 2025 at 7:18 pm

    #4532408

  • Reply To: Solution to a problem without using a CURSOR

    SELECT [Customer ID]

    FROM dbo.table_name

    GROUP BY [Customer ID]

    HAVING COUNT(DISTINCT [Profile ID]) = (SELECT COUNT(DISTINCT [Profile ID]) FROM dbo.table_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".

    February 7, 2025 at 3:08 pm

    #4532239

  • Reply To: Data Joining technique help

    You should definitely switch the key order on the lookup table, i.e., not:

    PRIMARY KEY CLUSTERED ( iid, cart_id )

    but instead do this:

    PRIMARY KEY CLUSTERED ( cart_id, iid )

    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".

    February 3, 2025 at 5:21 pm

    #4528921

  • 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

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