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,
a.LastName
FROM Northwind.dbo.Employees a
JOIN Northwind.dbo.Employees b
ON a.LastName >= b.LastName
GROUP BY
a.LastName
ORDER BY
a.LastName
The results from this query looks like this:
| RecNum |
LastName |
| 1 |
Buchanan |
| 2 |
Callahan |
| 3 |
Davolio |
| 4 |
Dodsworth |
| 5 |
Fuller |
| 6 |
King |
| 7 |
Leverling |
| 8 |
Peacock |
| 9 |
Suyama |
(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.
//To Disable
document.getElementById(element_client_id).setAttribute('disabled','true');
//To Enable
document.getElementById(element_client_id).removeAttribute('disabled');
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.
document.getElementById(element_client_id).parentElement.removeAttribute('disabled');
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.textFROM syscomments c JOIN sysobjects o ON c.id = o.id JOIN INFORMATION_SCHEMA.Tables t ON c.text LIKE '%'+t.TABLE_NAME+'%'