Forum Replies Created

Viewing 15 posts - 706 through 720 (of 7,613 total)

  • Reply To: Date vs Datetime recommendation

    2.  If the value is a date only, use a date data type, period.

    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 3, 2022 at 6:06 pm

    #4072108

  • Reply To: search index

    Jeff Moden wrote:

    Who says?  It didn't specify anything about the table.  Consider it to be "most convenient". 😀

    The OP said:

    you want to search for all users who have an email "@gmail"'

    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 3, 2022 at 2:01 pm

    #4071946

  • Reply To: Extracting first three words from Address field

    SELECT [FIRST_THREE_WORDS] = LEFT(ADDRESS + ' ', CHARINDEX(' ', ADDRESS + ' ', CHARINDEX(' ', ADDRESS, CHARINDEX(' ', ADDRESS + ' ', 0)+1)+1))

    FROM ( SELECT '123 Main Street' ) 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".

    August 2, 2022 at 9:08 pm

    #4071603

  • Reply To: search index

    Jeff Moden wrote:

    ScottPletcher wrote:

    That is a simple q, I say give it a simple answer.  There's no need to over-analyze every q or nit pick its wording.

    You're missing the point, Scott. ...

    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 2, 2022 at 8:44 pm

    #4071596

  • Reply To: search index

    That is a simple q, I say give it a simple answer.  There's no need to over-analyze every q or nit pick its wording.  Of course interview qs don't contain...

    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 2, 2022 at 3:41 pm

    #4071473

  • Reply To: Missing Data(finding the root problem)

    If there's any possibility that the NOT IN column checked can be NULL, then you need to explicitly exclude NULL(s).  It doesn't sound like it could be NULL for this...

    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 2, 2022 at 2:58 pm

    #4071427

  • Reply To: search index

    Naturally b is the answer to that specific question.

    As to why:

    (1) the table has 1M rows; therefore, creating an index with only the columns you need will typically drastically cut...

    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 2, 2022 at 2:51 pm

    #4071423

  • Reply To: Record with same process id > 1

    SELECT User, ProcessId, COUNT(*) AS ProcessDupCount

    FROM dbo.table_name

    GROUP BY User, ProcessId

    WHERE ProcessId > 1

    HAVING COUNT(*) > 1

    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 2, 2022 at 2:42 pm

    #4071422

  • Reply To: Issue "Error converting data type varchar to numeric."

    This should give you the data type of the column:

    SELECT TOP (1) wf.SMP_FLAG

    INTO #temp

    FROM WF_SMP wf

    EXEC tempdb.sys.sp_help #temp

    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 1, 2022 at 10:35 pm

    #4071129

  • Reply To: SQL Query Best Approach Recommendations

    Here's the original post:

    "I have a little exercise and would like to know your opinion on the most effective/best way to approach.  Please see the result I am looking 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".

    July 31, 2022 at 1:02 am

    #4070474

  • Reply To: Best approach for column data type alters for a large table?

    You're welcome.

    Yeah, you'll need lots of extra disk space.

    Presumably the log file is already a big size for a db that includes a table that size.  But you might want...

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

    July 29, 2022 at 6:22 pm

    #4070079

  • Reply To: Best approach for column data type alters for a large table?

    For example purposes, I'll assume your clustered index is on a $IDENTITY column, say "id".

    (10) Create a new table, with the new data types, and the clustered index (only!) from...

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

    July 29, 2022 at 5:27 pm

    #4070062

  • Reply To: Execution Times vs Logical Reads/Scan Count

    You need to convert the "NOT IN"(s) to "NOT EXISTS"(s), like this:

    NOT EXISTS(SELECT 1 FROM TB_REINPUT TR WITH (NOLOCK) WHERE TR.ORDER_DATE>=@LAST_ORDER_DATE AND TR.STATION_ID=@P_STATION_ID AND

    TR.ORDER_DATE+COMMIT_NO = TB_WORK_ORDER_MFD01.ORDER_DATE+TB_WORK_ORDER_MFD01.COMMIT_NO)

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

    July 29, 2022 at 2:48 pm

    #4070002

  • Reply To: query that will return the information of every table in a database

    You don't really need sys.indexes in the row-count query:

    OUTER APPLY (
        SELECT SUM(p.rows) AS TotalRows
        FROM sys.partitions p
       ...

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

    July 28, 2022 at 6:08 pm

    #4069605

  • Reply To: Substring charindex Mid - Get name within String

    My approach was similar (I couldn't post to this site for a while):

    SELECT 
        text_column,
        LTRIM(RTRIM(SUBSTRING(text_column, CASE WHEN position_after_required_name = 0...

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

    July 28, 2022 at 6:05 pm

    #4069604

Viewing 15 posts - 706 through 720 (of 7,613 total)