Today I had to add a new date column to one of my table, and populate it with some random entries. I found a script from SQL Team Blog about generating random numbers. I modified it a little and used it to populate these dates based on another date I had in my table.
Here is modified code:
-- Create the variables for the random number generation
DECLARE @Upper int;
DECLARE @Lower int-- This will create a random number between 1 and 365
SET @Lower = 1 -- The lowest random number
SET @Upper = 365 -- The highest random number
-- we'll use this temp table to assign a random number to each ID value
CREATE TABLE #temp (ID int NOT NULL, RandNum float NULL)
INSERT #temp (ID) SELECT ID FROM FOO
-- now, assign a new random value to each key value in #temp
DECLARE @id int
DECLARE Randomizer CURSOR
FOR SELECT RandNum FROM #temp
FETCH NEXT FROM Randomizer INTO @id
WHILE @@Fetch_Status != -1
UPDATE #temp SET RandNum = rand()
WHERE CURRENT OF Randomizer
FETCH NEXT FROM Randomizer
SET F.NewDate = dateadd(dd, round((T.RandNum * 100), 0), F.AnyDate)
FOO F INNER JOIN #temp T ON F.ID = T.ID
DROP TABLE #temp
Now say your table does not have an identify column, you don’t want to use a temporary table or alter your existing table, but you still would like to have a record number associated with each record. In this case you could use a self join to return a record number for each row. Here is an example that calculates a RecNum column, and displays the LastName for each record in the Northwind.dbo.Employees table. This example uses count(*) to count the number of records that are greater than or equal LastName in this self join.
SELECT COUNT(*) RecNum,
FROM Northwind.dbo.Employees a
JOIN Northwind.dbo.Employees b
ON a.LastName >= b.LastName
The results from this query looks like this:
Continue reading →
I’ve run across several applications where the use of IDENTITY values has caused some confusion, specifically in how the @@IDENTITY function operates given external influences. We’ve all likely been faced with how to grab the newly generated IDENTITY value from a SQL Server database table. And there are a 2 popular techniques that accomplish this with SQL Server:
- the @@IDENTITY function
- the SCOPE_IDENTITY() function
Continue reading →
To enable or disable .Net controls on client side use following scripts.
Nearly all html rendered control would adher to this coding guide, but aspx checkbox has a span element wrapped up on it. To disable checkbox properly add a extra line as below.
Here how you can convert string date to a datetime value:
DECLARE @DateString char(14)
SET @DateString = '20060703074815'
SELECT CAST(SUBSTRING(@DateString, 1, 4) + '-' +
SUBSTRING(@DateString, 5, 2) + '-' +
SUBSTRING(@DateString, 7, 2) + 'T' +
SUBSTRING(@DateString, 9, 2) + ':' +
SUBSTRING(@DateString, 11, 2) + ':' +
SUBSTRING(@DateString, 13, 2) AS datetime)
If you want to find row count for each table in your database without naming any table then use following script:
exec sp_MSforeachtable 'select count(*) as nr_of_rows, ''?'' as table_name from ?'
When you need to find all procedures and function referencing a particular table then use following:
SELECT o.name, t.TABLE_NAME, c.text
FROM syscomments c JOIN sysobjects o ON c.id = o.id JOIN INFORMATION_SCHEMA.Tables t ON c.text LIKE '%'+t.TABLE_NAME+'%'