March 16, 2016 at 3:06 am
Hi All,
Is it okay to create a database level function to convert a Bit value to something more legible to users, for instance for reports and forms? My primary reason for this is consistency and ease of maintenance across multiple presentation layers, e.g. what if customer changes their mind on how it should be displayed.
Since I don't/won't know all potential presentation layers, the database seemed the best place.
CREATE FUNCTION dbo.fsConvertBit(@bitValue BIT = NULL)
RETURNS VARCHAR(6)
AS
BEGIN
DECLARE @convertedBit VARCHAR(6) = 'Unknown'
IF @bitValue IS NOT NULL BEGIN
IF @bitValue = 1 BEGIN
SET @convertedBit = 'X'
END ELSE BEGIN
SET @convertedBit = ''
END
END
RETURN @convertedBit
END
Thanks
March 16, 2016 at 3:42 am
Can you create it? Yes.
Is it a good idea? In terms of performance, no (though on smaller resultsets the function overhead probably won't be noticeable)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 16, 2016 at 3:51 am
Yes, if you must do it in the data layer instead of the presentation layer, an inline table valued function would perform better. Better still, why not create a lookup table and join to that? It would be easier to maintain, since it's simpler to change data in a table than to change the definition of a function.
John
March 16, 2016 at 3:57 am
Hi Gail,
Thanks for the quick response.
My background programming so the idea of create a class/method to do this is 2nd nature that is why I want to ask on a RDMS forum.
By 'smaller' could you quantify that a little? Are we talking under 100, 1k, 10k? In general this would only be used in forward/user facing code so the data set would always be limited in some way, hopefully to < 100
Also can I assume that if I had a SP that returned a larger results set I could pre-fetch the values to variables like below and that would improve performance?
DECLARE @convertedBitNULL VARCHAR(6)
,@convertedBitTrue VARCHAR(6)
,@convertedBitFalse VARCHAR(6)
SET @convertedBitNULL = dbo.fsConvertBit(CONVERT(BIT,NULL))
SET @convertedBitTrue = dbo.fsConvertBit(CONVERT(BIT,1))
SET @convertedBitTrue = dbo.fsConvertBit(CONVERT(BIT,0))
Thanks
Steve
March 16, 2016 at 4:02 am
Hi John,
Actually I was thinking, and would prefer, that it would be a value in a table however I couldn't think how I might JOIN that table to all tables with Bit columns.
Thanks
Steve
March 16, 2016 at 4:11 am
SteveD SQL (3/16/2016)
Also can I assume that if I had a SP that returned a larger results set I could pre-fetch the values to variables like below and that would improve performance?
DECLARE @convertedBitNULL VARCHAR(6),@convertedBitTrue VARCHAR(6)
,@convertedBitFalse VARCHAR(6)
SET @convertedBitNULL = dbo.fsConvertBit(CONVERT(BIT,NULL))
SET @convertedBitTrue = dbo.fsConvertBit(CONVERT(BIT,1))
SET @convertedBitTrue = dbo.fsConvertBit(CONVERT(BIT,0))
Yes it would. The problem with scalar functions is that there's overhead in calling them, and if they're used in a query they're called once per row.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 16, 2016 at 4:47 am
GilaMonster (3/16/2016)
SteveD SQL (3/16/2016)
Also can I assume that if I had a SP that returned a larger results set I could pre-fetch the values to variables like below and that would improve performance?
DECLARE @convertedBitNULL VARCHAR(6),@convertedBitTrue VARCHAR(6)
,@convertedBitFalse VARCHAR(6)
SET @convertedBitNULL = dbo.fsConvertBit(CONVERT(BIT,NULL))
SET @convertedBitTrue = dbo.fsConvertBit(CONVERT(BIT,1))
SET @convertedBitTrue = dbo.fsConvertBit(CONVERT(BIT,0))
Yes it would. The problem with scalar functions is that there's overhead in calling them, and if they're used in a query they're called once per row.
They're pre-fetched so the function would be called only three times - once per possible value (please nobody flame me for referring to NULL as a value). The issue is - how often would this pre-fetching need to occur - every time a user connects, just before the query is run, or something else?
SteveD SQL (3/16/2016)
Hi John,Actually I was thinking, and would prefer, that it would be a value in a table however I couldn't think how I might JOIN that table to all tables with Bit columns.
Thanks
Steve
Something like this. You need to do a bit of massaging on NULL since it won't show up in the join otherwise. Bear in mind that if you change the converted values, you may need to change the width of the Converted column in the first table.
CREATE TABLE BitValues ( -- Lookup table
BitValue tinyint NOT NULL CONSTRAINT DF_BitValues_BitValue CHECK (BitValue BETWEEN 0 AND 2)
,Converted varchar(7) NOT NULL CONSTRAINT PK_BitValues_Converted PRIMARY KEY CLUSTERED
,CONSTRAINT UQ_BitValues_BitValue UNIQUE (BitValue)
)
INSERT INTO BitValues (
BitValue,Converted
) VALUES
(2, 'Unknown') -- this is the "NULL" value
,(0, '')
,(1, 'X')
CREATE TABLE SomeTable ( -- Data table
ID int IDENTITY(1,1)
,BitValue bit
)
INSERT INTO SomeTable (
BitValue
) VALUES
(NULL)
,(0)
,(1)
SELECT
t.ID
,b.Converted
FROM SomeTable t
INNER JOIN BitValues b
ON COALESCE(t.BitValue,2) = b.BitValue
John
Edit - changed LEFT JOIN to INNER JOIN and moved the "Data table" comment to the right place
March 16, 2016 at 5:05 am
Thanks John, this is definitely a much better approach and gets me where I wanted to be in terms of it being configurable via my application admins.
Take Care,
Steve
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply