Personal Tech Journal

1. Table variables are similar to the normal tables or temporary tables. 2. This is good if you don’t want to store large data for temporary use. 3. These are only available during the execution of the program. 4. The Syntax is as follows DECLARE @ TABLE >( , …) 5. The below shows the example to use table variables DECLARE @temp_employee TABLE(EmployeeID int PRIMARY KEY, FirstName varchar(50), LastName varchar(50), Sex char(1), Age int DEFAULT 18, DateOfBirth DateTime) SELECT * FROM @temp_employee

1. Temporary Tables are similar to tables unlike they are life is only for the current session and it will be automatically dropped after that. 2. This is very useful for storing some information during a large computations temporarily instead of using physical tables. 3. They cannot have foreign key constraints. 4. It is stored in the TempDB. 5. The Syntax is CREATE TABLE #< TableName >(< ColumnName1 > < Datatype >,< ColumnName2 > < Datatype >…) 6. The below shows an example how to create a temporary table CREATE TABLE #tempEmployees(EmployeeID int PRIMARY KEY, FirstName varchar(50), LastName varchar(50), Sex char(1), Age int CONSTRAINT df DEFAULT 18, DateOfBirth DateTime)

1. Derived Tables are nothing but using a result of a query as a table. 2. This is much better than using temporary tables. 3. This increases the performance. 4. The below shows how to return employee and department info for those who don’t belong to a inactive department Select EmployeeID, FirstName, LastName, Dt.DeptName from Employee INNER JOIN (Select * from Dept where Status !='InActive') Dt ON Dt.DeptID = Employee.DeptID

1. Common Table Expressions are similar to that of a Derived Table. 2. We can define a CTE and use it multiple times in a query unlike Derived Tables. 3. The number of columns in the expression should be similar to that of the query result used in CTE. 4. CTE can be used recursively also. The Syntax is as follows WITH expression_name [ ( column_name [,...n] ) ] AS ( CTE_query_definition ) The below example demonstrate the use WITH Dt_CTE (DeptID, DeptName, Status) AS (Select * from Dept where Status !='InActive') Select EmployeeID, FirstName, LastName, Dt.DeptName from Employee INNER JOIN Dt_CTE Dt ON Dt.DeptID = Employee.DeptID

The Limitations for XML Datatype is as follows 1. The size of the XML datatype cannot exceed 2GB. 2. We cannot cast or convert to text or ntext. 3. The column of XML datatype cannot be sorted or we can not group the same using GROUP BY. 4. This cannot be passed as parameters to be used with scalar or built-in function except with ISNULL, COALESCE, and DATALENGTH. 5. This cannot be used as a column that can have an Index. But we can add a non-clustered index using the INCLUDE keyword.

Office 2010 and Sharepoint 2010 Service Pack 1 is released. Check out here http://blogs.technet.com/b/office_sustained_engineering/archive/2011/06/28/announcing-office-2010-and-sharepoint-2010-service-pack-1-availability.aspx

When working with a RadioButtonList control and if you wish to disable one of the list items in it , the very obvious solution would be as mentioned below radioList.Items[1].Enabled = false; But to surprise this does not work. Bingo...the solution to this problem is to use as mentioned below radioList.Items[1].Attributes.Add("disabled", "disabled"); Happy Coding....

The nodes() method is useful when you want to shred an xml data type instance into relational data means it will be mapped into a new row. The syntax is nodes (XQuery) as Table(Column) The below shows how to display the xml data in relational data in different rows. SELECT JobHistory.query('.')as nodes FROM Employee CROSS APPLY JobHistory.nodes('/Job') as MyNodes(a)

The Microsoft SQL SERVER code name "Denali" CTP 3 is available to download at http://technet.microsoft.com/en-US/evalcenter/hh225126.aspx

I received a invite for Google+ from my friend. On it now.. Exploring and i would say its a welcome change from Facebook. Lets explore more.

