utility functions
-
I'm sure they aren't perfect by a longshot (I may even have functions that have other internal functions that do the same thing), but here are a couple of my non-application-specific functions.
drop function dbo.fnAlphabetOnly
go
create function dbo.fnAlphabetOnly (@Input nvarchar(400))
returns nvarchar(400)
as
begin
-- Author: Norman Seymore
-- 11/19/2003
-- Removes everything but spaces and letters (english) from a string
-- DB case insensitive
set @Input = ltrim(rtrim(@Input))
declare @StartLen int, @Output nvarchar(400), @ThisChar nchar(1), @CharCount int
set @Output = ''
set @StartLen = len(@Input)
set @CharCount = 0
while @StartLen > @CharCount
begin
set @CharCount = @CharCount + 1
set @ThisChar = right(@Input,1)
set @Input = left(@Input,@StartLen-@CharCount)
if @ThisChar in (' ','a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z')
set @OutPut = @ThisChar + @Output
end
return @Output
end
drop function dbo.fnPaddedMSDateDiffStr
go
CREATE FUNCTION dbo.fnPaddedMSDateDiffStr (
@Split datetime, @Now datetime, @MaxLen tinyint = 20)
RETURNS varchar(20)
AS
BEGIN
-- Author: Norman Seymore
-- 11/19/2003
-- Given two datetime values and how long of a string you want, returns a string of exactly that length
-- that contains the datediff in milliseconds of the two datetime values
-- I use this when I include formatted debug output code in a procedure or script.
-- It allows me to use indentation to clearly show what is nested in what
declare @MSStr varchar(20)
if @MaxLen > 20
set @MSStr = 'MAXLENEXCEEDED'
else
begin
set @MSStr = ltrim(rtrim(str(datediff(ms,@Split,@Now))))
set @MSStr = replicate(' ',@MaxLen-len(@MSStr))+@MSStr
end
RETURN @MSStr
END
go
drop function dbo.fnPadl
go
create function dbo.fnPadl (@Str nvarchar(200), @PadWith nvarchar(10), @PadLength int)
returns nvarchar(200)
as
begin
-- Author: Norman Seymore
-- 11/19/2003
declare @RtrnVal nvarchar(200), @Len int
set @Len = len(@Str)
set @RtrnVal = replicate(@PadWith,@PadLength-@Len)+@Str
return @RtrnVal
end
go
select dbo.fnPadl('1','0',4)
drop function dbo.fnPadr
go
create function dbo.fnPadr (@Str nvarchar(200), @PadWith nvarchar(10), @PadLength int)
returns nvarchar(200)
as
begin
-- Author: Norman Seymore
-- 11/19/2003
declare @RtrnVal nvarchar(200), @Len int
set @Len = len(ltrim(rtrim(@Str)))
set @RtrnVal = ltrim(rtrim(@Str))+replicate(@PadWith,@PadLength-@Len)
return @RtrnVal
end
go
select dbo.fnPadr('1','0',4)
-
drop function dbo.fnRemoveNChar
go
CREATE FUNCTION dbo.fnRemoveNChar
(@String nvarchar(400))
RETURNS nvarchar(32)
AS
BEGIN
-- Author: Norman Seymore
-- 11/19/2003
-- removes non-numeric data from an nchar string
-- essentially extracting numeric data from junky fields
-- One use of this is to extract numeric data from system stored procedure result output, such as the space used stuff.
-- declare @String varchar(400)
-- declare @x char(11)
-- set @x = '123131 kb'
-- set @String = @x
declare @Number nvarchar(128)
set @Number = N''
while len(@String) > 0
begin
if isnumeric(left(@string,1)) = 1 and left(@String,1) not in ('.','+','-','=')
begin
set @Number = @Number + ltrim(rtrim(left(@string,1)))
end
set @String = right(ltrim(rtrim(@String)),len(ltrim(rtrim(@String)))-1)
--print @String+'::'+ @Number
end
RETURN @Number
END
go
select convert(int,dbo.fnRemoveNChar('12kjliy1241240-'))
select abs(dbo.fnRemoveNChar('12kjliy1241240-')) as x
-
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.fnIndexableDateTimeString
(@SeedDate datetime)
RETURNS char(17)
AS
BEGIN
-- Author: Norman Seymore
-- 11/19/2003
-- does what it says. I use this a ton, lately
-- it's usually to just give me a formatted string of
-- CCYYMM or CCYYMMDD for a pre-extrapolated time sensitive
-- field for querying against.
declare
@DTStr char(17),
@CCYY varchar(4),
@m varchar(2),
@D varchar(2),
@h varchar(2),
@mn varchar(2),
@s-2 varchar(2),
@ms varchar(3)
set @CCYY = convert(varchar(4),datepart(year,@SeedDate))
set @m = convert(varchar(2),datepart(month,@SeedDate))
set @D = convert(varchar(2),datepart(day,@SeedDate))
set @h = convert(varchar(2),datepart(hour,@SeedDate))
set @mn = convert(varchar(2),datepart(minute,@SeedDate))
set @s-2 = convert(varchar(2),datepart(second,@SeedDate))
set @ms = convert(varchar(3),datepart(ms,@SeedDate))
/*
print @CCYY
print @m
Print @D
print @h
print @mn
print @s-2
print @ms
*/
set @m = replicate('0',2-len(@M))+@M
set @D = replicate('0',2-len(@D))+@D
set @h = replicate('0',2-len(@H))+@H
set @mn = replicate('0',2-len(@MN))+@MN
set @s-2 = replicate('0',2-len(@S))+@S
set @ms = replicate('0',3-len(@MS))+@MS
/*
print @CCYY
print @m
Print @D
print @h
print @mn
print @s-2
print @ms
*/
set @DTStr = @CCYY+@M+@D+@H+@MN+@S+@MS
RETURN @DTStr
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/*
--exec CCR_CheckForSnapshotRoom
exec sp_spaceused
dbcc sqlperf(logspace)
*/
-- if @Available-@Used < 2000
-- raiserror('May not be enough space for snapshot. Expand the db size or delete some snapshots.', 16, 1)
drop procedure UTIL_DataSpaceAvailable
go
create procedure UTIL_DataSpaceAvailable (@SupressOutput bit = 0) as
declare @dbsize dec(15,0)
declare @logsize dec(15)
declare @bytesperpagedec(15,0)
declare @pagesperMBdec(15,0)
declare @DataTotal dec(15,0)
declare @LogTotal dec(15,0)
declare @DataReserved dec(15,0)
--sp_spaceused
set nocount on
-- get the data file size
select @dbsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 = 0)
-- get the log file size
select @logsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 <> 0)
select @bytesperpage = low
from master.dbo.spt_values
where number = 1
and type = 'E'
select @pagesperMB = 1048576 / @bytesperpage
select @DataTotal = ((@dbsize) / @pagesperMB)
select @LogTotal = ((@logsize) / @pagesperMB)
-- get the data space used
select @DataReserved = reserved * d.low / 1024. /1024.
from (
select sum(convert(dec(15),reserved)) as reserved
from sysindexes
where indid in (0, 1, 255)) as a,
master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
-- get the log space used
-- nix. this can be monitored via perf mon and or alerts
select
@DataTotal as DataTotal
,@DataReserved as DataReserved
,@DataTotal-@DataReserved as DataAvailable
,@LogTotal as LogTotal
-- can't seem to figure out a way to get logused... maybe sysindex indid other than the ones being queried?
-- or can just use the dbcc sqlperf(logspace), but will have to use temp table to store output
return @DataTotal-@DataReserved
-
CREATE FUNCTION dbo.fnAlphabetOnly(@Input nvarchar(400))
RETURNS nvarchar(400) AS
BEGIN
DECLARE @pos smallint
SET @Pos = PATINDEX('%[^A-Za-z ]%',@Input)
WHILE @Pos > 0 BEGIN
SET @Input = STUFF(@Input,@pos,1,'')
SET @Pos = PATINDEX('%[^A-Za-z ]%',@Input)
END
RETURN @Input END
SELECT STR(DATEDIFF(ms,@split,GETDATE()),20)
SELECT RIGHT(REPLICATE('0',4)+'1',4)
SELECT LEFT('1'+REPLICATE('0',4),4)
CREATE FUNCTION dbo.fnRemoveNChar(@Input nvarchar(400))
RETURNS nvarchar(400) AS
BEGIN
DECLARE @pos smallint
SET @Pos = PATINDEX('%[^0-9]%',@Input)
WHILE @Pos > 0 BEGIN
SET @Input = STUFF(@Input,@pos,1,'')
SET @Pos = PATINDEX('%[^0-9]%',@Input)
END
RETURN @Input END
SELECT REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(char(23),@SeedDate,121),'-',''),' ',''),':',''),'.','')Why not just use the datetime value itself "for querying against"?
--Jonathan
--Jonathan -
Neato thanks for the improvements. I'll try them out in production soon as I burn em in.
"Why not just use the datetime value itself "for querying against"?"
If you mean the indexabledatetimestring? The name is a bit misleading, as my note may be also. In reality I use it when taking sales transactions and storing them for commission processing. Because they also had me provide MTD qualification info a process, including a 'compare' process, runs against history, so I frequently have to work with something that is no more and no less specific down to a month. Best Practice, i don't know. I do know that I use datetime far more frequently- any time I want to allow data more specific than CCYYMM, or the data is for transaction capture instead of for querying.
-
quote:
Neato thanks for the improvements. I'll try them out in production soon as I burn em in."Why not just use the datetime value itself "for querying against"?"
If you mean the indexabledatetimestring? The name is a bit misleading, as my note may be also. In reality I use it when taking sales transactions and storing them for commission processing. Because they also had me provide MTD qualification info a process, including a 'compare' process, runs against history, so I frequently have to work with something that is no more and no less specific down to a month. Best Practice, i don't know. I do know that I use datetime far more frequently- any time I want to allow data more specific than CCYYMM, or the data is for transaction capture instead of for querying.
A datetime column is eight bytes and you are expanding it to 17 bytes and storing that so you can compare it with char values by using something like "LIKE '200310%'"?
Let's say your stored procedure now looks something like this, so you can return the value for a year, month, or day:
CREATE PROC p_SalesByPd
@RepID smallint,
@Period varchar(8),
@PdSales money OUTPUT
AS
SET NOCOUNT ON
SELECT @PdSales = SUM(OrdTot) - SUM(TaxAmt)
FROM Sales
WHERE SalesRepID = @RepID
AND OrdDate LIKE @Period + '%'If we instead keep the temporal data in a temporal data type, it is tempting to just use something like:
AND CONVERT(char(8),OrdDate,112) LIKE @Period + '&'But, based on your udf name, you have found that such a predicate is not sargable and therefore will not use an index on the column. But something like this will work well with an indexed datetime column:
AND OrdDate >= LEFT(@Period + '0101',8)
AND OrdDate < CASE LEN(@Period)
WHEN 4 THEN DATEADD(yy,1,@Period + '0101')
WHEN 6 THEN DATEADD(m,1,@Period + '01')
ELSE DATEADD(d,1,@Period) END--Jonathan
--Jonathan -
I'm confused why you think the size is large - I use int to store the CCYYMM period. That's 4 bytes not 8 (datetime).
It's rare, if at all, that I use text fields for comparison- I usually only use them for descriptions in small type reference tables, which I typically don't access when saving transactions to the DB or when performing complex queries. Just when a report or GUI needs to see something in plain english rather than XXXTypeID.
Edited by - cmore on 11/20/2003 08:43:11 AM
-
quote:
I'm confused why you think the size is large - I use int to store the CCYYMM period.We were discussing your fnIndexableDateTimeString udf, which returns a char(17) value.
--Jonathan
--Jonathan -
I wanted something to call to set memory variables, instead of retyping the same code every time.
I set it up to return a string because at the time:
set @Period = convert(int,left(dbo.fn(@Date),6))
instead of
set @Period = convert(int,left(ltrim(str(dbo.fn(@Date))),6))
seemed a better option.
I should add to this that in my head when I was thinking 'indexable' I was not thinking of using as you did in your example. I was thinking of it as an int. If querying data without a period in a where clause, such as filtering sales transactions, I'll use @BeginDate datetime and @EndDate datetime, and sometimes datepart(month) and datepart(year), to match against the date of the sale. - I haven't checked to see which is faster or whatnot because it's never seemed to be boggy as long as the indexes are ok.
If I want a period field in the data that I am creating, I use the function to grab the first six characters and convert it into an int. The conversion of the time dimension into a specifiable scope is what was desirable.
Edited by - cmore on 11/20/2003 12:18:05 PM
-
quote:
I wanted something to call to set memory variables, instead of retyping the same code every time.I set it up to return a string because at the time:
set @Period = convert(int,left(dbo.fn(@Date),6))
instead of
set @Period = convert(int,left(ltrim(str(dbo.fn(@Date))),6))
seemed a better option.
I should add to this that in my head when I was thinking 'indexable' I was not thinking of using as you did in your example. I was thinking of it as an int. If querying data without a period in a where clause, such as filtering sales transactions, I'll use @BeginDate datetime and @EndDate datetime, and sometimes datepart(month) and datepart(year), to match against the date of the sale. - I haven't checked to see which is faster or whatnot because it's never seemed to be boggy as long as the indexes are ok.
If I want a period field in the data that I am creating, I use the function to grab the first six characters and convert it into an int. The conversion of the time dimension into a specifiable scope is what was desirable.
Edited by - cmore on 11/20/2003 12:18:05 PM
This reminds me I've got to write that article on using dates in SQL Server...
DECLARE @Period int
SET @Period = CONVERT(char(6),@Date,112)--Jonathan
--Jonathan -
Yes, that does the same thing. Mine has granularity out to the MS, but I haven't needed it. Yours is certainly more SQL guruish.
I bet yours is faster too, if one needed to convert a datetime field to an int with CCYYMM when copying a bunch of data.
-
quote:
Yes, that does the same thing. Mine has granularity out to the MS, but I haven't needed it. Yours is certainly more SQL guruish.I bet yours is faster too, if one needed to convert a datetime field to an int with CCYYMM when copying a bunch of data.
I've never seen an example where a UDF performs better than internal functions. This can make a big difference when applying them to large result sets. Note that I suggested internal function alternatives for two-thirds of the UDFs you posted.
--Jonathan
--Jonathan -
quote:
I've never seen an example where a UDF performs better than internal functions. This can make a big difference when applying them to large result sets.Yes, which is why I said I bet it would be faster.
quote:
Note that I suggested internal function alternatives for two-thirds of the UDFs you posted.Yes, it's actually what I hoped for. If you will look at the header of the original post you will note that I even specifically stated that there was likely pre-made utilities to do what the udfs do. It's also why I thanked you for the improvements.
I did not post these to compete, merely to carry on a conversation and perhaps glean something useful from it. I work in a vacuum of SQL talent, which means there's nobody here to collaborate or learn with. Unfortunately the only bad habits I end up breaking on my own are ones where they smack me in the face.
I apologize if I stepped on your toes in any way. I try to not have an ego. The day I assume I know more as a whole than someone else is the day I quit learning.
-
quote:
quote:
I've never seen an example where a UDF performs better than internal functions. This can make a big difference when applying them to large result sets.Yes, which is why I said I bet it would be faster.
Yes, I was trying to reinforce that impression.
quote:
quote:
Note that I suggested internal function alternatives for two-thirds of the UDFs you posted.Yes, it's actually what I hoped for. If you will look at the header of the original post you will note that I even specifically stated that there was likely pre-made utilities to do what the udfs do. It's also why I thanked you for the improvements.
I did not post these to compete, merely to carry on a conversation and perhaps glean something useful from it. I work in a vacuum of SQL talent, which means there's nobody here to collaborate or learn with. Unfortunately the only bad habits I end up breaking on my own are ones where they smack me in the face.
I apologize if I stepped on your toes in any way. I try to not have an ego. The day I assume I know more as a whole than someone else is the day I quit learning.
Don't know how I gave that impression; do I need to apologize?
I have trouble understanding your writing (still don't know what you're trying to do with the code fragments at the end of your third post), but I assumed you posted your scripts here in a forum rather than in the Scripts section because you wanted to know if there were better ways. A lot of beginners read these posts, and I also did not want them to assume your methods were the only way.--Jonathan
--Jonathan -
Sounds like we are on the same sheet then. The fragments were junk- I should have removed them before posting.
I have trouble understanding your writing (still don't know what you're trying to do with the code fragments at the end of your third post), but I assumed you posted your scripts here in a forum rather than in the Scripts section because you wanted to know if there were better ways. A lot of beginners read these posts, and I also did not want them to assume your methods were the only way.Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply