SQL Server and short tips – 1

• SQL Server is a backend system and its size limited by the Hard disk.
• SQL Server History:
• SQL 4.2 (oct 92).
• SQL 6.0 (jun 95).
• SQL 6.5(Apr 96).
• SQL 7.0(jan 99).
• SQL 2000(Aug 2000).
• SQL 2005(Nov 2005).
• SQL 2008(Aug 2008).
•  T-SQL is a Interpreted language and it’s not a full programming language.T-SQL comes from the SQL ANSI Standard language like PL,Postgre and MySQL.
• Sql Server 2005 express work only on 32 bit but Sql Server 2008 express work on 32/64 bit
• you can upgrade from SQL Server 2005/2008 Developer Edition to Enterprise edition
• You can run Sql 2005 or SQL 2008 side by side with earlier version but you need to consider the order of the installation and likewise you can use the Default instance name in different version.
• You can upgrade from SQL 2000(sp3) to SQL 2005 or 2008 but if you want to update earlier version before 2000 you have to upgrade it to SQL 2000 then to SQL 2005 or 2008.
• Database compatibility Level:
•  60 for SQL 6.0
• 65 for SQL 6.5
• 70 for SQL 7.0
• 80 for SQL 2000
• 90 for SQL 2005
• 100 for SQL 2008

So what is Database compatibility level?
Database compatibility represents the current version of Database which uses the specific version features or syntax that means for example some statements work fine with SQL Server 2000 and does not work with SQL 2005 so to run this statement in SQL Server 2005 keep the Database compatibility in 80 level.

You can change the database compatibility from SSMS or by using sp_dbcmptlevel system stored procedure.

• What about if I want to save my session with current Connections, Queries and other files so I can return back to it later on, so we can use a Solution in SSMS.

Let's see this example:

1. Open you SSMS and then go to File>New>Project and choose SQL Server Scripts
2. Open Solution Explorer and start your work so connect to the specific instance and run some queries even add some files as references and so on.

SQL Server and Arabic language

I have read some articles written about how this products Office 2010 by default support not only Hijri Date but also UmAlQura Date and this good news.

This article show you some points related to Arabic language (which is my mother tongue) with Sql Server.

In general Sql server deals with any language in the same way except some issues become different from language to other language.
So let's begin by Collation and see how Sql Server stored data and retrieve data in Arabic language.

What is a Collation?

A collation determines the rules SQL Server uses to compare and sort character data and determines which characters can be stored in non-Unicode character data types. Collation can be specified at the instance level, database, column level or clause level.

So let's begin by this example to make the points clear:

CREATE TABLE [dbo].[Test](
--here stored data as ASCII char
    [Name_Char_Latain] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
--here stored data as Unicode
    [Name_nChar_Latain] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
--here stored data as ASCII char
    [Name_Char_Arabic] [char](10) COLLATE Arabic_CI_AI NULL,
--here stored data as Unicode
    [Name_nChar_Arabic] [nchar](10) COLLATE Arabic_CI_AI NULL
) ON [PRIMARY]

GO

If your database collation is SQL_Latin1_General_CP1_CI_AS and you tried to insert Arabic data you will get ??? Characters so there are two solutions for these problems: Change database collation or use N' '

You can change database collation as following:

USE [master]
GO
ALTER DATABASE [TestDb] COLLATE Arabic_100_CI_AI
GO

Or you can write your statement like this

INSERT dbo.test VALUES (N'بدر',N'بدر',N'بدر',N'بدر');

After we created the table then inserts these records

INSERT dbo.test VALUES ('بدر','بدر','بدر','بدر');
INSERT dbo.test VALUES ('احمد','احمد','احمد','احمد');
INSERT dbo.test VALUES ('أحمد','أحمد','أحمد','أحمد');
INSERT dbo.test VALUES ('أيات','أيات','أيات','أيات');

Then run these commands:

USE TestDb
GO

SELECT * FROM dbo.test ORDER BY Name_Char_Latain
SELECT * FROM dbo.test ORDER BY Name_nChar_Latain
SELECT * FROM dbo.test ORDER BY Name_Char_Arabic
SELECT * FROM dbo.test ORDER BY Name_nChar_Arab

And you will get result set as the below:

Now what about the hijri date and UmAlQura date?

By default Sql server support Hijri date format and you can do it by using CONVERT function so let's see these examples:

