All About SQL Server, T-SQL

Exception Handling RAISEERROR, TRY- CATCH

2023-03-15T08:48:00.002-07:00

Exception handling is an essential aspect of any programming language, including T-SQL, the language used in Microsoft SQL Server. In this blog, we will discuss the basics of exception handling in T-SQL, including how to raise and catch exceptions, and best practices for handling errors in SQL Server.

What is an Exception in T-SQL?

An exception is an error condition that occurs during the execution of a T-SQL statement. When an exception occurs, the SQL Server engine raises an error message that provides details about the error, including the error number, severity, and message text. These error messages can be viewed in the SQL Server error log, or they can be returned to the client application that issued the T-SQL statement.

How to Raise an Exception in T-SQL

In T-SQL, you can raise an exception by using the RAISERROR statement. The RAISERROR statement allows you to specify an error message, error number, severity level, and state. For example, the following code raises an exception with an error message and error number:

RAISERROR('Error message', 16, 1)

In this example, the error message is 'Error message', the error number is 16, and the severity level is 1. The severity level can range from 1 to 25, with 1 being the least severe and 25 being the most severe.

How to Catch an Exception in T-SQL

In T-SQL, you can catch exceptions by using the TRY...CATCH construct. The TRY...CATCH construct allows you to specify a block of code to try, and a block of code to catch any exceptions that occur during the try block. For example, the following code uses a TRY...CATCH block to catch and handle exceptions:

BEGIN TRY

-- Code to try

END TRY

BEGIN CATCH

-- Code to handle exception

END CATCH

In this example, the code to try is contained within the BEGIN TRY and END TRY block, and the code to handle the exception is contained within the BEGIN CATCH and END CATCH block. If an exception occurs during the try block, the code in the catch block is executed.

Here are some best practices for handling exceptions in T-SQL:

Ø Always use the TRY...CATCH construct to catch and handle exceptions.

Ø Use descriptive error messages to make it easier to troubleshoot issues.

Ø Use the appropriate severity level for each error. Use lower severity levels for less severe errors and higher severity levels for more severe errors.

Ø Avoid catching exceptions at the highest level of your code. Instead, catch exceptions at the appropriate level, and allow higher-level code to handle exceptions that cannot be handled at lower levels.

Ø Always include an error-handling section in stored procedures and other T-SQL code.

Conclusion

Exception handling is an important aspect of any programming language, including T-SQL. By understanding how to raise and catch exceptions, and by following best practices for handling errors in SQL Server, you can make your T-SQL code more robust and reliable.

Temporary Tables in SQL server

2023-03-15T08:47:00.002-07:00

Temporary Tables in SQL server

In SQL Server, temporary tables are a type of table that is created and used for a specific session or connection. They are used to store data temporarily that can be used for later processing or analysis. Temporary tables can be created in two ways: local and global. In this blog, we will discuss both types of temporary tables and their usage in SQL Server.

Local Temporary Tables:

