Archive for the ‘SQL Server 2008’ Category
How to assign Default value of the Field in the table
Posted by: admin in MS SQL Server, SQL Server 2008 on June 16th, 2010
Use the ALTER TABLE command to assign the default value of the field.
Example:
ALTER TABLE [dbo].[tblHyperLink] ADD CONSTRAINT [DF__tblHyperL__LastU__021E29CA] DEFAULT (getdate()) FOR [LastUpdate]
GO
How to check if a varchar (string) column can be converted to numeric
Posted by: admin in Database, MS SQL Server, SQL Server 2008 on May 19th, 2010
Use the ISNUMERIC function of the sql server to this job.
To check all records of that particular column if it contains a string or can be converted to numeric use the script below.
select
case when sum(
case when COLUMN_NAME is null then 1
when ltrim(rtrim(COLUMN_NAME)) = ” then 1
else isnumeric(COLUMN_NAME)
end) = count(*) then 1 else 0 end x from TABLENAME
You can find more info here
How to email result query from SQL Server
Posted by: admin in Database, MS SQL Server, SQL Server 2008 on May 7th, 2010
To send the result via email in sql server use the sp_send_dbmail procedures but before you execute that sp you need to enable the Database Mail using the Configuration Wizard or use the sp_configure
Example:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘MyProfilename’,
@recipients = ‘myemail@gmail.com’,
@query =’SELECT * FROM tblname,
@body = ‘Description.’,
@subject = ‘Subject’ ;
How to check the SQL Server Version
Posted by: admin in Database, MS SQL Server, SQL Server 2008 on May 5th, 2010
You can check the sql server version by using the SERVERPROPERTY function.
Exampe:
SELECT
SERVERPROPERTY(’ProductVersion’) AS ProductVersion,
SERVERPROPERTY(’ProductLevel’) AS ProductLevel,
SERVERPROPERTY(’Edition’) AS Edition,
SERVERPROPERTY(’EngineEdition’) AS EngineEdition;
GO
Difference between Clustered and Non-Clustered Index
Posted by: admin in Database, MS SQL Server, SQL Server 2008 on April 13th, 2010
- Clustered Index
- A clustered index is a special type of index that reorders the way records in the table are physically stored.
- Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
- Non-Clustered Index
- A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk.
- The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
Rebuild Index Script for SQL Server
Posted by: admin in Database, MS SQL Server, SQL Server 2008 on March 12th, 2010
DECLARE @tblName VARCHAR(50)
DECLARE rs CURSOR FOR
SELECT TABLE_NAME FROM information_schema.tables WHERE [TABLE_TYPE] =N’BASE TABLE’
OPEN rs
FETCH NEXT FROM rs INTO @tblName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX( @tblName,”,90)
END
CLOSE rs
DEALLOCATE rs