Archive for the ‘MS SQL Server’ Category
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
http://www.banmanpro.com/support/filegroupfull.asp
Posted by: admin in Database, MS SQL Server on May 18th, 2009
Try to shrink / back up the database.
Solution:
1. Check if the autogrowth is enable
2. Check if the autoshrink is checked
How to change the table owner in Sql Server
Posted by: admin in Database, MS SQL Server on May 12th, 2009
Use the sp_changeobjectowner stored procedure.
Synstax:
sp_changeobjectowner ‘OldUser.tableName’, ‘NewUser’
Example
In Domain User
-
sp_changeobjectowner '[Domain\Username].[tblTableProperty]', 'dbo'
Local User
-
sp_changeobjectowner '[Username].[tblTableProperty]', 'dbo'
How to add a Line Break in T-SQL Query
Posted by: admin in MS SQL Server on April 23rd, 2009
char(13) + char(10)
example:
<pre lang=”vb” >
PRINT ‘LINE ONE ‘ + char(13) + char(10) + ‘LINE TWO’
</pre>
Sql Server 2008 Management Studio Express Installation
Posted by: admin in MS SQL Server on April 23rd, 2009
You have to download and install the following before installing SQL SERVER 2008 Management Studio Express
-
1. <a href="http://www.microsoft.com/downloads/details.aspx?FamilyId=AB99342F-5D1A-413D-8319-81DA479AB0D7&displaylang=en">Microsoft .Net Framework 3.5 SP1</a>
-
2. <a href="http://support.microsoft.com/kb/942288">Windows Installer 4.5</a>
-
3. <a href="http://www.microsoft.com/windowsserver2003/technologies/management/powershell/download.mspx">Windows PowerShell 1.0</a>
How to Compute Distance Between Two Points in SQL Server
Posted by: admin in .NET, Database, MS SQL Server, VB.Net on April 17th, 2009
-
CREATEfunction [dbo].[computedistance](@lat1 FLOAT, @lat2 FLOAT,@lng1 FLOAT,@lng2 FLOAT)
-
RETURNS FLOAT
-
AS
-
BEGIN
-
DECLARE @theta FLOAT
-
DECLARE @dist FLOAT
-
DECLARE @miles FLOAT
-
DECLARE @unit char(1)
-
-
SET @Unit = 'K'
-
set @theta = @lng1 - @lng2
-
set @dist = (180/PI())*(
-
ACOS(
-
( SIN(( (PI()/180)*@lat1)) * SIN(( (PI()/180)*@lat2)) ) + – degree to radiAn 'SIN(DEGREES(PI/@lat1))'
-
( COS(( (PI()/180)*@lat1)) * COS(( (PI()/180)*@lat2)) * COS(( (PI()/180)*@theta)) )
-
)
-
)
-
-
SET @miles = (@dist * 60 * 1.1515) – convert to miles
-
-
SET @miles = @miles * 1.609344 – convert to k
-
-
RETURN @miles
-
END