Forum Replies Created

Viewing 15 posts - 1,966 through 1,980 (of 7,619 total)

  • Reply To: alternative to temp tables with 3M+ records and growing

    It shouldn't be clustered, but you do need an index, as I noted above, on:

    ( user_id, allowed_member_id )

    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 15, 2021 at 6:26 pm

    #3833887

  • Reply To: alternative to temp tables with 3M+ records and growing

     If the user has a restriction on the members he can view, we're inserting into a temp table only the members he is allowed to see;

    Isn't the list of allowed...

    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 15, 2021 at 3:07 pm

    #3833817

  • Reply To: CONTAINS any of multiple values 

    contains (PRODUCTS, 'Product1 OR Product2 OR Product3 OR Product4 OR ...')

    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 15, 2021 at 9:08 am

    #3833690

  • Reply To: SQL Permissions

    frederico_fonseca wrote:

    kind of you can.

    https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-server-permissions-transact-sql?view=sql-server-ver15

    • View any database
    • connect any database
    • view any definition
    • select all user securables

    Nice.  I was familiar with the others but not with "select all user...

    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 14, 2021 at 5:41 pm

    #3833449

  • Reply To: Table Elimination In a View

    You specified an INNER JOIN.  That means SQL must check to see if a matching row(s) exist in the other table because, if not, SQL can't return the row.  YOU...

    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 14, 2021 at 5:38 pm

    #3833448

  • Reply To: SQL Permissions

    Your understanding is correct, I think.  As Grant noted, since tables, views, etc., exist only within a database, and not at the instance level, you can't grant permissions to them...

    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 13, 2021 at 11:30 pm

    #3832194

  • Reply To: Better way to flatten meta type data?

    1) You need another table with just ID and NAME.  Repeating the same name in multiple rows is a 1NF violation.

    2) The clustering key to this table should be (ID,...

    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 13, 2021 at 9:58 pm

    #3832152

  • Reply To: Update large table rows within a group

    For example, please provide us the table structure in code and sample data in code form, as INSERT statements:

    IF OBJECT_ID('tempdb.dbo.#Employee') IS NOT NULL
        DROP...

    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 13, 2021 at 9:49 pm

    #3832149

  • Reply To: CAST(NULL AS varchar(1))

    Jeff Moden wrote:

    BOR15K wrote:

    Yes, I also thought about it, but the following example shows it doesn't matter - SQL Server implicitly converts NULL to the correct type, no?

    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 13, 2021 at 9:41 pm

    #3832147

  • Reply To: authgrowth log during rebuil index

    Jeff Moden wrote:

    Grant Fritchey wrote:

    It's completely dependent on the indexes. I know Minion lets you pick lists of indexes & break up the process. I suppose Ola's script must too. Do 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".

    January 13, 2021 at 7:49 pm

    #3832097

  • Reply To: authgrowth log during rebuil index

    And you never want the log to autogrow during index maintenance because it's such a slow process.

    Instead, pre-grow the log to the total size you'll need.  If you really must,...

    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 13, 2021 at 3:30 pm

    #3831985

  • Reply To: Temp Table Strategy

    1. Use a SQL start up proc to create the table.  You could consider a regular table name in tempdb to make sure the table doesn't get dropped even if...

    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 12, 2021 at 4:09 pm

    #3831510

  • Reply To: Dropping and creating tables.

    Perhaps a synonym would work for you here.

    Why do the created tables have to have the same name?  Presumably it's because some common code needs to run against the tables?  If...

    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 12, 2021 at 2:35 pm

    #3831476

  • Reply To: SQL query for First swipe in and Last Swipe Out for each day for each pay period

    I've never seen a time system that didn't use separate system entries to record check in / check out.  If fact, I don't see how else they could work, for...

    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, 2021 at 7:23 pm

    #3831100

  • Reply To: Dynamic query based of business rules

    Btw, I too would process the rules tables with a cursor to create / generate the final code.  I think a cursor gives you much better control and more flexibility...

    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, 2021 at 6:49 pm

    #3830111

Viewing 15 posts - 1,966 through 1,980 (of 7,619 total)