The exist() method returns a bit as follows 1, this means the XQuery expression in a query returns a result which is at least a xml node. 0, this means it’s a empty result NULL, this means that it was executed against a NULL value. The Syntax is exist (XQuery) The below shows how to check if the JobID 1 exists SELECT JobHistory.exist('/Job[@JobID=1]') FROM Employee

This performs an XQuery against the XML and returns a value of SQL type. This method returns a scalar value. The syntax is value (XQuery, SQLType) The below shows how to return the company name value of a varchar datatype SELECT JobHistory.value('(/Job/CompanyName)[1]','varchar(100)') FROM Employee

The Query() method is used to query the XML data using XQuery Expression The Syntax is query ('XQuery') The below shows how to display the xml node where jobid is 1 SELECT JobHistory.query('/Job[@JobID=1]') FROM Employee The below shows how to display the company name node where jobid is 1 SELECT JobHistory.query('/Job[@JobID=1]/CompanyName/text()') FROM Employee

We can use the xml data type methods to modify/query an XML stored in a column of xml datatype. The methods are as follows 1. Modify() 2. Value() 3. Query() 4. Exist() 5. Nodes() Today, we will see the use of Modify() method The Modify() method is used to modify parts of the XML data in the XML column using XQuery Expression The Syntax is modify (XML_DML) The below example shows to add one more job node to the xml UPDATE Employee SET JobHistory.modify( 'insert News Corp 1 after (/Job)[1]') WHERE EMPLOYEEID = 1 The below example shows to replace a value in the xml node. UPDATE Employee SET JobHistory.modify( 'replace value of (/Job/@JobID)[1] with 3') WHERE EMPLOYEEID = 1 UPDATE Employee SET JobHistory.modify( 'replace value of (/Job/CompanyName/text())[1] with "BBC Corp"') WHERE EMPLOYEEID = 1 The below example shows to delete a node in the xml UPDATE Employee SET JobHistory.modify( 'delete (/Job[@JobID=3])') WHERE EMPLOYEEID = 1

The xml data type lets you store XML documents and fragments in a SQL Server database.  The below shows how to add a new column of XML datatype ALTER TABLE Employee ADD JobHistory XML The below shows how to Store the data in a XML column UPDATE Employee SET JobHistory= ' ABC Corp 1 ' WHERE EMPLOYEEID = 1

The "sp_executesql" is used for executing the sql statements that are build dynamically with or without parameters. The below example shows how to execute by dynamically passing the employee id. DECLARE @EmployeeNumber int; DECLARE @SQLString nvarchar(500); DECLARE @Parameter nvarchar(500); SET @SQLString = 'SELECT * FROM EMPLOYEE WHERE EMPLOYEEID = @EmployeeID'; SET @Parameter = '@EmployeeID int'; SET @EmployeeNumber = 2; EXECUTE sp_executesql @SQLString, @Parameter, @EmployeeID = @EmployeeNumber;

Dynamic SQL is nothing but SQL query that is generated programmatically. For example, we can get records of any table using the below Stored Procedure which implements Dynamic SQL CREATE PROC GETDETAILSBYTABLENAME(@TableName varchar(50)) AS BEGIN Declare @SQLQuery VarChar(5000) SELECT @SQLQuery = 'SELECT * FROM ' + @TableName Exec ( @SQLQuery) END

I am sure we commonly use SQL SERVER Reporting Services(SSRS)using the Data Source as the database. But if we have think to use XML, then one of the options to use the XML would be to host the XML's in a virtual directory in IIS. Then access the same as a connection string in SSRS. We can think of various options as well.

We used to always close the tags like for eg: Now in HTML 5 we can even write without closing tags

Check out the new Office 365 which is flexible and easy in creating documents , maintaining calendar, emails. It is all managed my microsoft, which means it can be accessed from anywhere. It offers the pay as you go service. For more details read here http://www.microsoft.com/en-in/office365/online-software.aspx To know how it work click here http://www.microsoft.com/en-in/office365/online-software.aspx