Jeff Moden

Forum Replies Created

Viewing 15 posts - 21,256 through 21,270 (of 59,072 total)

RE: Serious Index fragmentation questions

What's in this table? Is it by any chance something like a log, audit, or invoice detail table?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

March 14, 2015 at 8:17 pm

#1784080
RE: SQL choosing to use the worst possible index

ricardo_chicas (3/14/2015)
Hello All
I have a box with SQL Server 2008 R2, at the latest SP and CU, 16 cores and 196gb of ram (I also tested it at a copy...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

March 14, 2015 at 7:13 pm

#1784076
RE: Copy varchar(max) fields slow

Thanks, Terry. Sounds like you've done it right on the archive side.
On the DELETEs, remember that they affect ALL indexes and ALL FKs pointing at the table. If...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

March 14, 2015 at 6:55 pm

#1784075
RE: Convert RGB color to HEX Color using SQL

Just for grins, here are the results from the same test harness with only 500,000 rows. The times here will likely be shorter than for your production run because...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

March 14, 2015 at 6:47 pm

#1784074
RE: Convert RGB color to HEX Color using SQL

Awesome. I love helping but when someone that we've helped get's a great wow-factor on top of it all, that makes my day, as well. Thanks again for...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

March 14, 2015 at 6:43 pm

#1784073
RE: Convert RGB color to HEX Color using SQL

thomashohner (3/14/2015)
Thanks again Jeff , "Jefe el Sql ",
I ran both convert codes and on my limited test data (1,000 rows) they were both uber fast. Not even a 1...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

March 14, 2015 at 6:19 pm

#1784071
RE: Convert RGB color to HEX Color using SQL

Eirikur Eiriksson (3/14/2015)
With the integer you will have to reverse the byte order, here is a quick example
😎
DECLARE @COLOURINT INT = 14745599;
SELECT CHAR(35) + CONVERT(VARCHAR(6),SUBSTRING(CONVERT(BINARY(3),@COLOURINT,1),3,1) + SUBSTRING(CONVERT(BINARY(3),@COLOURINT,1),2,1) + SUBSTRING(CONVERT(BINARY(3),@COLOURINT,1),1,1),2)
Result
#FFFFE0
I make...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

March 14, 2015 at 4:44 pm

#1784069
RE: Convert RGB color to HEX Color using SQL

thomashohner (3/14/2015)
I am currently building the report with my test data. Its small so my original function is working well but wont on larger data sets. The stuff you pointed...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

March 14, 2015 at 4:31 pm

#1784068
RE: Convert RGB color to HEX Color using SQL

Huh... It's been a long time since I've played with hex in SQL Server. I believe I may have an even better and faster way than what I posted....
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

March 14, 2015 at 2:00 pm

#1784060
RE: Convert RGB color to HEX Color using SQL

This will really make it so you don't want to use the scalar function you posted. The sys.fn_varbintohexstr function calls yet another scalar function called sys.fn_varbintohexsubstring and it not...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

March 14, 2015 at 12:58 pm

#1784057
RE: Convert RGB color to HEX Color using SQL

thomashohner (3/14/2015)
Yes sir,
Did I say it wrong on what data type it should be?
Oh no... You did fine. It's just that I can't even spell "SSRS" and, although...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

March 14, 2015 at 12:44 pm

#1784054
RE: Convert RGB color to HEX Color using SQL

thomashohner (3/14/2015)
Hello all I have a field in one of my tables that has the RGB colors stored as 255,255,255 format. Is there a way to convert this to Hex...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

March 14, 2015 at 12:08 pm

#1784050
RE: Secondary XML Indexes

Ed Wagner (3/13/2015)
I must admit that I don't use XML very much. It's does store very flexible data structure, but it just doesn't perform well. Thanks for the...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

March 14, 2015 at 11:57 am

#1784048
RE: Text Qualifier inside a Text qualified field

SQLAssAS (3/12/2015)
Hi,
In SQL 2005 if i was trying to insert some data with a text qualifier inside a text qualified field, it would work, for example:
"Name","ID ","Location","","Comany",""House Name" Road",
In SQL...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

March 14, 2015 at 11:54 am

#1784046
RE: Parsing XML file using C# in Windows Form to display readable text.

nickwelhar (3/14/2015)
More XML Parser examples...
Nick
Thanks for that (and I'm serious. Thank you very much!). I'm not a C# programmer but, as a DBA, I've always wanted to know...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

March 14, 2015 at 11:43 am

#1784044

Viewing 15 posts - 21,256 through 21,270 (of 59,072 total)