Personal Tech Journal

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...

Partitioning is helpful for managing and accessing smaller chunks of data very efficiently and at a faster pace without compromising on data integrity. SQL Server supports Range Partitions. This means that the partitioning is based on data ranges.

To know more about the error information, we can use the below mentioned functions 1. ERROR_NUMBER() returns the number of the error. 2. ERROR_SEVERITY() returns the severity. 3. ERROR_STATE() returns the error state number. 4. ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred. 5. ERROR_LINE() returns the line number inside the routine that caused the error. 6. ERROR_MESSAGE() returns the complete text of the error message. As discussed in my previous post the divide by zero error can be seen using the error information functions. CREATE PROCEDURE GenerateDetailedException AS BEGIN BEGIN TRY SELECT 1/0 END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage; END CATCH END

Exception Handling is a practice to capture the error efficiently and display the error in a well formatted way. In SQL Server, we use the TRY..CATCH Block for handling exceptions. The syntax is as follows BEGIN TRY       { sql_statement | statement_block } END TRY BEGIN CATCH       [ { sql_statement | statement_block } ] END CATCH [ ; ] The below shows an exception message for divide by zero error CREATE PROCEDURE GenerateException AS BEGIN BEGIN TRY SELECT 1/0 END TRY BEGIN CATCH SELECT 'Error Occurred' END CATCH END

@@ERROR returns zero if the last executed statement did not throw an error, else it will return an error number. The below shows a error message with error number when an Divide by Zero is encountered. CREATE PROCEDURE ErrorNumber AS BEGIN DECLARE @ErrorNumber varchar(20) SELECT 1/0 SET @ErrorNumber = @@ERROR PRINT 'Error Generated : ' + @ErrorNumber END

RAISERROR will use the built-in messages in sys.messages view or dynamically generated error message. The Syntax is as follows RAISERROR ( { msg_id | msg_str | @local_variable }     { ,severity ,state }      [ ,argument [ ,...n ] ] )     [ WITH option [ ,...n ] ] Severity 0 – 18 , Can be raised by any user Severity 19 – 25, Can be raised by those who have sysadmin role. State : This is a integer value between 0 to 255 The below shows an error message if the employee id does not exists CREATE PROCEDURE GenerateError(@EmployeeID int) AS BEGIN If exists(SELECT 1 FROM employee WHERE EmployeeID = @EmployeeID) BEGIN SELECT * FROM Employee WHERE EmployeeID = @EmployeeID END ELSE BEGIN RAISERROR ('Employee ID does not exists', 10, 2) END END

To delete a function we need to use the drop function The Syntax is as follows DROP FUNCTION The below shows how to drop a function DROP FUNCTION USFORMATPHONE

We can modify the user defined functions by using the Alter command. The Syntax as shown below Scalar-valued Functions ALTER FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type     [ = default ] }     [ ,...n ]   ] ) RETURNS return_data_type     [ WITH [ ,...n ] ]     [ AS ]     BEGIN         function_body         RETURN scalar_expression     END [ ; ] Table-valued Functions ALTER FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type     [ = default ] }     [ ,...n ]   ] ) RETURNS @return_variable TABLE     [ WITH [ ,...n ] ]     [ AS ]     BEGIN         function_body         RETURN     END [ ; ] The below shows how a function has been modified to change the message ALTER FUNCTION USFORMATPHONE(@PhoneNumber varchar(25)) RETURNS Varchar(50) AS BEGIN DECLARE @phone varchar(50) IF(LEN(@PhoneNumber) = 10) SET @phone = '('+ SUBSTRING(@PhoneNumber,1,3) + ')...

We will look into the Table-Valued User Defined Function The syntax for Table-Valued is as follows CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type     [ = default ] [READONLY] }     [ ,...n ]   ] ) RETURNS @return_variable TABLE      [ WITH [ ,...n ] ]  [ AS ]      BEGIN          function_body          RETURN      END [ ; ] The below example shows how to create a multistatement table-valued function which provides details based on the department status. CREATE FUNCTION DEPT_DETAILS_BY_STATUS(@Status varchar(10)) RETURNS @DEPTSTATUS TABLE ( DEPTID int, DEPTNAME varchar(100), STATUS varchar(10) ) BEGIN INSERT INTO @DEPTSTATUS Select * from Dept where Status = @Status RETURN END The usage of the same is as follows Select EmployeeID, FirstName, LastName, Dt.DeptName from Employee INNER JOIN DEPT_DETAILS_BY_STATUS('Active') Dt ON Dt.DeptID = Employee.DeptID

I received this mail and i thought its really worth to share. JESUS VS. Satan Satan went to visit Jesus in the Garden of Eden, and Satan came all happy and boasting. (Lk 4: 1-12; Job 1: 6-12) "Yes, Lord, now I have everybody captive, (well nearly everybody down there). I set traps, I used the baits of temptation, I know well what each one of them can't resist. I nearly caught them all!" (I Pet 5: 8-9; Eph 6: 10-17) "What are you going to do with them?" Jesus asked. and He was praying to God the Father. Satan answered "Oh, I am going to have some fun with them!" I'll make them divorce after they have married so the foundation of humanity will never be able to be established "the family" (Mt 19: 4-6; Mal 2: 16 ) I will make them hate each other and abuse each other, make them fall into alcohol and drugs without control. (Rom 13: 12-14) I will teach them to make weapons and bombs, so they kill each other "I am really going to have...

We will look into the Scalar-Valued User Defined Function The syntax for Scalar-Valued is as follows CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type     [ = default ] [ READONLY ] }     [ ,...n ]   ] ) RETURNS return_data_type     [ WITH [ ,...n ] ]      [ AS ]      BEGIN          function_body          RETURN scalar_expression      END [ ; ] The below example shows how a function returns the phone number in US Phone format (XXX)XXX-XXXX CREATE FUNCTION USFORMATPHONE(@PhoneNumber varchar(25)) RETURNS Varchar(50) AS BEGIN DECLARE @phone varchar(50) IF(LEN(@PhoneNumber) = 10) SET @phone = '('+ SUBSTRING(@PhoneNumber,1,3) + ')' + SUBSTRING(@PhoneNumber,4,3) + '-' + SUBSTRING(@PhoneNumber,7,4) ELSE SET @phone = 'Invalid Phone Number' Return @phone END The usage of the same is as follows SELECT EMPLOYEE.DBO.USFORMATPHONE ('1234567890')

This afternoon had been for a team lunch with my colleagues to Benito's . It was a nice place with some great food. Especially there Benito's special soup, sizzlers .. . A good place to visit..