Forum Replies Created

Viewing 15 posts - 5,416 through 5,430 (of 7,619 total)

  • RE: sum result type character

    You can use sp_executesql, something like this:

    DECLARE @WIDTH decimal(19, 2)

    DECLARE @LONG decimal(19, 2)

    DECLARE @RESULT decimal(19, 2)

    DECLARE @sql nvarchar(4000)

    SET @sql = N'( @WIDTH / 3 ) + ( @LONG / 100)'

    SET...

    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 2, 2015 at 11:17 am

    #1774840

  • RE: 'sys.dm_db_index_physical_stats' giving 3 rows with different fragmentation.

    Since the page count is vastly smaller, I would think it's the highest level of the index rather than the lowest (leaf) level.

    But you should definitely change the query to...

    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 2, 2015 at 11:05 am

    #1774834

  • RE: How to speed up purge process

    Yeah, not bad, although it does have to do a full index scan for trans date on the TD table.

    You can do minor tweaks on the list itself, changing it...

    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, 2015 at 2:19 pm

    #1774478

  • RE: How to speed up purge process

    Ken Davis (1/30/2015)

    Scott, the Transactions table is the parent and ID is its primary key and clustered index. ID is the foreign key in TransactionDetails pointing to Transactions so...

    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, 2015 at 1:19 pm

    #1774467

  • RE: How to speed up purge process

    Is the TabletAuditLog.dbo.TransactionDetails table clustered on ID first?

    Just to confirm, the "ID" in the TD table is the same value as the ID in the T table, right?

    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, 2015 at 1:03 pm

    #1774461

  • RE: Help with query - Find recovered customers

    SELECT CustomerNumber,

    SUM(CASE WHEN [YearMonth] BETWEEN 201401 AND 201412 THEN TotalAmount ELSE 0 END) AS Sales_2014,

    SUM(CASE WHEN [YearMonth] BETWEEN 201201 AND 201312 THEN...

    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, 2015 at 9:51 am

    #1774400

  • RE: Problem with a query with join and SUM

    This should give you more accurate totals, although it will list every combination of ProductionLine and Category, which may not be what you want.

    SELECT D.Line AS Line, D.ProductionLine AS ProductionLine,...

    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 29, 2015 at 3:42 pm

    #1774201

  • RE: Clustered index not in Sys.Partions (The metadata is inconsistent. Run DBCC CHECKDB to check for a metadata corruption)

    Dell Dude (1/29/2015)

    ScottPletcher (1/29/2015)

    sp_help elmah gives this for indexes

    So I guess the table name is "elmah"?! It's somewhat confusing with all the different "table names" given.

    DROP INDEX PK_ELMAH_Error ON...

    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 29, 2015 at 2:22 pm

    #1774186

  • RE: How do i update an identity column with identity value?

    David Burrows (1/29/2015)

    dwain.c (1/28/2015)

    David - that's a very clever alternative!

    Thank you :blush:

    It is very annoying that you can use ALTER COLUMN to do everything except add or remove IDENTITY 🙁

    True,...

    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 29, 2015 at 1:03 pm

    #1774162

  • RE: Clustered index not in Sys.Partions (The metadata is inconsistent. Run DBCC CHECKDB to check for a metadata corruption)

    sp_help elmah gives this for indexes

    So I guess the table name is "elmah"?! It's somewhat confusing with all the different "table names" given.

    DROP INDEX PK_ELMAH_Error ON elmah

    DROP INDEX idx_ELMAH_TimeUtc...

    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 29, 2015 at 12:39 pm

    #1774157

  • RE: Clustered index not in Sys.Partions (The metadata is inconsistent. Run DBCC CHECKDB to check for a metadata corruption)

    Btw, be sure to script out the existing indexes before you delete them so you can recreate them easily ;-).

    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 29, 2015 at 12:32 pm

    #1774154

  • RE: Clustered index not in Sys.Partions (The metadata is inconsistent. Run DBCC CHECKDB to check for a metadata corruption)

    Dell Dude (1/29/2015)

    ScottPletcher (1/29/2015)

    Drop all the indexes, starting with the nonclustered, then recreate them, starting with clustered. Unless the table itself is damaged, that's likely your best chance to...

    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 29, 2015 at 12:31 pm

    #1774153

  • RE: Need to increase query performance

    You could try a nonclustered index on a that contains id,

    and a nonclustered index on b that contains id.

    That may or may not help, depending on the total sizes 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 29, 2015 at 11:45 am

    #1774130

  • RE: Clustered index not in Sys.Partions (The metadata is inconsistent. Run DBCC CHECKDB to check for a metadata corruption)

    Drop all the indexes, starting with the nonclustered, then recreate them, starting with clustered. Unless the table itself is damaged, that's likely your best chance to clean this up.

    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 29, 2015 at 11:42 am

    #1774129

  • RE: Display one row for the query

    I believe CROSS APPLY would be more appropriate to the original requirement ... but I just see that may be a moot point now :-D.

    SELECT

    ...

    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 29, 2015 at 10:24 am

    #1774088

Viewing 15 posts - 5,416 through 5,430 (of 7,619 total)