A local temporary table is a table that is created within the current connection and is only available to the session that created it. It is automatically dropped when the session is closed. The table name begins with a single hash (#) sign. Here is an example of how to create a local temporary table:

CREATE TABLE #TempTable

(

ID INT PRIMARY KEY,

Name VARCHAR(50),

Age INT

);

In the above example, we have created a local temporary table named #TempTable with three columns: ID, Name, and Age. Once the table is created, we can insert data into it using the INSERT INTO statement:

INSERT INTO #TempTable (ID, Name, Age)

VALUES (1, 'John Doe', 30),

(2, 'Jane Smith', 25),

(3, 'Bob Johnson', 40);

After inserting data, we can retrieve the data from the temporary table using a SELECT statement:

SELECT * FROM #TempTable;

Global Temporary Tables:

A global temporary table is a table that is created within the current session, but it is available to all sessions. It is dropped when all sessions referencing it have closed. The table name begins with two hash (##) signs. Here is an example of how to create a global temporary table:

CREATE TABLE ##TempTable

(

ID INT PRIMARY KEY,

Name VARCHAR(50),

Age INT

);

In the above example, we have created a global temporary table named ##TempTable with three columns: ID, Name, and Age. Once the table is created, we can insert data into it using the INSERT INTO statement:

INSERT INTO ##TempTable (ID, Name, Age)

VALUES (1, 'John Doe', 30),

(2, 'Jane Smith', 25),

(3, 'Bob Johnson', 40);

After inserting data, we can retrieve the data from the global temporary table using a SELECT statement:

SELECT * FROM ##TempTable;

Temporary Tables with SELECT INTO:

Another way to create temporary tables is by using the SELECT INTO statement. This statement creates a new table with the same structure as the result set returned by a SELECT statement. Here is an example:

SELECT ID, Name, Age INTO #TempTable FROM Employees;

In the above example, we have created a local temporary table named #TempTable by selecting data from an existing table named Employees. The new table has the same structure as the Employees table, and the data is copied into it. Once the table is created, we can manipulate the data in the same way as any other table.

SQL Server temporary tables are a useful feature that allows you to create tables that exist only for the duration of a session or transaction. Like any feature, there are both advantages and disadvantages to using temporary tables.

Pros:

Ø Temporary tables can be used to store intermediate results during complex query processing. This can help improve query performance and reduce the number of round trips between the client and server.

Ø They allow you to break down complex queries into smaller, more manageable steps. This can make queries easier to understand and debug.

Ø Temporary tables are often used in stored procedures to store data for later use. This can be useful when working with large amounts of data or when you need to reuse the same data across multiple queries.

Ø Since temporary tables are only visible within the current session or transaction, they can help to reduce naming conflicts between different users and applications.

Cons:

Ø Temporary tables can increase disk usage and memory consumption, especially when working with large datasets. This can lead to performance issues and may require additional resources to manage.

Ø They can also be a source of contention when multiple users or applications are working with the same set of temporary tables. This can lead to issues with locking and blocking, which can impact query performance.

Ø Since temporary tables are only visible within the current session or transaction, they cannot be used to share data between different sessions or transactions. This can make it more difficult to write code that relies on shared data.

Ø Temporary tables can be more difficult to manage and maintain than regular tables. They require more attention to ensure that they are created and dropped at the appropriate times, and that they are not causing performance issues.

Ø Overall, temporary tables are a useful feature that can help improve query performance and simplify complex queries. However, they should be used judiciously, and you should be aware of their potential downsides.

Conditional Statements in SQL Server – IF, ELSE, WHILE,CASE & CHOOSE

2023-03-14T12:14:00.000-07:00

T-SQL, or Transact-SQL, is a procedural programming language used in Microsoft SQL Server. It provides several conditional statements that allow users to create decision-making processes based on specific conditions. These statements include IF...ELSE, CASE, and CHOOSE.

In this blog, we will explore these conditional statements in T-SQL and their usage.

IF...ELSE Statement:

The IF...ELSE statement is used to execute a particular set of SQL statements if a certain condition is true or false. It has the following syntax:

IF condition

BEGIN

-- statement or block of statements executed if condition is true

END

ELSE

BEGIN

-- statement or block of statements executed if condition is false

END

In this syntax, the condition is a logical expression that evaluates to either true or false. If the condition is true, the statement or block of statements inside the BEGIN and END keywords will be executed. If the condition is false, the statement or block of statements inside the ELSE clause will be executed.

Let's take a look at an example to better understand the usage of the IF-ELSE statement:

DECLARE @number int = 10

IF @number > 0

BEGIN

PRINT 'The number is positive'

END

ELSE

BEGIN

PRINT 'The number is negative or zero'

END

In this example, the condition is @number > 0, which is true since @number is 10. Therefore, the statement inside the BEGIN and END keywords after the IF keyword will be executed, which is to print "The number is positive".

You can also use the IF-ELSE statement with multiple conditions by using the ELSE IF clause. The syntax for the ELSE IF clause is as follows:

IF condition1

BEGIN

-- statement or block of statements executed if condition1 is true

END

ELSE IF condition2

BEGIN

-- statement or block of statements executed if condition2 is true

END

ELSE

BEGIN

-- statement or block of statements executed if all conditions are false

END

Let's take a look at an example that uses the ELSE IF clause:

DECLARE @number int = 0

IF @number > 0

BEGIN

PRINT 'The number is positive'

END

ELSE IF @number < 0

BEGIN

PRINT 'The number is negative'

END

ELSE

BEGIN

PRINT 'The number is zero'

END

In this example, the condition @number > 0 is false since @number is 0. Therefore, the ELSE IF clause will be evaluated, and the condition @number < 0 will also be false. Finally, the ELSE clause will be executed, which prints "The number is zero"

CASE Statement:

The CASE statement is used to create multiple conditions in a single SQL statement. It has two variations: the simple CASE and the searched CASE.

The simple CASE statement has the following syntax:

CASEexpression

WHEN value1 THEN result1

WHEN value2 THEN result2

...

ELSE resultN

END

Here, expression is the value that we want to evaluate. If it matches any of the valueX, then the corresponding resultX is returned. If none of the values match, then the ELSE block is executed.

For example, consider a table product that contains the details of products and their prices. We can use the CASE statement to categorize the products based on their prices.

SELECT name, price,

CASE

WHEN price < 100 THEN 'Low Price'

WHEN price >= 100 AND price < 500 THEN 'Medium Price'

ELSE 'High Price'

END AS price_category

FROM product

Here, the CASE statement evaluates the price of each product and categorizes it as Low Price, Medium Price, or High Price.

The searched CASE statement has a similar syntax but uses Boolean expressions instead of values.

CASE

WHEN boolean_expression1 THEN result1

WHEN boolean_expression2 THEN result2

...

ELSE resultN

END

CHOOSE Statement:

The CHOOSE statement is used to select a value from a list of expressions based on an index. It has the following syntax:

CHOOSE (index, value1, value2, ..., valueN)

Here, index is the position of the value that we want to select, and valueX is the list of expressions from which we want to select.

For example, consider the following query:

SELECT CHOOSE(2, 'apple', 'banana', 'orange')

Here, the CHOOSE statement selects the value banana as it is in the second position in the list of expressions.

WHILE loop

In SQL Server, the WHILE loop is a control flow statement that allows you to repeatedly execute a block of statements as long as a specific condition is true. It is a powerful feature of T-SQL that is commonly used in various database applications. This blog will cover the usage of the WHILE loop in SQL Server and how it can be used to write effective code.

The syntax for the WHILE loop is as follows:

WHILEcondition

BEGIN

-- statement or block of statements executed as long as condition is true

END

In this syntax, the condition is a logical expression that evaluates to either true or false. As long as the condition is true, the statement or block of statements inside the BEGIN and END keywords will be executed repeatedly.

Let's take a look at an example to better understand the usage of the WHILE loop:

DECLARE @count int = 1

WHILE @count <= 10

BEGIN

PRINT @count

SET @count = @count + 1

END

In this example, the condition is @count <= 10, which is true since @count is initially 1. Therefore, the statement inside the BEGIN and END keywords after the WHILE keyword will be executed, which is to print the value of @count. The value of @count is then incremented by 1 using the SET statement. The loop will continue to execute as long as @count is less than or equal to 10.

You can also use the WHILE loop with multiple conditions by using the logical operators AND and OR. The syntax for using logical operators in the WHILE loop is as follows:

WHILEcondition1 AND/OR condition2 AND/OR condition3

BEGIN

-- statement or block of statements executed as long as all conditions are true

END

Let's take a look at an example that uses logical operators in the WHILE loop:

DECLARE @count int = 1

DECLARE @flag bit = 1

WHILE @count <= 10 AND @flag = 1

BEGIN

PRINT @count

SET @count = @count + 1

IF @count = 6

SET @flag = 0

END

In this example, the WHILE loop executes as long as @count is less than or equal to 10 AND @flag is equal to 1. Inside the loop, the value of @count is printed and incremented by 1 using the SET statement. If @count is equal to 6, the value of @flag is set to 0, which will cause the loop to exit after the current iteration.

INSERT and UPDATE Statements in SQL Server

2023-03-14T12:11:00.004-07:00

SQL Server is a powerful database management system that allows you to manipulate and manage data in your databases. Two of the most commonly used SQL statements are INSERT and UPDATE, which are used to add new data to a table and modify existing data, respectively. In this blog post, we will explore the syntax and usage of these two statements in SQL Server.

INSERT Statement

The INSERT statement is used to add new data to a table in SQL Server. The basic syntax of the INSERT statement is as follows:

Syntax

INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);

In this syntax, table_name is the name of the table where the data will be inserted, and column1, column2, column3, etc. are the names of the columns in the table where the data will be inserted. The VALUES clause contains the actual data to be inserted into the table, in the same order as the columns.

Here is an example of an INSERT statement that adds a new record to a table called employees:

INSERT INTO employees (first_name, last_name, age, salary)

VALUES ('John', 'Doe', 30, 50000);

In this example, we are inserting a new employee record with the first name 'John', last name 'Doe', age 30, and salary $50,000 into the employees table.

UPDATE Statement

The UPDATE statement is used to modify existing data in a table in SQL Server. The basic syntax of the UPDATE statement is as follows:

UPDATE table_name

SET column1 = value1, column2 = value2, ...

WHERE condition;

In this syntax, table_name is the name of the table where the data will be updated, column1, column2, etc. are the names of the columns to be updated, and value1, value2, etc. are the new values for the columns. The WHERE clause specifies the condition that must be met for the update to take place.

Here is an example of an UPDATE statement that modifies an existing record in the employees table:

UPDATE employees

SET salary = 60000

WHERE last_name = 'Doe';

In this example, we are updating the salary of all employees with the last name 'Doe' to $60,000.

Best Practices for Writing INSERT and UPDATE Statements

To write efficient and maintainable INSERT and UPDATE statements in SQL Server, you should follow these best practices:

Use parameterized queries: Use parameterized queries to prevent SQL injection attacks and improve performance. Parameterized queries allow you to pass parameters to a query instead of concatenating values into the query string.

Validate user input: Always validate user input to prevent SQL injection attacks and ensure that only valid data is inserted or updated in the database.

Use transactions: Use transactions to ensure that multiple INSERT and UPDATE statements are executed as a single atomic operation. This helps to maintain the integrity of the data and prevent data corruption.

Use indexes: Use indexes to improve query performance. Indexes can speed up data retrieval by allowing the database engine to quickly locate the data that is required for a query.

Notes:

The INSERT and UPDATE statements are powerful tools for manipulating data in SQL Server. By following best practices and writing efficient code, you can use these statements to add new data to a table and modify existing data in a way that meets the needs of your application.

Methods to insert and Update the Table

In SQL Server, there are two primary ways to update or insert data into a table: row-by-row and set-based. In this blog post, we will explore the concept of set-based operations, and how they can be used to improve the efficiency and performance of update and insert statements.

Row-By-Row vs Set-Based Operations

Traditionally, SQL Server has executed update and insert statements on a row-by-row basis. This means that the statement is executed once for each row that needs to be updated or inserted. While this approach is simple to understand and easy to implement, it can be very inefficient for large data sets.

Set-based operations, on the other hand, process all rows that meet a certain condition at once, using a single statement. This can greatly improve performance, as SQL Server can take advantage of optimizations such as parallel processing and optimized data access to complete the operation much faster.

Set-Based Update

A set-based update operation updates multiple rows in a single statement, based on a condition or set of conditions. The basic syntax of a set-based update statement is as follows:

UPDATE table_name

SET column1 = value1, column2 = value2, ...

WHERE condition;

In this syntax, table_name is the name of the table to be updated, column1, column2, etc. are the columns to be updated, and value1, value2, etc. are the new values for the columns. The WHERE clause specifies the condition that must be met for the update to occur.

For example, consider a table employees with columns employee_id, first_name, last_name, and salary. To give all employees with a salary less than $50,000 a raise of 10%, we could use the following set-based update statement:

UPDATE employees

SET salary = salary * 1.1

WHERE salary < 50000;

Set-Based Insert

A set-based insert operation inserts multiple rows in a single statement, typically by selecting data from another table or using a set of hard-coded values. The basic syntax of a set-based insert statement is as follows:

INSERT INTO table_name (column1, column2, ...)

SELECT value1, value2, ...

FROMsource_table

WHERE condition;

In this syntax, table_name is the name of the table to insert data into, column1, column2, etc. are the columns to be inserted, and value1, value2, etc. are the values to be inserted. The SELECT statement specifies the data source for the insert, and the WHERE clause specifies any conditions that must be met for the insert to occur.

For example, consider a table employees with columns employee_id, first_name, last_name, and salary. To insert a new employee record for all employees with a salary less than $50,000, we could use the following set-based insert statement:

INSERT INTO employees (first_name, last_name, salary)

SELECTfirst_name, last_name, 50000

FROMemployees

WHERE salary < 50000;

This statement selects all employees with a salary less than $50,000 from the employees table and inserts a new record for each employee into the same table, with a salary of $50,000.

ORDER BY Clause - SQL

2023-03-13T10:46:00.003-07:00

Order BY Clause

The ORDER BY clause is a critical part of the SQL SELECT statement that allows you to sort the result set of a query. In this blog post, we will explore the ORDER BY clause in more detail, including its syntax, common use cases, and best practices for writing efficient queries.

Syntax

The basic syntax of the SELECT statement with ORDER BY clause is as follows:

SELECT [column1, column2, ...]

FROM [table1]

[JOIN table2 ON condition]

WHERE [condition]

GROUP BY [column1, column2, ...]

HAVING [condition]

ORDER BY [column1, column2, ...] [ASC|DESC]

The ORDER BY clause comes at the end of the SELECT statement, after the WHERE, GROUP BY, and HAVING clauses (if they are present). It allows you to specify one or more columns by which the result set should be sorted, along with the direction of the sort (either ascending or descending).

Common Use Cases

The ORDER BY clause can be used in many different ways to sort data, including:

Single column sort: To sort the result set by a single column, simply specify the column name in the ORDER BY clause. For example, ORDER BY column1 ASC will sort the result set by column1 in ascending order.

Multiple column sort: To sort the result set by multiple columns, separate the column names with commas in the ORDER BY clause. For example, ORDER BY column1 ASC, column2 DESC will sort the result set first by column1 in ascending order, and then by column2 in descending order.

Sorting by expression: You can also sort the result set based on an expression, such as ORDER BY column1 + column2 DESC to sort the result set by the sum of column1 and column2 in descending order.

Sorting by position: You can also sort the result set by the position of the column in the SELECT list, such as ORDER BY 2 ASC to sort the result set by the second column in ascending order.

Best Practices

When using the ORDER BY clause in SQL Server, there are several best practices you should follow to ensure that your queries are efficient and maintainable:

Ø Use the ORDER BY clause to sort the result set in the database, rather than in the application layer. Sorting data in the database can be much faster than sorting it in the application.

Ø Use the most selective column first in the ORDER BY clause. The more selective the column, the fewer rows need to be sorted, which can improve performance.

Ø Use indexes to optimize queries with large datasets.

Ø Use the TOP clause to limit the number of rows returned if you only need a subset of the result set.

Ø Use the OFFSET and FETCH clauses to paginate the result set if you need to display data in pages.

Conclusion

The ORDER BY clause is a powerful tool that allows you to sort the result set of a SQL SELECT statement in many different ways. By following best practices and writing efficient queries, you can use the ORDER BY clause to retrieve and manipulate data in powerful and useful ways.

Reference

https://learn.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-ver16

WHERE Clause - SQL

2023-03-13T10:25:00.003-07:00

WHERE Clause

The WHERE clause is a crucial part of the SELECT statement in SQL Server. It is used to filter the data retrieved from one or more tables based on a specified condition. In this blog post, we will explore the WHERE clause in more detail, including its syntax, common use cases, and some best practices for writing efficient queries.

Syntax

The basic syntax of the SELECT statement with WHERE clause is as follows:

SELECT [column1, column2, ...]

FROM [table1]

[JOIN table2 ON condition]

WHERE [condition]

GROUP BY [column1, column2, ...]

HAVING [condition]

ORDER BY [column1, column2, ...] [ASC|DESC]

As you can see, the WHERE clause comes after the FROM and JOIN clauses, but before the GROUP BY, HAVING, and ORDER BY clauses. The condition in the WHERE clause can be a simple comparison, such as column1 = 'value', or a more complex expression involving logical operators such as AND, OR, and NOT.

Common Use Cases

The WHERE clause can be used in a variety of ways to filter data, including:

Basic comparisons: To filter data based on a simple comparison, such as column1 = 'value', column2 > 10, or column3 <> 'other value'.

Multiple conditions: To filter data based on multiple conditions, use logical operators such as AND, OR, and NOT. For example, column1 = 'value' AND column2 > 10 will only retrieve rows where both conditions are true.

Wildcard characters: To filter data based on partial matches, use wildcard characters such as % and _ in your condition. For example, column1 LIKE 'value%' will retrieve rows where column1 starts with 'value'.

Subqueries: To filter data based on a subquery, use the IN or EXISTS operators. For example, column1 IN (SELECT column2 FROM table2) will retrieve rows where column1 matches any value in the specified column from table2.

Null values: To filter data based on null values, use the IS NULL or IS NOT NULL operators. For example, column1 IS NULL will retrieve rows where column1 is null.

Best Practices

Ø When using the WHERE clause with SELECT statements, there are several best practices you should follow to ensure that your queries are efficient and maintainable:

Ø Use the most specific condition possible to filter the data. The more specific the condition, the fewer rows need to be retrieved, which can improve performance.

Ø Use indexes to optimize queries with large datasets.

Ø Use the EXISTS operator instead of the IN operator for subqueries with large datasets, as it can be faster.

Ø Avoid using functions in the WHERE clause, as they can slow down performance. If possible, pre-calculate the value of the function and use that in your condition instead.

Ø Use parentheses to group conditions for clarity and to ensure the correct order of operations.

Conclusion

The WHERE clause is a powerful tool that allows you to filter the data retrieved by SELECT statements based on a specified condition. By following best practices and writing efficient queries, you can use the WHERE clause to retrieve and manipulate data in powerful and useful ways.

Reference

https://learn.microsoft.com/en-us/sql/t-sql/queries/where-transact-sql?view=sql-server-ver16

SELECT Clause - SQL

2023-03-13T10:23:00.005-07:00

SELECT

SELECT is a fundamental SQL statement that is used to retrieve data from one or more tables in a SQL Server database. In this blog post, we will explore the SELECT statement in more detail, including its syntax, common use cases, and some best practices for writing efficient queries.

Syntax

The basic syntax of the SELECT statement is as follows:

SELECT [column1, column2, ...]

FROM [table1]

[JOIN table2 ON condition]

WHERE [condition]

GROUP BY [column1, column2, ...]

HAVING [condition]

ORDER BY [column1, column2, ...] [ASC|DESC]

Let's break down each of these components:

SELECT: This keyword is used to specify which columns we want to retrieve from the database.

FROM: This keyword specifies the table or tables from which we want to retrieve data.

JOIN: This keyword is used to join two or more tables together based on a specified condition.

WHERE: This keyword is used to filter the data based on a specified condition.

GROUP BY: This keyword is used to group the data based on one or more columns.

HAVING: This keyword is used to filter the data based on a specified condition after grouping.

ORDER BY: This keyword is used to sort the data based on one or more columns, either in ascending (ASC) or descending (DESC) order.

Common Use Cases

SELECT statements can be used for a variety of purposes, including:

Retrieving all columns from a table: To retrieve all columns from a table, simply use the * wildcard character after the SELECT keyword, like this: SELECT * FROM table1.

Retrieving specific columns from a table: To retrieve specific columns from a table, list the column names after the SELECT keyword, separated by commas, like this: SELECT column1, column2 FROM table1.

Filtering data: To retrieve only the rows that meet a certain condition, use the WHERE keyword, like this: SELECT * FROM table1 WHERE column1 = 'value'.

Joining tables: To combine data from two or more tables, use the JOIN keyword, like this: SELECT * FROM table1 JOIN table2 ON table1.column1 = table2.column2.

Grouping and aggregating data:To group data by one or more columns and perform aggregate functions such as SUM or COUNT, use the GROUP BY and HAVING keywords, like this:

SELECT column1, SUM(column2) FROM table1 GROUP BY column1 HAVING SUM(column2) > 100.

Sorting data: To sort the data by one or more columns, use the ORDER BY keyword, like this: SELECT * FROM table1 ORDER BY column1 DESC.

Best Practices

When writing SELECT statements, there are several best practices you should follow to ensure that your queries are efficient and maintainable:

Ø Avoid using the * wildcard character, as it can lead to unnecessary data retrieval and slower performance. Instead, list the specific columns you need.

Ø Use table aliases to make your queries easier to read and write. For example, instead of SELECT column1 FROM table1, you could write SELECT t1.column1 FROM table1 AS t1.

Ø Use indexes to optimize queries with large datasets.

Ø Avoid using subqueries unless necessary, as they can be slower and harder to read and maintain.

Ø Use the EXPLAIN PLAN feature to analyze query performance and optimize your queries as needed.

Conclusion

The SELECT statement is a fundamental component of SQL that is used to retrieve data from one or more tables in a SQL Server database. By following best practices and writing efficient queries, you can use SELECT statements to retrieve and manipulate data in powerful and useful ways.

Reference

https://learn.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql?view=sql-server-ver16

Views - SQL Server

2023-03-13T10:11:00.005-07:00

Views

A view in SQL Server is a virtual table that is based on the result of a select statement. Views can simplify complex queries, hide the complexity of the underlying data model, and provide a security mechanism by limiting access to sensitive data. In addition, views can be used to provide a consistent view of data across multiple tables or to present a subset of data that is tailored to a specific application or user.

Creating a view in SQL Server is straightforward. Here's an example:

CREATE VIEW CustomersWithOrders AS

SELECT c.CustomerID, c.CompanyName, COUNT(o.OrderID) AS OrderCount

FROM Customers c

LEFT JOIN Orders o ON c.CustomerID = o.CustomerID

GROUP BY c.CustomerID, c.CompanyName;

In this example, we're creating a view called CustomersWithOrders that contains three columns: CustomerID, CompanyName, and OrderCount. The view is based on a select statement that joins the Customers and Orders tables and groups the results by customer ID and company name.

To modify a view in SQL Server, you can use the ALTER VIEW statement. Here's an example:

ALTER VIEW CustomersWithOrders

SELECT c.CustomerID, c.CompanyName, COUNT(o.OrderID) AS OrderCount, AVG(o.TotalAmount) AS AvgAmount

FROM Customers c

LEFT JOIN Orders o ON c.CustomerID = o.CustomerID

GROUP BY c.CustomerID, c.CompanyName;

In this example, we're modifying the CustomersWithOrders view to include a new column that calculates the average order amount for each customer. The ALTER VIEW statement is used to update the definition of the view to include this new column.

Note that when you modify a view, you need to ensure that the changes do not violate any existing constraints or dependencies. For example, if the view is used in other queries or in stored procedures, you may need to modify those objects as well to ensure that they continue to work correctly with the updated view.

Indexed View

In SQL Server, an indexed view is a view that has been materialized and stored in the database as a physical index. Indexed views can improve query performance by pre-aggregating data and reducing the number of joins required to satisfy a query.

To create an indexed view in SQL Server, follow these steps:

Create a view that contains the desired query logic. For example:

CREATE VIEW SalesByProduct AS

SELECT ProductID, SUM(OrderQty) AS TotalSales

FROM SalesOrderDetail

GROUP BY ProductID;

Add the WITH SCHEMABINDING option to the view definition. This option ensures that the schema of the underlying tables cannot be modified while the indexed view exists.

ALTER VIEW SalesByProduct WITH SCHEMABINDING AS

SELECT ProductID, SUM(OrderQty) AS TotalSales

FROM SalesOrderDetail

GROUP BY ProductID;

Create a clustered index on the view. This index will be used to store the materialized data and support queries against the view.

CREATE UNIQUE CLUSTERED INDEXIX_SalesByProduct ON SalesByProduct (ProductID);

In this example, we're creating a clustered index on the SalesByProduct view, using the ProductID column as the index key. The index is marked as unique, since each product should have only one total sales value.Note that creating an indexed view can have some performance implications, as it requires additional storage and maintenance overhead. However, for certain types of queries and data models, indexed views can be an effective way to improve query performance.

Delete the view

To delete a view in SQL Server, you can use the DROP VIEW statement followed by the name of the view you want to delete. Here's an example:

DROPVIEW CustomersWithOrders;

In this example, we're deleting the CustomersWithOrders view. Once the view is deleted, any dependent objects such as stored procedures or other views that use the view will also be affected and may need to be updated or deleted as well.

It's important to note that once a view is deleted, it cannot be recovered. So be sure to double-check that you have selected the correct view before deleting it.

Reference

https://learn.microsoft.com/en-us/sql/relational-databases/views/views?view=sql-server-ver16

Synonyms, Trigger & Sequences - SQL Server

2023-03-13T10:11:00.004-07:00

Synonyms

In SQL Server, a synonym is an alternative name for a database object, such as a table, view, stored procedure, or function. Synonyms can be useful for simplifying complex object names, abstracting the underlying object structure, or providing a layer of indirection between objects and their callers.

Creating a synonym in SQL Server is straightforward. Here's an example:

CREATE SYNONYM MyTable FORAdventureWorks2019.dbo.MyTable;

In this example, we're creating a synonym called MyTable that points to a table called MyTable in the AdventureWorks2019 database. Now, instead of referring to the table as AdventureWorks2019.dbo.MyTable, we can simply use the synonym name MyTable.

To modify a synonym in SQL Server, you can use the ALTER SYNONYM statement. Here's an example:

ALTER SYNONYM MyTable

RENAME TO MyNewTable;

In this example, we're modifying the MyTable synonym to have a new name MyNewTable. This will update all references to the synonym to use the new name instead.

Note that when you create or modify a synonym, the underlying object must exist and be accessible to the user creating the synonym. Additionally, any permissions or security settings on the underlying object will apply to the synonym as well.

Triggers

In SQL Server, a trigger is a special type of stored procedure that is automatically executed in response to certain events, such as the insertion, deletion, or modification of data in a table. Triggers can be used to enforce business rules, maintain referential integrity, or perform other custom logic based on changes to the data.

Creating a trigger in SQL Server is straightforward. Here's an example:

CREATE TRIGGER MyTrigger

ON MyTable

AFTER INSERT, UPDATE, DELETE

BEGIN

-- Trigger logic here

END;

In this example, we're creating a trigger called MyTrigger that will be executed after insert, update, or delete operations on the MyTable table. The trigger logic goes inside the BEGIN...END block.

To modify a trigger in SQL Server, you can use the ALTER TRIGGER statement. Here's an example:

ALTER TRIGGERMyTrigger

ON MyTable

AFTER INSERT, UPDATE, DELETE

BEGIN

-- Updated trigger logic here

END;

In this example, we're modifying the MyTrigger trigger to update the trigger logic. The ALTER TRIGGER statement is similar to the CREATE TRIGGER statement, but with the updated trigger logic inside the BEGIN...END block.

Note that triggers can have a significant impact on database performance and should be used judiciously. Additionally, triggers can sometimes be disabled or deleted by other users or processes, so be sure to test your trigger logic carefully to ensure that it behaves correctly in all scenarios.

Sequences

In SQL Server, a sequence is a database object that generates a series of numeric values according to a defined set of rules. Sequences can be useful for generating unique identifiers, such as primary keys for tables, without requiring access to the underlying data.

Sequences can be defined with a starting value, an increment value, and a maximum or minimum value. When you request the next value from a sequence, SQL Server generates the next number in the sequence based on these parameters. Sequences can be accessed using the NEXT VALUE FOR function.

Here's an example of creating a sequence in SQL Server:

CREATE SEQUENCE MySequence

START WITH 1

INCREMENT BY 1

MINVALUE 1

MAXVALUE 1000

CYCLE;

In this example, we're creating a sequence called MySequence that starts with 1 and increments by 1 for each value, up to a maximum value of 1000. When the maximum value is reached, the sequence will cycle back to the starting value.

You can use the NEXT VALUE FOR function to retrieve the next value from the sequence:

This will return the next value in the sequence, starting with 1 in this example.

SELECT NEXT VALUE FOR MySequence;

Sequences can be useful for generating unique values for primary keys, especially in situations where multiple clients may be inserting data concurrently. However, it's important to consider the potential impact on performance, as generating unique values using a sequence may be slower than using a default or generated identity column.

Primary Key, Foreign Key Column and Check Constraint - SQL Server

2023-03-13T10:10:00.005-07:00

Ø What is Primary Key?

A primary key is a unique identifier for a record or row in a database table. It is a column or set of columns that uniquely identifies each record in a table and serves as a reference point for other tables that need to link to that table's records. Primary keys are used to enforce the integrity of the database by ensuring that each record can be uniquely identified, and they also help to optimize database performance by providing a fast way to retrieve data.

To create a primary key in SQL Server, you can use the CREATE TABLE statement with the PRIMARY KEY constraint. Here's an example:

CREATE TABLE Customers(

CustomerID intPRIMARY KEY,

FirstName varchar(50),

LastName varchar(50),

Email varchar(50)

);

In this example, we're creating a table called Customers with four columns. The CustomerID column is declared as an int and is designated as the primary key using the PRIMARY KEY constraint. The FirstName, LastName, and Email columns are declared as varchar data types.

Note that you can also create a primary key using the ALTER TABLE statement. For example:

ALTER TABLE Customers

ADD CONSTRAINTPK_Customers PRIMARY KEY (CustomerID);

This statement adds a primary key constraint to an existing Customers table, using the ADD CONSTRAINT clause and the PRIMARY KEY keyword. The CustomerID column is specified as the primary key column.

Ø What is Foreign Key?

A foreign key is a column or set of columns in a table that refers to the primary key of another table. It is used to establish a relationship between two tables in a relational database. The purpose of a foreign key is to ensure referential integrity, which means that the data in the related tables remains consistent and accurate.

When a table has a foreign key, it is creating a reference to another table's primary key. This creates a link between the two tables, allowing data to be shared and used in different ways. For example, if you have a table of orders and a table of customers, you could use a foreign key in the orders table to reference the customer ID in the customers table. This would allow you to see which customer placed each order, and to link customer information to the orders as needed.

To add a foreign key in SQL Server, you can use the ALTER TABLE statement with the ADD CONSTRAINT clause. Here's an example:

ALTER TABLE Orders

ADD CONSTRAINTFK_Orders_Customers

FOREIGN KEY (CustomerID)REFERENCES Customers(CustomerID);

In this example, we're adding a foreign key constraint to the Orders table, using the ADD CONSTRAINT clause and the FOREIGN KEY keyword. The CustomerID column in the Orders table is specified as the foreign key column, and the Customers table is specified as the referenced table, with its CustomerID column as the referenced column.

Note that you can also specify additional options for the foreign key constraint, such as ON DELETE and ON UPDATE actions. For example:

ALTER TABLE Orders

ADD CONSTRAINTFK_Orders_Customers

FOREIGN KEY (CustomerID)REFERENCES Customers(CustomerID)

ON DELETECASCADE

ON UPDATECASCADE;

In this example, we're adding ON DELETE CASCADE and ON UPDATE CASCADE options to the foreign key constraint. This means that if a customer is deleted or updated, all related orders will also be deleted or updated automatically.

Ø Check Constraints

A check constraint in SQL Server is used to ensure that the data entered into a column or a set of columns meets a certain condition. It can be used to restrict the range of values that can be entered into a column or to validate data against a specific pattern or formula. Check constraints can be applied to a single column or to multiple columns within a table.

Here's an example of creating a check constraint:

CREATE TABLEEmployees

(

EmployeeID intPRIMARY KEY,

FirstName varchar(50),

LastName varchar(50),

Age int,

CONSTRAINTCHK_Employees_Age CHECK (Age >=18 AND Age <= 65)

)

In this example, we're creating a table called Employees with four columns: EmployeeID, FirstName, LastName, and Age. We've also added a check constraint to the table to ensure that the Age column contains values between 18 and 65.

Once the check constraint is in place, any attempt to insert or update data in the Age column that falls outside the specified range will be rejected with an error message. For example:

INSERT INTOEmployees (EmployeeID,FirstName, LastName,Age) VALUES (1,'John', 'Doe',16);

This statement will fail with the following error message:

The INSERT statement conflicted with the CHECK constraint "CHK_Employees_Age". The conflict occurred in database "MyDatabase", table "dbo.Employees", column 'Age'.

Note that you can also use check constraints to validate data against other columns within the same table or to perform more complex validations using user-defined functions.

Stored Procedures - SQL Server

2023-03-13T10:10:00.004-07:00

Stored Procedures

Stored procedures are precompiled SQL statements that are stored in a database and can be called and executed by users or applications. They can be used to perform complex database operations, retrieve data, or modify data in a database. Stored procedures can improve performance and security by reducing network traffic and preventing SQL injection attacks.

To create a stored procedure in SQL Server, you can use the CREATE PROCEDURE statement. Here's an example:

CREATE PROCEDURE GetCustomers

BEGIN

SELECT * FROMCustomers

END

In this example, we're creating a stored procedure called GetCustomers that retrieves all the records from the Customers table. The AS keyword is used to separate the procedure definition from the procedure body. The BEGIN and END keywords define the procedure body, which contains the SQL code that will be executed when the stored procedure is called.

To execute the stored procedure, you can use the EXECUTE statement, like this:

EXECUTE GetCustomers

This statement will execute the GetCustomers stored procedure and return all the records from the Customers table.

Note that stored procedures can also accept parameters and return values. You can define input parameters using the @parameter_name data_type syntax, and you can define output parameters using the @parameter_name data_type OUTPUT syntax. Here's an example:

CREATE PROCEDURE GetCustomerByID

@CustomerID int,

@FirstName varchar(50) OUTPUT,

@LastName varchar(50) OUTPUT

BEGIN

SELECT @FirstName = FirstName, @LastName = LastName

FROM Customers

WHERE CustomerID = @CustomerID

END

In this example, we're creating a stored procedure called GetCustomerByID that accepts an input parameter @CustomerID and two output parameters @FirstName and @LastName. The procedure retrieves the first and last name of the customer with the specified ID and assigns them to the output parameters. To execute this stored procedure, you can use the EXECUTE statement with parameters, like this:

DECLARE @FirstName varchar(50), @LastName varchar(50)

EXECUTE GetCustomerByID 1, @FirstName OUTPUT, @LastName OUTPUT

SELECT @FirstName, @LastName

This statement will execute the GetCustomerByID stored procedure with a parameter value of 1 and will assign the first and last name of the customer to the @FirstName and @LastName variables, which are then selected and displayed in the result set.

Note that you can also modify existing stored procedures using the ALTER PROCEDURE statement, or delete them using the DROP PROCEDURE statement. You can also view a list of stored procedures in a database using the sp_help system stored procedure.

Reference :

https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/create-a-stored-procedure?view=sql-server-ver16

Functions - SQL Server

2023-03-13T10:10:00.003-07:00

Functions

In SQL Server, a function is a pre-written piece of code that performs a specific task and returns a value. Functions can be used to encapsulate complex calculations, to improve performance by reducing network traffic, and to enforce security by restricting direct access to database tables.

There are two types of functions in SQL Server:

1. Scalar Functions: These functions return a single value based on input parameters. Examples include LEN, which returns the length of a string, and SUM, which returns the sum of a set of values.

2. Table-Valued Functions: These functions return a table or a set of rows based on input parameters. Examples include fnSplit, which splits a string into rows based on a delimiter, and fnGetSalesByMonth, which returns sales data grouped by month.

To create a function in SQL Server, you can use the CREATE FUNCTION statement. Here's an example of creating a scalar function:

CREATE FUNCTION GetTotalOrders

(

@CustomerID int

)

RETURNS int

BEGIN

DECLARE @TotalOrders int;

SELECT @TotalOrders = COUNT(*) FROM Orders WHERECustomerID = @CustomerID;

RETURN @TotalOrders;

END

In this example, we're creating a scalar function called GetTotalOrders that takes an input parameter called @CustomerID and returns an integer value. The function uses a SELECT statement to count the number of orders for the specified customer ID and stores the result in a variable called @TotalOrders. Finally, the function returns the value of @TotalOrders.

Here's an example of creating a table-valued function:

CREATE FUNCTION fnGetSalesByMonth

(

@Year int

)

RETURNS TABLE

RETURN

(

SELECT MONTH(OrderDate) AS Month, SUM(TotalAmount) AS TotalSales

FROM Orders

WHERE YEAR(OrderDate) = @Year

GROUP BY MONTH(OrderDate)

)

In this example, we're creating a table-valued function called fnGetSalesByMonth that takes an input parameter called @Year and returns a table with two columns: Month and TotalSales. The function uses a SELECT statement to retrieve sales data for the specified year, grouping the data by month and calculating the total sales for each month.

Once you've created the function, you can call it like any other function. For example:

SELECT dbo.GetTotalOrders(12345);

In this example, we're calling the GetTotalOrders function with an input parameter of 12345. The function will return the total number of orders for the

specified customer ID.

Reference :

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql?view=sql-server-ver16

Identity Column - SQL Server

2023-03-12T22:37:00.001-07:00

Identity column in SQL server

IDENTITY property used to create an identity column in a table. It will be used with the CREATE TABLE and ALTER TABLE Transact-SQL statements. This column should be integer based column which will be handled by SQL server.

Syntax

IDENTITY [ (seed , increment) ]

seed
Is the value that is used for the very first row loaded into the table.

increment
Is the incremental value that is added to the identity value of the previous row that was loaded.

You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).

