Viewing 15 posts - 14,536 through 14,550 (of 14,953 total)
Jack Corbett (3/17/2008)
In SSMS you can right-click the table and and use script as create and it includes all the contraints, indexes, etc...
Actually, it doesn't include indexes (except the primary...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2008 at 2:56 pm
Use the IsDate function in your Where clause? It'll be slow, but it'll get the job done.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2008 at 2:49 pm
In Management Studio, right-click the table name, click Script Table as -> CREATE to -> New Query Editor window.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2008 at 2:45 pm
You also have a table scan on xxidh_cost2. If that has more than about 200 rows in it, create a covering index. Include columns xxidh_inv_nbr, xxidh_nbr, xxidh_line, xxidh_mtl_std,...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2008 at 2:07 pm
The bookmark lookup on ih_hist can probably be sped up with:
create index IDX_IH_Hist_Inv_Nbr_Cust on
dbo.ih_hist(ih_inv_date, ih_inv_nbr, ih_nbr, ih_cust)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2008 at 2:00 pm
To handle the first table scan, create an index on idh_inv_nbr, idh_nbr, idh_line, idh_part, idh_qty_inv on table idh_hist. (Make sure you have the columns in the index in that...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2008 at 1:54 pm
In the execution plan, I see two table scans and several hash joins. Those are going to slow you down.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2008 at 1:47 pm
The execution plan is the key.
You're looking for Table Scans and Index Scans, first.
The other thing I'd look at, based on what you've said so far, is the statistics on...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2008 at 1:45 pm
Good article.
I ended up as a DBA by accident. I don't have the certs yet, but will definitely get them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2008 at 1:39 pm
I haven't tested this enough to be sure, but it should work:
create function dbo.NumberClean
(@String_in nvarchar(100))
returns bigint
as
begin
declare @NumberStr nvarchar(100), @Number_out bigint
;with Chars (Seq, Chr) as
(select number, substring(@string_in, number, 1)
from dbo.Numbers
where number...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2008 at 12:42 pm
Change the final While loop to get rid of any spaces (right now, it gets rid of double-spaces), and it should do what you need.
If you have extended characters, you'll...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2008 at 12:32 pm
USE [Common]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[AlphaRemove]
(@String_in varchar(max))
returns varchar(max)
as
begin
/*
*****Takes a string variable and turns it into a set of
*****numbers separated by spaces.
*****Despite the name, it also removes punctuation,...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2008 at 12:29 pm
http://www.Simple-Talk.com has an SQL pretifier that does that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2008 at 12:24 pm
Besides anything else that may come up in this thread, I have to ask, why not just use an auto-increment ("identity") value in the tables you want to insert to?
That...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2008 at 12:22 pm
tuseau (3/17/2008)
However, I wrote the following function...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 17, 2008 at 12:21 pm
Viewing 15 posts - 14,536 through 14,550 (of 14,953 total)