Personal Tech Journal

We may need to do some operation each record wise in the record set returned. This is reason for cursors. The Syntax is as follows DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ] [;] The below example shows that we need to print the employee name and employee id in a specific manner as Robert Smith (1000) DECLARE @ EmployeeID int DECLARE @Name varchar (100) DECLARE cursor_name CURSOR FOR SELECT EmployeeID , FirstName + ' ' + LastName FROM Employee OPEN cursor_name FETCH NEXT FROM cursor_name INTO @ EmployeeID , @Name WHILE @@FETCH_STATUS = 0 BEGIN   PRINT @Name + '(' + Convert( varchar (10), @ EmployeeID ) + ')'   FETCH NEXT FROM cursor_name   INTO @ EmployeeID , @Name END CLOSE cursor_name       DEALLOCATE cursor_name

Partition Function Information The Partition Function Information is obtained from the sys.partition_functions table as shown below SELECT * FROM SYS.PARTITION_FUNCTIONS Partition Range Information The Partition Range information is obtained from the sys.partition_range_values as shown below SELECT * FROM SYS.PARTITION_RANGE_VALUES Partition Scheme Information The Partition Scheme information is obtained from the sys.partition_scheme as shown below SELECT * FROM SYS.PARTITION_SCHEME Partitions Information The Partition information can be obtained from the sys.partitions table as shown below . SELECT * FROM SYS.PARTITIONS

Drop a Partition Function To drop a partition function it should not have any partition scheme associated with it. The Syntax is as follows   DROP PARTITION FUNCTION < Partition Function Name  > The below example shows how to drop a partition function   DROP PARTITION FUNCTION EmployeePartitionLeft Drop a Partition Scheme To drop a partition scheme it should not have any table associated with it . The Syntax is as follows   DROP PARTITION SCHEME < Partition Scheme Name > The below example shows how to drop a partition scheme   DROP PARTITION SCHEME EmployeePartitionScheme

I just happened to see this on Phil Haack's blog that the ASP.NET MVC 4 Developer Preview Released. Check out for more at   http://haacked.com/archive/2011/09/14/asp-net-mvc-4-developer-preview-released.aspx

If you want your tweets from twitter to update facebook, then check out this article on how to achieve the same  https://support.twitter.com/articles/31113-how-to-use-twitter-with-facebook

§ The partition scheme is altered to add new filegroup which may be required when partitions are splitted . § The Syntax is as follows   ALTER PARTITION SCHEME   NEXT USED < Filegroup Name > § The below example shows how it associates a new filegroup FG4 to the partition scheme.    ALTER PARTITION SCHEME  EmployeePartitionScheme NEXT   USED           EmployeePartitionFG3

I came across these good articles. Do read when you have time 30 Ways to Quickly Improve Your Life Experience - http://advancedlifeskills.com/blog/30-ways-to-quickly-improve-your-life/ 10 Simple Ways to Be More Likable - http://advancedlifeskills.com/blog/be-more-likable/ 5 Ways to Keep Clutter Under Control - http://advancedlifeskills.com/blog/5-ways-to-keep-clutter-under-control/

§ The partitions can be merged by merging the partition ranges. The partition range value mentioned will merge that to the next greater partition range value into a singe partition. § The syntax is as follows   ALTER PARTITION FUNCTION partition_function_name () {   SPLIT RANGE ( boundary_value ) | MERGE RANGE (   boundary_value ) } [ ; ] § The below shows the example for the same         ALTER PARTITION FUNCTION EmployeePartitionLeft ()   MERGE RANGE(150)

The adding of a new partition is nothing we are splitting an existing one and creating another out of that. The splitting is done by using the alter partition command. We have to note that before we split the partition there should be a additional filegroup already associated in the partition scheme. If there is no unused filegroup available then we cannot split. So before splitting we have to ensure that a filegroup is added to the partition scheme The syntax is as follows ALTER PARTITION FUNCTION partition_function_name() { SPLIT RANGE ( boundary_value ) | MERGE RANGE ( boundary_value ) } [ ; ] The below example shows how to add a new partition by the SPLIT option  ALTER PARTITION FUNCTION EmployeePartitionLeft () SPLIT RANGE (150)

1. Querying the data from a particular partition The Syntax is as follows SELECT …/* FROM WHERE $PARTITION. ( ) = The Partition number refers to first partition range or second partition range and so on, The first partition range is referred as 1 , second as 2 and so on. The below examples returns all the records in the partition 1. SELECT * FROM EMP WHERE $PARTITION.EmployeePartitionLeft (EMPLOYEEID) = 1 2. Querying for Knowing the Partition Number The syntax is as follows SELECT $PARTITION. ( ) = The below example returns 2 as the partition number for partition range >1 and less than equal to 100. SELECT $PARTITION.EmployeePartitionLeft(100) 3. Querying to find the count of records in each partition The syntax is as follows SELECT $PARTITION. ( ), COUNT(*) FROM GROUP BY $PARTITION. ( ) The below example returns the number of records in each partitions. SELECT $PARTITION.EmployeePartitionLeft (EMPLOYEEID) AS Partition, COUNT(*) AS [COUNT] FROM EMPLOYEE GROUP BY $PARTITION...

The final step during the process of creating partition is to create the partitioned table. The below example shows the creation of Partitioned Tables Create Table Employee( EmployeeID int PRIMARY KEY, FirstName varchar(50), LastName varchar(50), Sex char(1), Age int CONSTRAINT df DEFAULT 18, DateOfBirth DateTime) ON EmployeePartitionScheme (EmployeeID)

This is needed for associating the partitions to a specific filegroups. This is the second step in creating partitions. The Syntax is as follows CREATE PARTITION SCHEME  AS PARTITION  TO ( , ,….) The example below shows the creation of the same CREATE PARTITION SCHEME EmployeePartitionScheme AS PARTITION EmployeePartitionLeft TO ([EmployeePartitionFG1], [EmployeePartitionFG2], [PRIMARY]) The below example shows if all reside on the same filegroup   CREATE PARTITION SCHEME EmployeePartitionScheme  AS PARTITION EmployeePartitionLeft ALL TO (EmployeePartitionFG1)

The partition function is created for setting the range partitions. The ranges can be set for a lower or upper threshold. This is the first step in creating partitions The syntax is as follows CREATE PARTITION FUNCTION ( ) AS RANGE LEFT/RIGHT FOR VALUES ( , ,…) LEFT : This specifies the maximum value of the first partition RIGHT : This specifies the minimum value of the second partition. The below examples shows creating a partitions as follows Partition 1 : column1 Partition 2 : column1 > 1 and column1 Partition 3 : column1 > 100 and column1 Partition 4 : column1 > 1000 CREATE PARTITION FUNCTION EmployeePartitionLeft (int) AS RANGE LEFT FOR VALUES (1, 100, 1000) The below examples shows creating a partitions as follows Partition 1 : column1 Partition 2 : column1 >= 1 and column1 Partition 3 : column1 >= 100 and column1 Partition 4 : column1 >= 1000 CREATE PARTITION FUNCTION EmployeePartitionRight (int) AS RANGE RIGHT FOR VALU...