Example

CREATE TABLE Student

(

RollNumber int IDENTITY(1,1),

name varchar (50),

..);

You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).

Remarks

Identity columns can be used for generating key values. The identity property on a column guarantees the following:

· Each new value is generated based on the current seed & increment.

· Each new value for a particular transaction is different from other concurrent transactions on the table.

The identity property on a column does not guarantee the following:

· Uniqueness of the value - Uniqueness must be enforced by using a PRIMARY KEY or UNIQUE constraint or UNIQUE index.

· Consecutive values within a transaction - A transaction inserting multiple rows is not guaranteed to get consecutive values for the rows because other concurrent inserts might occur on the table. If values must be consecutive then the transaction should use an exclusive lock on the table or use the SERIALIZABLE isolation level.

· Consecutive values after server restart or other failures - SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use its own mechanism to generate key values. Using a sequence generator with the NOCACHE option can limit the gaps to transactions that are never committed.

· Reuse of values - For a given identity property with specific seed/increment, the identity values are not reused by the engine. If a particular insert statement fails or if the insert statement is rolled back then the consumed identity values are lost and will not be generated again. This can result in gaps when the subsequent identity values are generated.

Reference :

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-ver16

SQL Server Table Contd- Columns

2023-03-12T01:00:00.002-08:00