SELECT CONVERT(char(40),GETDATE(),130)
SELECT CONVERT(char(40),GETDATE(),131)

The result set:


29 ربيع الاول 1431 12:53:11:130AM

29/03/1431 12:53:11:130AM

So Sql Server does a great effort to display the date as Hijri but what about the UmAlQura date.

You need to write custom code using .NET CLR to implement it in SQL Server and this great feature introduced with version of SQL Server 2005 to enable you to write .Net code and by this approach we see new functions and data types like HIERARCHYID.

Let's see how we can Convert Gregorian Date to UmAlQura Date by using .Net UDF:

1. First open VS 2005/2008/2010 and create CLR database project

2. Choose a database connection for your reference

3. Right click on your project and Add New item.. and select UDF

4. Add reference using System.Globalization;

5. The complete code:

using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Globalization;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString UmAlQuraConverter(DateTime date, string format)
    {
        return new SqlString(getUmAlQuraDate(date, format));
    }

    static string getUmAlQuraDate(DateTime date, string format)
    {
        UmAlQuraCalendar umAlQuraCal = new UmAlQuraCalendar();
        CultureInfo umAlQuraCulture = new CultureInfo("ar-SA");
        umAlQuraCulture.DateTimeFormat.Calendar = umAlQuraCal;
        return date.ToString(format, umAlQuraCulture);
    }
};

Then choose deploy solution

Then run this command to enable using CLR in your SQL server instance

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

Then run this command to get UmAlQura Date

SELECT dbo.UmAlQuraConverter(GETDATE(),'dd/MM/yyyy')

And you will get result as UmAlQura Date.


Note:
If you use Sql server database as backend system and .net application as frontend system ,in this case leave your date column as it's (Gregorian date) and just change the formatting at design level or user interface application by using inline function to format the date or use Global file to change it at the application level so by this approach you decrease the operations of convert date at Sql server and you avoid to have multiple columns or create custom data type or use custom functions and this make the integration easy to you because your data stored as standard or default data types.

Setting value to a variable by using SELECT or SET statement

You can assign value to a variable by using SET or SELECT statement and almost they work the same except that a SELECT statement has the ability to have the source value come from a column within the SELECT statement.


Let's see these examples to clarify the difference between the two statements:

USE AdventureWorks
GO

DECLARE @Res DateTime
-- Assign value to a variable by using SET statement
SET @Res = GETDATE();
SELECT @Res

SET @Res = (SELECT ModifiedDate FROM Person.Contact WHERE ContactID = 1)
SELECT @Res
-------------------------------------------------------------------------
--Just this case is show the diff between the SET and SELECT
-- It will cause an error
SET @Res = ModifiedDate FROM Person.Contact WHERE ContactID = 1
SELECT @Res
-------------------------------------------------------------------------
-------------------------------------------------------------------------
-- Assign value to a variable by using SELECT statement
SELECT @Res = GETDATE();
SELECT @Res

SELECT @Res = (SELECT ModifiedDate FROM Person.Contact WHERE ContactID = 1)
SELECT @Res
--------------------------------------------------------------------------
-- It will success
SELECT @Res = ModifiedDate FROM Person.Contact WHERE ContactID = 1
SELECT @Res
--------------------------------------------------------------------------

Full Text Search and Service Broker in SQL Server 2008

Some basic points to remember before Implementing Full Text Search:

• Full Text Search scenario come when you have queries beyond the Like clause capabilities against word or phrase.
•  In SQL Server 2008, Full Text Search service is fully integrated with SQL Server database engine.
• Search results from data source like PDF which stored as varbinary data type.
• Search specific word or phrase
• SBS is a process of sending and receiving asynchronous messages in the same database or between different instances of Sql Server.

FOR XML modes

Why retrieve data as XML structure?

1. You need to pass the data as XML structure to others like outside applications
2. Sometime if you retrieve the data as XML structure is easier in read or describable more than if you retrieve it as ROWS AND COLUMNS like for example if you retrieve the authors and their books.

To retrieve the result set as XML structure there are four modes:

1. FOR XML RAW
2. FOR XML AUTO
3. FOR XML EXPLICIT
4. FOR XML PATH

So let's start by FOR XML RAW: It returns each row as an XML element and each column as an XML attribute.

Let's see examples:

