Forum Replies Created

Viewing 15 posts - 121 through 135 (of 7,613 total)

  • Reply To: Help with unique constraint

    You will need a trigger if you have to reference other rows in the table.  A well-written trigger won't hurt your performance that much  assuming you have an index available...

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

    October 7, 2024 at 5:43 pm

    #4466106

  • Reply To: Need to group the same valued ID fields to run an aggregate average over them

    Maybe this?:

    SELECT  poll_id ,start_date,end_date,
    candidate_name, sample_size,
    (select avg(pp2.pct) from [president_polls] pp2 
        where pp2.state = pp.state and pp2.poll_id = pp.poll_id 
        having...

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

    October 7, 2024 at 7:08 am

    #4465904

  • Reply To: Help with unique constraint

    I may not fully understand your requirement, but either:

    (1) A CHECK constraint

    or

    (2) an index definition

    can be created to do what you want to do.

    For example, if you just want to make...

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

    October 7, 2024 at 6:38 am

    #4465895

  • Reply To: Case or LOOKUP Table on Common but simplistic values

    I'd flip the check around: check first for = 1 as 'True' else 'False'.  That way NULL will show as false, if a NULL happens to slip in there.

    Use the...

    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 19, 2024 at 10:34 pm

    #4460054

  • Reply To:

    You can use ctes, which will create virtual tables, but no actual physical tables:

    ;WITH TempA AS (
        SELECT
        InvoiceNo, Date,Type,Code
     ...

    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 18, 2024 at 3:39 pm

    #4459612

  • Reply To: Need assistance to remove numbers from a Column result

    I would use STUFF for this, as most custom-fitted to do this task:

    SELECT s_Result,
        STUFF(s_Result, 1, CASE s_credit_card_type 
         ...

    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 16, 2024 at 3:13 pm

    #4458756

  • Reply To: Need help on results output 


    ;WITH cte_1 AS (
        SELECT *, ROW_NUMBER() OVER(PARTITION BY HH ORDER BY HH) AS row_num
        FROM MyTable
    )
    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".

    September 13, 2024 at 2:04 pm

    #4457299

  • Reply To: Text column is taking More time while Querying, Need to Replace DATALENGTH

    Mr. Brian Gale wrote:

    Phil Parkin wrote:

    ScottPletcher wrote:

    There might be some fair overhead to getting the current length of a text column.  If you really have to have that, add a column 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".

    August 30, 2024 at 3:59 pm

    #4452690

  • Reply To: Text column is taking More time while Querying, Need to Replace DATALENGTH

    Phil Parkin wrote:

    ScottPletcher wrote:

    There might be some fair overhead to getting the current length of a text column.  If you really have to have that, add a column to the...

    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 30, 2024 at 3:31 pm

    #4452681

  • Reply To: Text column is taking More time while Querying, Need to Replace DATALENGTH

    Actually "<" is NOT a safe way to check for not space.  CR and LF are both not "<" a space.

    select case when char(10) > space(1) then 'LF ' else...

    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 29, 2024 at 7:54 pm

    #4451418

  • Reply To: Does a Query Lock to the Table_Name or Table_ID

    References in SQL are to object names.  The code will resolve based on the name.

    A rename can't happen while the table is being queried, because a schema lock is in...

    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 27, 2024 at 7:02 pm

    #4450702

  • Reply To: Arithmetic overflow error converting IDENTITY to data type bigint

    I agree.  Why the "need" to jump to such a huge number?

    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 20, 2024 at 8:22 pm

    #4447770

  • Reply To: Arithmetic overflow error converting IDENTITY to data type bigint

    Correct.  You'd have to use decimal(38, 0) or some other larger value type rather than bigint.

    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 19, 2024 at 8:07 pm

    #4447426

  • Reply To: Arithmetic overflow error converting IDENTITY to data type bigint

    So you deliberately set the value so that it would get too large for a bigint ... and that seems to be your error.

    Not sure why you felt the need...

    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 19, 2024 at 3:08 pm

    #4447329

  • Reply To: Arithmetic overflow error converting IDENTITY to data type bigint

    Theoretically @@IDENTITY value could be too large for a bigint.  Hard to imagine actually using enough values to go past 19 digits, but it's theoretically possible.

    Btw, you should get @@IDENTITY...

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

    #4447305

Viewing 15 posts - 121 through 135 (of 7,613 total)