Add the new column in SQL server table

Once we have created the tables, all applications will used to store the details in the tables so we cant use the create table along with data. We can add the column in two ways in SSMS SQL server.

1. Using the SSMS Object Explorer Design Option

2. Using the ALTER keyword we can add the new column in SQL serve Table

Syntax

ALTER TABLE TableName

ADD Column1 Datatype1,Colun2 Datatype2

Example

ALTER TABLE dbo.Student

ADD EMailID VARCHAR(50) NULL,FatherName VARCHAR(80)

Please refer the below link:

https://learn.microsoft.com/en-us/sql/relational-databases/tables/add-columns-to-a-table-database-engine?view=sql-server-ver16

Delete column in a SQL server table

Once we have created the tables, we might need to delete the column which is not required any more. We can use the following ways to delete the table.

1. Using the SSMS Object Explorer Design Option

2. Using the ALTER and DROP keyword we can add the new column in SQL serve Table

Syntax

ALTER TABLE TableName

DROP COLUMN Column1

Example

ALTER TABLE dbo.Student

DROP COLUMN EMailID

Please refer the below link:

https://learn.microsoft.com/en-us/sql/relational-databases/tables/delete-columns-from-a-table?view=sql-server-ver16

Rename the column in a SQL server table

Once we have created the tables, we might need to change the column name which will help to give meaningful. We can use the following ways to rename the table column name.

1. Using the SSMS Object Explorer Design and Table Design Option

2. Using the pre-defined Stored procedure available in SQL server

Syntax

EXEC sp_rename

'Fully Qualified Column Name', 'new column name', 'COLUMN';

Example

EXEC sp_rename 'dbo.Student.FatherName', 'FatherFullName', 'COLUMN';

While running the query system will throw the caution message. Please make sure we haven’t used this name any dependent objects of SQL server.

Please refer the below link:

https://learn.microsoft.com/en-us/sql/relational-databases/tables/rename-columns-database-engine?view=sql-server-ver16

Modify the column in a SQL server table

Modifying column will help us to change the column data type or increase the length of the data type which is required if the application usage increased or if we wrongly specified the column details. We can use the following ways to rename the table column name.

1. Using the SSMS Object Explorer Table Design Option

2. Using the ALTER keyword we can modify the table column.

Syntax

ALTER TABLETableName

ALTER COLUMN ColunName VARCHAR(150) NOT NULL

Example

ALTER TABLE dbo.Student

ALTER COLUMN FatherFullName VARCHAR(150) NOT NULL

Here I am changing the column length and adding NOT NULL condition using alter.

Please refer the below link:

https://learn.microsoft.com/en-us/sql/relational-databases/tables/modify-columns-database-engine?view=sql-server-ver16

Setting Default Value to the column

Instead of insert the NULL value in column value we can insert the default value for the column. We can use default parameter while you want to insert the system date in the table

Using the Default keyword we can add the default value in SQL server.

Example while create

CREATE TABLE dbo.doc_exz (

column_a INT,

column_b INT DEFAULT 50);

While Alter Table

ALTER TABLE dbo.doc_exz

ADD CONSTRAINT DF_Doc_Exz_Column_B

DEFAULT 50 FOR column_b;

Generally Default value is a one of the SQL server constraint. So we can give the specific name for the same. Please refer the below

CREATE TABLE dbo.doc_exz (

column_a INT,

column_b INTCONSTRAINT DF_Doc_Exz_Column_B DEFAULT 50);

Please refer the below link:

https://learn.microsoft.com/en-us/sql/relational-databases/tables/specify-default-values-for-columns?view=sql-server-ver16

SQL Server - Tables

2023-03-11T00:57:00.004-08:00

What is Table?

Tables are database objects that contain all the data in a database. In tables, data is logically organized in a row-and-column format similar to a spreadsheet. Each row represents a unique record, and each column represents a field in the record.

There are different types of table available in SQL Server

1. Partitioned Tables

2. Temporal tables

3. Wide Tables

4. System Tables

Please refer the below link to more about the Tables.

https://learn.microsoft.com/en-us/sql/relational-databases/tables/tables?view=sql-server-ver16

Create a table in SQL Server

There are multiple ways to create a table in SQL server.