USE AdventureWorks
GO

SELECT TOP 3 ProductID
            ,Name
            ,ProductNumber
FROM   Production.Product
FOR XML RAW

Result set of the above query:

--You can specify the name of root as well as name of elements
SELECT TOP 3 ProductID
            ,Name
            ,ProductNumber
FROM   Production.Product
FOR XML RAW('Product'),ROOT('Products')

Result set of the above query:

Note:

Simply remove the attribute if the value is NULL.So you can define you result as separate it element to show the empty value or Null.

SELECT TOP 3 ProductID
            ,Name
            ,ProductNumber
FROM   Production.Product
FOR XML RAW('Product'),ROOT('Products'),ELEMENTS

Now let's start by FOR XML AUTO: It returns result as hierarchy structure and only one level supported.

SELECT TOP 3 p.ProductID
            ,p.Name
            ,pm.Name as 'Model'
FROM   Production.Product p
INNER JOIN Production.ProductModel pm
ON p.ProductModelID = pm.ProductModelID
FOR XML AUTO,ROOT('Products')

Now let's start by FOR XML PATH: allows for the easy creation of different XML structures by simply interpreting column names specified using an XPath-like expression.

Let's see this example:

SELECT TOP 4
    ProductID AS 'ProductID'
    ,ProductNumber AS "comment()"
    ,CAST('<Break/>' AS XML) AS "node()"
FROM Production.Product p
FOR XML PATH('Product'), ROOT('Products');

Result set of the above query:

Now let's start by FOR XML EXPLICIT: provides very specific control over your XML structure.

But if you want to use it there are a set of rules to follow them to build your select statement.

Some of these rules:

1. You must include two columns in your select statement:
    a. Tag column with a numerical value for each level of the hierarchy
    b. Parent column with a numerical value based in your Tag column value
2. you must create a SELECT statement for each level of that hierarchy and use UNION ALL clauses to join those statements.
Let's see this example:

USE AdventureWorks
GO

SELECT 1 AS Tag, 
        NULL AS Parent,
        ProductID AS [Product!1!ProductID], 
        NULL AS [ProductName!2!Name!ELEMENT]
FROM Production.Product  
WHERE Name like 'a%'
UNION ALL
SELECT 2 AS Tag, 
        1 AS Parent,
        ProductID , 
        Name
FROM Production.Product  
WHERE Name like 'a%'
FOR XML EXPLICIT;

Result set of the above query:

Note:

Notice an alias to ProductID, Name column. Notice I use a very specific structure to define the alias name

XML Data type (Internally, stored using the varbinary(max))

Xml data type used to store:
1. XML fragments
2. XML documents

 XML fragment lacks the XML declaration () and does not have to have a root element .

The XML data type can be either typed or untyped. Typed simply means that an XML schema collection is assigned to the type to verify its contents.

Let's see an example to clarify the idea:

USE TestDb
GO

CREATE XML SCHEMA COLLECTION EmployeeSchema
AS
'<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
elementFormDefault="unqualified">
    <xs:element name="employee">
        <xs:complexType>
            <xs:attribute name="FullName" type="xs:string"/>
            <xs:attribute name="IdentityNo" type="xs:integer"/>
        </xs:complexType>
    </xs:element>
</xs:schema>';
GO

CREATE TABLE Employees
(
     EmpID INT IDENTITY PRIMARY KEY
     --this column called as typed because its contents need 
     --to match the schema
    ,FullName XML(DOCUMENT EmployeeSchema)
    --this column called as untyped 
    ,FullName2 XML NULL
)
GO

-- Will succeed:
INSERT Employees VALUES 
('<employee FullName="Fadi Ahmad Abdulwahab" IdentityNo="123234532"/>','<data>fadi</data>');
-- Will not succeed:
INSERT Employees VALUES 
('<employee FullName="Fadi Ahmad Abdulwahab" IdentityNo="fadi"/>','<data>fadi</data>');
GO

Last point need to take care of it:


If you need to change the schema you must first alter all columns that use the schema to instead use untyped XML. Then, you must drop the schema collection, re-create it with the added attributes, and finally alter the columns again to use the schema. And this also harms the performance of Sql server engine because it needs to convert typed data to untyped data.

Note:SQL SERVER 2008 provide you with many of methods that can help you to query or retrieve data as XML.