Jeff Moden

Forum Replies Created

Viewing 15 posts - 48,046 through 48,060 (of 59,102 total)

RE: Add a User-Defined Function to Master DB?

Just about anything that begins with "sp_" willl be checked for in Master first. I've known people to make Calendar and Tally tables that begin with sp_ just to...
--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)

September 30, 2008 at 6:16 am

#878788
RE: sorting algorithm in sql server

Anirban Paul (9/30/2008)
I am not sure whether you could open the link provided by Jeff. You can try this:
http://msdn.microsoft.com/en-us/library/aa174541(SQL.80).aspx
The link is a Books Online link... you need to copy and...
--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)

September 30, 2008 at 6:05 am

#878779
RE: sorting algorithm in sql server

SQL Server uses a lot of "B-Tree" for this type of stuff... start at the following Books Online listing and drill down on the various types of indexes...
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/8936789a-e803-4814-83f2-77e717f81736.htm
--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)

September 30, 2008 at 5:39 am

#878768
RE: Why 249 is the limit on NonClustered indexes?

Gail is correct, but just curious... you need more than 249 indexes on a single 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)

September 30, 2008 at 5:33 am

#878765
RE: Sometimes getting issues in sp [SQLServer]Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation (such as CREATE FILE) operations on a database must be serialized. Reissue the statement after the current backup, CHECKALLOC, or file manipulation operation is completed.

Dunno about anyone else, but I'm not seeing an SP...
--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)

September 30, 2008 at 5:29 am

#878763
RE: Can I use a Tally table to do this?

Sorry for the delay...
Not everything that goes into TempDB has to begin with a "#" sign... you can use TempDB just like any other database. In other words, 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)

September 30, 2008 at 4:49 am

#878748
RE: Add a User-Defined Function to Master DB?

I agree... call it "DBA" or "UTIL" or whatever... but avoid Master like the plague if you can. Just use the 3 part naming convention when you call it.
--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)

September 29, 2008 at 9:31 pm

#878667
RE: Trying to join to xp_fileexist

Yep... but I need to know... do you expect that all of the file names are going to be in the same directory, or 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)

September 29, 2008 at 9:22 pm

#878665
RE: Autogrowth - Best Pratice

Don't ever let the defaults for auto-growth on a database occur... it takes 73 disk fragments just to reach 1GB using the default settings.
I'm with Steve... make sure the size...
--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)

September 29, 2008 at 9:03 pm

#878664
RE: varchar to datetime

The_SQL_DBA (9/29/2008)
When I put forth the idea and the risk involved in doing this conversion to management, they said they were better off without doing it. The reason being 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)

September 29, 2008 at 8:58 pm

#878663
RE: shifting data to eliminate nulls

OK... here we go... as usual, how the code works is explained as comments in the code...
/* Careful! This bit of code drops tables for test purposes! Could...
--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)

September 29, 2008 at 8:42 pm

#878662
RE: shifting data to eliminate nulls

Wow! Someone either left you a heck of a mess, or you're trying to resolve a "hunt group" from an Avaya telephone switch (seriously, been there, done that). ...
--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)

September 29, 2008 at 7:36 pm

#878659
RE: Script to return Rows count from table using sp_ExecuteSQL

I must admit, though, there is absolutely no need for the dynamic SQL...
DECLARE @TableNameSYSNAME
SET @TableName = 'dbo.JBMTest' --LOOK!!!! CHANGE THE TABLE NAME!!!!
SELECT TOP 1 [Rows]...
--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)

September 29, 2008 at 7:04 pm

#878657
RE: Script to return Rows count from table using sp_ExecuteSQL

The problemwith all of you folks getting the Null is that you didn't read the script... it's setup to get the rowcount for a table called "TimeZones" which you probably...
--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)

September 29, 2008 at 6:56 pm

#878656
RE: Best way to reindex large tables/indexes

Not "ShowConfig"... DBCC SHOWCONTIG
--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)

September 29, 2008 at 6:26 pm

#878655

Viewing 15 posts - 48,046 through 48,060 (of 59,102 total)