1. Using Table designer SQL server Management Studio(SSMS)

2. Using T SQL

In this blog we are going to see about, creating table using the Transaction SQL.

Please find the generic basic syntax on create table using the SQL.

CREATE TABLE Table_Name(

Column_Name1 DataType1,

Colun_Name2 DataType2,

Column_Name3 DataType3)

CREATE and TABLE are the keywords used to create the table with proceeding with table name

After the table name we need to mention the column names to be stored in the table name.

We will create the sample table for storing the student details.

CREATE TABLE Student(

StudentRollNo INT,

StudentName VARCHAR(70),

Gender CHAR(1),

StudentDOB DATE

)

Delete the table in SQL Server

We can use the same way to delete the table in SQL server.

We can use the SSMS Object Explorer -> Right Click the Table and click the drop.

We can Use T SQL DROP command to delete the table

Syntax

DROP TABLE TableName

We can use the below code to delete the table using the above command.

DROP TABLE Student

Notes : There are set of Limitations and restrictions apply while delete the table in SQL server. Please refer the below link on the same.

https://learn.microsoft.com/en-us/sql/relational-databases/tables/delete-tables-database-engine?view=sql-server-ver16

Rename the Table in SQL Server

We can use the same two way to rename the table in SQL server.

We can use the SSMS Object Explorer -> Right Click the Table and click the rename.

We can use pre defined Stored Procedure to rename the table name

Syntax

EXEC sp_rename ‘Table Name to be renamed along with Schema Name’, 'New table name';

We can use the below code to rename the table using the below.

EXEC sp_rename 'dbo.Student', 'StudentDetils'

Notes : Please refer the below link for more info

https://learn.microsoft.com/en-us/sql/relational-databases/tables/rename-tables-database-engine?view=sql-server-ver16

View the existing Table Definition

We will often encounter the problem to view the table definition details while look into the existing SQL server DB. For that we can use the below.

1. We can use the existing procedure sp_help to know about the table definition

Ex : EXEC sp_help 'dbo.student;

2. Using the system tables we can view the table definition

SELECT s.name as schema_name, t.name as table_name, c.* FROMsys.columns AS c INNER JOIN sys.tables AS t ON t.object_id = c.object_id INNERJOIN sys.schemas AS s ON s.schema_id = t.schema_id WHEREt.name = 'mytable' AND s.name = 'dbo';

3. Using the SSMS object explorer design option

Refer the below link to know more about this

https://learn.microsoft.com/en-us/sql/relational-databases/tables/view-the-table-definition?view=sql-server-ver16

View the dependencies of the Table in SQL server

We will often encounter the problem to view the table dependency details while look into the existing SQL server DB for modification. For that we can use the below.

1. We can use the existing view to search about dependency of all SQL Server Objects

USE AdventureWorks2012;

SELECT * FROM sys.sql_expression_dependencies

WHERE referencing_id = OBJECT_ID(N'Production.vProductAndDescription');

2. Using the SSMS object explorer design option right click the table and click on view dependencies to vie the details.

Refer the below link to know more about this

https://learn.microsoft.com/en-us/sql/relational-databases/tables/view-the-dependencies-of-a-table?view=sql-server-ver16

SFTP Integration in SSIS package Using WinSCP DLL

2023-03-10T09:36:00.001-08:00

In this blog, I am planning to write about SSIS SFTP Task details and reference sites, upload and download the files from SFTP server using SSIS package with help of the winscp library.

Steps to configure the winscp DLL and Download Files

1. Please download the DLL and required files from below path

https://winscp.net/eng/downloads.php#additional

2. Once Downloaded, use the below comments to add in Local GAC

"Path to the gacutil exe \gacutil.exe" /i WinSCPnet.dll

3. Please use the below URL as guide to implement the SSIS script task to download the files from SFTP using winscp as library

https://winscp.net/eng/docs/library_ssis

4. I am just briefing the steps based on the above URL what we can try in SSIS package.

5. Please create the below variables in SSIS package which we need to pass into the Script Task as a variable

· Host Name

· SFTP Username

· SFTP Password

· SFTP Source Path – From where we need to download the files

· Destination Path – To which location we need to transfer the file

· Winscp Executable Path – Download winscp execution path

6. Please add script Task and click edit.

7. Once Added the variables in Read Write Variables. Please click Edit to edit Script the C# code.

8. Please add the reference for the DLL by right clicking the reference section solution explorer.

Please browse the DLL path download in step one and add it in reference.

9. Once added we have ready to add the code for move the files.

public void Main()

