Skip to main content

Posts

XML data type Functions – SQL Server

 The XML data type in MSSQL Server is a powerful tool for handling and manipulating XML data within a relational database system. In addition to storing XML data as a column in a table, SQL Server provides a number of XML functions and methods that allow for easy parsing, querying, and transformation of XML data. In this blog post, we will cover all XML data type methods available in MSSQL Server. value() Method: The value() method is used to extract a single value from an XML instance. This method accepts an XQuery expression as a parameter, which is used to identify the value to be extracted. The syntax of the value() method is as follows: xml . value ( XQuery expression , Data type ) For example, the following query extracts the value of the 'name' element from an XML column called 'MyXMLColumn': SELECT MyXMLColumn . value ( '(/Root/Person/Name)[1]' , 'varchar(50)' ) AS Name FROM MyTable   query() Method: The query() method i...
Post a Comment
Read more
Recent posts

Analytic Functions - SQL Server

 Analytic functions in SQL Server are used to perform complex calculations on a set of data within a specific partition, and can be useful in data analysis and business intelligence applications. In this blog, we will discuss all the analytic functions available in SQL Server with examples. ROW_NUMBER The ROW_NUMBER function assigns a unique sequential number to each row within a partition of a result set. Example: SELECT ROW_NUMBER () OVER ( ORDER BY Sales DESC ) as SalesRank , Salesperson , Sales FROM SalesData   RANK The RANK function assigns a rank to each row within a partition of a result set, with ties receiving the same rank. Example: SELECT RANK () OVER ( PARTITION BY Department ORDER BY Salary DESC ) as DepartmentRank , Name , Salary FROM Employee   DENSE_RANK The DENSE_RANK function assigns a rank to each row within a partition of a result set, with ties receiving the same rank and no gaps in ranking. Example: ...
Post a Comment
Read more

Aggregate Functions In SQL Server

       In SQL Server, aggregate functions are used to perform calculations on a set of values and return a single value. These functions operate on multiple rows of a table and return a single value based on the specified criteria. In this blog, we will discuss the aggregate functions available in SQL Server. COUNT Function: The COUNT function returns the number of rows that match the specified criteria. It can be used with the asterisk (*) wildcard to count all rows in a table. Syntax: COUNT (* | [ALL|DISTINCT] expression ) Example: SELECT COUNT (*) AS TotalEmployees FROM Employees In this example, the COUNT function is used to count the total number of rows in the Employees table. SUM Function: The SUM function returns the sum of all values in a column. Syntax: SUM ( [ALL|DISTINCT] expression ) Example: SELECT SUM ( Salary ) AS TotalSalary FROM Employees In this example, the SUM function is used to calculate the total salary of...
Post a Comment
Read more

Data Type Conversion - SQL Server

 In SQL Server, data type conversion allows us to change the data type of a value from one data type to another. Data type conversion can be implicit or explicit, depending on whether the conversion is performed automatically or explicitly by the user. In this blog, we will discuss data type conversion in SQL Server with examples. Implicit Data Type Conversion Implicit data type conversion is the automatic conversion of data from one data type to another. SQL Server automatically converts data when it is required, such as during arithmetic operations. Here is an example of implicit data type conversion: DECLARE @x int = 5 DECLARE @y float = 3.14 DECLARE @result float SET @result = @x + @y SELECT @result In this example, we have declared two variables, @x as an integer and @y as a float. We then add the values of @x and @y and store the result in @result, which is also a float. Since the data types of @x and @y are different, SQL Server implicitly converts...
Post a Comment
Read more