SELECT Number, 1 - SIGN(Number & (Number - 1)) FROM master..spt_values WHERE Type = 'P' AND Number > 0
Other way is
CREATEFUNCTION dbo.isPowerOf2 ( @i INT ) RETURNSBIT AS BEGIN DECLARE @x FLOAT SET @x = LOG(Number) / LOG(2) RETURN CASE WHEN FLOOR(@x) = CEILING(@x) THEN 1 ELSE 0 END END
Legacy Comments
RamiReddy
2008-08-13
re: How to tell if a number is a "POWER of 2"-number Is that first query will work for the number 524288 which is 2 power 19.
Read more →
I started out with typing SELECT@@VERSION and got the result as Microsoft SQL Server 2005 - 9.00.3215.00 (Intel X86) Dec 8 2007 18:51:32 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.
Read more →
CREATEFUNCTION dbo.fnGetSQLServerAuthenticationMode ( ) RETURNSINT AS BEGIN DECLARE @InstanceName NVARCHAR(1000), @Key NVARCHAR(4000), @LoginMode INT EXEC master..xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\', N'MSSQLSERVER', @InstanceName OUTPUT IF @@ERROR <> 0 OR @InstanceName IS NULL RETURN NULL SET @Key = N'Software\Microsoft\Microsoft SQL Server\' + @InstanceName + N'\MSSQLServer\' EXEC master.
Read more →
CREATEPROCEDURE dbo.uspSetSQLServerAuthenticationMode ( @MixedMode BIT ) AS SETNOCOUNT ON DECLARE@InstanceName NVARCHAR(1000), @Key NVARCHAR(4000), @NewLoginMode INT, @OldLoginMode INT EXECmaster..xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\', N'MSSQLSERVER', @InstanceName OUTPUT IF@@ERROR <> 0 OR @InstanceName IS NULL BEGIN RAISERROR('Could not read SQL Server instance name.
Read more →
DECLARE@Stage TABLE ( RowID INT IDENTITY(0, 1) PRIMARY KEY CLUSTERED, Data VARCHAR(90), Section INT ) INSERT@Stage ( Data ) EXECxp_cmdshell 'ipconfig /all' DECLARE@Section INT SET@Section = 0 UPDATE @Stage SET@Section = Section = CASE WHENASCII(LEFT(Data, 1)) > 32 THEN @Section + 1 ELSE@Section END SELECTMAX(CASE WHEN x.
Read more →
The last two days I have been involved in a rather interesting discussion. The original poster wanted a fast way to get missing date ranges in a series of date pairs.
Read more →
I just played around with some different techniques to fetch relevant data from XML content.
DECLARE@XMLString XML, @Search VARCHAR(50) SELECT@XMLString = ' <Customers> <Customer> <FirstName>Kevin</FirstName> <LastName>Goff</LastName> <City type="aca">Camp Hill</City> </Customer> <Customer> <FirstName>Steve</FirstName> <LastName>Goff</LastName> <City type="acb">Philadelphia</City> </Customer> </Customers>', @Search = 'Camp Hill' -- Get all customers living in Camp Hill SELECTcust.
Read more →
This is an updated version for SQL 2005 and later to search all code for a specific keyword SELECT p.RoutineName, 'EXEC sp_helptext ' + QUOTENAME(p.RoutineName) AS [Exec] FROM ( SELECT OBJECT_NAME(so.
Read more →
This is what I pondered about today. Maybe I also will have some time to test it.
CREATE PROCEDURE dbo.uspPaginate ( @PageNumber INT, @RecordsPerPage TINYINT = 50 ) AS SET NOCOUNT ON DECLARE @MaxRows INT SET @MaxRows = @PageNumber * @RecordsPerPage SELECT SomeColumns FROM ( SELECT TOP (@RecordsPerPage) SomeColumns FROM ( SELECT TOP (@MaxRows) SomeColumns FROM YourTable ORDER BY SomeCase ASC/DESC ) ORDER BY SomeCase DESC/ASC ) ORDER BY SomeCase ASC/DESC Topic is here http://www.
Read more →
I worked with this topic recent weekend and posted the final functions here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97454 The general idea is to have a generic purge functionality.
Legacy Comments
Jason
2008-02-16
re: Finding table reference levels and simulating cascading deletes I understand why some people might want this, but I think it is truly a bad idea to even do something like this.
Read more →
After a good nights sleep when almost all pieces fit together here weblogs.sqlteam.com/peterl/archive/2008/02/06/Curiosity-found.aspx
I realized this morning that this behaviour also explains why there are gaps in identity sequences when inserting a record that violates a contraint.
Read more →
CREATEFUNCTION dbo.fnSplitType ( @Data VARCHAR(200), @PartSize TINYINT ) RETURNSVARCHAR(8000) AS BEGIN DECLARE @Result VARCHAR(8000), @Alpha TINYINT, @OldPosition SMALLINT, @NewPosition SMALLINT SELECT @Result = '', @Alpha = 1, @OldPosition = 1, @NewPosition = 1 IF @Data LIKE '[0-9]%' SELECT @Result = REPLICATE(' ', @PartSize), @Alpha = 0 WHILE @NewPosition < LEN(@Data) SELECT @NewPosition = CASE @Alpha WHEN 1 THEN PATINDEX('%[0-9]%', SUBSTRING(@Data, @OldPosition, 8000)) ELSE PATINDEX('%[a-z]%', SUBSTRING(@Data, @OldPosition, 8000)) END, @NewPosition = CASE @NewPosition WHEN 0 THEN LEN(@Data) ELSE @OldPosition + @NewPosition - 2 END, @Result = @Result + CASE @Alpha WHEN 1 THEN LEFT(LTRIM(SUBSTRING(@Data, @OldPosition, @NewPosition - @OldPosition + 1)) + REPLICATE(' ', @PartSize), @PartSize) ELSE RIGHT(REPLICATE(' ', @PartSize) + RTRIM(SUBSTRING(@Data, @OldPosition, @NewPosition - @OldPosition + 1)), @PartSize) END, @Alpha = 1 - @Alpha, @OldPosition = @NewPosition + 1 RETURNRTRIM(@Result) END Here is the code to test with DECLARE@Sample TABLE (Info VARCHAR(200)) INSERT@Sample SELECT'S0C 4610' UNIONALL SELECT'S9C 113' UNIONALL SELECT'S1C 462' UNIONALL SELECT'112' UNIONALL SELECT'113' UNIONALL SELECT'MM20BC' UNIONALL SELECT'SSC 113' UNIONALL SELECT'SSC 201' UNIONALL SELECT'SSC 461' UNIONALL SELECT'SSC 4610' UNIONALL SELECT'SSC 462' UNIONALL SELECT'SSCPZ202C' UNIONALL SELECT'Z1' UNIONALL SELECT'Z100' UNIONALL SELECT'ZZ' SELECTInfo FROM@Sample ORDERBY dbo.
Read more →
Today, I was involved in an interesting discussion.
Someone asked for a moving average solution. I joined the discussion late. The previous solutions and mine were all set-based and very slow.
Read more →
In this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926I gave a suggestion how to get a more detailed error message when working with OPENROWSET.
But the same thinking is applicable for LINKED SERVER and other "outer referenced" data retreival methods.
Read more →
DECLARE@Sample TABLE (Col1 VARCHAR(6), Col3 VARCHAR(200)) INSERT @Sample SELECT'123', '125,124,126' UNION ALL SELECT'124', '127,21,245' --SELECT Col1, -- Data --FROM @Sample --CROSS APPLY fnParseList(',', Col3) SELECTa.Col1, SUBSTRING(',' + a.
Read more →
-- Prepare sample data DECLARE@Accounts TABLE (AccountNumber CHAR(11), ParentAccountNumber CHAR(11)) INSERT@Accounts SELECT'100-000-000', NULLUNION ALL SELECT'100-001-000', '100-000-000' UNIONALL SELECT'100-002-000', '100-000-000' UNIONALL SELECT'100-002-001', '100-002-000' UNIONALL SELECT'100-002-002', '100-002-000' DECLARE @Transactions TABLE (AccountNumber CHAR(11), Amount MONEY) INSERT@Transactions SELECT'100-001-000', 1000.
Read more →
Let’s play with the new OUTPUT operator!<o:p></o:p>
-- Setup TableA & TableB
CREATETABLE#TableA
(
i INT
)
CREATETABLE#TableB
(
i INT
)
CREATETABLE#TableC
Read more →
Sometimes you have a denormalized table with several BIT columns used as flags.
Say you want to select every row that has at least one flag set. That's easy.
SELECT * FROM Table1 WHERE Flag1 = 1 OR Flag2 = 1 OR Flag3 = 1.
Read more →
I was involved in a discussion today about the ISNUMERIC() function Someone proposed a nested solution like this
SELECT Column_Name from ( SELECT Column_Name FROM ( select 'staff' as Column_Name union all select '234000' as Column_Name union all select '12d1' as Column_Name union all select '45e0' as Column_Name union all select '$123.
Read more →
Consider this test data CREATE TABLE #Temp (ID INT, Directory TEXT) INSERT #Temp SELECT 1, 'Sports' UNION ALL SELECT 2, 'Sports/Football' UNION ALL SELECT 3, 'Sports/Football/American' UNION ALL SELECT 4, 'Sports/Football/American/College_and_University' UNION ALL SELECT 5, 'Sports/Football/American/College_and_University/NCAA_Division_III' UNION ALL SELECT 6, 'Sports/Football/American/College_and_University/NCAA_Division_III/Atlantic_Central_Football_Conference' UNION ALL SELECT 7, 'Sports/Football/American/College_and_University/NCAA_Division_III/Atlantic_Central_Football_Conference/Frostburg_State' UNION ALL SELECT 8, 'Sports/Darts' UNION ALL SELECT 9, 'Sports/Darts/Organizations' UNION ALL SELECT 10, 'Sports/Darts/Organizations/United_States' UNION ALL SELECT 11, 'Sports/Darts/Organizations/United_States/Alabama' Say you want to return all records that are at least three directories down, ie having at least 2 dividers.
Read more →