{

// Setup session options

string HostName = (string)Dts.Variables["User::HostName"].Value;

string UserName = (string)Dts.Variables["User::UserName"].Value;

string Password = (string)Dts.Variables["User::Password"].Value;

string SSHPath = (string)Dts.Variables["User::SSHPath"].Value;

if (SSHPath == null) {

SSHPath = String.Empty;

}

//User::DestinationPath,User::HostName,User::Password,User::SourcePath,User::SSHPath,User::UserName

MessageBox.Show("Please FinD the Host Name"+HostName+"_"+ UserName+"_"+Password+"_"+SSHPath);

SessionOptions sessionOptions = new SessionOptions

{

Protocol = Protocol.Sftp,

// To setup these variables, go to SSIS > Variables.

// To make them accessible from the script task, in the context menu of the

// task, choose Edit. On the Script task editor on Script page,

// select ReadOnlyVariables, and tick the below properties.

HostName = HostName,

UserName = UserName,

Password = Password

};

sessionOptions.SshHostKeyPolicy = SshHostKeyPolicy.GiveUpSecurityAndAcceptAny;

try

{

using (Session session = new Session())

{

// If WinSCP .NET assembly has been stored in GAC to be used with SSIS,

// you need to set path to WinSCP.exe explicitly.

// This is not needed if you have subscribed AppDomain.AssemblyResolve event

// and the WinSCP.exe is in the same location as WinSCPnet.dll.

string ExecutablePath= (string)Dts.Variables["User::WinscpExecutablesPath"].Value;

session.ExecutablePath = (string)Dts.Variables["User::WinscpExecutablesPath"].Value;

string destinationFilePath= (string)Dts.Variables["User::DestinationPath"].Value;

string sourceFilePath= (string)Dts.Variables["User::SourcePath"].Value;

MessageBox.Show("Please FinD the Host Name 3" + ExecutablePath+"_"+destinationFilePath+sourceFilePath);

// Connect

session.Open(sessionOptions);

// Upload files

TransferOptions transferOptions = newTransferOptions();

transferOptions.TransferMode = TransferMode.Binary;

/* TransferOperationResult transferResult = session.PutFiles(

destinationFilePath, sourceFilePath, false, transferOptions);

TransferOperationResult transferResult = session.GetFiles(sourceFilePath, destinationFilePath, false, transferOptions);

// Throw on any error

transferResult.Check();

// Print results

bool fireAgain = false;

foreach (TransferEventArgs transfer in transferResult.Transfers)

{

Dts.Events.FireInformation(0, null,

string.Format("Upload of {0} succeeded", transfer.FileName),

null, 0, ref fireAgain);

}

Dts.TaskResult = (int)DTSExecResult.Success;

}

catch (Exception e)

{

Dts.Events.FireError(0, null,

string.Format("Error when using WinSCP to upload files: {0}", e),

null, 0);

Dts.TaskResult = (int)DTSExecResult.Failure;

}

10. Please use the above code in Visual studio editor and save the details. Please run and check.

SQL Server - Data Types

2023-03-09T10:21:00.000-08:00

Data Types

In this post I have collected the information from micro soft site and keep all the same in a single page. Please find the details

Data types are the heart of the SQL language, as developer or designer we need to see the every data as data type, how we can store the data in the database.

In SQL Server, each column, local variable, expression, and parameter has a related data type. A data type is an attribute that specifies the type of data that the object can hold: integer data, character data, monetary data, date and time data, binary strings, and so on.

In SQL Server, based on the characteristic data types grouped as below categories

Exact Numerics	Unicode character strings

Approximate Numerics	Binary strings

Date and time	Other data types

Character strings

Exact Numerics

a. bigint

b. numeric

c. bit

d. smallint

e. decimal

f. smallmoney

g. int

h. tinyint

i. money

Approximate numerics

a. float

b. real

Date and Time

a. date

b. datetimeoffset

c. datetime2

d. smalldatetime

e. datetime

f. time

Character Strings

a. char

b. varchar

c. text

Unicode Character Strings

a. nchar

b. nvarchar

c. ntext

Binary Strings

a. binary

b. varbinary

c. image

Other Data Types

a. cursor

b. rowversion

c. hierarchyid

d. uniqueidentifier

e. sql_variant

f. xml

g. Spatial Geometry Types

h. Spatial Geography Types

i. table

int, bigint, smallint, and tinyint

Exact-number data types that use integer data. To save space in the database, use the smallest data type that can reliably contain all possible values. For example, tinyint would be sufficient for a person's age because no one lives to be more than 255 years old. But tinyint would not be sufficient for a building's age because a building can be more than 255 years old.

Data type	Range	Range expression	Storage
bigint	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807	-2^63 to 2^63-1	8 Bytes
int	-2,147,483,648 to 2,147,483,647	-2^31 to 2^31-1	4 Bytes
smallint	-32,768 to 32,767	-2^15 to 2^15-1	2 Bytes
tinyint	0 to 255	2^0-1 to 2^8-1	1 Byte

Remarks

The int data type is the primary integer data type in SQL Server. The bigint data type is intended for use when integer values might exceed the range that is supported by the int data type.

bigint fits between smallmoney and int in the data type precedence chart.

Functions return bigint only if the parameter expression is a bigint data type. SQL Server does not automatically promote other integer data types (tinyint, smallint, and int) to bigint.

decimal and numeric

Numeric data types that have fixed precision and scale. Decimal and numeric are synonyms and can be used interchangeably.

Arguments

decimal[ (p[ ,s] )] and numeric[ (p[ ,s] )]
Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The ISO synonyms for decimal are dec and dec(p, s). numeric is functionally identical to decimal.

p (precision)
The maximum total number of decimal digits to be stored. This number includes both the left and the right sides of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.

s (scale)
The number of decimal digits that are stored to the right of the decimal point. This number is subtracted from p to determine the maximum number of digits to the left of the decimal point. Scale must be a value from 0 through p, and can only be specified if precision is specified. The default scale is 0 and so 0 <= s <= p. Maximum storage sizes vary, based on the precision.

Precision	Storage bytes
1 - 9	5
10-19	9
20-28	13
29-38	17

Converting decimal and numeric data

For decimal and numeric data types, SQL Server considers each combination of precision and scale as a different data type. For example, decimal(5,5) and decimal(5,0) are considered different data types.

In Transact-SQL statements, a constant with a decimal point is automatically converted into a numeric data value, using the minimum precision and scale necessary. For example, the constant 12.345 is converted into a numeric value with a precision of 5 and a scale of 3.

Converting from decimal or numeric to float or real can cause some loss of precision. Converting from int, smallint, tinyint, float, real, money, or smallmoney to either decimal or numeric can cause overflow.

By default, SQL Server uses rounding when converting a number to a decimal or numeric value with a lower precision and scale. Conversely, if the SET ARITHABORT option is ON, SQL Server raises an error when overflow occurs. Loss of only precision and scale isn't sufficient to raise an error.

Prior to SQL Server 2016 (13.x), conversion of float values to decimal or numeric is restricted to values of precision 17 digits only. Any float value less than 5E-18 (when set using either the scientific notation of 5E-18 or the decimal notation of 0.000000000000000005) rounds down to 0. This is no longer a restriction as of SQL Server 2016 (13.x).

money and smallmoney

Data types that represent monetary or currency values.

Data type	Range	Storage
money	-922,337,203,685,477.5808 to 922,337,203,685,477.5807 (-922,337,203,685,477.58 to 922,337,203,685,477.58 for Informatica. Informatica only supports two decimals, not four.)	8 bytes
smallmoney	- 214,748.3648 to 214,748.3647	4 bytes

The money and smallmoney data types are accurate to a ten-thousandth of the monetary units that they represent. For Informatica, the money and smallmoney data types are accurate to a one-hundredth of the monetary units that they represent.Use a period to separate partial monetary units, like cents, from whole monetary units. For example, 2.15 specifies 2 dollars and 15 cents.These data types can use any one of the following currency symbols.

Currency or monetary data does not need to be enclosed in single quotation marks ( ' ). It is important to remember that while you can specify monetary values preceded by a currency symbol, SQL Server does not store any currency information associated with the symbol, it only stores the numeric value.

Converting money data

When you convert to money from integer data types, units are assumed to be in monetary units. For example, the integer value of 4 is converted to the money equivalent of 4 monetary units.

The following example converts smallmoney and money values to varchar and decimal data types, respectively.

float and real

Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly. The ISO synonym for real is float(24).

Syntax

float [ (n) ] Where n is the number of bits that are used to store the mantissa of the float number in scientific notation and, therefore, dictates the precision and storage size. If n is specified, it must be a value between 1 and 53. The default value of n is 53.

n value	Precision	Storage size
1-24	7 digits	4 bytes
25-53	15 digits	8 bytes

The SQL Server float[(n)] data type complies with the ISO standard for all values of n from 1 through 53. The synonym for double precision is float(53).

Remarks

Data type	Range	Storage
float	- 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308	Depends on the value of n
real	- 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38	4 Bytes

Converting float and real data

Values of float are truncated when they are converted to any integer type.

When you want to convert from float or real to character data, using the STR string function is usually more useful than CAST( ). This is because STR enables more control over formatting. For more information, see STR (Transact-SQL) and Functions (Transact-SQL).

date

Defines a date in SQL Server.

date description

Property	Value
Syntax	date
Usage	DECLARE @MyDate date CREATE TABLE Table1 ( Column1 date )
Default string literal format (used for down-level client)	YYYY-MM-DD For more information, see the "Backward Compatibility for Down-level Clients" section that follows.
Range	0001-01-01 through 9999-12-31 (1582-10-15 through 9999-12-31 for Informatica) January 1, 1 CE (Common Era) through December 31, 9999 CE (October 15, 1582 CE through December 31, 9999 CE for Informatica)
Element ranges	YYYY is four digits from 0001 to 9999 that represent a year. For Informatica, YYYY is limited to the range 1582 to 9999. MM is two digits from 01 to 12 that represent a month in the specified year. DD is two digits from 01 to 31, depending on the month, that represents a day of the specified month.
Character length	10 positions
Precision, scale	10, 0
Storage size	3 bytes, fixed
Storage structure	1, 3-byte integer stores date.
Accuracy	One day
Default value	1900-01-01 This value is used for the appended date part for implicit conversion from time to datetime2 or datetimeoffset.
Calendar	Gregorian
User-defined fractional second precision	No
Time zone offset aware and preservation	No
Daylight saving aware	No

Supported string literal formats for date

The following tables show the valid string literal formats for the date data type.

Numeric	Description
mdy [m]m/dd/[yy]yy [m]m-dd-[yy]yy [m]m.dd.[yy]yy myd mm/[yy]yy/dd mm-[yy]yy/dd [m]m.[yy]yy.dd dmy dd/[m]m/[yy]yy dd-[m]m-[yy]yy dd.[m]m.[yy]yy dym dd/[yy]yy/[m]m dd-[yy]yy-[m]m dd.[yy]yy.[m]m ymd [yy]yy/[m]m/dd [yy]yy-[m]m-dd [yy]yy-[m]m-dd	[m]m, dd, and [yy]yy represent month, day, and year in a string with slash marks (/), hyphens (-), or periods (.) as separators. Only four- or two-digit years are supported. Use four-digit years whenever possible. To specify an integer from 0001 to 9999 that represents the cutoff year for interpreting two-digit years as four-digit years, use the Configure the two digit year cutoff Server Configuration Option. Note: For Informatica, YYYY is limited to the range 1582 to 9999. A two-digit year that is less than or equal to the last two digits of the cutoff year is in the same century as the cutoff year. A two-digit year greater than the last two digits of the cutoff year is in the century that comes before the cutoff year. For example, if the two-digit year cutoff is the default 2049, the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950. The default date format is determined by the current language setting. You can change the date format by using the SET LANGUAGE and SET DATEFORMAT statements. The ydm format isn't supported for date.
Alphabetical	Description
mon [dd][,] yyyy mon dd[,] [yy] mon yyyy [dd] [dd] mon[,] yyyy dd mon[,][yy]yy dd [yy]yy mon [dd] yyyy mon yyyy mon [dd] yyyy [dd] mon	mon represents the full month name or the month abbreviation given in the current language. Commas are optional and capitalization is ignored. To avoid ambiguity, use four-digit years. If the day is missing, the first day of the month is supplied.

ISO 8601	Description
YYYY-MM-DD YYYYMMDD	Same as the SQL standard. This format is the only format defined as an international standard.
Unseparated	Description
[yy]yymmdd yyyy[mm][dd]	The date data can be specified with four, six, or eight digits. A six- or eight-digit string is always interpreted as ymd. The month and day must always be two digits. A four-digit string is interpreted as year.

ODBC	Description
{ d 'yyyy-mm-dd' }	ODBC API specific.
W3C XML format	Description
yyyy-mm-ddTZD	Supported for XML/SOAP usage. TZD is the time zone designator (Z or +hh:mm or -hh:mm): - hh:mm represents the time zone offset. hh is two digits, ranging from 0 to 14, that represent the number of hours in the time zone offset. - MM is two digits, ranging from 0 to 59, that represent the number of additional minutes in the time zone offset. - + (plus) or - (minus) the mandatory sign of the time zone offset. This sign indicates that, to obtain the local time, the time zone offset is added or subtracted from the Coordinated Universal Times (UTC) time. The valid range of time zone offset is from -14:00 to +14:00.

ANSI and ISO 8601 compliance

date complies with the ANSI SQL standard definition for the Gregorian calendar: "NOTE 85 - Datetime data types will allow dates in the Gregorian format to be stored in the date range 0001-01-01 CE through 9999-12-31 CE."

The default string literal format, which is used for down-level clients, complies with the SQL standard form that is defined as YYYY-MM-DD. This format is the same as the ISO 8601 definition for DATE.

datetime2

Defines a date that is combined with a time of day that is based on 24-hour clock. datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision.

Property	Value
Syntax	datetime2 [ (fractional seconds precision) ]
Usage	DECLARE @MyDatetime2 datetime2(7) CREATE TABLE Table1 ( Column1 datetime2(7) )
Default string literal format (used for down-level client)	YYYY-MM-DD hh:mm:ss[.fractional seconds] For more information, see the "Backward Compatibility for Down-level Clients" section that follows.
Date range	0001-01-01 through 9999-12-31 January 1,1 CE through December 31, 9999 CE
Time range	00:00:00 through 23:59:59.9999999
Time zone offset range	None
Element ranges	YYYY is a four-digit number, ranging from 0001 through 9999, that represents a year. MM is a two-digit number, ranging from 01 to 12, that represents a month in the specified year. DD is a two-digit number, ranging from 01 to 31 depending on the month, that represents a day of the specified month. hh is a two-digit number, ranging from 00 to 23, that represents the hour. mm is a two-digit number, ranging from 00 to 59, that represents the minute. ss is a two-digit number, ranging from 00 to 59, that represents the second. n* is a zero- to seven-digit number from 0 to 9999999 that represents the fractional seconds. In Informatica, the fractional seconds will be truncated when n > 3.
Character length	19 positions minimum (YYYY-MM-DD hh:mm:ss ) to 27 maximum (YYYY-MM-DD hh:mm:ss.0000000)
Precision, scale	0 to 7 digits, with an accuracy of 100ns. The default precision is 7 digits.
Storage size ¹	6 bytes for precision less than 3. 7 bytes for precision 3 or 4. All other precision require 8 bytes.²
Accuracy	100 nanoseconds
Default value	1900-01-01 00:00:00
Calendar	Gregorian
User-defined fractional second precision	Yes
Time zone offset aware and preservation	No
Daylight saving aware	No

¹ Provided values are for uncompressed rowstore. Use of data compression or columnstore may alter storage size for each precision. Additionally, storage size on disk and in memory may differ. For example, datetime2 values always require 8 bytes in memory when batch mode is used.

² When a datetime2 value is cast to a varbinary value, an additional byte is added to the varbinary value to store precision.

For data type metadata, see sys.systypes (Transact-SQL) or TYPEPROPERTY (Transact-SQL). Precision and scale are variable for some date and time data types. To obtain the precision and scale for a column, see COLUMNPROPERTY (Transact-SQL), COL_LENGTH (Transact-SQL), or sys.columns (Transact-SQL).

datetime

Defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock.

Note

Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.

Description

Property	Value
Syntax	datetime
Usage	DECLARE @MyDatetime datetime CREATE TABLE Table1 ( Column1 datetime )
Default string literal formats (used for down-level client)	Not applicable
Date range	January 1, 1753, through December 31, 9999
Time range	00:00:00 through 23:59:59.997
Time zone offset range	None
Element ranges	YYYY is four digits from 1753 through 9999 that represent a year. MM is two digits, ranging from 01 to 12, that represent a month in the specified year. DD is two digits, ranging from 01 to 31 depending on the month, that represent a day of the specified month. hh is two digits, ranging from 00 to 23, that represent the hour. mm is two digits, ranging from 00 to 59, that represent the minute. ss is two digits, ranging from 00 to 59, that represent the second. n* is zero to three digits, ranging from 0 to 999, that represent the fractional seconds.
Character length	19 positions minimum to 23 maximum
Storage size	8 bytes
Accuracy	Rounded to increments of .000, .003, or .007 seconds
Default value	1900-01-01 00:00:00
Calendar	Gregorian (Does include the complete range of years.)
User-defined fractional second precision	No
Time zone offset aware and preservation	No
Daylight saving aware	No

Supported string literal formats for datetime

The following tables list the supported string literal formats for datetime. Except for ODBC, datetime string literals are in single quotation marks ('), for example, 'string_literaL'. If the environment isn't us_english, the string literals should be in the format N'string_literaL'.

Numeric	Description
Date formats: [0]4/15/[19]96 -- (mdy) [0]4-15-[19]96 -- (mdy) [0]4.15.[19]96 -- (mdy) [0]4/[19]96/15 -- (myd) 15/[0]4/[19]96 -- (dmy) 15/[19]96/[0]4 -- (dym) [19]96/15/[0]4 -- (ydm) [19]96/[0]4/15 -- (ymd) Time formats: 14:30 14:30[:20:999] 14:30[:20.9] 4am 4 PM	You can specify date data with a numeric month specified. For example, 5/20/97 represents the twentieth day of May 1997. When you use numeric date format, specify the month, day, and year in a string that uses slash marks (/), hyphens (-), or periods (.) as separators. This string must appear in the following form: number separator number separator number [time] [time] When the language is set to us_english, the default order for the date is mdy. You can change the date order by using the SET DATEFORMAT statement. The setting for SET DATEFORMAT determines how date values are interpreted. If the order doesn't match the setting, the values aren't interpreted as dates. Out-of-order dates may be misinterpreted as out of range or with wrong values. For example, 12/10/08 can be interpreted as one of six dates, depending on the DATEFORMAT setting. A four-part year is interpreted as the year.
Alphabetical	Description
Apr[il] [15][,] 1996 Apr[il] 15[,] [19]96 Apr[il] 1996 [15] [15] Apr[il][,] 1996 15 Apr[il][,][19]96 15 [19]96 apr[il] [15] 1996 apr[il] 1996 APR[IL] [15] 1996 [15] APR[IL]	You can specify date data with a month specified as the full month name. For example, April or the month abbreviation of Apr specified in the current language; commas are optional and capitalization is ignored. Here are some guidelines for using alphabetical date formats: 1) Enclose the date and time data in single quotation marks ('). For languages other than English, use N' 2) Characters that are enclosed in brackets are optional. 3) If you specify only the last two digits of the year, values less than the last two digits of the value of the Configure the two digit year cutoff Server Configuration Option configuration option are in the same century as the cutoff year. Values greater than or equal to the value of this option are in the century that comes before the cutoff year. For example, if two digit year cutoff is 2050 (default), 25 is interpreted as 2025 and 50 is interpreted as 1950. To avoid ambiguity, use four-digit years. 4) If the day is missing, the first day of the month is supplied. The SET DATEFORMAT session setting isn't applied when you specify the month in alphabetical form.

ISO 8601	Description
YYYY-MM-DDThh:mm:ss[.mmm] YYYYMMDD[ hh:mm:ss[.mmm]]	Examples: 1) 2004-05-23T14:25:10 2) 2004-05-23T14:25:10.487 To use the ISO 8601 format, you must specify each element in the format, including the T, the colons (:), and the period (.) that are shown in the format. The brackets indicate that the fraction of second component is optional. The time component is specified in the 24-hour format. The T indicates the start of the time part of the datetime value. The advantage in using the ISO 8601 format is that it is an international standard with unambiguous specification. Also, this format isn't affected by the SET DATEFORMAT or SET LANGUAGE setting.
Unseparated	Description
YYYYMMDD hh:mm:ss[.mmm]

ODBC	Description
{ ts '1998-05-02 01:23:56.123' } { d '1990-10-02' } { t '13:33:41' }	The ODBC API defines escape sequences to represent date and time values, which ODBC calls timestamp data. This ODBC timestamp format is also supported by the OLE DB language definition (DBGUID-SQL) supported by the Microsoft OLE DB provider for SQL Server. Applications that use the ADO, OLE DB, and ODBC-based APIs can use this ODBC timestamp format to represent dates and times. ODBC timestamp escape sequences are of the format: { literal_type 'constant_value' }: - literal_type specifies the type of the escape sequence. Timestamps have three literal_type specifiers: 1) d = date only 2) t = time only 3) ts = timestamp (time + date) - 'constant_value' is the value of the escape sequence. constant_value must follow these formats for each literal_type. d : yyyy-mm-dd t : hh:mm:ss[.fff] ts : yyyy-mm-dd hh:mm:ss[.fff]

ODBC

Description

{ ts '1998-05-02 01:23:56.123' }

{ d '1990-10-02' }

{ t '13:33:41' }

The ODBC API defines escape sequences to represent date and time values, which ODBC calls timestamp data. This ODBC timestamp format is also supported by the OLE DB language definition (DBGUID-SQL) supported by the Microsoft OLE DB provider for SQL Server. Applications that use the ADO, OLE DB, and ODBC-based APIs can use this ODBC timestamp format to represent dates and times.

ODBC timestamp escape sequences are of the format: { literal_type 'constant_value' }:

- literal_type specifies the type of the escape sequence. Timestamps have three literal_type specifiers:
1) d = date only
2) t = time only
3) ts = timestamp (time + date)

- 'constant_value' is the value of the escape sequence. constant_value must follow these formats for each literal_type.
d : yyyy-mm-dd
t : hh:mm:ss[.fff]
ts : yyyy-mm-dd hh:mm:ss[.fff]

Rounding of datetime fractional second precision

datetime values are rounded to increments of .000, .003, or .007 seconds, as shown in the following table.

User-specified value	System stored value
01/01/98 23:59:59.999	1998-01-02 00:00:00.000
01/01/98 23:59:59.995 01/01/98 23:59:59.996 01/01/98 23:59:59.997 01/01/98 23:59:59.998	1998-01-01 23:59:59.997
01/01/98 23:59:59.992 01/01/98 23:59:59.993 01/01/98 23:59:59.994	1998-01-01 23:59:59.993
01/01/98 23:59:59.990 01/01/98 23:59:59.991	1998-01-01 23:59:59.990

ANSI and ISO 8601 compliance

datetime isn't ANSI or ISO 8601 compliant.

Converting date and time data

When you convert to date and time data types, SQL Server rejects all values it can't recognize as dates or times. For information about using the CAST and CONVERT functions with date and time data, see CAST and CONVERT (Transact-SQL).

Converting other date and time types to the datetime data type

This section describes what occurs when other date and time data types are converted to the datetime data type.

When the conversion is from date, the year, month, and day are copied. The time component is set to 00:00:00.000. The following code shows the results of converting a date value to a datetime value.

datetimeoffset

Defines a date that is combined with a time of a day based on a 24-hour clock like datetime2, and adds time zone awareness based on UTC (Universal Time Coordinate or Greenwich Mean Time).

datetimeoffset description

Property		Value
Syntax		datetimeoffset [ (fractional seconds precision) ]
Usage		DECLARE @MyDatetimeoffset datetimeoffset(7) CREATE TABLE Table1 ( Column1 datetimeoffset(7) )
Default string literal formats (used for down-level client)		YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+\|-}hh:mm] For more information, see the "Backward Compatibility for Down-level Clients" section that follows.
Date range		0001-01-01 through 9999-12-31 January 1, 1 CE through December 31, 9999 CE
Time range		00:00:00 through 23:59:59.9999999
Time zone offset range		-14:00 through +14:00
Element ranges		YYYY is four digits, ranging from 0001 through 9999, that represent a year. MM is two digits, ranging from 01 to 12, that represent a month in the specified year. DD is two digits, ranging from 01 to 31 depending on the month, that represent a day of the specified month. hh is two digits, ranging from 00 to 23, that represent the hour. mm is two digits, ranging from 00 to 59, that represent the minute. ss is two digits, ranging from 00 to 59, that represent the second. n* is zero to seven digits, ranging from 0 to 9999999, that represent the fractional seconds. hh is two digits that range from -14 to +14. mm is two digits that range from 00 to 59.
Character length		26 positions minimum (YYYY-MM-DD hh:mm:ss {+\|-}hh:mm) to 34 maximum (YYYY-MM-DD hh:mm:ss.nnnnnnn {+\|-}hh:mm)
Precision, scale		See the table below.
Storage size		10 bytes, fixed is the default with the default of 100ns fractional second precision.
Accuracy		100 nanoseconds
Default value		1900-01-01 00:00:00 00:00
Calendar		Gregorian
User-defined fractional second precision		Yes
Time zone offset aware and preservation		Yes
Daylight saving aware		No
Specified scale	Result (precision, scale)		Column length (bytes)	Fractional seconds precision
datetimeoffset	(34,7)		10	7
datetimeoffset(0)	(26,0)		8	0-2
datetimeoffset(1)	(28,1)		8	0-2
datetimeoffset(2)	(29,2)		8	0-2
datetimeoffset(3)	(30,3)		9	3-4
datetimeoffset(4)	(31,4)		9	3-4
datetimeoffset(5)	(32,5)		10	5-7
datetimeoffset(6)	(33,6)		10	5-7
datetimeoffset(7)	(34,7)		10	5-7

smalldatetime

Defines a date that is combined with a time of day. The time is based on a 24-hour day, with seconds always zero (:00) and without fractional seconds.

smalldatetime description

Property	Value
Syntax	smalldatetime
Usage	DECLARE @MySmalldatetime smalldatetime CREATE TABLE Table1 ( Column1 smalldatetime )
Default string literal formats (used for down-level client)	Not applicable
Date range	1900-01-01 through 2079-06-06 January 1, 1900, through June 6, 2079
Time range	00:00:00 through 23:59:59 2007-05-09 23:59:59 will round to 2007-05-10 00:00:00
Element ranges	YYYY is four digits, ranging from 1900, to 2079, that represent a year. MM is two digits, ranging from 01 to 12, that represent a month in the specified year. DD is two digits, ranging from 01 to 31 depending on the month, that represent a day of the specified month. hh is two digits, ranging from 00 to 23, that represent the hour. mm is two digits, ranging from 00 to 59, that represent the minute. ss is two digits, ranging from 00 to 59, that represent the second. Values that are 29.998 seconds or less are rounded down to the nearest minute. Values of 29.999 seconds or more are rounded up to the nearest minute.
Character length	19 positions maximum
Storage size	4 bytes, fixed.
Accuracy	One minute
Default value	1900-01-01 00:00:00
Calendar	Gregorian (Doesn't include the complete range of years.)
User-defined fractional second precision	No
Time zone offset aware and preservation	No
Daylight saving aware	No

time

Defines a time of a day. The time is without time zone awareness and is based on a 24-hour clock.

time Description

Property	Value
Syntax	time [ (fractional second scale) ]
Usage	DECLARE @MyTime time(7) CREATE TABLE Table1 ( Column1 time(7) )
fractional seconds scale	Specifies the number of digits for the fractional part of the seconds. This can be an integer from 0 to 7. For Informatica, this can be an integer from 0 to 3. The default fractional scale is 7 (100ns).
Default string literal format (used for down-level client)	hh:mm:ss[.nnnnnnn] for Informatica) For more information, see the Backward Compatibility for Down-level Clients section.
Range	00:00:00.0000000 through 23:59:59.9999999 (00:00:00.000 through 23:59:59.999 for Informatica)
Element ranges	hh is two digits, ranging from 0 to 23, that represent the hour. mm is two digits, ranging from 0 to 59, that represent the minute. ss is two digits, ranging from 0 to 59, that represent the second. n* is zero to seven digits, ranging from 0 to 9999999, that represent the fractional seconds. For Informatica, n* is zero to three digits, ranging from 0 to 999.
Character length	8 positions minimum (hh:mm:ss) to 16 maximum (hh:mm:ss.nnnnnnn). For Informatica, the maximum is 12 (hh:mm:ss.nnn).
Precision, scale (user specifies scale only)	See the table below.
Storage size	5 bytes, fixed, is the default with the default of 100ns fractional second precision. In Informatica, the default is 4 bytes, fixed, with the default of 1ms fractional second precision.
Accuracy	100 nanoseconds (1 millisecond in Informatica)
Default value	00:00:00 This value is used for the appended time part for implicit conversion from date to datetime2 or datetimeoffset.
User-defined fractional second precision	Yes
Time zone offset aware and preservation	No
Daylight saving aware	No
Specified scale	Result (precision, scale)	Column length (bytes)	Fractional seconds precision
time	(16,7) [(12,3) in Informatica]	5 (4 in Informatica)	7 (3 in Informatica)
time(0)	(8,0)	3	0-2
time(1)	(10,1)	3	0-2
time(2)	(11,2)	3	0-2
time(3)	(12,3)	4	3-4
time(4) Not supported in Informatica.	(13,4)	4	3-4
time(5) Not supported in Informatica.	(14,5)	5	5-7
time(6) Not supported in Informatica.	(15,6)	5	5-7
time(7) Not supported in Informatica.	(16,7)	5	5-7

char and varchar

Character data types that are either fixed-size, char, or variable-size, varchar. Starting with SQL Server 2019 (15.x), when a UTF-8 enabled collation is used, these data types store the full range of Unicode character data and use the UTF-8 character encoding. If a non-UTF-8 collation is specified, then these data types store only a subset of characters supported by the corresponding code page of that collation.

Arguments

char [ ( n ) ]

Fixed-size string data. n defines the string size in bytes and must be a value from 1 through 8,000. For single-byte encoding character sets such as Latin, the storage size is n bytes and the number of characters that can be stored is also n. For multibyte encoding character sets, the storage size is still n bytes but the number of characters that can be stored may be smaller than n. The ISO synonym for char is character. For more information on character sets, see Single-Byte and Multibyte Character Sets.

varchar [ ( n | max ) ]

Variable-size string data. Use n to define the string size in bytes and can be a value from 1 through 8,000, or use max to indicate a column constraint size up to a maximum storage of 2^31-1 bytes (2 GB). For single-byte encoding character sets such as Latin, the storage size is n bytes + 2 bytes and the number of characters that can be stored is also n. For multibyte encoding character sets, the storage size is still n bytes + 2 bytes but the number of characters that can be stored may be smaller than n. The ISO synonyms for varchar are charvarying or charactervarying. For more information on character sets, see Single-Byte and Multibyte Character Sets.

Remarks

A common misconception is to think that with char(n) and varchar(n), the n defines the number of characters. However, in char(n) and varchar(n), the n defines the string length in bytes (0 to 8,000). n never defines numbers of characters that can be stored. This is similar to the definition of nchar(n) and nvarchar(n).

The misconception happens because when using single-byte encoding, the storage size of char and varchar is n bytes and the number of characters is also n. However, for multibyte encoding such as UTF-8, higher Unicode ranges (128 to 1,114,111) result in one character using two or more bytes. For example, in a column defined as char(10), the Database Engine can store 10 characters that use single-byte encoding (Unicode range 0 to 127), but fewer than 10 characters when using multibyte encoding (Unicode range 128 to 1,114,111). For more information about Unicode storage and character ranges, see Storage differences between UTF-8 and UTF-16.

When n isn't specified in a data definition or variable declaration statement, the default length is 1. If n isn't specified when using the CAST and CONVERT functions, the default length is 30.

Objects that use char or varchar are assigned the default collation of the database, unless a specific collation is assigned using the COLLATE clause. The collation controls the code page that is used to store the character data.

Multibyte encodings in SQL Server include:

· Double-byte character sets (DBCS) for some East Asian languages using code pages 936 and 950 (Chinese), 932 (Japanese), or 949 (Korean).

· UTF-8 with code page 65001.

Applies to: SQL Server 2019 (15.x) and later versions.

If you have sites that support multiple languages:

· Starting with SQL Server 2019 (15.x), consider using a UTF-8 enabled collation to support Unicode and minimize character conversion issues.

· If using a previous version of the SQL Server Database Engine, consider using the Unicode nchar or nvarchar data types to minimize character conversion issues.

If you use char or varchar, we recommend that you:

· Use char when the sizes of the column data entries are consistent.

· Use varchar when the sizes of the column data entries vary considerably.

· Use varchar(max) when the sizes of the column data entries vary considerably, and the string length might exceed 8,000 bytes.

If SET ANSI_PADDING is OFF when either CREATE TABLE or ALTER TABLE is executed, a char column that is defined as NULL is handled as varchar.

ntext, text, and image

Fixed and variable-length data types for storing large non-Unicode and Unicode character and binary data. Unicode data uses the Unicode UCS-2 character set.

All three data types deprecated in latest version. Same will be removed in future versions of SQL server

Arguments

ntext

Variable-length Unicode data with a maximum string length of 2^30 - 1 (1,073,741,823) bytes. Storage size, in bytes, is two times the string length that is entered. The ISO synonym for ntext is national text.

text

Variable-length non-Unicode data in the code page of the server and with a maximum string length of 2^31-1 (2,147,483,647). When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes. Depending on the character string, the storage size may be less than 2,147,483,647 bytes.

image

Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes.

Remarks

The following functions and statements can be used with ntext, text, or image data.

Functions	Statements
DATALENGTH	READTEXT
PATINDEX	SET TEXTSIZE
SUBSTRING	UPDATETEXT
TEXTPTR	WRITETEXT
TEXTVALID

nchar and nvarchar

Character data types that are either fixed-size, nchar, or variable-size, nvarchar. Starting with SQL Server 2012 (11.x), when a Supplementary Character (SC) enabled collation is used, these data types store the full range of Unicode character data and use the UTF-16 character encoding. If a non-SC collation is specified, then these data types store only the subset of character data supported by the UCS-2 character encoding.

Arguments

nchar [ ( n ) ]

Fixed-size string data. n defines the string size in byte-pairs, and must be a value from 1 through 4,000. The storage size is two times n bytes. For UCS-2 encoding, the storage size is two times n bytes and the number of characters that can be stored is also n. For UTF-16 encoding, the storage size is still two times n bytes, but the number of characters that can be stored may be smaller than n because Supplementary Characters use two byte-pairs (also called surrogate-pair). The ISO synonyms for nchar are national char and national character.

nvarchar [ ( n | max ) ]

Variable-size string data. n defines the string size in byte-pairs, and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 characters (2 GB). The storage size is two times n bytes + 2 bytes. For UCS-2 encoding, the storage size is two times n bytes + 2 bytes and the number of characters that can be stored is also n. For UTF-16 encoding, the storage size is still two times n bytes + 2 bytes, but the number of characters that can be stored may be smaller than n because Supplementary Characters use two byte-pairs (also called surrogate-pair). The ISO synonyms for nvarchar are national char varying and national character varying.

Remarks

A common misconception is to think that with nchar(n) and nvarchar(n), the n defines the number of characters. However, in nchar(n) and nvarchar(n), the n defines the string length in byte-pairs (0-4,000). n never defines numbers of characters that can be stored. This is similar to the definition of char(n) and varchar(n).

The misconception happens because when using characters defined in the Unicode range 0 to 65,535, one character can be stored per each byte-pair. However, in higher Unicode ranges (65,536 to 1,114,111) one character may use two byte-pairs. For example, in a column defined as nchar(10), the Database Engine can store 10 characters that use one byte-pair (Unicode range 0 to 65,535), but fewer than 10 characters when using two byte-pairs (Unicode range 65,536 to 1,114,111). For more information about Unicode storage and character ranges, see Storage differences between UTF-8 and UTF-16.

When n isn't specified in a data definition or variable declaration statement, the default length is 1. When n isn't specified with the CAST function, the default length is 30.

If you use nchar or nvarchar, we recommend that you:

· Use nchar when the sizes of the column data entries are consistent.

· Use nvarchar when the sizes of the column data entries vary considerably.

· Use nvarchar(max) when the sizes of the column data entries vary considerably, and the string length might exceed 4,000 byte-pairs.

sysname is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128), except that it isn't nullable. sysname is used to reference database object names.

Objects that use nchar or nvarchar are assigned the default collation of the database unless a specific collation is assigned using the COLLATE clause.

SET ANSI_PADDING is always ON for nchar and nvarchar. SET ANSI_PADDING OFF doesn't apply to the nchar or nvarchar data types.

Prefix a Unicode character string constants with the letter N to signal UCS-2 or UTF-16 input, depending on whether an SC collation is used or not. Without the N prefix, the string is converted to the default code page of the database that may not recognize certain characters. Starting with SQL Server 2019 (15.x), when a UTF-8 enabled collation is used, the default code page is capable of storing the Unicode UTF-8 character set.

When prefixing a string constant with the letter N, the implicit conversion will result in a UCS-2 or UTF-16 string if the constant to convert doesn't exceed the max length for the nvarchar string data type (4,000). Otherwise, the implicit conversion will result in a large-value nvarchar(max).

ntext, text, and image

Fixed and variable-length data types for storing large non-Unicode and Unicode character and binary data. Unicode data uses the Unicode UCS-2 character set.

The ntext, text, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

Arguments

ntext

text

image

Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes.

Remarks

The following functions and statements can be used with ntext, text, or image data.

Functions	Statements
DATALENGTH	READTEXT
PATINDEX	SET TEXTSIZE
SUBSTRING	UPDATETEXT
TEXTPTR	WRITETEXT
TEXTVALID

binary and varbinary

Binary data types of either fixed length or variable length.

Arguments

binary [ ( n ) ]

Fixed-length binary data with a length of n bytes, where n is a value from 1 through 8,000. The storage size is n bytes.

varbinary [ ( n | max ) ]

Variable-length binary data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of the data entered + 2 bytes. The data that is entered can be 0 bytes in length. The ANSI SQL synonym for varbinary is binary varying.

Remarks

The default length is 1 when n isn't specified in a data definition or variable declaration statement. When n isn't specified with the CAST function, the default length is 30.

Data type	Use when ...
binary	the sizes of the column data entries are consistent.
varbinary	the sizes of the column data entries vary considerably.
varbinary(max)	the column data entries exceed 8,000 bytes.

Convert binary and varbinary data

When converting data from a string data type to a binary or varbinary data type of unequal length, SQL Server pads or truncates the data on the right. These string data types are:

· char

· varchar

· nchar

· nvarchar

· binary

· varbinary

· text

· ntext

· image

When other data types are converted to binary or varbinary, the data is padded or truncated on the left. Padding is achieved by using hexadecimal zeros.

Converting data to the binary and varbinary data types is useful if binary data is the easiest way to move around data. At some point, you might convert a value type to a binary value of large enough size and then convert it back. This conversion always results in the same value if both conversions are taking place on the same version of SQL Server. The binary representation of a value might change from version to version of SQL Server.

You can convert int, smallint, and tinyint to binary or varbinary. If you convert the binary value back to an integer value, this value will be different from the original integer value if truncation has occurred. For example, the following SELECT statement shows that the integer value 123456 is stored as a binary 0x0001e240:

Dynamic Event Binding in JQuery

2012-08-01T03:42:00.001-07:00

Create a sample form with button which used to create dynamic select tag and dynamic event binding

Here i am using the select tag for my test to bind the event dynamically. we are created a sample form that form contains one button for creating a dynamic select tag. We are going to write a change event for that dynamically created select tag.

The following code may explain how can i write a events for dynamically created elements in html using JQuery. For that we need jquery.

Source Code:

<html>
<head>
<script src="jquery-1.6.js" type="text/javascript"></script>
<script>
var n=1;
$(document).ready(function(){
$("#createselect").click(function(){

var newelement=$(document.createElement('div')).attr('id','test'+n);
newelement.html('<label>select tag'+n+'</label><select id="selectid'+n+'" class="dynamicallycreated" ><option>option1</option><option>option2</option><option>option3</option></select>');
newelement.appendTo("#testing");
n++
});

$('select.dynamicallycreated').live('change',function(){
$(this).next('p').empty();
$(this).after("<p>"+$(this).val()+" is selected</p>");

});
});
</script>
</head>
<body>
<div id="testing">
</div>
<input type="button" id="createselect" value="create">

</body>
</html>

This code is for creating any number of select tag and bind the change method for each element in dynamically. For that i used the live method also take class name as a selector of my test.

Create a Select Tag Dynamically Using Jquery

2012-08-01T00:12:00.000-07:00

Create a Select Tag Dynamically Using Jquery and HTML

From this blog, i want to share my any learning technology in program level. Today i want to create a select and input tag dynamically and and write the change event for all the select tag which are created dynamically.

We can create a element using a method document.createElement() in javascript. After that we can add the id, name like this properties using .attr() method in Jquery.

After setting all properties, we created our element but we need to join that element with document. For that we can use appendTo() or insertAfter() to join with already existing element in html document.

Example coding for that:

In javascript function:

   var newelemet=$(document.createElement('p')).attr('id','elementId');
    newelement.html('This is the paragraph content');

this code only for creating a paragraph.
After creating paragraph we need to append this to any existing element like div, etc..

newelement.appendTo("#dividtobeappend");

After that we can learn adding select tag also like that only..

Adding select also very simple if create a div or ol in the html document we cant add it easily.

what i am done is create a one div containing a ol. In script i am create a list dynamically and within the the content of list i am create a select tag like following .

1. Creating li element
var newelemet=$(document.createElement('li')).attr('id','elementId');

2. Adding select tag content using html() method.

   newelement.html('<select id=selectid ><option>option1</option><option>option2</option></select>');
3. After adding the content we set into div using appendTo() method in jquery.

   newelement.appendTo("divtobeappend");

These post is used for creating select element dynamically. We can write dynamic change method for let we see in next post.