Forum Replies Created

Viewing 15 posts - 4,171 through 4,185 (of 7,619 total)

  • RE: Add columns to multiple tables

    You can add all 5 columns to a given table in one statement, but you need separate statements for each 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".

    September 6, 2016 at 10:44 am

    #1899401

  • RE: Indexing - Should primary key be a natural key or an identity?

    I reject entirely the notion that there is such a thing as a "default" clustering key. That is the issue to me. While certainly there are times when...

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

    September 6, 2016 at 10:40 am

    #1899398

  • RE: Why does the query optimizer choose this way?

    If the overall number of pages to be read is small either way, it's just not worth the optimizer's time to keep going.

    SQL naturally favors the clustered index. Overall...

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

    September 2, 2016 at 9:55 am

    #1899009

  • RE: Add unique valules to a heap

    Sean Lange (9/2/2016)

    ScottPletcher (9/2/2016)

    A clustering index is enough to avoid having a heap, you don't have to have a primary key. In particular, don't slap an identity column 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".

    September 2, 2016 at 9:53 am

    #1899007

  • RE: How to get list of all tables/index that are not compressed for all Databases in an instance

    Don't have time right now to fully test/refine this, but it should be close at least:

    SELECT DISTINCT

    SERVERPROPERTY('servername') [instance]

    ,DB_NAME() [database]

    ,QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) +'.'+QUOTENAME(Object_name(p.object_id))

    ,p.data_compression

    ,p.data_compression_desc

    FROM sys.partitions p

    INNER JOIN (

    SELECT container_id, SUM(total_pages)...

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

    September 2, 2016 at 8:28 am

    #1898990

  • RE: Add unique valules to a heap

    A clustering index is enough to avoid having a heap, you don't have to have a primary key. In particular, don't slap an identity column on the table just...

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

    September 2, 2016 at 8:00 am

    #1898987

  • RE: Is it a bad idea to declare a cursor within a TRY block?

    Luis Cazares (9/1/2016)

    drew.allen (9/1/2016)

    First, the mantra is "It's a bad idea to declare a cursor. Period. End of sentence." 😀

    Drew

    Second, cursors can be local or global and the default is...

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

    September 1, 2016 at 1:44 pm

    #1898886

  • RE: Add unique valules to a heap

    Look at the missing index stats and the index usage stats to help determine a useful clustered index. Only if no useful clustering key exists, and remember to consider...

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

    September 1, 2016 at 1:40 pm

    #1898883

  • RE: Temporary Functions?

    CELKO (9/1/2016)

    ]I've found myself creating loads of functions recently which are typically used for one specific task and rarely required again.

    This is not how we intended SQL to be...

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

    September 1, 2016 at 12:35 pm

    #1898859

  • RE: CREATE INDEX statement rollback doesn't behave as expected

    Rollbacks very often take more time than the initial modification. They are only very quick if all the pages are still in memory.

    Are there nonclustered index(es) on the 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".

    September 1, 2016 at 11:10 am

    #1898837

  • RE: DELETE making SELECT slow in RCSI

    TheSQLGuru (8/31/2016)

    ScottPletcher (8/31/2016)

    TheSQLGuru (8/31/2016)

    The optimizer can and often does (in my experience) introduce a SORT into the query plan on a small table to facilitate a merge join against a...

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

    August 31, 2016 at 2:55 pm

    #1898659

  • RE: DELETE making SELECT slow in RCSI

    TheSQLGuru (8/31/2016)

    The optimizer can and often does (in my experience) introduce a SORT into the query plan on a small table to facilitate a merge join against a larger 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".

    August 31, 2016 at 1:20 pm

    #1898643

  • RE: DELETE making SELECT slow in RCSI

    TheSQLGuru (8/31/2016)

    ScottPletcher (8/31/2016)

    TheSQLGuru (8/31/2016)

    ScottPletcher (8/31/2016)

    TheSQLGuru (8/31/2016)

    ScottPletcher (8/31/2016)

    Tarun Jaggi (8/29/2016)

    Hi All,

    I have a table which has million rows and database is set under RCSI. The table has a clustered primary key...

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

    August 31, 2016 at 1:10 pm

    #1898639

  • RE: DELETE making SELECT slow in RCSI

    TheSQLGuru (8/31/2016)

    ScottPletcher (8/31/2016)

    TheSQLGuru (8/31/2016)

    ScottPletcher (8/31/2016)

    Tarun Jaggi (8/29/2016)

    Hi All,

    I have a table which has million rows and database is set under RCSI. The table has a clustered primary key on Column1...

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

    August 31, 2016 at 12:05 pm

    #1898623

  • RE: DELETE making SELECT slow in RCSI

    TheSQLGuru (8/31/2016)

    ScottPletcher (8/31/2016)

    Tarun Jaggi (8/29/2016)

    Hi All,

    I have a table which has million rows and database is set under RCSI. The table has a clustered primary key on Column1 and a...

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

    August 31, 2016 at 11:49 am

    #1898619

Viewing 15 posts - 4,171 through 4,185 (of 7,619 total)