Difference between const and static readonly keywords C#
If in your C# class you want to use a variable whose value will not change during the lifetime of that class, then you have
two options:
1. Use the "const" keyword: for e.g. public const int myVar = 100;
2. Use the "static readonly" keyword: for e.g. public static readonly int myVar = ConfigurationManager.AppSettings["value"];
The const variable has a limitation: it can ONLY be set during compile time. This means you cannot set its value using any code
logic, like getting its value from some config file, or from some "if" condition. Whereas the value of a static readonly field
can be set at run time, and can thus be modified by the containing class using any custom logic.
In the static readonly case, the containing class is allowed to modify it only
* in the variable declaration (through a variable initializer)
* in the static constructor (instance constructors, if it's not static)
21 December 2010
What are the basic differences between user controls and custom controls?
What are the basic differences between user controls and custom controls?
User control
Deployment
Designed for single-application scenarios ,Deployed in the source form (.ascx) along with the source code of the application
If the same control needs to be used in more than one application, it introduces redundancy and maintenance problems
Custom control
Designed so that it can be used by more than one application Deployed either in the application's Bin directory or in the global assembly cache
Distributed easily and without problems associated with redundancy and maintenance.
Creation
User control
Creation is similar to the way Web Forms pages are created; well-suited for rapid application development (RAD)
Custom control
Writing involves lots of code because there is no designer support.
Content
User control
A much better choice when you need static content within a fixed layout, for example, when you make headers and footers
Custom Control
More suited for when an application requires dynamic content to be displayed; can be reused across an application,
for example, for a data bound table control with dynamic rows
Design
User Control
Writing doesn't require much application designing because they are authored at design time and mostly contain static data
Custom control
Writing from scratch requires a good understanding of the control's life cycle and the order in which events execute,
which is normally taken care of in user controls
How a user control is processed
When a page with a user control is requested, the following occurs:
The page parser parses the .ascx file specified in the Src attribute in the @ Register directive and generates a class that derives from the System.Web.UI.UserControl class.
The parser then dynamically compiles the class into an assembly.
If you are using Visual Studio, then at design time only, Visual Studio creates a code behind file for the user control, and the file is precompiled by the designer itself.
Finally, the class for the user control, which is generated through the process of dynamic code generation and compilation, includes the code for the code behind file (.ascx.cs) as well as the code written inside the .ascx file.
How to choose the base class for your custom control
To write a custom control, you should directly or indirectly derive the new class from the System.Web.UI.Control class or from the System.Web.UI.WebControls.WebControl class:
You should derive from System.Web.UI.Control if you want the control to render nonvisual elements. For example, and are examples of nonvisual rendering.
You should derive from System.Web.UI.WebControls.WebControl if you want the control to render HTML that generates a visual interface on the client computer.
User control
Deployment
Designed for single-application scenarios ,Deployed in the source form (.ascx) along with the source code of the application
If the same control needs to be used in more than one application, it introduces redundancy and maintenance problems
Custom control
Designed so that it can be used by more than one application Deployed either in the application's Bin directory or in the global assembly cache
Distributed easily and without problems associated with redundancy and maintenance.
Creation
User control
Creation is similar to the way Web Forms pages are created; well-suited for rapid application development (RAD)
Custom control
Writing involves lots of code because there is no designer support.
Content
User control
A much better choice when you need static content within a fixed layout, for example, when you make headers and footers
Custom Control
More suited for when an application requires dynamic content to be displayed; can be reused across an application,
for example, for a data bound table control with dynamic rows
Design
User Control
Writing doesn't require much application designing because they are authored at design time and mostly contain static data
Custom control
Writing from scratch requires a good understanding of the control's life cycle and the order in which events execute,
which is normally taken care of in user controls
How a user control is processed
When a page with a user control is requested, the following occurs:
The page parser parses the .ascx file specified in the Src attribute in the @ Register directive and generates a class that derives from the System.Web.UI.UserControl class.
The parser then dynamically compiles the class into an assembly.
If you are using Visual Studio, then at design time only, Visual Studio creates a code behind file for the user control, and the file is precompiled by the designer itself.
Finally, the class for the user control, which is generated through the process of dynamic code generation and compilation, includes the code for the code behind file (.ascx.cs) as well as the code written inside the .ascx file.
How to choose the base class for your custom control
To write a custom control, you should directly or indirectly derive the new class from the System.Web.UI.Control class or from the System.Web.UI.WebControls.WebControl class:
You should derive from System.Web.UI.Control if you want the control to render nonvisual elements. For example, and are examples of nonvisual rendering.
You should derive from System.Web.UI.WebControls.WebControl if you want the control to render HTML that generates a visual interface on the client computer.
14 December 2010
SQL Tips and Guidelines- Sql Server 2008
SQL Tips and Guidelines- Sql Server 2008
• As a common practice, every table should have a clustered index. Generally, but not always, the clustered index should be on a column that monotonically increases, such as an identity column or some other column where the value is unique. In many cases, the primary key is the ideal column for a clustered index.
• Indexes should be measured on all columns that are frequently used in WHERE, ORDER BY, GROUP BY, TOP and DISTINCT clauses.
• Do not automatically add indexes on a table because it seems like the right thing to do. Only add indexes if you know that they will be used by the queries run against the table.
• Do not add more indexes on your OLTP tables to minimize the overhead that occurs with indexes during data modifications.
• Do not add the same index more than once on a table with different names.
• Ensure that all your tables are linked with foreign keys. foreign keys enhance the performance of queries with joins. Database tables inside each application are naturally related. Islands of tables are rarely needed if your application's business logic is well defined.
• Drop all those indexes that are not used by the Query Optimizer, generally. You probably won't want to add an index to a table under the following conditions:
•If the index is not used by the query optimizer. Use the Query Analyzer's "Show Execution Plan" option to see if your queries against a particular table use an index or not.
•If the table is small, most likely indexes will not be used.
•If the column(s) to be indexed are very wide.
•If the column(s) are defined as TEXT, NTEXT or IMAGE data types.
•If the table is rarely queried but insertion, updating is frequent.
• To provide up-to-date statistics, the query optimizer needs to make smart query optimization decisions. You will generally want to leave the "Auto Update Statistics" database option on. This helps to ensure that the optimizer statistics are valid, ensuring that queries are properly optimized when they are run.
• Keep the "width" of your indexes as narrow as possible. This reduces the size of the index and reduces the number of disk I/O reads required to read the index.
• If possible, try to create indexes on columns that have integer values instead of characters. Integer values use less overhead than character values.
• If you have two or more tables that are frequently joined together, then the columns used for the joins should have an appropriate index. If the columns used for the joins are not naturally compact, then consider adding surrogate keys to the tables that are compact in order to reduce the size of the keys. This will decrease I/O during the join process, which increases overall performance.
• When creating indexes, try to make them unique indexes if at all possible. SQL Server can often search through a unique index faster than a non-unique index. This is because, in a unique index, each row is unique and once the needed record is found, SQL Server doesn't have to look any further.
• If a particular query against a table is run infrequently and the addition of an index greatly speeds the performance of the query, but the performance of INSERTS, UPDATES and DELETES is negatively affected by the addition of the index, consider creating the index for the table for the duration of when the query is run and then dropping the index. An example of this is when monthly reports are run at the end of the month on an OLTP application.
• Avoid using FLOAT or REAL data types as primary keys, as they add unnecessary overhead that can hurt performance.
• If you want to boost the performance of a query that includes an AND operator in the WHERE clause, consider the following:
•Of the search criteria in the WHERE clause, at least one of them should be based on a highly selective column that has an index.
•If at least one of the search criteria in the WHERE clause is not highly selective, consider adding indexes to all of the columns referenced in the WHERE clause.
•If none of the columns in the WHERE clause are selective enough to use an index on their own, consider creating a covering index for this query.
• The Query Optimizer will always perform a table scan or a clustered index scan on a table if the WHERE clause in the query contains an OR operator and if any of the referenced columns in the OR clause are not indexed (or do not have a useful index). Because of this, if you use many queries with OR clauses, you will want to ensure that each referenced column in the WHERE clause has an index.
• If you have a query that uses ORs and it is not making the best use of indexes, consider rewriting it as a UNION and then testing performance. Only through testing can you be sure that one version of your query will be faster than another.
• If you use the SOUNDEX function against a table column in a WHERE clause, the Query Optimizer will ignore any available indexes and perform a table scan.
• Queries that include either the DISTINCT or the GROUP BY clauses can be optimized by including appropriate indexes. Any of the following indexing strategies can be used:
•Include a covering, non-clustered index (covering the appropriate columns) of the DISTINCT or the GROUP BY clauses.
•Include a clustered index on the columns in the GROUP BY clause.
•Include a clustered index on the columns found in the SELECT clause.
•Adding appropriate indexes to queries that include DISTINCT or GROUP BY is most important for those queries that run often.
• Avoid clustered indexes on columns that are already "covered" by non-clustered indexes. A clustered index on a column that is already "covered" is redundant. Use the clustered index for columns that can better make use of it.
• Ideally a clustered index should be based on a single column (not multiple columns) that are as narrow as possible. This not only reduces the clustered index's physical size, it also reduces the physical size of non-clustered indexes and boosts SQL Server's overall performance.
• When you create a clustered index, try to create it as a unique clustered index, not a non-unique clustered index.
• SET NOCOUNT ON at the beginning of each stored procedure you write. This statement should be included in every stored procedure, trigger, etc. that you write.
• Keep Transact-SQL transactions as short as possible within a stored procedure. This helps to reduce the number of locks, helping to speed up the overall performance of your SQL Server application.
• If you are creating a stored procedure to run in a database other than the Master database, don't use the prefix sp_ in its name. This special prefix is reserved for system stored procedures. Although using this prefix will not prevent a user defined stored procedure from working, what it can do is to slow down its execution ever so slightly.
• Before you are done with your stored procedure code, review it for any unused code, parameters or variables that you may have forgotten to remove while you were making changes and remove them. Unused code just adds unnecessary bloat to your stored procedures, although it will not necessarily negatively affect performance of the stored procedure.
• For best performance, all objects that are called within the same stored procedure should be owned by the same object owner or schema, preferably dbo, and should also be referred to in the format of object_owner.object_name or schema_owner.object_name.
• When you need to execute a string of Transact-SQL, you should use the sp_executesql stored procedure instead of the EXECUTE statement.
• If you use input parameters in your stored procedures, you should validate all of them at the beginning of your stored procedure. This way, if there is a validation problem and the client application needs to be notified of the problem, it happens before any stored procedure processing takes place, preventing wasted effort and boosting performance.
• When calling a stored procedure from your application, it is important that you call it using its qualified name, for example:
Collapse Copy Code
exec dbo.myProc
...instead of:
Collapse Copy Code
exec myProc
• If you think a stored procedure will return only a single value and not a record set, consider returning the single value as an output parameter.
• Use stored procedures instead of views. They offer better performance.
• Don't include code, variable or parameters that don't do anything.
• Don't be afraid to make broad-minded use of in-line and block comments in your Transact-SQL code. They will not affect the performance of your application and they will enhance your productivity when you have to come back to the code and try to modify it.
• If possible, avoid using SQL Server cursors. They generally use a lot of SQL Server resources and reduce the performance and scalability of your applications.
• If you have the choice of using a join or a sub-query to perform the same task within a query, generally the join is faster. This is not always the case, however, and you may want to test the query using both methods to determine which is faster for your particular application.
• If your application requires you to create temporary tables for use on a global or per connection use, consider the possibility of creating indexes for these temporary tables. While most temporary tables probably won't need -- or even use -- an index, some larger temporary tables can benefit from them. A properly designed index on a temporary table can be as great a benefit as a properly designed index on a standard database table.
• Instead of using temporary tables, consider using a derived table instead. A derived table is the result of using a SELECT statement in the FROM clause of an existing SELECT statement. By using derived tables instead of temporary tables, you can reduce I/O and often boost your application's performance.
• For better performance, if you need a temporary table in your Transact-SQL code, consider using a table variable instead of creating a conventional temporary table.
• Don't repeatedly reuse the same function to calculate the same result over and over within your Transact-SQL code.
• If you use BULK INSERT to import data into SQL Server, then use the TABLOCK hint along with it. This will prevent SQL Server from running out of locks during very large imports and will also boost performance due to the reduction of lock contention.
• Always specify the narrowest columns you can. The narrower the column, the less amount of data SQL Server has to store and the faster SQL Server is able to read and write data. In addition, if any sorts need to be performed on the column, the narrower the column, the faster the sort will be.
• If you need to store large strings of data and they are less than 8000 characters, use a VARCHAR data type instead of a TEXT data type. TEXT data types have extra overhead that drag down performance.
• Don't use the NVARCHAR or NCHAR data types unless you need to store 16-bit character (Unicode) data. They take up twice as much space as VARCHAR or CHAR data types, increasing server I/O and wasting unnecessary space in your buffer cache.
• If the text data in a column varies greatly in length, use a VARCHAR data type instead of a CHAR data type. The amount of space saved by using VARCHAR over CHAR on variable length columns can greatly reduce the I/O reads that the cache memory uses to hold data, improving overall SQL Server performance.
• If a column's data does not vary widely in length, consider using a fixed-length CHAR field instead of a VARCHAR. While it may take up a little more space to store the data, processing fixed-length columns is faster in SQL Server than processing variable-length columns.
• If you have a column that is designed to hold only numbers, use a numeric data type such as INTEGER instead of a VARCHAR or CHAR data type. Numeric data types generally require less space to hold the same numeric value than does a character data type. This helps to reduce the size of the columns and can boost performance when the columns are searched (WHERE clause), joined to another column or sorted.
• If you use the CONVERT function to convert a value to a variable length data type such as VARCHAR, always specify the length of the variable data type. If you do not, SQL Server assumes a default length of 30. Ideally, you should specify the shortest length to accomplish the required task. This helps to reduce memory use and SQL Server resources.
• Avoid using the new BIGINT data type unless you really need its additional storage capacity. The BIGINT data type uses 8 bytes of memory, versus 4 bytes for the INT data type.
• Don't use the DATETIME data type as a primary key. From a performance perspective, it is more efficient to use a data type that uses less space. For example, the DATETIME data type uses 8 bytes of space, while the INT data type only takes up 4 bytes. The less space used, the smaller the table and index, and the less I/O overhead that is required to access the primary key.
• If you are creating a column that you know will be subject to many sorts, consider making the column integer-based and not character-based. This is because SQL Server can sort integer data much faster than character data.
• Carefully evaluate whether your SELECT query needs the DISTINCT clause or not. Some developers automatically add this clause to every one of their SELECT statements, even when it is not necessary. This is a bad habit that should be stopped.
• When you need to use SELECT INTO option, keep in mind that it can lock system tables, preventing other users from accessing the data they need while the data is being inserted. In order to prevent or minimize the problems caused by locked tables, try to schedule the use of SELECT INTO when your SQL Server is less busy. In addition, try to keep the amount of data inserted to a minimum. In some cases, it may be better to perform several smaller SELECT INTOs instead of performing one large SELECT INTO.
• If you need to verify the existence of a record in a table, don't use SELECT COUNT (*) in your Transact-SQL code to identify it. This is very inefficient and wastes server resources. Instead, use the Transact-SQL IF EXISTS to determine if the record in question exists, which is much more efficient.
• By default, some developers -- especially those who have not worked with SQL Server before -- routinely include code similar to this in their WHERE clauses when they make string comparisons:
Collapse Copy Code
SELECT column_name FROM table_name
WHERE LOWER (column_name) = 'name'
In other words, these developers are making the assumption that the data in SQL Server is case-sensitive, which it generally is not. If your SQL Server database is not configured to be case sensitive, you don't need to use LOWER or UPPER to force the case of text to be equal for a comparison to be performed. Just leave these functions out of your code. This will speed up the performance of your query, as any use of text functions in a WHERE clause hurts performance.
However, what if your database has been configured to be case-sensitive? Should you then use the LOWER and UPPER functions to ensure that comparisons are properly compared? No. The above example is still poor coding. If you have to deal with ensuring case is consistent for proper comparisons, use the technique described below, along with appropriate indexes on the column in question:
Collapse Copy Code
SELECT column_name FROM table_name
WHERE column_name = 'NAME' or column_name = 'name'
This code will run much faster than the first example.
• If you currently have a query that uses NOT IN, which offers poor performance because the SQL Server optimizer has to use a nested table scan to perform this activity, instead try to use one of the following options, all of which offer better performance:
•Use EXISTS or NOT EXISTS
•Use IN
•Perform a LEFT OUTER JOIN and check for a NULL condition
• When you have a choice of using the IN or the EXISTS clause in your Transact-SQL, you will generally want to use the EXISTS clause, as it is usually more efficient and performs faster.
• If you find that SQL Server uses a TABLE SCAN instead of an INDEX SEEK when you use an IN/OR clause as part of your WHERE clause, even when those columns are covered by an index, consider using an index hint to force the Query Optimizer to use the index.
• If you use LIKE in your WHERE clause, try to use one or more leading characters in the clause, if possible. For example, use:
Collapse Copy Code
LIKE 'm%' instead of LIKE ‘%m’
• If your application needs to retrieve summary data often, but you don't want to have the overhead of calculating it on the fly every time it is needed, consider using a trigger that updates summary values after each transaction into a summary table.
• When you have a choice of using the IN or the BETWEEN clauses in your Transact-SQL, you will generally want to use the BETWEEN clause, as it is much more efficient. For example...
Collapse Copy Code
SELECT task_id, task_name
FROM tasks
WHERE task_id in (1000, 1001, 1002, 1003, 1004)
...is much less efficient than this:
Collapse Copy Code
SELECT task_id, task_name
FROM tasks
WHERE task_id BETWEEN 1000 and 1004
• If possible, try to avoid using the SUBSTRING function in your WHERE clauses. Depending on how it is constructed, using the SUBSTRING function can force a table scan instead of allowing the optimizer to use an index (assuming there is one). If the substring you are searching for does not include the first character of the column you are searching for, then a table scan is performed.
• If possible, you should avoid using the SUBSTRING function and use the LIKE condition instead for better performance. Instead of doing this:
Collapse Copy Code
WHERE SUBSTRING(task_name,1,1) = 'b'
Try using this instead:
Collapse Copy Code
WHERE task_name LIKE 'b%'
• Avoid using optimizer hints in your WHERE clauses. This is because it is generally very hard to out-guess the Query Optimizer. Optimizer hints are special keywords that you include with your query to force how the Query Optimizer runs. If you decide to include a hint in a query, this forces the Query Optimizer to become static, preventing the Query Optimizer from dynamically adapting to the current environment for the given query. More often than not, this hurts -- not helps -- performance.
• If you have a WHERE clause that includes expressions connected by two or more AND operators, SQL Server will evaluate them from left to right in the order they are written. This assumes that no parentheses have been used to change the order of execution. Because of this, you may want to consider one of the following when using AND:
• Locate the least likely true AND expression first.
• If both parts of an AND expression are equally likely of being false, put the least complex AND expression first.
• You may want to consider using Query Analyzer or Management Studio to look at the execution plans of your queries to see which is best for your situation
• Don't use ORDER BY in your SELECT statements unless you really need to, as it adds a lot of extra overhead. For example, perhaps it may be more efficient to sort the data at the client than at the server.
• Whenever SQL Server has to perform a sorting operation, additional resources have to be used to perform this task. Sorting often occurs when any of the following Transact-SQL statements are executed:
•ORDER BY
•GROUP BY
•SELECT DISTINCT
•UNION
• If you have to sort by a particular column often, consider making that column a clustered index. This is because the data is already presorted for you and SQL Server is smart enough not to resort the data.
• If your WHERE clause includes an IN operator along with a list of values to be tested in the query, order the list of values so that the most frequently found ones are placed at the start of the list and the less frequently found ones are placed at the end of the list. This can speed up performance because the IN option returns true as soon as any of the values in the list produce a match. The sooner the match is made, the faster the query completes.
• If your application performs many wildcard (LIKE %) text searches on CHAR or VARCHAR columns, consider using SQL Server's full-text search option. The Search Service can significantly speed up wildcard searches of text stored in a database.
• The GROUP BY clause can be used with or without an aggregate function. However, if you want optimum performance, don't use the GROUP BY clause without an aggregate function. This is because you can accomplish the same end result by using the DISTINCT option instead, and it is faster. For example, you could write your query two different ways:
Collapse Copy Code
SELECT task_id
FROM tasks
WHERE task_id BETWEEN 10 AND 20
GROUP BY OrderID
...or:
Collapse Copy Code
SELECT DISTINCT task_id
FROM tasks
WHERE task_id BETWEEN 10 AND 20
• It is important to design applications that keep transactions as short as possible. This reduces locking and increases application concurrently, which helps to boost performance.
• In order to reduce network traffic between the client or middle-tier and SQL Server -- and also to boost your SQL Server-based application's performance -- only the data needed by the client or middle-tier should be returned by SQL Server. In other words, don't return more data (both rows and columns) from SQL Server than you need to the client or middle-tier and then further reduce the data to the data you really need at the client or middle-tier. This wastes SQL Server resources and network bandwidth.
• To make complex queries easier to analyze, consider breaking them down into their smaller constituent parts. One way to do this is to simply create lists of the key components of the query, such as:
•List all of the columns that are to be returned
•List all of the columns that are used in the WHERE clause
•List all of the columns used in the JOINs (if applicable)
•List all the tables used in JOINs (if applicable)
•Once you have the above information organized into this easy-to-comprehend form, it is much easier to identify those columns that could potentially make use of indexes when executed.
• As a common practice, every table should have a clustered index. Generally, but not always, the clustered index should be on a column that monotonically increases, such as an identity column or some other column where the value is unique. In many cases, the primary key is the ideal column for a clustered index.
• Indexes should be measured on all columns that are frequently used in WHERE, ORDER BY, GROUP BY, TOP and DISTINCT clauses.
• Do not automatically add indexes on a table because it seems like the right thing to do. Only add indexes if you know that they will be used by the queries run against the table.
• Do not add more indexes on your OLTP tables to minimize the overhead that occurs with indexes during data modifications.
• Do not add the same index more than once on a table with different names.
• Ensure that all your tables are linked with foreign keys. foreign keys enhance the performance of queries with joins. Database tables inside each application are naturally related. Islands of tables are rarely needed if your application's business logic is well defined.
• Drop all those indexes that are not used by the Query Optimizer, generally. You probably won't want to add an index to a table under the following conditions:
•If the index is not used by the query optimizer. Use the Query Analyzer's "Show Execution Plan" option to see if your queries against a particular table use an index or not.
•If the table is small, most likely indexes will not be used.
•If the column(s) to be indexed are very wide.
•If the column(s) are defined as TEXT, NTEXT or IMAGE data types.
•If the table is rarely queried but insertion, updating is frequent.
• To provide up-to-date statistics, the query optimizer needs to make smart query optimization decisions. You will generally want to leave the "Auto Update Statistics" database option on. This helps to ensure that the optimizer statistics are valid, ensuring that queries are properly optimized when they are run.
• Keep the "width" of your indexes as narrow as possible. This reduces the size of the index and reduces the number of disk I/O reads required to read the index.
• If possible, try to create indexes on columns that have integer values instead of characters. Integer values use less overhead than character values.
• If you have two or more tables that are frequently joined together, then the columns used for the joins should have an appropriate index. If the columns used for the joins are not naturally compact, then consider adding surrogate keys to the tables that are compact in order to reduce the size of the keys. This will decrease I/O during the join process, which increases overall performance.
• When creating indexes, try to make them unique indexes if at all possible. SQL Server can often search through a unique index faster than a non-unique index. This is because, in a unique index, each row is unique and once the needed record is found, SQL Server doesn't have to look any further.
• If a particular query against a table is run infrequently and the addition of an index greatly speeds the performance of the query, but the performance of INSERTS, UPDATES and DELETES is negatively affected by the addition of the index, consider creating the index for the table for the duration of when the query is run and then dropping the index. An example of this is when monthly reports are run at the end of the month on an OLTP application.
• Avoid using FLOAT or REAL data types as primary keys, as they add unnecessary overhead that can hurt performance.
• If you want to boost the performance of a query that includes an AND operator in the WHERE clause, consider the following:
•Of the search criteria in the WHERE clause, at least one of them should be based on a highly selective column that has an index.
•If at least one of the search criteria in the WHERE clause is not highly selective, consider adding indexes to all of the columns referenced in the WHERE clause.
•If none of the columns in the WHERE clause are selective enough to use an index on their own, consider creating a covering index for this query.
• The Query Optimizer will always perform a table scan or a clustered index scan on a table if the WHERE clause in the query contains an OR operator and if any of the referenced columns in the OR clause are not indexed (or do not have a useful index). Because of this, if you use many queries with OR clauses, you will want to ensure that each referenced column in the WHERE clause has an index.
• If you have a query that uses ORs and it is not making the best use of indexes, consider rewriting it as a UNION and then testing performance. Only through testing can you be sure that one version of your query will be faster than another.
• If you use the SOUNDEX function against a table column in a WHERE clause, the Query Optimizer will ignore any available indexes and perform a table scan.
• Queries that include either the DISTINCT or the GROUP BY clauses can be optimized by including appropriate indexes. Any of the following indexing strategies can be used:
•Include a covering, non-clustered index (covering the appropriate columns) of the DISTINCT or the GROUP BY clauses.
•Include a clustered index on the columns in the GROUP BY clause.
•Include a clustered index on the columns found in the SELECT clause.
•Adding appropriate indexes to queries that include DISTINCT or GROUP BY is most important for those queries that run often.
• Avoid clustered indexes on columns that are already "covered" by non-clustered indexes. A clustered index on a column that is already "covered" is redundant. Use the clustered index for columns that can better make use of it.
• Ideally a clustered index should be based on a single column (not multiple columns) that are as narrow as possible. This not only reduces the clustered index's physical size, it also reduces the physical size of non-clustered indexes and boosts SQL Server's overall performance.
• When you create a clustered index, try to create it as a unique clustered index, not a non-unique clustered index.
• SET NOCOUNT ON at the beginning of each stored procedure you write. This statement should be included in every stored procedure, trigger, etc. that you write.
• Keep Transact-SQL transactions as short as possible within a stored procedure. This helps to reduce the number of locks, helping to speed up the overall performance of your SQL Server application.
• If you are creating a stored procedure to run in a database other than the Master database, don't use the prefix sp_ in its name. This special prefix is reserved for system stored procedures. Although using this prefix will not prevent a user defined stored procedure from working, what it can do is to slow down its execution ever so slightly.
• Before you are done with your stored procedure code, review it for any unused code, parameters or variables that you may have forgotten to remove while you were making changes and remove them. Unused code just adds unnecessary bloat to your stored procedures, although it will not necessarily negatively affect performance of the stored procedure.
• For best performance, all objects that are called within the same stored procedure should be owned by the same object owner or schema, preferably dbo, and should also be referred to in the format of object_owner.object_name or schema_owner.object_name.
• When you need to execute a string of Transact-SQL, you should use the sp_executesql stored procedure instead of the EXECUTE statement.
• If you use input parameters in your stored procedures, you should validate all of them at the beginning of your stored procedure. This way, if there is a validation problem and the client application needs to be notified of the problem, it happens before any stored procedure processing takes place, preventing wasted effort and boosting performance.
• When calling a stored procedure from your application, it is important that you call it using its qualified name, for example:
Collapse Copy Code
exec dbo.myProc
...instead of:
Collapse Copy Code
exec myProc
• If you think a stored procedure will return only a single value and not a record set, consider returning the single value as an output parameter.
• Use stored procedures instead of views. They offer better performance.
• Don't include code, variable or parameters that don't do anything.
• Don't be afraid to make broad-minded use of in-line and block comments in your Transact-SQL code. They will not affect the performance of your application and they will enhance your productivity when you have to come back to the code and try to modify it.
• If possible, avoid using SQL Server cursors. They generally use a lot of SQL Server resources and reduce the performance and scalability of your applications.
• If you have the choice of using a join or a sub-query to perform the same task within a query, generally the join is faster. This is not always the case, however, and you may want to test the query using both methods to determine which is faster for your particular application.
• If your application requires you to create temporary tables for use on a global or per connection use, consider the possibility of creating indexes for these temporary tables. While most temporary tables probably won't need -- or even use -- an index, some larger temporary tables can benefit from them. A properly designed index on a temporary table can be as great a benefit as a properly designed index on a standard database table.
• Instead of using temporary tables, consider using a derived table instead. A derived table is the result of using a SELECT statement in the FROM clause of an existing SELECT statement. By using derived tables instead of temporary tables, you can reduce I/O and often boost your application's performance.
• For better performance, if you need a temporary table in your Transact-SQL code, consider using a table variable instead of creating a conventional temporary table.
• Don't repeatedly reuse the same function to calculate the same result over and over within your Transact-SQL code.
• If you use BULK INSERT to import data into SQL Server, then use the TABLOCK hint along with it. This will prevent SQL Server from running out of locks during very large imports and will also boost performance due to the reduction of lock contention.
• Always specify the narrowest columns you can. The narrower the column, the less amount of data SQL Server has to store and the faster SQL Server is able to read and write data. In addition, if any sorts need to be performed on the column, the narrower the column, the faster the sort will be.
• If you need to store large strings of data and they are less than 8000 characters, use a VARCHAR data type instead of a TEXT data type. TEXT data types have extra overhead that drag down performance.
• Don't use the NVARCHAR or NCHAR data types unless you need to store 16-bit character (Unicode) data. They take up twice as much space as VARCHAR or CHAR data types, increasing server I/O and wasting unnecessary space in your buffer cache.
• If the text data in a column varies greatly in length, use a VARCHAR data type instead of a CHAR data type. The amount of space saved by using VARCHAR over CHAR on variable length columns can greatly reduce the I/O reads that the cache memory uses to hold data, improving overall SQL Server performance.
• If a column's data does not vary widely in length, consider using a fixed-length CHAR field instead of a VARCHAR. While it may take up a little more space to store the data, processing fixed-length columns is faster in SQL Server than processing variable-length columns.
• If you have a column that is designed to hold only numbers, use a numeric data type such as INTEGER instead of a VARCHAR or CHAR data type. Numeric data types generally require less space to hold the same numeric value than does a character data type. This helps to reduce the size of the columns and can boost performance when the columns are searched (WHERE clause), joined to another column or sorted.
• If you use the CONVERT function to convert a value to a variable length data type such as VARCHAR, always specify the length of the variable data type. If you do not, SQL Server assumes a default length of 30. Ideally, you should specify the shortest length to accomplish the required task. This helps to reduce memory use and SQL Server resources.
• Avoid using the new BIGINT data type unless you really need its additional storage capacity. The BIGINT data type uses 8 bytes of memory, versus 4 bytes for the INT data type.
• Don't use the DATETIME data type as a primary key. From a performance perspective, it is more efficient to use a data type that uses less space. For example, the DATETIME data type uses 8 bytes of space, while the INT data type only takes up 4 bytes. The less space used, the smaller the table and index, and the less I/O overhead that is required to access the primary key.
• If you are creating a column that you know will be subject to many sorts, consider making the column integer-based and not character-based. This is because SQL Server can sort integer data much faster than character data.
• Carefully evaluate whether your SELECT query needs the DISTINCT clause or not. Some developers automatically add this clause to every one of their SELECT statements, even when it is not necessary. This is a bad habit that should be stopped.
• When you need to use SELECT INTO option, keep in mind that it can lock system tables, preventing other users from accessing the data they need while the data is being inserted. In order to prevent or minimize the problems caused by locked tables, try to schedule the use of SELECT INTO when your SQL Server is less busy. In addition, try to keep the amount of data inserted to a minimum. In some cases, it may be better to perform several smaller SELECT INTOs instead of performing one large SELECT INTO.
• If you need to verify the existence of a record in a table, don't use SELECT COUNT (*) in your Transact-SQL code to identify it. This is very inefficient and wastes server resources. Instead, use the Transact-SQL IF EXISTS to determine if the record in question exists, which is much more efficient.
• By default, some developers -- especially those who have not worked with SQL Server before -- routinely include code similar to this in their WHERE clauses when they make string comparisons:
Collapse Copy Code
SELECT column_name FROM table_name
WHERE LOWER (column_name) = 'name'
In other words, these developers are making the assumption that the data in SQL Server is case-sensitive, which it generally is not. If your SQL Server database is not configured to be case sensitive, you don't need to use LOWER or UPPER to force the case of text to be equal for a comparison to be performed. Just leave these functions out of your code. This will speed up the performance of your query, as any use of text functions in a WHERE clause hurts performance.
However, what if your database has been configured to be case-sensitive? Should you then use the LOWER and UPPER functions to ensure that comparisons are properly compared? No. The above example is still poor coding. If you have to deal with ensuring case is consistent for proper comparisons, use the technique described below, along with appropriate indexes on the column in question:
Collapse Copy Code
SELECT column_name FROM table_name
WHERE column_name = 'NAME' or column_name = 'name'
This code will run much faster than the first example.
• If you currently have a query that uses NOT IN, which offers poor performance because the SQL Server optimizer has to use a nested table scan to perform this activity, instead try to use one of the following options, all of which offer better performance:
•Use EXISTS or NOT EXISTS
•Use IN
•Perform a LEFT OUTER JOIN and check for a NULL condition
• When you have a choice of using the IN or the EXISTS clause in your Transact-SQL, you will generally want to use the EXISTS clause, as it is usually more efficient and performs faster.
• If you find that SQL Server uses a TABLE SCAN instead of an INDEX SEEK when you use an IN/OR clause as part of your WHERE clause, even when those columns are covered by an index, consider using an index hint to force the Query Optimizer to use the index.
• If you use LIKE in your WHERE clause, try to use one or more leading characters in the clause, if possible. For example, use:
Collapse Copy Code
LIKE 'm%' instead of LIKE ‘%m’
• If your application needs to retrieve summary data often, but you don't want to have the overhead of calculating it on the fly every time it is needed, consider using a trigger that updates summary values after each transaction into a summary table.
• When you have a choice of using the IN or the BETWEEN clauses in your Transact-SQL, you will generally want to use the BETWEEN clause, as it is much more efficient. For example...
Collapse Copy Code
SELECT task_id, task_name
FROM tasks
WHERE task_id in (1000, 1001, 1002, 1003, 1004)
...is much less efficient than this:
Collapse Copy Code
SELECT task_id, task_name
FROM tasks
WHERE task_id BETWEEN 1000 and 1004
• If possible, try to avoid using the SUBSTRING function in your WHERE clauses. Depending on how it is constructed, using the SUBSTRING function can force a table scan instead of allowing the optimizer to use an index (assuming there is one). If the substring you are searching for does not include the first character of the column you are searching for, then a table scan is performed.
• If possible, you should avoid using the SUBSTRING function and use the LIKE condition instead for better performance. Instead of doing this:
Collapse Copy Code
WHERE SUBSTRING(task_name,1,1) = 'b'
Try using this instead:
Collapse Copy Code
WHERE task_name LIKE 'b%'
• Avoid using optimizer hints in your WHERE clauses. This is because it is generally very hard to out-guess the Query Optimizer. Optimizer hints are special keywords that you include with your query to force how the Query Optimizer runs. If you decide to include a hint in a query, this forces the Query Optimizer to become static, preventing the Query Optimizer from dynamically adapting to the current environment for the given query. More often than not, this hurts -- not helps -- performance.
• If you have a WHERE clause that includes expressions connected by two or more AND operators, SQL Server will evaluate them from left to right in the order they are written. This assumes that no parentheses have been used to change the order of execution. Because of this, you may want to consider one of the following when using AND:
• Locate the least likely true AND expression first.
• If both parts of an AND expression are equally likely of being false, put the least complex AND expression first.
• You may want to consider using Query Analyzer or Management Studio to look at the execution plans of your queries to see which is best for your situation
• Don't use ORDER BY in your SELECT statements unless you really need to, as it adds a lot of extra overhead. For example, perhaps it may be more efficient to sort the data at the client than at the server.
• Whenever SQL Server has to perform a sorting operation, additional resources have to be used to perform this task. Sorting often occurs when any of the following Transact-SQL statements are executed:
•ORDER BY
•GROUP BY
•SELECT DISTINCT
•UNION
• If you have to sort by a particular column often, consider making that column a clustered index. This is because the data is already presorted for you and SQL Server is smart enough not to resort the data.
• If your WHERE clause includes an IN operator along with a list of values to be tested in the query, order the list of values so that the most frequently found ones are placed at the start of the list and the less frequently found ones are placed at the end of the list. This can speed up performance because the IN option returns true as soon as any of the values in the list produce a match. The sooner the match is made, the faster the query completes.
• If your application performs many wildcard (LIKE %) text searches on CHAR or VARCHAR columns, consider using SQL Server's full-text search option. The Search Service can significantly speed up wildcard searches of text stored in a database.
• The GROUP BY clause can be used with or without an aggregate function. However, if you want optimum performance, don't use the GROUP BY clause without an aggregate function. This is because you can accomplish the same end result by using the DISTINCT option instead, and it is faster. For example, you could write your query two different ways:
Collapse Copy Code
SELECT task_id
FROM tasks
WHERE task_id BETWEEN 10 AND 20
GROUP BY OrderID
...or:
Collapse Copy Code
SELECT DISTINCT task_id
FROM tasks
WHERE task_id BETWEEN 10 AND 20
• It is important to design applications that keep transactions as short as possible. This reduces locking and increases application concurrently, which helps to boost performance.
• In order to reduce network traffic between the client or middle-tier and SQL Server -- and also to boost your SQL Server-based application's performance -- only the data needed by the client or middle-tier should be returned by SQL Server. In other words, don't return more data (both rows and columns) from SQL Server than you need to the client or middle-tier and then further reduce the data to the data you really need at the client or middle-tier. This wastes SQL Server resources and network bandwidth.
• To make complex queries easier to analyze, consider breaking them down into their smaller constituent parts. One way to do this is to simply create lists of the key components of the query, such as:
•List all of the columns that are to be returned
•List all of the columns that are used in the WHERE clause
•List all of the columns used in the JOINs (if applicable)
•List all the tables used in JOINs (if applicable)
•Once you have the above information organized into this easy-to-comprehend form, it is much easier to identify those columns that could potentially make use of indexes when executed.
50 New Features of SQL Server 2008
50 New Features of SQL Server 2008
•Transparent Data Encryption
Enable encryption of an entire database, data files, or log files, without the need for application changes. Benefits of this include: Search encrypted data using both range and fuzzy searches, search secure data from unauthorized users, and data encryption without any required changes in existing applications.
•Extensible Key Management
SQL Server 2005 provides a comprehensive solution for encryption and key management. SQL Server 2008 delivers an excellent solution to this growing need by supporting third-party key management and HSM products.
•Auditing
Create and manage auditing via DDL, while simplifying compliance by providing more comprehensive data auditing. This enables organizations to answer common questions, such as, "What data was retrieved?"
•Enhanced Database Mirroring
SQL Server 2008 builds on SQL Server 2005 by providing a more reliable platform that has enhanced database mirroring, including automatic page repair, improved performance, and enhanced supportability.
•Automatic Recovery of Data Pages
SQL Server 2008 enables the principal and mirror machines to transparently recover from 823/824 types of data page errors by requesting a fresh copy of the suspect page from the mirroring partner transparently to end users and applications.
•Log Stream Compression
Database mirroring requires data transmissions between the participants of the mirroring implementations. With SQL Server 2008, compression of the outgoing log stream between the participants delivers optimal performance and minimizes the network bandwidth used by database mirroring.
•Resource Governor
Provide a consistent and predictable response to end users with the introduction of Resource Governor, allowing organizations to define resource limits and priorities for different workloads, which enable concurrent workloads to provide consistent performance to their end users.
•Predictable Query Performance
Enable greater query performance stability and predictability by providing functionality to lock down query plans, enabling organizations to promote stable query plans across hardware server replacements, server upgrades, and production deployments.
•Data Compression
Enable data to be stored more effectively, and reduce the storage requirements for your data. Data compression also provides significant performance improvements for large I/O bound workloads, like data warehousing.
•Hot Add CPU
Dynamically scale a database on demand by allowing CPU resources to be added to SQL Server 2008 on supported hardware platforms without forcing any downtime on applications. Note that SQL Server already supports the ability to add memory resources online.
•Policy-Based Management
Policy-Based Management is a policy-based system for managing one or more instances of SQL Server 2008. Use this with SQL Server Management Studio to create policies that manage entities on the server, such as the instance of SQL Server, databases, and other SQL Server objects.
•Streamlined Installation
SQL Server 2008 introduces significant improvements to the service life cycle for SQL Server through the re-engineering of the installation, setup, and configuration architecture. These improvements separate the installation of the physical bits on the hardware from the configuration of the SQL Server software, enabling organizations and software partners to provide recommended installation configurations.
•Performance Data Collection
Performance tuning and troubleshooting are time-consuming tasks for the administrator. To provide actionable performance insights to administrators, SQL Server 2008 includes more extensive performance data collection, a new centralized data repository for storing performance data, and new tools for reporting and monitoring.
•Language Integrated Query (LINQ)
Enable developers to issue queries against data, using a managed programming language, such as C# or VB.NET, instead of SQL statements. Enable seamless, strongly typed, set-oriented queries written in .NET languages to run against ADO.NET (LINQ to SQL), ADO.NET DataSets (LINQ to DataSets), the ADO.NET Entity Framework (LINQ to Entities), and to the Entity Data Service Mapping provider. Use the new LINQ to SQL provider that enables developers to use LINQ directly on SQL Server 2008 tables and columns.
•ADO.NET Data Services
The Object Services layer of ADO.NET enables the materialization, change tracking, and persistence of data as CLR objects. Developers using the ADO.NET framework can program against a database, using CLR objects that are managed by ADO.NET. SQL Server 2008 introduces more efficient, optimized support that improves performance and simplifies development.
•DATE/TIME
SQL Server 2008 introduces new date and time data types:
oDATE—A date-only type
oTIME—A time-only type
oDATETIMEOFFSET—A time-zone-aware datetime type
oDATETIME2—A datetime type with larger fractional seconds and year range than the existing DATETIME type
The new data types enable applications to have separate data and time types while providing large data ranges or user defined precision for time values.
•HIERARCHY ID
Enable database applications to model tree structures in a more efficient way than currently possible. New system type HierarchyId can store values that represent nodes in a hierarchy tree. This new type will be implemented as a CLR UDT, and will expose several efficient and useful built-in methods for creating and operating on hierarchy nodes with a flexible programming model.
•FILESTREAM Data
Allow large binary data to be stored directly in an NTFS file system, while preserving an integral part of the database and maintaining transactional consistency. Enable the scale-out of large binary data traditionally managed by the database to be stored outside the database on more cost-effective storage without compromise.
•Integrated Full Text Search
Integrated Full Text Search makes the transition between Text Search and relational data seamless, while enabling users to use the Text Indexes to perform high-speed text searches on large text columns.
•Sparse Columns
NULL data consumes no physical space, providing a highly efficient way of managing empty data in a database. For example, Sparse Columns allows object models that typically have numerous null values to be stored in a SQL Server 2005 database without experiencing large space costs.
•Large User-Defined Types
SQL Server 2008 eliminates the 8-KB limit for User-Defined Types (UDTs), allowing users to dramatically expand the size of their UDTs.
•Spatial Data Types
Build spatial capabilities into your applications by using the support for spatial data.
oImplement Round Earth solutions with the geography data type. Use latitude and longitude coordinates to define areas on the Earth's surface.
oImplement Flat Earth solutions with the geometry data type. Store polygons, points, and lines that are associated with projected planar surfaces and naturally planar data, such as interior spaces.
•Backup Compression
Keeping disk-based backups online is expensive and time-consuming. With SQL Server 2008 backup compression, less storage is required to keep backups online, and backups run significantly faster since less disk I/O is required.
•Partitioned Table Parallelism
Partitions enable organizations to manage large growing tables more effectively by transparently breaking them into manageable blocks of data. SQL Server 2008 builds on the advances of partitioning in SQL Server 2005 by improving the performance on large partitioned tables.
•Star Join Query Optimizations
SQL Server 2008 provides improved query performance for common data warehouse scenarios. Star Join Query optimizations reduce query response time by recognizing data warehouse join patterns.
•Grouping Sets
Grouping Sets is an extension to the GROUP BY clause that lets users define multiple groupings in the same query. Grouping Sets produces a single result set that is equivalent to a UNION ALL of differently grouped rows, making aggregation querying and reporting easier and faster.
•Change Data Capture
With Change Data Capture, changes are captured and placed in change tables. It captures complete content of changes, maintains cross-table consistency, and even works across schema changes. This enables organizations to integrate the latest information into the data warehouse.
•MERGE SQL Statement
With the introduction of the MERGE SQL Statement, developers can more effectively handle common data warehousing scenarios, like checking whether a row exists, and then executing an insert or update.
•SQL Server Integration Services (SSIS) Pipeline Improvements
Data Integration packages can now scale more effectively, making use of available resources and managing the largest enterprise-scale workloads. The new design improves the scalability of runtime into multiple processors.
•SQL Server Integration Services (SSIS) Persistent Lookups
The need to perform lookups is one of the most common ETL operations. This is especially prevalent in data warehousing, where fact records need to use lookups to transform business keys to their corresponding surrogates. SSIS increases the performance of lookups to support the largest tables.
•Analysis Scale and Performance
SQL Server 2008 drives broader analysis with enhanced analytical capabilities and with more complex computations and aggregations. New cube design tools help users streamline the development of the analysis infrastructure enabling them to build solutions for optimized performance.
•Block Computations
Block Computations provides a significant improvement in processing performance enabling users to increase the depth of their hierarchies and complexity of the computations.
•Writeback
New MOLAP enabled writeback capabilities in SQL Server 2008 Analysis Services removes the need to query ROLAP partitions. This provides users with enhanced writeback scenarios from within analytical applications without sacrificing the traditional OLAP performance.
•Enterprise Reporting Engine
Reports can easily be delivered throughout the organization, both internally and externally, with simplified deployment and configuration. This enables users to easily create and share reports of any size and complexity.
•Internet Report Deployment
Customers and suppliers can effortlessly be reached by deploying reports over the Internet.
•Manage Reporting Infrastructure
Increase supportability and the ability to control server behaviour with memory management, infrastructure consolidation, and easier configuration through a centralized store and API for all configuration settings.
•Report Builder Enhancements
Easily build ad-hoc and author reports with any structure through Report Designer.
•Forms Authentication Support
Support for Forms authentication enables users to choose between Windows and Forms authentication.
•Report Server Application Embedding
Report Server application embedding enables the URLs in reports and subscriptions to point back to front-end applications.
•Microsoft Office Integration
SQL Server 2008 provides new Word rendering that enables users to consume reports directly from within Microsoft Office Word. In addition, the existing Excel renderer has been greatly enhanced to accommodate the support of features, like nested data regions, sub-reports, as well as merged cell improvements. This lets users maintain layout fidelity and improves the overall consumption of reports from Microsoft Office applications.
•Predictive Analysis
SQL Server Analysis Services continues to deliver advanced data mining technologies. Better Time Series support extends forecasting capabilities. Enhanced Mining Structures deliver more flexibility to perform focused analysis through filtering as well as to deliver complete information in reports beyond the scope of the mining model. New cross-validation enables confirmation of both accuracy and stability for results that you can trust. Furthermore, the new features delivered with SQL Server 2008 Data Mining Add-ins for Office 2007 empower every user in the organization with even more actionable insight at the desktop.
•Transparent Data Encryption
Enable encryption of an entire database, data files, or log files, without the need for application changes. Benefits of this include: Search encrypted data using both range and fuzzy searches, search secure data from unauthorized users, and data encryption without any required changes in existing applications.
•Extensible Key Management
SQL Server 2005 provides a comprehensive solution for encryption and key management. SQL Server 2008 delivers an excellent solution to this growing need by supporting third-party key management and HSM products.
•Auditing
Create and manage auditing via DDL, while simplifying compliance by providing more comprehensive data auditing. This enables organizations to answer common questions, such as, "What data was retrieved?"
•Enhanced Database Mirroring
SQL Server 2008 builds on SQL Server 2005 by providing a more reliable platform that has enhanced database mirroring, including automatic page repair, improved performance, and enhanced supportability.
•Automatic Recovery of Data Pages
SQL Server 2008 enables the principal and mirror machines to transparently recover from 823/824 types of data page errors by requesting a fresh copy of the suspect page from the mirroring partner transparently to end users and applications.
•Log Stream Compression
Database mirroring requires data transmissions between the participants of the mirroring implementations. With SQL Server 2008, compression of the outgoing log stream between the participants delivers optimal performance and minimizes the network bandwidth used by database mirroring.
•Resource Governor
Provide a consistent and predictable response to end users with the introduction of Resource Governor, allowing organizations to define resource limits and priorities for different workloads, which enable concurrent workloads to provide consistent performance to their end users.
•Predictable Query Performance
Enable greater query performance stability and predictability by providing functionality to lock down query plans, enabling organizations to promote stable query plans across hardware server replacements, server upgrades, and production deployments.
•Data Compression
Enable data to be stored more effectively, and reduce the storage requirements for your data. Data compression also provides significant performance improvements for large I/O bound workloads, like data warehousing.
•Hot Add CPU
Dynamically scale a database on demand by allowing CPU resources to be added to SQL Server 2008 on supported hardware platforms without forcing any downtime on applications. Note that SQL Server already supports the ability to add memory resources online.
•Policy-Based Management
Policy-Based Management is a policy-based system for managing one or more instances of SQL Server 2008. Use this with SQL Server Management Studio to create policies that manage entities on the server, such as the instance of SQL Server, databases, and other SQL Server objects.
•Streamlined Installation
SQL Server 2008 introduces significant improvements to the service life cycle for SQL Server through the re-engineering of the installation, setup, and configuration architecture. These improvements separate the installation of the physical bits on the hardware from the configuration of the SQL Server software, enabling organizations and software partners to provide recommended installation configurations.
•Performance Data Collection
Performance tuning and troubleshooting are time-consuming tasks for the administrator. To provide actionable performance insights to administrators, SQL Server 2008 includes more extensive performance data collection, a new centralized data repository for storing performance data, and new tools for reporting and monitoring.
•Language Integrated Query (LINQ)
Enable developers to issue queries against data, using a managed programming language, such as C# or VB.NET, instead of SQL statements. Enable seamless, strongly typed, set-oriented queries written in .NET languages to run against ADO.NET (LINQ to SQL), ADO.NET DataSets (LINQ to DataSets), the ADO.NET Entity Framework (LINQ to Entities), and to the Entity Data Service Mapping provider. Use the new LINQ to SQL provider that enables developers to use LINQ directly on SQL Server 2008 tables and columns.
•ADO.NET Data Services
The Object Services layer of ADO.NET enables the materialization, change tracking, and persistence of data as CLR objects. Developers using the ADO.NET framework can program against a database, using CLR objects that are managed by ADO.NET. SQL Server 2008 introduces more efficient, optimized support that improves performance and simplifies development.
•DATE/TIME
SQL Server 2008 introduces new date and time data types:
oDATE—A date-only type
oTIME—A time-only type
oDATETIMEOFFSET—A time-zone-aware datetime type
oDATETIME2—A datetime type with larger fractional seconds and year range than the existing DATETIME type
The new data types enable applications to have separate data and time types while providing large data ranges or user defined precision for time values.
•HIERARCHY ID
Enable database applications to model tree structures in a more efficient way than currently possible. New system type HierarchyId can store values that represent nodes in a hierarchy tree. This new type will be implemented as a CLR UDT, and will expose several efficient and useful built-in methods for creating and operating on hierarchy nodes with a flexible programming model.
•FILESTREAM Data
Allow large binary data to be stored directly in an NTFS file system, while preserving an integral part of the database and maintaining transactional consistency. Enable the scale-out of large binary data traditionally managed by the database to be stored outside the database on more cost-effective storage without compromise.
•Integrated Full Text Search
Integrated Full Text Search makes the transition between Text Search and relational data seamless, while enabling users to use the Text Indexes to perform high-speed text searches on large text columns.
•Sparse Columns
NULL data consumes no physical space, providing a highly efficient way of managing empty data in a database. For example, Sparse Columns allows object models that typically have numerous null values to be stored in a SQL Server 2005 database without experiencing large space costs.
•Large User-Defined Types
SQL Server 2008 eliminates the 8-KB limit for User-Defined Types (UDTs), allowing users to dramatically expand the size of their UDTs.
•Spatial Data Types
Build spatial capabilities into your applications by using the support for spatial data.
oImplement Round Earth solutions with the geography data type. Use latitude and longitude coordinates to define areas on the Earth's surface.
oImplement Flat Earth solutions with the geometry data type. Store polygons, points, and lines that are associated with projected planar surfaces and naturally planar data, such as interior spaces.
•Backup Compression
Keeping disk-based backups online is expensive and time-consuming. With SQL Server 2008 backup compression, less storage is required to keep backups online, and backups run significantly faster since less disk I/O is required.
•Partitioned Table Parallelism
Partitions enable organizations to manage large growing tables more effectively by transparently breaking them into manageable blocks of data. SQL Server 2008 builds on the advances of partitioning in SQL Server 2005 by improving the performance on large partitioned tables.
•Star Join Query Optimizations
SQL Server 2008 provides improved query performance for common data warehouse scenarios. Star Join Query optimizations reduce query response time by recognizing data warehouse join patterns.
•Grouping Sets
Grouping Sets is an extension to the GROUP BY clause that lets users define multiple groupings in the same query. Grouping Sets produces a single result set that is equivalent to a UNION ALL of differently grouped rows, making aggregation querying and reporting easier and faster.
•Change Data Capture
With Change Data Capture, changes are captured and placed in change tables. It captures complete content of changes, maintains cross-table consistency, and even works across schema changes. This enables organizations to integrate the latest information into the data warehouse.
•MERGE SQL Statement
With the introduction of the MERGE SQL Statement, developers can more effectively handle common data warehousing scenarios, like checking whether a row exists, and then executing an insert or update.
•SQL Server Integration Services (SSIS) Pipeline Improvements
Data Integration packages can now scale more effectively, making use of available resources and managing the largest enterprise-scale workloads. The new design improves the scalability of runtime into multiple processors.
•SQL Server Integration Services (SSIS) Persistent Lookups
The need to perform lookups is one of the most common ETL operations. This is especially prevalent in data warehousing, where fact records need to use lookups to transform business keys to their corresponding surrogates. SSIS increases the performance of lookups to support the largest tables.
•Analysis Scale and Performance
SQL Server 2008 drives broader analysis with enhanced analytical capabilities and with more complex computations and aggregations. New cube design tools help users streamline the development of the analysis infrastructure enabling them to build solutions for optimized performance.
•Block Computations
Block Computations provides a significant improvement in processing performance enabling users to increase the depth of their hierarchies and complexity of the computations.
•Writeback
New MOLAP enabled writeback capabilities in SQL Server 2008 Analysis Services removes the need to query ROLAP partitions. This provides users with enhanced writeback scenarios from within analytical applications without sacrificing the traditional OLAP performance.
•Enterprise Reporting Engine
Reports can easily be delivered throughout the organization, both internally and externally, with simplified deployment and configuration. This enables users to easily create and share reports of any size and complexity.
•Internet Report Deployment
Customers and suppliers can effortlessly be reached by deploying reports over the Internet.
•Manage Reporting Infrastructure
Increase supportability and the ability to control server behaviour with memory management, infrastructure consolidation, and easier configuration through a centralized store and API for all configuration settings.
•Report Builder Enhancements
Easily build ad-hoc and author reports with any structure through Report Designer.
•Forms Authentication Support
Support for Forms authentication enables users to choose between Windows and Forms authentication.
•Report Server Application Embedding
Report Server application embedding enables the URLs in reports and subscriptions to point back to front-end applications.
•Microsoft Office Integration
SQL Server 2008 provides new Word rendering that enables users to consume reports directly from within Microsoft Office Word. In addition, the existing Excel renderer has been greatly enhanced to accommodate the support of features, like nested data regions, sub-reports, as well as merged cell improvements. This lets users maintain layout fidelity and improves the overall consumption of reports from Microsoft Office applications.
•Predictive Analysis
SQL Server Analysis Services continues to deliver advanced data mining technologies. Better Time Series support extends forecasting capabilities. Enhanced Mining Structures deliver more flexibility to perform focused analysis through filtering as well as to deliver complete information in reports beyond the scope of the mining model. New cross-validation enables confirmation of both accuracy and stability for results that you can trust. Furthermore, the new features delivered with SQL Server 2008 Data Mining Add-ins for Office 2007 empower every user in the organization with even more actionable insight at the desktop.
COALESCE (Transact-SQL)
COALESCE (Transact-SQL)
Returns the first nonnull expression among its arguments.
Return Types
Returns the data type of expression with the highest data type precedence.
If all expressions are nonnullable, the result is typed as nonnullable.
Remarks
If all arguments are NULL, COALESCE returns NULL.
Note Note
At least one of the null values must be a typed NULL.
COALESCE(expression1,...n) is equivalent to the following CASE expression:
CASE
WHEN (expression1 IS NOT NULL) THEN expression1
WHEN (expression2 IS NOT NULL) THEN expression2
...
ELSE expressionN
END
ISNULL and COALESCE though equivalent, can behave differently. An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL. In SQL Server, to index expressions involving COALESCE with non-null parameters, the computed column can be persisted using the PERSISTED column attribute as in the following statement:
Copy
CREATE TABLE #CheckSumTest
(
ID int identity ,
Num int DEFAULT ( RAND() * 100 ) ,
RowCheckSum AS COALESCE( CHECKSUM( id , num ) , 0 ) PERSISTED PRIMARY KEY
);
Returns the first nonnull expression among its arguments.
Return Types
Returns the data type of expression with the highest data type precedence.
If all expressions are nonnullable, the result is typed as nonnullable.
Remarks
If all arguments are NULL, COALESCE returns NULL.
Note Note
At least one of the null values must be a typed NULL.
COALESCE(expression1,...n) is equivalent to the following CASE expression:
CASE
WHEN (expression1 IS NOT NULL) THEN expression1
WHEN (expression2 IS NOT NULL) THEN expression2
...
ELSE expressionN
END
ISNULL and COALESCE though equivalent, can behave differently. An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL. In SQL Server, to index expressions involving COALESCE with non-null parameters, the computed column can be persisted using the PERSISTED column attribute as in the following statement:
Copy
CREATE TABLE #CheckSumTest
(
ID int identity ,
Num int DEFAULT ( RAND() * 100 ) ,
RowCheckSum AS COALESCE( CHECKSUM( id , num ) , 0 ) PERSISTED PRIMARY KEY
);
SET NOCOUNT { ON | OFF } in DOT NET
SET NOCOUNT { ON | OFF } in DOT NET
Stops the message indicating the number of rows affected by a Transact-SQL statement from being returned
as part of the results.
Syntax
SET NOCOUNT { ON | OFF }
Remarks
When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement)
is not returned. When SET NOCOUNT is OFF, the count is returned.
The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.
SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the client for each statement in a
stored procedure. When using the utilities provided with Microsoft® SQL Server™ to execute queries,
the results prevent "nn rows affected" from being displayed at the end Transact-SQL statements such
as SELECT, INSERT, UPDATE, and DELETE.
For stored procedures that contain several statements that do not return much actual data, this can
provide a significant performance boost because network traffic is greatly reduced.
Stops the message indicating the number of rows affected by a Transact-SQL statement from being returned
as part of the results.
Syntax
SET NOCOUNT { ON | OFF }
Remarks
When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement)
is not returned. When SET NOCOUNT is OFF, the count is returned.
The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.
SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the client for each statement in a
stored procedure. When using the utilities provided with Microsoft® SQL Server™ to execute queries,
the results prevent "nn rows affected" from being displayed at the end Transact-SQL statements such
as SELECT, INSERT, UPDATE, and DELETE.
For stored procedures that contain several statements that do not return much actual data, this can
provide a significant performance boost because network traffic is greatly reduced.
4 December 2010
DELEGATES IN C#
DELEGATES IN C#
A delegate type represents references to methods with a particular parameter list and return type.
Delegates make it possible to treat methods as entities that can be assigned to variables and passed as parameters.
Delegates are similar to the concept of function pointers found in some other languages, but unlike function pointers, delegates are object-oriented and type-safe.
The following example declares and uses a delegate type named Function.
using System;
delegate double Function(double x);
class Multiplier
{
double factor;
public Multiplier(double factor) {
this.factor = factor;
}
public double Multiply(double x) {
return x * factor;
}
}
class Test
{
static double Square(double x) {
return x * x;
}
static double[] Apply(double[] a, Function f) {
double[] result = new double[a.Length];
for (int i = 0; i < a.Length; i++) result[i] = f(a[i]);
return result;
}
static void Main() {
double[] a = {0.0, 0.5, 1.0};
double[] squares = Apply(a, Square);
double[] sines = Apply(a, Math.Sin);
Multiplier m = new Multiplier(2.0);
double[] doubles = Apply(a, m.Multiply);
}
}
An instance of the Function delegate type can reference any method that takes a double argument and returns
a double value. The Apply method applies a given Function to the elements of a double[], returning a double[]
with the results. In the Main method, Apply is used to apply three different functions to a double[].
A delegate can reference either a static method (such as Square or Math.Sin in the previous example) or an instance method (such as m.Multiply in the previous example). A delegate that references an instance method also references a particular object, and when the instance method is invoked through the delegate, that object becomes this in the invocation.Delegates can also be created using anonymous functions,which are “inline methods” that are created on the fly. Anonymous functions can see the local variables
of the sourrounding methods. Thus, the multiplier example above can be written more easily without using a Multiplier class:
double[] doubles = Apply(a, (double x) => x * 2.0);
An interesting and useful property of a delegate is that it does not know or care about the class
of the method it references; all that matters is that the referenced method has the same parameters and
return type as the delegate.
A delegate type represents references to methods with a particular parameter list and return type.
Delegates make it possible to treat methods as entities that can be assigned to variables and passed as parameters.
Delegates are similar to the concept of function pointers found in some other languages, but unlike function pointers, delegates are object-oriented and type-safe.
The following example declares and uses a delegate type named Function.
using System;
delegate double Function(double x);
class Multiplier
{
double factor;
public Multiplier(double factor) {
this.factor = factor;
}
public double Multiply(double x) {
return x * factor;
}
}
class Test
{
static double Square(double x) {
return x * x;
}
static double[] Apply(double[] a, Function f) {
double[] result = new double[a.Length];
for (int i = 0; i < a.Length; i++) result[i] = f(a[i]);
return result;
}
static void Main() {
double[] a = {0.0, 0.5, 1.0};
double[] squares = Apply(a, Square);
double[] sines = Apply(a, Math.Sin);
Multiplier m = new Multiplier(2.0);
double[] doubles = Apply(a, m.Multiply);
}
}
An instance of the Function delegate type can reference any method that takes a double argument and returns
a double value. The Apply method applies a given Function to the elements of a double[], returning a double[]
with the results. In the Main method, Apply is used to apply three different functions to a double[].
A delegate can reference either a static method (such as Square or Math.Sin in the previous example) or an instance method (such as m.Multiply in the previous example). A delegate that references an instance method also references a particular object, and when the instance method is invoked through the delegate, that object becomes this in the invocation.Delegates can also be created using anonymous functions,which are “inline methods” that are created on the fly. Anonymous functions can see the local variables
of the sourrounding methods. Thus, the multiplier example above can be written more easily without using a Multiplier class:
double[] doubles = Apply(a, (double x) => x * 2.0);
An interesting and useful property of a delegate is that it does not know or care about the class
of the method it references; all that matters is that the referenced method has the same parameters and
return type as the delegate.
INDEXERS in C#
INDEXERS in C#
An indexer is a member that enables objects to be indexed in the same way as an array. An indexer is declared like a property except that the name of the member is this followed by a parameter list written between the delimiters [ and ]. The parameters are available in the accessor(s) of the indexer. Similar to properties, indexers can be read-write, read-only, and write-only, and the accessor(s) of an indexer can be virtual.
The List class declares a single read-write indexer that takes an int parameter. The indexer makes it possible to index List instances with int values. For example
List names = new List();
names.Add("Liz");
names.Add("Martha");
names.Add("Beth");
for (int i = 0; i < names.Count; i++) {
string s = names[i];
names[i] = s.ToUpper();
}
Indexers can be overloaded, meaning that a class can declare multiple indexers as long as the number or types of their parameters differ.
An indexer is a member that enables objects to be indexed in the same way as an array. An indexer is declared like a property except that the name of the member is this followed by a parameter list written between the delimiters [ and ]. The parameters are available in the accessor(s) of the indexer. Similar to properties, indexers can be read-write, read-only, and write-only, and the accessor(s) of an indexer can be virtual.
The List class declares a single read-write indexer that takes an int parameter. The indexer makes it possible to index List instances with int values. For example
List
names.Add("Liz");
names.Add("Martha");
names.Add("Beth");
for (int i = 0; i < names.Count; i++) {
string s = names[i];
names[i] = s.ToUpper();
}
Indexers can be overloaded, meaning that a class can declare multiple indexers as long as the number or types of their parameters differ.
OUTPUT PARAMETER IN C#
OUTPUT PARAMETER IN C#
An output parameter is used for output parameter passing. An output parameter is similar to a reference parameter except that the initial value of the caller-provided argument is unimportant. An output parameter is declared with the out modifier. The following example shows the use of out parameters.
using System;
class Test
{
static void Divide(int x, int y, out int result, out int remainder) {
result = x / y;
remainder = x % y;
}
static void Main() {
int res, rem;
Divide(10, 3, out res, out rem);
Console.WriteLine("{0} {1}", res, rem); // Outputs "3 1"
}
An output parameter is used for output parameter passing. An output parameter is similar to a reference parameter except that the initial value of the caller-provided argument is unimportant. An output parameter is declared with the out modifier. The following example shows the use of out parameters.
using System;
class Test
{
static void Divide(int x, int y, out int result, out int remainder) {
result = x / y;
remainder = x % y;
}
static void Main() {
int res, rem;
Divide(10, 3, out res, out rem);
Console.WriteLine("{0} {1}", res, rem); // Outputs "3 1"
}
15 November 2010
Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE
Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE
MERGE is a new feature that provides an efficient way to perform multiple DML operations.
One of the most important advantage of MERGE statement is all the data is read and processed only once. In previous versions three different statement has to be written to process three different activity (INSERT, UPDATE or DELETE), however using MERGE statement all update activity can be done in one pass of database table. This is quite an improvement in performance of database query.
Consider there are two tables StudentDetails and StudentTotalMarks
In our example we will consider three main conditions while we merge this two tables.
1. Delete the records whose marks are more than 250.
2. Update marks and add 25 to each as internals if records exist.
3. Insert the records if record does not exists
MERGE StudentTotalMarks AS stm
USING (SELECT StudentID,StudentName FROM StudentDetails) AS sd
ON stm.StudentID = sd.StudentID
WHEN MATCHED AND stm.StudentMarks > 250 THEN DELETE
WHEN MATCHED THEN UPDATE SET stm.StudentMarks = stm.StudentMarks + 25
WHEN NOT MATCHED THEN
INSERT(StudentID,StudentMarks)
VALUES(sd.StudentID,25);
GO
There are two very important points to remember while using MERGE statement.
• Semicolon is mandatory after the merge statement.
• When there is a MATCH clause used along with some condition, it has to be specified first amongst all other WHEN MATCH clause.
MERGE is a new feature that provides an efficient way to perform multiple DML operations.
One of the most important advantage of MERGE statement is all the data is read and processed only once. In previous versions three different statement has to be written to process three different activity (INSERT, UPDATE or DELETE), however using MERGE statement all update activity can be done in one pass of database table. This is quite an improvement in performance of database query.
Consider there are two tables StudentDetails and StudentTotalMarks
In our example we will consider three main conditions while we merge this two tables.
1. Delete the records whose marks are more than 250.
2. Update marks and add 25 to each as internals if records exist.
3. Insert the records if record does not exists
MERGE StudentTotalMarks AS stm
USING (SELECT StudentID,StudentName FROM StudentDetails) AS sd
ON stm.StudentID = sd.StudentID
WHEN MATCHED AND stm.StudentMarks > 250 THEN DELETE
WHEN MATCHED THEN UPDATE SET stm.StudentMarks = stm.StudentMarks + 25
WHEN NOT MATCHED THEN
INSERT(StudentID,StudentMarks)
VALUES(sd.StudentID,25);
GO
There are two very important points to remember while using MERGE statement.
• Semicolon is mandatory after the merge statement.
• When there is a MATCH clause used along with some condition, it has to be specified first amongst all other WHEN MATCH clause.
Insert Multiple Rows in a single statement in Sql Server 2008
Insert Multiple Rows in a single statement in Sql Server 2008
In SQL Server 2008, This new feature enables the developer to insert multiple rows in a single SQL Statement.
insert into insertbulk (name,age) values ('arun',19),('prakash',23),('rajesh',22)
In SQL Server 2008, This new feature enables the developer to insert multiple rows in a single SQL Statement.
insert into insertbulk (name,age) values ('arun',19),('prakash',23),('rajesh',22)
Advantages Of Visual Studio 2008
Advantages Of Visual Studio 2008
1. LINQ with ASP.NET
Language-Integrated Query (LINQ) is a set of features in Visual Studio 2008 that extends powerful query capabilities to the language syntax of C# and Visual Basic. LINQ introduces standard, easily-learned patterns for querying and updating data, and the technology can be extended to support potentially any kind of data store. Visual Studio 2008 includes LINQ provider assemblies that enable the use of LINQ with .NET Framework collections, SQL Server databases, ADO.NET Datasets, and XML documents.
In Visual Studio you can write LINQ queries in Visual Basic or C# with SQL Server databases, XML documents, ADO.NET Datasets, and any collection of objects that supports IEnumerable or the generic IEnumerable interface. LINQ support for the ADO.NET Entity Framework is also planned, and LINQ providers are being written by third parties for many Web services and other database implementations.
asp:LinqDataSource
ContextTypeName="AdventureWorksDataContext"
TableName="Contacts"
Where="EmailPromotion=1"
ID="LinqDataSource1"
runat="server">
Getting Started with Standard Query Operators
To see language integrated query at work, we’ll begin with a simple C# 3.0 program that uses the standard query operators to process the contents of an array:
using System;
using System.Linq;
using System.Collections.Generic;
class app {
static void Main() {
string[] names = { "Burke", "Connor", "Frank",
"Everett", "Albert", "George",
"Harris", "David" };
IEnumerable query = from s in names
where s.Length == 5
orderby s
select s.ToUpper();
foreach (string item in query)
Console.WriteLine(item);
}
}
If you were to compile and run this program, you’d see this as output:
BURKE
DAVID
FRANK
2. Debug Expression Blend application in Visual Studio 2008
Microsoft Expression Blend 2 is a design tool for creating rich Windows Presentation Foundation applications for Microsoft Windows, and Microsoft Silverlight 1.0 applications for the web. Microsoft Visual Studio 2008, which is also used to build Microsoft Windows applications, can open, build, and debug Expression Blend 2 projects. If you are having trouble debugging your application using the Test (F5) feature of Expression Blend, you can use Visual Studio 2008 to obtain detailed error messages about runtime errors. Sometimes, you can fix runtime errors by trying out different changes in your XAML or code until you understand what is going on behind the scenes. However, it is faster to actually watch what is going on behind the scenes by stepping through your code line by line as the application is running.
3. WPF Windows Presentation Foundation
Windows Presentation Foundation (WPF) provides developers with a unified programming model for building rich Windows smart client user experiences that incorporate UI, media, and documents.
The core of WPF is a resolution-independent and vector-based rendering engine that is built to take advantage of modern graphics hardware. WPF extends the core with a comprehensive set of application-development features that include Extensible Application Markup Language (XAML), controls, data binding, layout, 2-D and 3-D graphics, animation, styles, templates, documents, media, text, and typography. WPF is included in the Microsoft .NET Framework, so you can build applications that incorporate other elements of the .NET Framework class library.
4. Visual Studio 2008 Multi Targeting Support
Earlier you were not able to working with .NET 1.1 applications directly in visual studio 2005. Now in Visual studio 2008 you are able to create, run, debug the .NET 2.0, .NET 3.0 and .NET 3.5 applications. You can also deploy .NET 2.0 applications in the machines which contains only .NET 2.0 not .NET 3.x.
5.Ajax support for ASP.NET
Previously developer has to install AJAX control library separately that does not come from VS, but now if you install Visual Studio 2008, you can built-in AJAX control library. This Ajax Library contains plenty of rich AJAX controls like Menu, TreeView, webparts and also these components support JSON and VS 2008 contains in built ASP.NET AJAX Control Extenders.
6. JavaScript Debugging Support
Since starting of web development all the developers got frustration with solving javascript errors. Debugging the error in javascript is very difficult. Now Visual Studio 2008 makes it is simpler with javascript debugging. You can set break points and run the javaScript step by step and you can watch the local variables when you were debugging the javascript and solution explorer provides javascript document navigation support.
7. Nested Master Page Support
Already Visual Studio 2005 supports nested master pages concept with .NET 2.0, but the problem with this Visual Studio 2005 that pages based on nested masters can't be edited using WYSIWYG web designer. But now in VS 2008 you can even edit the nested master pages.
8. LINQ Intellisense and Javascript Intellisense support for silverlight applications
Most happy part for .NET developers is Visual Studio 2008 contains intellisense support for javascript. Javascript Intellisense makes developers life easy when writing client side validation, AJAX applications and also when writing Silverlight applications
Intellisense Support: When we are writing the LINQ Query VS provides LINQ query syntax as tool tips.
9. Organize Imports or Usings:
We have Organize Imports feature already in Eclipse. SInce many days I have been waiting for this feature even in VS. Now VS contains Organize Imports feature which removes unnecessary namespaces which you have imported. You can select all the namespaces and right click on it, then you can get context menu with Organize imports options like "Remove Unused Usings", "Sort Usings", "Remove and Sort". Refactoring support for new .NET 3.x features like Anonymous types, Extension Methods, Lambda Expressions.
10. Intellisense Filtering:
Earlier in VS 2005 when we were typing with intellisense box all the items were being displayed. For example If we type the letter 'K' then intellisense takes you to the items starts with 'K' but also all other items will be presented in intellisense box. Now in VS 2008 if you press 'K' only the items starts with 'K' will be filtered and displayed.
11. Intellisense Box display position
Earlier in some cases when you were typing the an object name and pressing . (period) then intellisense was being displayed in the position of the object which you have typed. Here the code which we type will go back to the dropdown, in this case sometimes programmer may disturb to what he was typing. Now in VS 2008 If you hold the Ctrl key while the intellisense is dropping down then intellisense box will become semi-transparent mode.
12. Visual Studio 2008 Split View
VS 205 has a feature show both design and source code in single window. but both the windows tiles horizontally. In VS 2008 we can configure this split view feature to vertically, this allows developers to use maximum screen on laptops and wide-screen monitors.
Here one of the good feature is if you select any HTML or ASP markup text in source window automatically corresponding item will be selected in design window.
13. HTML JavaScript warnings, not as errors:
VS 2005 mixes HTML errors and C# and VB.NET errors and shows in one window. Now VS 2008 separates this and shows javascript and HTML errors as warnings. But this is configurable feature.
14. Debugging .NET Framework Library Source Code:
Now in VS 2008 you can debug the source code of .NET Framework Library methods. Lets say If you want to debug the DataBind() method of DataGrid control you can place a debugging point over there and continue with debug the source code of DataBind() method.
15. In built Silverlight Library
Earlier we used to install silverlight SDK separately, Now in VS 2008 it is inbuilt, with this you can create, debug and deploy the silverlight applications.
16. Visual Studio LINQ Designer
Already you know in VS 2005 we have inbuilt SQL Server IDE feature. by this you no need to use any other tools like SQL Server Query Analyzer and SQL Server Enterprise Manger. You have directly database explorer by this you can create connections to your database and you can view the tables and stored procedures in VS IDE itself. But now in VS 2008 it has View Designer window capability with LINQ-to-SQL.
1. LINQ with ASP.NET
Language-Integrated Query (LINQ) is a set of features in Visual Studio 2008 that extends powerful query capabilities to the language syntax of C# and Visual Basic. LINQ introduces standard, easily-learned patterns for querying and updating data, and the technology can be extended to support potentially any kind of data store. Visual Studio 2008 includes LINQ provider assemblies that enable the use of LINQ with .NET Framework collections, SQL Server databases, ADO.NET Datasets, and XML documents.
In Visual Studio you can write LINQ queries in Visual Basic or C# with SQL Server databases, XML documents, ADO.NET Datasets, and any collection of objects that supports IEnumerable or the generic IEnumerable
asp:LinqDataSource
ContextTypeName="AdventureWorksDataContext"
TableName="Contacts"
Where="EmailPromotion=1"
ID="LinqDataSource1"
runat="server">
Getting Started with Standard Query Operators
To see language integrated query at work, we’ll begin with a simple C# 3.0 program that uses the standard query operators to process the contents of an array:
using System;
using System.Linq;
using System.Collections.Generic;
class app {
static void Main() {
string[] names = { "Burke", "Connor", "Frank",
"Everett", "Albert", "George",
"Harris", "David" };
IEnumerable
where s.Length == 5
orderby s
select s.ToUpper();
foreach (string item in query)
Console.WriteLine(item);
}
}
If you were to compile and run this program, you’d see this as output:
BURKE
DAVID
FRANK
2. Debug Expression Blend application in Visual Studio 2008
Microsoft Expression Blend 2 is a design tool for creating rich Windows Presentation Foundation applications for Microsoft Windows, and Microsoft Silverlight 1.0 applications for the web. Microsoft Visual Studio 2008, which is also used to build Microsoft Windows applications, can open, build, and debug Expression Blend 2 projects. If you are having trouble debugging your application using the Test (F5) feature of Expression Blend, you can use Visual Studio 2008 to obtain detailed error messages about runtime errors. Sometimes, you can fix runtime errors by trying out different changes in your XAML or code until you understand what is going on behind the scenes. However, it is faster to actually watch what is going on behind the scenes by stepping through your code line by line as the application is running.
3. WPF Windows Presentation Foundation
Windows Presentation Foundation (WPF) provides developers with a unified programming model for building rich Windows smart client user experiences that incorporate UI, media, and documents.
The core of WPF is a resolution-independent and vector-based rendering engine that is built to take advantage of modern graphics hardware. WPF extends the core with a comprehensive set of application-development features that include Extensible Application Markup Language (XAML), controls, data binding, layout, 2-D and 3-D graphics, animation, styles, templates, documents, media, text, and typography. WPF is included in the Microsoft .NET Framework, so you can build applications that incorporate other elements of the .NET Framework class library.
4. Visual Studio 2008 Multi Targeting Support
Earlier you were not able to working with .NET 1.1 applications directly in visual studio 2005. Now in Visual studio 2008 you are able to create, run, debug the .NET 2.0, .NET 3.0 and .NET 3.5 applications. You can also deploy .NET 2.0 applications in the machines which contains only .NET 2.0 not .NET 3.x.
5.Ajax support for ASP.NET
Previously developer has to install AJAX control library separately that does not come from VS, but now if you install Visual Studio 2008, you can built-in AJAX control library. This Ajax Library contains plenty of rich AJAX controls like Menu, TreeView, webparts and also these components support JSON and VS 2008 contains in built ASP.NET AJAX Control Extenders.
6. JavaScript Debugging Support
Since starting of web development all the developers got frustration with solving javascript errors. Debugging the error in javascript is very difficult. Now Visual Studio 2008 makes it is simpler with javascript debugging. You can set break points and run the javaScript step by step and you can watch the local variables when you were debugging the javascript and solution explorer provides javascript document navigation support.
7. Nested Master Page Support
Already Visual Studio 2005 supports nested master pages concept with .NET 2.0, but the problem with this Visual Studio 2005 that pages based on nested masters can't be edited using WYSIWYG web designer. But now in VS 2008 you can even edit the nested master pages.
8. LINQ Intellisense and Javascript Intellisense support for silverlight applications
Most happy part for .NET developers is Visual Studio 2008 contains intellisense support for javascript. Javascript Intellisense makes developers life easy when writing client side validation, AJAX applications and also when writing Silverlight applications
Intellisense Support: When we are writing the LINQ Query VS provides LINQ query syntax as tool tips.
9. Organize Imports or Usings:
We have Organize Imports feature already in Eclipse. SInce many days I have been waiting for this feature even in VS. Now VS contains Organize Imports feature which removes unnecessary namespaces which you have imported. You can select all the namespaces and right click on it, then you can get context menu with Organize imports options like "Remove Unused Usings", "Sort Usings", "Remove and Sort". Refactoring support for new .NET 3.x features like Anonymous types, Extension Methods, Lambda Expressions.
10. Intellisense Filtering:
Earlier in VS 2005 when we were typing with intellisense box all the items were being displayed. For example If we type the letter 'K' then intellisense takes you to the items starts with 'K' but also all other items will be presented in intellisense box. Now in VS 2008 if you press 'K' only the items starts with 'K' will be filtered and displayed.
11. Intellisense Box display position
Earlier in some cases when you were typing the an object name and pressing . (period) then intellisense was being displayed in the position of the object which you have typed. Here the code which we type will go back to the dropdown, in this case sometimes programmer may disturb to what he was typing. Now in VS 2008 If you hold the Ctrl key while the intellisense is dropping down then intellisense box will become semi-transparent mode.
12. Visual Studio 2008 Split View
VS 205 has a feature show both design and source code in single window. but both the windows tiles horizontally. In VS 2008 we can configure this split view feature to vertically, this allows developers to use maximum screen on laptops and wide-screen monitors.
Here one of the good feature is if you select any HTML or ASP markup text in source window automatically corresponding item will be selected in design window.
13. HTML JavaScript warnings, not as errors:
VS 2005 mixes HTML errors and C# and VB.NET errors and shows in one window. Now VS 2008 separates this and shows javascript and HTML errors as warnings. But this is configurable feature.
14. Debugging .NET Framework Library Source Code:
Now in VS 2008 you can debug the source code of .NET Framework Library methods. Lets say If you want to debug the DataBind() method of DataGrid control you can place a debugging point over there and continue with debug the source code of DataBind() method.
15. In built Silverlight Library
Earlier we used to install silverlight SDK separately, Now in VS 2008 it is inbuilt, with this you can create, debug and deploy the silverlight applications.
16. Visual Studio LINQ Designer
Already you know in VS 2005 we have inbuilt SQL Server IDE feature. by this you no need to use any other tools like SQL Server Query Analyzer and SQL Server Enterprise Manger. You have directly database explorer by this you can create connections to your database and you can view the tables and stored procedures in VS IDE itself. But now in VS 2008 it has View Designer window capability with LINQ-to-SQL.
18 September 2010
SQL PRIMARY KEY
SQL PRIMARY KEY
The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain unique values.
A primary key column cannot contain NULL values.
SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)
SQL PRIMARY KEY Constraint on ALTER TABLE
To create a PRIMARY KEY constraint on the "P_Id" column when the table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)
To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
Note: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already have been declared to not contain NULL values (when the table was first created).
To DROP a PRIMARY KEY Constraint
To drop a PRIMARY KEY constraint, use the following SQL:
ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID
To see the Constraint of table use <> in sql server
The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain unique values.
A primary key column cannot contain NULL values.
SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)
SQL PRIMARY KEY Constraint on ALTER TABLE
To create a PRIMARY KEY constraint on the "P_Id" column when the table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)
To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
Note: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already have been declared to not contain NULL values (when the table was first created).
To DROP a PRIMARY KEY Constraint
To drop a PRIMARY KEY constraint, use the following SQL:
ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID
To see the Constraint of table use <
SQL FOREIGN KEY
SQL FOREIGN KEY
A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
The "Orders" table:
O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 2
4 24562 1
Note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the "Persons" table.
The "P_Id" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.
The "P_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.
The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
SQL Server / Oracle / MS Access:
CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)
To DROP a FOREIGN KEY Constraint
SQL Server / Oracle / MS Access:
ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders
SQL PRIMARY KEY
The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain unique values.
A primary key column cannot contain NULL values.
SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)
A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
The "Orders" table:
O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 2
4 24562 1
Note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the "Persons" table.
The "P_Id" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.
The "P_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.
The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
SQL Server / Oracle / MS Access:
CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)
To DROP a FOREIGN KEY Constraint
SQL Server / Oracle / MS Access:
ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders
SQL PRIMARY KEY
The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain unique values.
A primary key column cannot contain NULL values.
SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)
Meaning For Dotnet First line of Code
Meaning For Dotnet First line of Code
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
@ Page directive on top of page
Page Language="C#" Denotes C# language is used in this project
AutoEventWireup attribute defaults to true,
ASP.NET does not require that you explicitly bind event handlers to a page event such as Load.
When AutoEventWireup is false, you must explicitly bind the event to a method. For example, if you have a Page_Load method in the code for a page, the method will be called in response to the Load event only if you write code like that in the following example
Otherwise you have to bind the event explicitly
public partial class AutoEventWireupExample : System.Web.UI.Page
{
protected void Page_Load(object sender, System.EventArgs e)
{
Response.Write("Executing Page_Load");
}
override protected void OnInit(EventArgs e)
{
this.Load += new System.EventHandler(this.Page_Load);
}
}
The following code example shows how to set or read the AutoEventWireup property in code.
// Get the current AutoEventWireup property value.
Console.WriteLine(
"Current AutoEventWireup value: '{0}'",
pagesSection.AutoEventWireup);
// Set the AutoEventWireup property to false.
pagesSection.AutoEventWireup = false;
The following example shows the two forms of method signatures that are automatically attached to page events when AutoEventWireup is true.
protected void Page_Load(object sender, EventArgs e)
{
Response.Write("Hello world");
}
// This method will be automatically bound to the Load event
// when AutoEventWireup is true only if no overload having
// object and EventArgs parameters is found.
protected void Page_Load()
{
Response.Write("Hello world");
}
// Following are three alternative ways of binding an event
// handler to an event when AutoEventWireup is false. For
// any given event do this binding only once or the handler
// will be called multiple times.
// You can wire up events in the page's constructor.
public _Default()
{
Load += new EventHandler(Page_Load);
}
// You can override the OnInit event and wire up events there.
protected override void OnInit(EventArgs e)
{
base.OnInit(e);
Load += new EventHandler(Page_Load);
}
// Or you can override the event's OnEventname method and
// call your handler from there. You can also put the code
// execute when the event fires within the override method itself.
protected override void OnLoad(EventArgs e)
{
Page_Load(null, null);
base.OnLoad(e);
}
protected void Page_Load(object sender, EventArgs e)
{
Response.Write("Hello world");
}
Inherits="_Default"
the Inherits attribute tells the runtime the name of the class it will use as a base class for this web form,
look at the CodeFile (code-behind) file for the ASPX page.
using System;
using System.Web.UI;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
}
‘partial’ keyword.
Partial allows us to split a class definition across two or more source code files.
The partial keyword plays an important role because it will allow the runtime to extend the definition of our _Default class with additional members.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
@ Page directive on top of page
Page Language="C#" Denotes C# language is used in this project
AutoEventWireup attribute defaults to true,
ASP.NET does not require that you explicitly bind event handlers to a page event such as Load.
When AutoEventWireup is false, you must explicitly bind the event to a method. For example, if you have a Page_Load method in the code for a page, the method will be called in response to the Load event only if you write code like that in the following example
Otherwise you have to bind the event explicitly
public partial class AutoEventWireupExample : System.Web.UI.Page
{
protected void Page_Load(object sender, System.EventArgs e)
{
Response.Write("Executing Page_Load");
}
override protected void OnInit(EventArgs e)
{
this.Load += new System.EventHandler(this.Page_Load);
}
}
The following code example shows how to set or read the AutoEventWireup property in code.
// Get the current AutoEventWireup property value.
Console.WriteLine(
"Current AutoEventWireup value: '{0}'",
pagesSection.AutoEventWireup);
// Set the AutoEventWireup property to false.
pagesSection.AutoEventWireup = false;
The following example shows the two forms of method signatures that are automatically attached to page events when AutoEventWireup is true.
protected void Page_Load(object sender, EventArgs e)
{
Response.Write("Hello world");
}
// This method will be automatically bound to the Load event
// when AutoEventWireup is true only if no overload having
// object and EventArgs parameters is found.
protected void Page_Load()
{
Response.Write("Hello world");
}
// Following are three alternative ways of binding an event
// handler to an event when AutoEventWireup is false. For
// any given event do this binding only once or the handler
// will be called multiple times.
// You can wire up events in the page's constructor.
public _Default()
{
Load += new EventHandler(Page_Load);
}
// You can override the OnInit event and wire up events there.
protected override void OnInit(EventArgs e)
{
base.OnInit(e);
Load += new EventHandler(Page_Load);
}
// Or you can override the event's OnEventname method and
// call your handler from there. You can also put the code
// execute when the event fires within the override method itself.
protected override void OnLoad(EventArgs e)
{
Page_Load(null, null);
base.OnLoad(e);
}
protected void Page_Load(object sender, EventArgs e)
{
Response.Write("Hello world");
}
Inherits="_Default"
the Inherits attribute tells the runtime the name of the class it will use as a base class for this web form,
look at the CodeFile (code-behind) file for the ASPX page.
using System;
using System.Web.UI;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
}
‘partial’ keyword.
Partial allows us to split a class definition across two or more source code files.
The partial keyword plays an important role because it will allow the runtime to extend the definition of our _Default class with additional members.
Difference between Stored procedure vs User Functions in Sql server
Difference between Stored procedure vs User Functions in Sql server
• Procedure may return none or more values. Function must always return one value either a scalar value or a table.
• A user-defined function takes zero or more input parameters and returns either a scalar value or a table.
• Procedure have input,output parameters.Functions have only input parameters.
• Stored procedures are called independently by EXEC command whereas Functions are called from within SQL statement.
• Functions can be called from procedure. Procedures cannot be called from function.
• Exception can be handled in Procedure by try-catch block but try-catch block cannot be used in a function.(error-handling)
• Transaction management possible in procedure but not in function.
• Procedure may return none or more values. Function must always return one value either a scalar value or a table.
• A user-defined function takes zero or more input parameters and returns either a scalar value or a table.
• Procedure have input,output parameters.Functions have only input parameters.
• Stored procedures are called independently by EXEC command whereas Functions are called from within SQL statement.
• Functions can be called from procedure. Procedures cannot be called from function.
• Exception can be handled in Procedure by try-catch block but try-catch block cannot be used in a function.(error-handling)
• Transaction management possible in procedure but not in function.
Difference Between SCOPE_IDENTITY and ROWCOUNT in sql server
Difference Between SCOPE_IDENTITY and atatROWCOUNT in sql server
SCOPE_IDENTITY
Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.
ROWCOUNT"
Returns the number of rows affected by the last statement.
SCOPE_IDENTITY
Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.
ROWCOUNT"
Returns the number of rows affected by the last statement.
How to convert from string to date / datetime? in sql server
Execute the following T-SQL scripts in Microsoft SQL Server Manangement Studio Query Editor to demonstrate T-SQL convert and cast functions in transforming string date, string time & string datetime data to datetime data type. T-SQL date / datetime functions usage examples are presented as well.
-- SQL Server string to date / datetime conversion - datetime string format sql server
-- MSSQL string to datetime conversion - convert char to date - convert varchar to date
-- Subtract 100 from style number (format) for yy instead yyyy (or ccyy with century)
SELECT convert(datetime, 'Oct 23 2012 11:01AM', 100) -- mon dd yyyy hh:mmAM (or PM)
SELECT convert(datetime, 'Oct 23 2012 11:01AM') -- 2012-10-23 11:01:00.000
-- Without century (yy) string date conversion - convert string to datetime function
SELECT convert(datetime, 'Oct 23 12 11:01AM', 0) -- mon dd yy hh:mmAM (or PM)
SELECT convert(datetime, 'Oct 23 12 11:01AM') -- 2012-10-23 11:01:00.000
-- Convert string to datetime sql - convert string to date sql - sql dates format
-- T-SQL convert string to datetime - SQL Server convert string to date
SELECT convert(datetime, '10/23/2016', 101) -- mm/dd/yyyy
SELECT convert(datetime, '2016.10.23', 102) -- yyyy.mm.dd
SELECT convert(datetime, '23/10/2016', 103) -- dd/mm/yyyy
SELECT convert(datetime, '23.10.2016', 104) -- dd.mm.yyyy
SELECT convert(datetime, '23-10-2016', 105) -- dd-mm-yyyy
-- mon types are nondeterministic conversions, dependent on language setting
SELECT convert(datetime, '23 OCT 2016', 106) -- dd mon yyyy
SELECT convert(datetime, 'Oct 23, 2016', 107) -- mon dd, yyyy
-- 2016-10-23 00:00:00.000
SELECT convert(datetime, '20:10:44', 108) -- hh:mm:ss
-- 1900-01-01 20:10:44.000
-- mon dd yyyy hh:mm:ss:mmmAM (or PM) - sql time format - SQL Server datetime format
SELECT convert(datetime, 'Oct 23 2016 11:02:44:013AM', 109)
-- 2016-10-23 11:02:44.013
SELECT convert(datetime, '10-23-2016', 110) -- mm-dd-yyyy
SELECT convert(datetime, '2016/10/23', 111) -- yyyy/mm/dd
-- YYYYMMDD ISO date format works at any language setting - international standard
SELECT convert(datetime, '20161023')
SELECT convert(datetime, '20161023', 112) -- yyyymmdd
-- 2016-10-23 00:00:00.000
SELECT convert(datetime, '23 Oct 2016 11:02:07:577', 113) -- dd mon yyyy hh:mm:ss:mmm
-- 2016-10-23 11:02:07.577
SELECT convert(datetime, '20:10:25:300', 114) -- hh:mm:ss:mmm(24h)
-- 1900-01-01 20:10:25.300
SELECT convert(datetime, '2016-10-23 20:44:11', 120) -- yyyy-mm-dd hh:mm:ss(24h)
-- 2016-10-23 20:44:11.000
SELECT convert(datetime, '2016-10-23 20:44:11.500', 121) -- yyyy-mm-dd hh:mm:ss.mmm
-- 2016-10-23 20:44:11.500
-- Style 126 is ISO 8601 format: international standard - works with any language setting
SELECT convert(datetime, '2008-10-23T18:52:47.513', 126) -- yyyy-mm-ddThh:mm:ss(.mmm)
-- 2008-10-23 18:52:47.513
-- Convert DDMMYYYY format to datetime - sql server to date / datetime
SELECT convert(datetime, STUFF(STUFF('31012016',3,0,'-'),6,0,'-'), 105)
-- 2016-01-31 00:00:00.000
-- SQL Server T-SQL string to datetime conversion without century - some exceptions
-- nondeterministic means language setting dependent such as Mar/Mär/mars/márc
SELECT convert(datetime, 'Oct 23 16 11:02:44AM') -- Default
SELECT convert(datetime, '10/23/16', 1) -- mm/dd/yy U.S.
SELECT convert(datetime, '16.10.23', 2) -- yy.mm.dd ANSI
SELECT convert(datetime, '23/10/16', 3) -- dd/mm/yy UK/FR
SELECT convert(datetime, '23.10.16', 4) -- dd.mm.yy German
SELECT convert(datetime, '23-10-16', 5) -- dd-mm-yy Italian
SELECT convert(datetime, '23 OCT 16', 6) -- dd mon yy non-det.
SELECT convert(datetime, 'Oct 23, 16', 7) -- mon dd, yy non-det.
SELECT convert(datetime, '20:10:44', 8) -- hh:mm:ss
SELECT convert(datetime, 'Oct 23 16 11:02:44:013AM', 9) -- Default with msec
SELECT convert(datetime, '10-23-16', 10) -- mm-dd-yy U.S.
SELECT convert(datetime, '16/10/23', 11) -- yy/mm/dd Japan
SELECT convert(datetime, '161023', 12) -- yymmdd ISO
SELECT convert(datetime, '23 Oct 16 11:02:07:577', 13) -- dd mon yy hh:mm:ss:mmm EU dflt
SELECT convert(datetime, '20:10:25:300', 14) -- hh:mm:ss:mmm(24h)
SELECT convert(datetime, '2016-10-23 20:44:11',20) -- yyyy-mm-dd hh:mm:ss(24h) ODBC can.
SELECT convert(datetime, '2016-10-23 20:44:11.500', 21)-- yyyy-mm-dd hh:mm:ss.mmm ODBC
------------
-- SQL Datetime Data Type: Combine date & time string into datetime - sql hh mm ss
-- String to datetime - mssql datetime - sql convert date - sql concatenate string
DECLARE @DateTimeValue varchar(32), @DateValue char(8), @TimeValue char(6)
SELECT @DateValue = '20120718',
@TimeValue = '211920'
SELECT @DateTimeValue =
convert(varchar, convert(datetime, @DateValue), 111)
+ ' ' + substring(@TimeValue, 1, 2)
+ ':' + substring(@TimeValue, 3, 2)
+ ':' + substring(@TimeValue, 5, 2)
SELECT
DateInput = @DateValue, TimeInput = @TimeValue,
DateTimeOutput = @DateTimeValue;
/*
DateInput TimeInput DateTimeOutput
20120718 211920 2012/07/18 21:19:20
*/
/* Datetime 8 bytes internal storage structure
o 1st 4 bytes: number of days after the base date 1900-01-01
o 2nd 4 bytes: number of milliseconds since midnight */
-- SQL convert seconds to HH:MM:SS - sql times format - sql hh mm
DECLARE @Seconds INT
SET @Seconds = 20000
SELECT HH = @Seconds / 3600, MM = (@Seconds%3600) / 60, SS = (@Seconds%60)
/* HH MM SS
5 33 20 */
------------
-- SQL Server 2008 convert datetime to date - sql yyyy mm dd
SELECT TOP (3) OrderDate = CONVERT(date, OrderDate),
Today = CONVERT(date, getdate())
FROM AdventureWorks2008.Sales.SalesOrderHeader
ORDER BY newid();
/* OrderDate Today
2003-07-09 2012-06-18
2003-09-26 2012-06-18
2004-02-15 2012-06-18 */
------------
-- SQL date yyyy mm dd - sqlserver yyyy mm dd - date format yyyymmdd
SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]
/* YYYY/MM/DD
2015/07/11 */
SELECT CONVERT(VARCHAR(10), GETDATE(), 112) AS [YYYYMMDD]
/* YYYYMMDD
20150711 */
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111),'/',' ') AS [YYYY MM DD]
/* YYYY MM DD
2015 07 11 */
-- Converting to special (non-standard) date fomats: DD-MMM-YY
SELECT UPPER(REPLACE(CONVERT(VARCHAR,GETDATE(),6),' ','-'))
-- 07-MAR-14
------------
-- SQL convert date string to datetime - time set to 00:00:00.000 or 12:00AM
PRINT CONVERT(datetime,'07-10-2012',110) -- Jul 10 2012 12:00AM
PRINT CONVERT(datetime,'2012/07/10',111) -- Jul 10 2012 12:00AM
PRINT CONVERT(datetime,'20120710', 112) -- Jul 10 2012 12:00AM
------------
-- String to date conversion - sql date yyyy mm dd - sql date formatting
-- SQL Server cast string to date - sql convert date to datetime
SELECT [Date] = CAST (@DateValue AS datetime)
-- 2012-07-18 00:00:00.000
-- SQL convert string date to different style - sql date string formatting
SELECT CONVERT(varchar, CONVERT(datetime, '20140508'), 100)
-- May 8 2014 12:00AM
-- SQL Server convert date to integer
DECLARE @Date datetime; SET @Date = getdate();
SELECT DateAsInteger = CAST (CONVERT(varchar,@Date,112) as INT);
-- Result: 20161225
-- SQL Server convert integer to datetime
DECLARE @iDate int
SET @iDate = 20151225
SELECT IntegerToDatetime = CAST(convert(varchar,@iDate) as datetime)
-- 2015-12-25 00:00:00.000
-- Alternates: date-only datetime values
-- SQL Server floor date - sql convert datetime
SELECT [DATE-ONLY]=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))
SELECT [DATE-ONLY]=CONVERT(DATETIME, FLOOR(CONVERT(MONEY, GETDATE())))
-- SQL Server cast string to datetime
-- SQL Server datetime to string convert
SELECT [DATE-ONLY]=CAST(CONVERT(varchar, GETDATE(), 101) AS DATETIME)
-- SQL Server dateadd function - T-SQL datediff function
-- SQL strip time from date - MSSQL strip time from datetime
SELECT getdate() ,DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
-- Results: 2016-01-23 05:35:52.793 2016-01-23 00:00:00.000
-- String date - 10 bytes of storage
SELECT [STRING DATE]=CONVERT(varchar, GETDATE(), 110)
SELECT [STRING DATE]=CONVERT(varchar, CURRENT_TIMESTAMP, 110)
-- Same results: 01-02-2012
-- SQL Server cast datetime as string - sql datetime formatting
SELECT stringDateTime=CAST (getdate() as varchar) -- Dec 29 2012 3:47AM
----------
-- SQL date range BETWEEN operator
----------
-- SQL date range select - date range search - T-SQL date range query - sql date ranges
-- Count Sales Orders for 2003 OCT-NOV
DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = convert(DATETIME,'10/01/2003',101)
SET @EndDate = convert(DATETIME,'11/30/2003',101)
SELECT @StartDate, @EndDate
-- 2003-10-01 00:00:00.000 2003-11-30 00:00:00.000
SELECT DATEADD(DAY,1,@EndDate),
DATEADD(ms,-3,DATEADD(DAY,1,@EndDate))
-- 2003-12-01 00:00:00.000 2003-11-30 23:59:59.997
-- MSSQL date range select using >= and <
SELECT [Sales Orders for 2003 OCT-NOV] = COUNT(* )
FROM Sales.SalesOrderHeader
WHERE OrderDate >= @StartDate AND OrderDate < DATEADD(DAY,1,@EndDate)
/* Sales Orders for 2003 OCT-NOV
3668 */
-- Equivalent date range query using BETWEEN comparison
-- It requires a bit of trick programming
SELECT [Sales Orders for 2003 OCT-NOV] = COUNT(* )
FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN @StartDate AND DATEADD(ms,-3,DATEADD(DAY,1,@EndDate))
-- 3668
USE AdventureWorks;
-- SQL between string dates
SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
WHERE OrderDate BETWEEN '20040201' AND '20040210' -- Result: 108
-- SQL BETWEEN dates without time - time stripped - time removed - date part only
SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
WHERE DATEDIFF(dd,0,OrderDate)
BETWEEN DATEDIFF(dd,0,'20040201 12:11:39') AND DATEDIFF(dd,0,'20040210 14:33:19')
-- 108
-- BETWEEN is equivalent to >=...AND....<=
SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
WHERE OrderDate
BETWEEN '2004-02-01 00:00:00.000' AND '2004-02-10 00:00:00.000'
/* Orders with OrderDates
'2004-02-10 00:00:01.000' - 1 second after midnight (12:00AM)
'2004-02-10 00:01:00.000' - 1 minute after midnight
'2004-02-10 01:00:00.000' - 1 hour after midnight
are not included in the two queries above. */
-- To include the entire day of 2004-02-10 use:
SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
WHERE OrderDate >= '20040201' AND OrderDate < '20040211'
----------
-- Date validation function ISDATE - returns 1 or 0 - SQL datetime functions
------------
DECLARE @StringDate varchar(32)
SET @StringDate = '2011-03-15 18:50'
IF EXISTS( SELECT * WHERE ISDATE(@StringDate) = 1)
PRINT 'VALID DATE: ' + @StringDate
ELSE
PRINT 'INVALID DATE: ' + @StringDate
GO
-- Result: VALID DATE: 2011-03-15 18:50
DECLARE @StringDate varchar(32)
SET @StringDate = '20112-03-15 18:50'
IF EXISTS( SELECT * WHERE ISDATE(@StringDate) = 1)
PRINT 'VALID DATE: ' + @StringDate
ELSE
PRINT 'INVALID DATE: ' + @StringDate
GO
-- Result: INVALID DATE: 20112-03-15 18:50
-- First and last day of date periods - SQL Server 2008 and on code
DECLARE @Date DATE = '20161023'
SELECT ReferenceDate = @Date
SELECT FirstDayOfYear = CONVERT(DATE, DATEADD(yy, DATEDIFF(yy,0, @Date),0))
SELECT LastDayOfYear = CONVERT(DATE, DATEADD(yy, DATEDIFF(yy,0, @Date)+1,-1))
SELECT FirstDayOfSemester = CONVERT(DATE, DATEADD(qq,((DATEDIFF(qq,0,@Date)/2)*2),0))
SELECT LastDayOfSemester = CONVERT(DATE, DATEADD(qq,((DATEDIFF(qq,0,@Date)/2)*2)+2,-1))
SELECT FirstDayOfQuarter = CONVERT(DATE, DATEADD(qq, DATEDIFF(qq,0, @Date),0))
-- 2016-10-01
SELECT LastDayOfQuarter = CONVERT(DATE, DATEADD(qq, DATEDIFF(qq,0, @Date)+1,-1))
-- 2016-12-31
SELECT FirstDayOfMonth = CONVERT(DATE, DATEADD(mm, DATEDIFF(mm,0, @Date),0))
SELECT LastDayOfMonth = CONVERT(DATE, DATEADD(mm, DATEDIFF(mm,0, @Date)+1,-1))
SELECT FirstDayOfWeek = CONVERT(DATE, DATEADD(wk, DATEDIFF(wk,0, @Date),0))
SELECT LastDayOfWeek = CONVERT(DATE, DATEADD(wk, DATEDIFF(wk,0, @Date)+1,-1))
-- 2016-10-30
------------
-- Selected named date styles
------------
DECLARE @DateTimeValue varchar(32)
-- US-Style
SELECT @DateTimeValue = '10/23/2016'
SELECT StringDate=@DateTimeValue,
[US-Style] = CONVERT(datetime, @DatetimeValue)
SELECT @DateTimeValue = '10/23/2016 23:01:05'
SELECT StringDate = @DateTimeValue,
[US-Style] = CONVERT(datetime, @DatetimeValue)
-- UK-Style, British/French - convert string to datetime sql
-- sql convert string to datetime
SELECT @DateTimeValue = '23/10/16 23:01:05'
SELECT StringDate = @DateTimeValue,
[UK-Style] = CONVERT(datetime, @DatetimeValue, 3)
SELECT @DateTimeValue = '23/10/2016 04:01 PM'
SELECT StringDate = @DateTimeValue,
[UK-Style] = CONVERT(datetime, @DatetimeValue, 103)
-- German-Style
SELECT @DateTimeValue = '23.10.16 23:01:05'
SELECT StringDate = @DateTimeValue,
[German-Style] = CONVERT(datetime, @DatetimeValue, 4)
SELECT @DateTimeValue = '23.10.2016 04:01 PM'
SELECT StringDate = @DateTimeValue,
[German-Style] = CONVERT(datetime, @DatetimeValue, 104)
------------
-- Double conversion to US-Style 107 with century: Oct 23, 2016
SET @DateTimeValue='10/23/16'
SELECT StringDate=@DateTimeValue,
[US-Style] = CONVERT(varchar, CONVERT(datetime, @DateTimeValue),107)
-- Using DATEFORMAT - UK-Style - SQL dateformat
SET @DateTimeValue='23/10/16'
SET DATEFORMAT dmy
SELECT StringDate=@DateTimeValue,
[Date Time] = CONVERT(datetime, @DatetimeValue)
-- Using DATEFORMAT - US-Style
SET DATEFORMAT mdy
-- Convert date string from DD/MM/YYYY UK format to MM/DD/YYYY US format
DECLARE @UKdate char(10) = '15/03/2016'
SELECT CONVERT(CHAR(10), CONVERT(datetime, @UKdate,103),101)
-- 03/15/2016
-- DATEPART datetime function example - SQL Server datetime functions
SELECT * FROM Northwind.dbo.Orders
WHERE DATEPART(YEAR, OrderDate) = '1996' AND
DATEPART(MONTH,OrderDate) = '07' AND
DATEPART(DAY, OrderDate) = '10'
-- Alternate syntax for DATEPART example
SELECT * FROM Northwind.dbo.Orders
WHERE YEAR(OrderDate) = '1996' AND
MONTH(OrderDate) = '07' AND
DAY(OrderDate) = '10'
------------
------------
-- T-SQL calculate the number of business days function / UDF - exclude SAT & SUN
------------
CREATE FUNCTION fnBusinessDays (@StartDate DATETIME, @EndDate DATETIME)
RETURNS INT AS
BEGIN
IF (@StartDate IS NULL OR @EndDate IS NULL) RETURN (0)
DECLARE @i INT = 0;
WHILE (@StartDate <= @EndDate)
BEGIN
SET @i = @i + CASE
WHEN datepart(dw,@StartDate) BETWEEN 2 AND 6 THEN 1
ELSE 0
END
SET @StartDate = @StartDate + 1
END -- while
RETURN (@i)
END -- function
GO
SELECT dbo.fnBusinessDays('2016-01-01','2016-12-31')
-- 261
------------
-- T-SQL DATENAME function usage for weekdays
SELECT DayName=DATENAME(weekday, OrderDate), SalesPerWeekDay = COUNT(*)
FROM AdventureWorks2008.Sales.SalesOrderHeader
GROUP BY DATENAME(weekday, OrderDate), DATEPART(weekday,OrderDate)
ORDER BY DATEPART(weekday,OrderDate)
/* DayName SalesPerWeekDay
Sunday 4482
Monday 4591
Tuesday 4346.... */
-- DATENAME application for months
SELECT MonthName=DATENAME(month, OrderDate), SalesPerMonth = COUNT(*)
FROM AdventureWorks2008.Sales.SalesOrderHeader
GROUP BY DATENAME(month, OrderDate), MONTH(OrderDate) ORDER BY MONTH(OrderDate)
/* MonthName SalesPerMonth
January 2483
February 2686
March 2750
April 2740.... */
-- Getting month name from month number
SELECT DATENAME(MM,DATEADD(MM,7,-1)) -- July
------------
------------
-- Extract string date from text with PATINDEX pattern matching
-- Apply sql server string to date conversion
------------
USE tempdb;
go
CREATE TABLE InsiderTransaction (
InsiderTransactionID int identity primary key,
TradeDate datetime,
TradeMsg varchar(256),
ModifiedDate datetime default (getdate()))
go
-- Populate table with dummy data
INSERT InsiderTransaction (TradeMsg) VALUES(
'INSIDER TRAN QABC Hammer, Bruce D. CSO 09-02-08 Buy 2,000 6.10')
INSERT InsiderTransaction (TradeMsg) VALUES(
'INSIDER TRAN QABC Schmidt, Steven CFO 08-25-08 Buy 2,500 6.70')
INSERT InsiderTransaction (TradeMsg) VALUES(
'INSIDER TRAN QABC Hammer, Bruce D. CSO 08-20-08 Buy 3,000 8.59')
INSERT InsiderTransaction (TradeMsg) VALUES(
'INSIDER TRAN QABC Walters, Jeff CTO 08-15-08 Sell 5,648 8.49')
INSERT InsiderTransaction (TradeMsg) VALUES(
'INSIDER TRAN QABC Walters, Jeff CTO 08-15-08 Option Execute 5,648 2.15')
INSERT InsiderTransaction (TradeMsg) VALUES(
'INSIDER TRAN QABC Hammer, Bruce D. CSO 07-31-08 Buy 5,000 8.05')
INSERT InsiderTransaction (TradeMsg) VALUES(
'INSIDER TRAN QABC Lennot, Mark B. Director 08-31-07 Buy 1,500 9.97')
INSERT InsiderTransaction (TradeMsg) VALUES(
'INSIDER TRAN QABC O''Neal, Linda COO 08-01-08 Sell 5,000 6.50')
go
-- Extract dates from stock trade message text
-- Pattern match for MM-DD-YY using the PATINDEX string function
SELECT TradeDate=substring(TradeMsg,
patindex('%[01][0-9]-[0123][0-9]-[0-9][0-9]%', TradeMsg),8)
FROM InsiderTransaction
WHERE patindex('%[01][0-9]-[0123][0-9]-[0-9][0-9]%', TradeMsg) > 0
/* Partial results
TradeDate
09-02-08
08-25-08
08-20-08 */
-- Update table with extracted date
-- Convert string date to datetime
UPDATE InsiderTransaction
SET TradeDate = convert(datetime, substring(TradeMsg,
patindex('%[01][0-9]-[0123][0-9]-[0-9][0-9]%', TradeMsg),8))
WHERE patindex('%[01][0-9]-[0123][0-9]-[0-9][0-9]%', TradeMsg) > 0
SELECT * FROM InsiderTransaction ORDER BY TradeDate desc
/* Partial results
InsiderTransactionID TradeDate TradeMsg ModifiedDate
1 2008-09-02 00:00:00.000 INSIDER TRAN QABC Hammer, Bruce D. CSO 09-02-08 Buy 2,000 6.10 2008-12-22 20:25:19.263
2 2008-08-25 00:00:00.000 INSIDER TRAN QABC Schmidt, Steven CFO 08-25-08 Buy 2,500 6.70 2008-12-22 20:25:19.263
3 2008-08-20 00:00:00.000 INSIDER TRAN QABC Hammer, Bruce D. CSO 08-20-08 Buy 3,000 8.59 2008-12-22 20:25:19.263 */
-- Cleanup task
DROP TABLE InsiderTransaction
/************
VALID DATE RANGES FOR DATE / DATETIME DATA TYPES
DATE (3 bytes) date range:
January 1, 1 A.D. through December 31, 9999 A.D.
SMALLDATETIME (4 bytes) date range:
January 1, 1900 through June 6, 2079
DATETIME (8 bytes) date range:
January 1, 1753 through December 31, 9999
DATETIME2 (6-8 bytes) date range:
January 1, 1 A.D. through December 31, 9999 A.D.
-- The statement below will give a date range error
SELECT CONVERT(smalldatetime, '2110-01-01')
/* Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a smalldatetime data type
resulted in an out-of-range value. */
************/
------------
-- SQL CONVERT DATE/DATETIME script applying table variable
------------
-- SQL Server convert date
-- Datetime column is converted into date only string column
DECLARE @sqlConvertDate TABLE ( DatetimeColumn datetime,
DateColumn char(10));
INSERT @sqlConvertDate (DatetimeColumn) SELECT GETDATE()
UPDATE @sqlConvertDate
SET DateColumn = CONVERT(char(10), DatetimeColumn, 111)
SELECT * FROM @sqlConvertDate
-- SQL Server convert datetime - String date column is converted into datetime column
UPDATE @sqlConvertDate
SET DatetimeColumn = CONVERT(Datetime, DateColumn, 111)
SELECT * FROM @sqlConvertDate
-- Equivalent formulation - SQL Server cast datetime
UPDATE @sqlConvertDate
SET DatetimeColumn = CAST(DateColumn AS datetime)
SELECT * FROM @sqlConvertDate
/* First results
DatetimeColumn DateColumn
2012-12-25 15:54:10.363 2012/12/25 */
/* Second results:
DatetimeColumn DateColumn
2012-12-25 00:00:00.000 2012/12/25 */
------------
-- SQL date sequence generation with DATEADD & table variable
-- SQL Server cast datetime to string - SQL Server insert default values method
DECLARE @Sequence table (Sequence int identity(1,1))
DECLARE @i int; SET @i = 0
WHILE ( @i < 500)
BEGIN
INSERT @Sequence DEFAULT VALUES
SET @i = @i + 1
END
SELECT DateSequence = CAST(DATEADD(day, Sequence,getdate()) AS varchar) FROM @Sequence
/* Partial results:
DateSequence
Dec 31 2008 3:02AM
Jan 1 2009 3:02AM
Jan 2 2009 3:02AM
Jan 3 2009 3:02AM
Jan 4 2009 3:02AM
Jan 5 2009 3:02AM */
------------
-- SQL Last Week calculations
------------
-- SQL last Friday
-- Implied string to datetime conversions in DATEADD & DATEDIFF
DECLARE @BaseFriday CHAR(8), @LastFriday datetime, @LastMonday datetime
SET @BaseFriday = '19000105'
SELECT @LastFriday = DATEADD(dd,
(DATEDIFF (dd, @BaseFriday, CURRENT_TIMESTAMP) / 7) * 7, @BaseFriday)
SELECT [Last Friday] = @LastFriday
-- Result: 2008-12-26 00:00:00.000
-- SQL last Monday (last week's Monday)
SELECT @LastMonday=DATEADD(dd,
(DATEDIFF (dd, @BaseFriday, CURRENT_TIMESTAMP) / 7) * 7 - 4, @BaseFriday)
SELECT [Last Monday]= @LastMonday
-- Result: 2008-12-22 00:00:00.000
-- SQL last week - SUN - SAT
SELECT [Last Week] = CONVERT(varchar,dateadd(day, -1, @LastMonday), 101)+ ' - ' +
CONVERT(varchar,dateadd(day, 1, @LastFriday), 101)
-- Result: 12/21/2008 - 12/27/2008
-----------------
-- Specific day calculations
------------
-- First day of current month
SELECT dateadd(month, datediff(month, 0, getdate()), 0)
-- 15th day of current month
SELECT dateadd(day,14,dateadd(month,datediff(month,0,getdate()),0))
-- First Monday of current month
SELECT dateadd(day, (9-datepart(weekday,
dateadd(month, datediff(month, 0, getdate()), 0)))%7,
dateadd(month, datediff(month, 0, getdate()), 0))
-- Last Friday of current month
SELECT dateadd(day, -7+(6-datepart(weekday,
dateadd(month, datediff(month, 0, getdate())+1, 0)))%7,
dateadd(month, datediff(month, 0, getdate())+1, 0))
-- First day of next month
SELECT dateadd(month, datediff(month, 0, getdate())+1, 0)
-- 15th of next month
SELECT dateadd(day,14, dateadd(month, datediff(month, 0, getdate())+1, 0))
-- First Monday of next month
SELECT dateadd(day, (9-datepart(weekday,
dateadd(month, datediff(month, 0, getdate())+1, 0)))%7,
dateadd(month, datediff(month, 0, getdate())+1, 0))
------------
-- SQL Last Date calculations
------------
-- Last day of prior month - Last day of previous month
SELECT convert( varchar, dateadd(dd,-1,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0)),101)
-- 01/31/2019
-- Last day of current month
SELECT convert( varchar, dateadd(dd,-1,DATEADD(mm, DATEDIFF(mm,0,getdate())+1, 0)),101)
-- 02/28/2019
-- Last day of prior quarter - Last day of previous quarter
SELECT convert( varchar, dateadd(dd,-1,DATEADD(qq, DATEDIFF(qq,0,getdate() ), 0)),101)
-- 12/31/2018
-- Last day of current quarter - Last day of current quarter
SELECT convert( varchar, dateadd(dd,-1,DATEADD(qq, DATEDIFF(qq,0,getdate())+1, 0)),101)
-- 03/31/2019
-- Last day of prior year - Last day of previous year
SELECT convert( varchar, dateadd(dd,-1,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0)),101)
-- 12/31/2018
-- Last day of current year
SELECT convert( varchar, dateadd(dd,-1,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)),101)
-- 12/31/2019
------------
------------
-- SQL Server dateformat and language setting
------------
-- T-SQL set language - String to date conversion
SET LANGUAGE us_english
SELECT CAST('2018-03-15' AS datetime)
-- 2018-03-15 00:00:00.000
SET LANGUAGE british
SELECT CAST('2018-03-15' AS datetime)
/* Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime data type resulted in
an out-of-range value.
*/
SELECT CAST('2018-15-03' AS datetime)
-- 2018-03-15 00:00:00.000
SET LANGUAGE us_english
-- SQL dateformat with language dependency
SELECT name, alias, dateformat
FROM sys.syslanguages
WHERE langid in (0,1,2,4,5,6,7,10,11,13,23,31)
GO
/*
name alias dateformat
us_english English mdy
Deutsch German dmy
Français French dmy
Dansk Danish dmy
Español Spanish dmy
Italiano Italian dmy
Nederlands Dutch dmy
Suomi Finnish dmy
Svenska Swedish ymd
magyar Hungarian ymd
British British English dmy
Arabic Arabic dmy
*/
------------
-- SQL Server string to date / datetime conversion - datetime string format sql server
-- MSSQL string to datetime conversion - convert char to date - convert varchar to date
-- Subtract 100 from style number (format) for yy instead yyyy (or ccyy with century)
SELECT convert(datetime, 'Oct 23 2012 11:01AM', 100) -- mon dd yyyy hh:mmAM (or PM)
SELECT convert(datetime, 'Oct 23 2012 11:01AM') -- 2012-10-23 11:01:00.000
-- Without century (yy) string date conversion - convert string to datetime function
SELECT convert(datetime, 'Oct 23 12 11:01AM', 0) -- mon dd yy hh:mmAM (or PM)
SELECT convert(datetime, 'Oct 23 12 11:01AM') -- 2012-10-23 11:01:00.000
-- Convert string to datetime sql - convert string to date sql - sql dates format
-- T-SQL convert string to datetime - SQL Server convert string to date
SELECT convert(datetime, '10/23/2016', 101) -- mm/dd/yyyy
SELECT convert(datetime, '2016.10.23', 102) -- yyyy.mm.dd
SELECT convert(datetime, '23/10/2016', 103) -- dd/mm/yyyy
SELECT convert(datetime, '23.10.2016', 104) -- dd.mm.yyyy
SELECT convert(datetime, '23-10-2016', 105) -- dd-mm-yyyy
-- mon types are nondeterministic conversions, dependent on language setting
SELECT convert(datetime, '23 OCT 2016', 106) -- dd mon yyyy
SELECT convert(datetime, 'Oct 23, 2016', 107) -- mon dd, yyyy
-- 2016-10-23 00:00:00.000
SELECT convert(datetime, '20:10:44', 108) -- hh:mm:ss
-- 1900-01-01 20:10:44.000
-- mon dd yyyy hh:mm:ss:mmmAM (or PM) - sql time format - SQL Server datetime format
SELECT convert(datetime, 'Oct 23 2016 11:02:44:013AM', 109)
-- 2016-10-23 11:02:44.013
SELECT convert(datetime, '10-23-2016', 110) -- mm-dd-yyyy
SELECT convert(datetime, '2016/10/23', 111) -- yyyy/mm/dd
-- YYYYMMDD ISO date format works at any language setting - international standard
SELECT convert(datetime, '20161023')
SELECT convert(datetime, '20161023', 112) -- yyyymmdd
-- 2016-10-23 00:00:00.000
SELECT convert(datetime, '23 Oct 2016 11:02:07:577', 113) -- dd mon yyyy hh:mm:ss:mmm
-- 2016-10-23 11:02:07.577
SELECT convert(datetime, '20:10:25:300', 114) -- hh:mm:ss:mmm(24h)
-- 1900-01-01 20:10:25.300
SELECT convert(datetime, '2016-10-23 20:44:11', 120) -- yyyy-mm-dd hh:mm:ss(24h)
-- 2016-10-23 20:44:11.000
SELECT convert(datetime, '2016-10-23 20:44:11.500', 121) -- yyyy-mm-dd hh:mm:ss.mmm
-- 2016-10-23 20:44:11.500
-- Style 126 is ISO 8601 format: international standard - works with any language setting
SELECT convert(datetime, '2008-10-23T18:52:47.513', 126) -- yyyy-mm-ddThh:mm:ss(.mmm)
-- 2008-10-23 18:52:47.513
-- Convert DDMMYYYY format to datetime - sql server to date / datetime
SELECT convert(datetime, STUFF(STUFF('31012016',3,0,'-'),6,0,'-'), 105)
-- 2016-01-31 00:00:00.000
-- SQL Server T-SQL string to datetime conversion without century - some exceptions
-- nondeterministic means language setting dependent such as Mar/Mär/mars/márc
SELECT convert(datetime, 'Oct 23 16 11:02:44AM') -- Default
SELECT convert(datetime, '10/23/16', 1) -- mm/dd/yy U.S.
SELECT convert(datetime, '16.10.23', 2) -- yy.mm.dd ANSI
SELECT convert(datetime, '23/10/16', 3) -- dd/mm/yy UK/FR
SELECT convert(datetime, '23.10.16', 4) -- dd.mm.yy German
SELECT convert(datetime, '23-10-16', 5) -- dd-mm-yy Italian
SELECT convert(datetime, '23 OCT 16', 6) -- dd mon yy non-det.
SELECT convert(datetime, 'Oct 23, 16', 7) -- mon dd, yy non-det.
SELECT convert(datetime, '20:10:44', 8) -- hh:mm:ss
SELECT convert(datetime, 'Oct 23 16 11:02:44:013AM', 9) -- Default with msec
SELECT convert(datetime, '10-23-16', 10) -- mm-dd-yy U.S.
SELECT convert(datetime, '16/10/23', 11) -- yy/mm/dd Japan
SELECT convert(datetime, '161023', 12) -- yymmdd ISO
SELECT convert(datetime, '23 Oct 16 11:02:07:577', 13) -- dd mon yy hh:mm:ss:mmm EU dflt
SELECT convert(datetime, '20:10:25:300', 14) -- hh:mm:ss:mmm(24h)
SELECT convert(datetime, '2016-10-23 20:44:11',20) -- yyyy-mm-dd hh:mm:ss(24h) ODBC can.
SELECT convert(datetime, '2016-10-23 20:44:11.500', 21)-- yyyy-mm-dd hh:mm:ss.mmm ODBC
------------
-- SQL Datetime Data Type: Combine date & time string into datetime - sql hh mm ss
-- String to datetime - mssql datetime - sql convert date - sql concatenate string
DECLARE @DateTimeValue varchar(32), @DateValue char(8), @TimeValue char(6)
SELECT @DateValue = '20120718',
@TimeValue = '211920'
SELECT @DateTimeValue =
convert(varchar, convert(datetime, @DateValue), 111)
+ ' ' + substring(@TimeValue, 1, 2)
+ ':' + substring(@TimeValue, 3, 2)
+ ':' + substring(@TimeValue, 5, 2)
SELECT
DateInput = @DateValue, TimeInput = @TimeValue,
DateTimeOutput = @DateTimeValue;
/*
DateInput TimeInput DateTimeOutput
20120718 211920 2012/07/18 21:19:20
*/
/* Datetime 8 bytes internal storage structure
o 1st 4 bytes: number of days after the base date 1900-01-01
o 2nd 4 bytes: number of milliseconds since midnight */
-- SQL convert seconds to HH:MM:SS - sql times format - sql hh mm
DECLARE @Seconds INT
SET @Seconds = 20000
SELECT HH = @Seconds / 3600, MM = (@Seconds%3600) / 60, SS = (@Seconds%60)
/* HH MM SS
5 33 20 */
------------
-- SQL Server 2008 convert datetime to date - sql yyyy mm dd
SELECT TOP (3) OrderDate = CONVERT(date, OrderDate),
Today = CONVERT(date, getdate())
FROM AdventureWorks2008.Sales.SalesOrderHeader
ORDER BY newid();
/* OrderDate Today
2003-07-09 2012-06-18
2003-09-26 2012-06-18
2004-02-15 2012-06-18 */
------------
-- SQL date yyyy mm dd - sqlserver yyyy mm dd - date format yyyymmdd
SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]
/* YYYY/MM/DD
2015/07/11 */
SELECT CONVERT(VARCHAR(10), GETDATE(), 112) AS [YYYYMMDD]
/* YYYYMMDD
20150711 */
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111),'/',' ') AS [YYYY MM DD]
/* YYYY MM DD
2015 07 11 */
-- Converting to special (non-standard) date fomats: DD-MMM-YY
SELECT UPPER(REPLACE(CONVERT(VARCHAR,GETDATE(),6),' ','-'))
-- 07-MAR-14
------------
-- SQL convert date string to datetime - time set to 00:00:00.000 or 12:00AM
PRINT CONVERT(datetime,'07-10-2012',110) -- Jul 10 2012 12:00AM
PRINT CONVERT(datetime,'2012/07/10',111) -- Jul 10 2012 12:00AM
PRINT CONVERT(datetime,'20120710', 112) -- Jul 10 2012 12:00AM
------------
-- String to date conversion - sql date yyyy mm dd - sql date formatting
-- SQL Server cast string to date - sql convert date to datetime
SELECT [Date] = CAST (@DateValue AS datetime)
-- 2012-07-18 00:00:00.000
-- SQL convert string date to different style - sql date string formatting
SELECT CONVERT(varchar, CONVERT(datetime, '20140508'), 100)
-- May 8 2014 12:00AM
-- SQL Server convert date to integer
DECLARE @Date datetime; SET @Date = getdate();
SELECT DateAsInteger = CAST (CONVERT(varchar,@Date,112) as INT);
-- Result: 20161225
-- SQL Server convert integer to datetime
DECLARE @iDate int
SET @iDate = 20151225
SELECT IntegerToDatetime = CAST(convert(varchar,@iDate) as datetime)
-- 2015-12-25 00:00:00.000
-- Alternates: date-only datetime values
-- SQL Server floor date - sql convert datetime
SELECT [DATE-ONLY]=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))
SELECT [DATE-ONLY]=CONVERT(DATETIME, FLOOR(CONVERT(MONEY, GETDATE())))
-- SQL Server cast string to datetime
-- SQL Server datetime to string convert
SELECT [DATE-ONLY]=CAST(CONVERT(varchar, GETDATE(), 101) AS DATETIME)
-- SQL Server dateadd function - T-SQL datediff function
-- SQL strip time from date - MSSQL strip time from datetime
SELECT getdate() ,DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
-- Results: 2016-01-23 05:35:52.793 2016-01-23 00:00:00.000
-- String date - 10 bytes of storage
SELECT [STRING DATE]=CONVERT(varchar, GETDATE(), 110)
SELECT [STRING DATE]=CONVERT(varchar, CURRENT_TIMESTAMP, 110)
-- Same results: 01-02-2012
-- SQL Server cast datetime as string - sql datetime formatting
SELECT stringDateTime=CAST (getdate() as varchar) -- Dec 29 2012 3:47AM
----------
-- SQL date range BETWEEN operator
----------
-- SQL date range select - date range search - T-SQL date range query - sql date ranges
-- Count Sales Orders for 2003 OCT-NOV
DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = convert(DATETIME,'10/01/2003',101)
SET @EndDate = convert(DATETIME,'11/30/2003',101)
SELECT @StartDate, @EndDate
-- 2003-10-01 00:00:00.000 2003-11-30 00:00:00.000
SELECT DATEADD(DAY,1,@EndDate),
DATEADD(ms,-3,DATEADD(DAY,1,@EndDate))
-- 2003-12-01 00:00:00.000 2003-11-30 23:59:59.997
-- MSSQL date range select using >= and <
SELECT [Sales Orders for 2003 OCT-NOV] = COUNT(* )
FROM Sales.SalesOrderHeader
WHERE OrderDate >= @StartDate AND OrderDate < DATEADD(DAY,1,@EndDate)
/* Sales Orders for 2003 OCT-NOV
3668 */
-- Equivalent date range query using BETWEEN comparison
-- It requires a bit of trick programming
SELECT [Sales Orders for 2003 OCT-NOV] = COUNT(* )
FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN @StartDate AND DATEADD(ms,-3,DATEADD(DAY,1,@EndDate))
-- 3668
USE AdventureWorks;
-- SQL between string dates
SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
WHERE OrderDate BETWEEN '20040201' AND '20040210' -- Result: 108
-- SQL BETWEEN dates without time - time stripped - time removed - date part only
SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
WHERE DATEDIFF(dd,0,OrderDate)
BETWEEN DATEDIFF(dd,0,'20040201 12:11:39') AND DATEDIFF(dd,0,'20040210 14:33:19')
-- 108
-- BETWEEN is equivalent to >=...AND....<=
SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
WHERE OrderDate
BETWEEN '2004-02-01 00:00:00.000' AND '2004-02-10 00:00:00.000'
/* Orders with OrderDates
'2004-02-10 00:00:01.000' - 1 second after midnight (12:00AM)
'2004-02-10 00:01:00.000' - 1 minute after midnight
'2004-02-10 01:00:00.000' - 1 hour after midnight
are not included in the two queries above. */
-- To include the entire day of 2004-02-10 use:
SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
WHERE OrderDate >= '20040201' AND OrderDate < '20040211'
----------
-- Date validation function ISDATE - returns 1 or 0 - SQL datetime functions
------------
DECLARE @StringDate varchar(32)
SET @StringDate = '2011-03-15 18:50'
IF EXISTS( SELECT * WHERE ISDATE(@StringDate) = 1)
PRINT 'VALID DATE: ' + @StringDate
ELSE
PRINT 'INVALID DATE: ' + @StringDate
GO
-- Result: VALID DATE: 2011-03-15 18:50
DECLARE @StringDate varchar(32)
SET @StringDate = '20112-03-15 18:50'
IF EXISTS( SELECT * WHERE ISDATE(@StringDate) = 1)
PRINT 'VALID DATE: ' + @StringDate
ELSE
PRINT 'INVALID DATE: ' + @StringDate
GO
-- Result: INVALID DATE: 20112-03-15 18:50
-- First and last day of date periods - SQL Server 2008 and on code
DECLARE @Date DATE = '20161023'
SELECT ReferenceDate = @Date
SELECT FirstDayOfYear = CONVERT(DATE, DATEADD(yy, DATEDIFF(yy,0, @Date),0))
SELECT LastDayOfYear = CONVERT(DATE, DATEADD(yy, DATEDIFF(yy,0, @Date)+1,-1))
SELECT FirstDayOfSemester = CONVERT(DATE, DATEADD(qq,((DATEDIFF(qq,0,@Date)/2)*2),0))
SELECT LastDayOfSemester = CONVERT(DATE, DATEADD(qq,((DATEDIFF(qq,0,@Date)/2)*2)+2,-1))
SELECT FirstDayOfQuarter = CONVERT(DATE, DATEADD(qq, DATEDIFF(qq,0, @Date),0))
-- 2016-10-01
SELECT LastDayOfQuarter = CONVERT(DATE, DATEADD(qq, DATEDIFF(qq,0, @Date)+1,-1))
-- 2016-12-31
SELECT FirstDayOfMonth = CONVERT(DATE, DATEADD(mm, DATEDIFF(mm,0, @Date),0))
SELECT LastDayOfMonth = CONVERT(DATE, DATEADD(mm, DATEDIFF(mm,0, @Date)+1,-1))
SELECT FirstDayOfWeek = CONVERT(DATE, DATEADD(wk, DATEDIFF(wk,0, @Date),0))
SELECT LastDayOfWeek = CONVERT(DATE, DATEADD(wk, DATEDIFF(wk,0, @Date)+1,-1))
-- 2016-10-30
------------
-- Selected named date styles
------------
DECLARE @DateTimeValue varchar(32)
-- US-Style
SELECT @DateTimeValue = '10/23/2016'
SELECT StringDate=@DateTimeValue,
[US-Style] = CONVERT(datetime, @DatetimeValue)
SELECT @DateTimeValue = '10/23/2016 23:01:05'
SELECT StringDate = @DateTimeValue,
[US-Style] = CONVERT(datetime, @DatetimeValue)
-- UK-Style, British/French - convert string to datetime sql
-- sql convert string to datetime
SELECT @DateTimeValue = '23/10/16 23:01:05'
SELECT StringDate = @DateTimeValue,
[UK-Style] = CONVERT(datetime, @DatetimeValue, 3)
SELECT @DateTimeValue = '23/10/2016 04:01 PM'
SELECT StringDate = @DateTimeValue,
[UK-Style] = CONVERT(datetime, @DatetimeValue, 103)
-- German-Style
SELECT @DateTimeValue = '23.10.16 23:01:05'
SELECT StringDate = @DateTimeValue,
[German-Style] = CONVERT(datetime, @DatetimeValue, 4)
SELECT @DateTimeValue = '23.10.2016 04:01 PM'
SELECT StringDate = @DateTimeValue,
[German-Style] = CONVERT(datetime, @DatetimeValue, 104)
------------
-- Double conversion to US-Style 107 with century: Oct 23, 2016
SET @DateTimeValue='10/23/16'
SELECT StringDate=@DateTimeValue,
[US-Style] = CONVERT(varchar, CONVERT(datetime, @DateTimeValue),107)
-- Using DATEFORMAT - UK-Style - SQL dateformat
SET @DateTimeValue='23/10/16'
SET DATEFORMAT dmy
SELECT StringDate=@DateTimeValue,
[Date Time] = CONVERT(datetime, @DatetimeValue)
-- Using DATEFORMAT - US-Style
SET DATEFORMAT mdy
-- Convert date string from DD/MM/YYYY UK format to MM/DD/YYYY US format
DECLARE @UKdate char(10) = '15/03/2016'
SELECT CONVERT(CHAR(10), CONVERT(datetime, @UKdate,103),101)
-- 03/15/2016
-- DATEPART datetime function example - SQL Server datetime functions
SELECT * FROM Northwind.dbo.Orders
WHERE DATEPART(YEAR, OrderDate) = '1996' AND
DATEPART(MONTH,OrderDate) = '07' AND
DATEPART(DAY, OrderDate) = '10'
-- Alternate syntax for DATEPART example
SELECT * FROM Northwind.dbo.Orders
WHERE YEAR(OrderDate) = '1996' AND
MONTH(OrderDate) = '07' AND
DAY(OrderDate) = '10'
------------
------------
-- T-SQL calculate the number of business days function / UDF - exclude SAT & SUN
------------
CREATE FUNCTION fnBusinessDays (@StartDate DATETIME, @EndDate DATETIME)
RETURNS INT AS
BEGIN
IF (@StartDate IS NULL OR @EndDate IS NULL) RETURN (0)
DECLARE @i INT = 0;
WHILE (@StartDate <= @EndDate)
BEGIN
SET @i = @i + CASE
WHEN datepart(dw,@StartDate) BETWEEN 2 AND 6 THEN 1
ELSE 0
END
SET @StartDate = @StartDate + 1
END -- while
RETURN (@i)
END -- function
GO
SELECT dbo.fnBusinessDays('2016-01-01','2016-12-31')
-- 261
------------
-- T-SQL DATENAME function usage for weekdays
SELECT DayName=DATENAME(weekday, OrderDate), SalesPerWeekDay = COUNT(*)
FROM AdventureWorks2008.Sales.SalesOrderHeader
GROUP BY DATENAME(weekday, OrderDate), DATEPART(weekday,OrderDate)
ORDER BY DATEPART(weekday,OrderDate)
/* DayName SalesPerWeekDay
Sunday 4482
Monday 4591
Tuesday 4346.... */
-- DATENAME application for months
SELECT MonthName=DATENAME(month, OrderDate), SalesPerMonth = COUNT(*)
FROM AdventureWorks2008.Sales.SalesOrderHeader
GROUP BY DATENAME(month, OrderDate), MONTH(OrderDate) ORDER BY MONTH(OrderDate)
/* MonthName SalesPerMonth
January 2483
February 2686
March 2750
April 2740.... */
-- Getting month name from month number
SELECT DATENAME(MM,DATEADD(MM,7,-1)) -- July
------------
------------
-- Extract string date from text with PATINDEX pattern matching
-- Apply sql server string to date conversion
------------
USE tempdb;
go
CREATE TABLE InsiderTransaction (
InsiderTransactionID int identity primary key,
TradeDate datetime,
TradeMsg varchar(256),
ModifiedDate datetime default (getdate()))
go
-- Populate table with dummy data
INSERT InsiderTransaction (TradeMsg) VALUES(
'INSIDER TRAN QABC Hammer, Bruce D. CSO 09-02-08 Buy 2,000 6.10')
INSERT InsiderTransaction (TradeMsg) VALUES(
'INSIDER TRAN QABC Schmidt, Steven CFO 08-25-08 Buy 2,500 6.70')
INSERT InsiderTransaction (TradeMsg) VALUES(
'INSIDER TRAN QABC Hammer, Bruce D. CSO 08-20-08 Buy 3,000 8.59')
INSERT InsiderTransaction (TradeMsg) VALUES(
'INSIDER TRAN QABC Walters, Jeff CTO 08-15-08 Sell 5,648 8.49')
INSERT InsiderTransaction (TradeMsg) VALUES(
'INSIDER TRAN QABC Walters, Jeff CTO 08-15-08 Option Execute 5,648 2.15')
INSERT InsiderTransaction (TradeMsg) VALUES(
'INSIDER TRAN QABC Hammer, Bruce D. CSO 07-31-08 Buy 5,000 8.05')
INSERT InsiderTransaction (TradeMsg) VALUES(
'INSIDER TRAN QABC Lennot, Mark B. Director 08-31-07 Buy 1,500 9.97')
INSERT InsiderTransaction (TradeMsg) VALUES(
'INSIDER TRAN QABC O''Neal, Linda COO 08-01-08 Sell 5,000 6.50')
go
-- Extract dates from stock trade message text
-- Pattern match for MM-DD-YY using the PATINDEX string function
SELECT TradeDate=substring(TradeMsg,
patindex('%[01][0-9]-[0123][0-9]-[0-9][0-9]%', TradeMsg),8)
FROM InsiderTransaction
WHERE patindex('%[01][0-9]-[0123][0-9]-[0-9][0-9]%', TradeMsg) > 0
/* Partial results
TradeDate
09-02-08
08-25-08
08-20-08 */
-- Update table with extracted date
-- Convert string date to datetime
UPDATE InsiderTransaction
SET TradeDate = convert(datetime, substring(TradeMsg,
patindex('%[01][0-9]-[0123][0-9]-[0-9][0-9]%', TradeMsg),8))
WHERE patindex('%[01][0-9]-[0123][0-9]-[0-9][0-9]%', TradeMsg) > 0
SELECT * FROM InsiderTransaction ORDER BY TradeDate desc
/* Partial results
InsiderTransactionID TradeDate TradeMsg ModifiedDate
1 2008-09-02 00:00:00.000 INSIDER TRAN QABC Hammer, Bruce D. CSO 09-02-08 Buy 2,000 6.10 2008-12-22 20:25:19.263
2 2008-08-25 00:00:00.000 INSIDER TRAN QABC Schmidt, Steven CFO 08-25-08 Buy 2,500 6.70 2008-12-22 20:25:19.263
3 2008-08-20 00:00:00.000 INSIDER TRAN QABC Hammer, Bruce D. CSO 08-20-08 Buy 3,000 8.59 2008-12-22 20:25:19.263 */
-- Cleanup task
DROP TABLE InsiderTransaction
/************
VALID DATE RANGES FOR DATE / DATETIME DATA TYPES
DATE (3 bytes) date range:
January 1, 1 A.D. through December 31, 9999 A.D.
SMALLDATETIME (4 bytes) date range:
January 1, 1900 through June 6, 2079
DATETIME (8 bytes) date range:
January 1, 1753 through December 31, 9999
DATETIME2 (6-8 bytes) date range:
January 1, 1 A.D. through December 31, 9999 A.D.
-- The statement below will give a date range error
SELECT CONVERT(smalldatetime, '2110-01-01')
/* Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a smalldatetime data type
resulted in an out-of-range value. */
************/
------------
-- SQL CONVERT DATE/DATETIME script applying table variable
------------
-- SQL Server convert date
-- Datetime column is converted into date only string column
DECLARE @sqlConvertDate TABLE ( DatetimeColumn datetime,
DateColumn char(10));
INSERT @sqlConvertDate (DatetimeColumn) SELECT GETDATE()
UPDATE @sqlConvertDate
SET DateColumn = CONVERT(char(10), DatetimeColumn, 111)
SELECT * FROM @sqlConvertDate
-- SQL Server convert datetime - String date column is converted into datetime column
UPDATE @sqlConvertDate
SET DatetimeColumn = CONVERT(Datetime, DateColumn, 111)
SELECT * FROM @sqlConvertDate
-- Equivalent formulation - SQL Server cast datetime
UPDATE @sqlConvertDate
SET DatetimeColumn = CAST(DateColumn AS datetime)
SELECT * FROM @sqlConvertDate
/* First results
DatetimeColumn DateColumn
2012-12-25 15:54:10.363 2012/12/25 */
/* Second results:
DatetimeColumn DateColumn
2012-12-25 00:00:00.000 2012/12/25 */
------------
-- SQL date sequence generation with DATEADD & table variable
-- SQL Server cast datetime to string - SQL Server insert default values method
DECLARE @Sequence table (Sequence int identity(1,1))
DECLARE @i int; SET @i = 0
WHILE ( @i < 500)
BEGIN
INSERT @Sequence DEFAULT VALUES
SET @i = @i + 1
END
SELECT DateSequence = CAST(DATEADD(day, Sequence,getdate()) AS varchar) FROM @Sequence
/* Partial results:
DateSequence
Dec 31 2008 3:02AM
Jan 1 2009 3:02AM
Jan 2 2009 3:02AM
Jan 3 2009 3:02AM
Jan 4 2009 3:02AM
Jan 5 2009 3:02AM */
------------
-- SQL Last Week calculations
------------
-- SQL last Friday
-- Implied string to datetime conversions in DATEADD & DATEDIFF
DECLARE @BaseFriday CHAR(8), @LastFriday datetime, @LastMonday datetime
SET @BaseFriday = '19000105'
SELECT @LastFriday = DATEADD(dd,
(DATEDIFF (dd, @BaseFriday, CURRENT_TIMESTAMP) / 7) * 7, @BaseFriday)
SELECT [Last Friday] = @LastFriday
-- Result: 2008-12-26 00:00:00.000
-- SQL last Monday (last week's Monday)
SELECT @LastMonday=DATEADD(dd,
(DATEDIFF (dd, @BaseFriday, CURRENT_TIMESTAMP) / 7) * 7 - 4, @BaseFriday)
SELECT [Last Monday]= @LastMonday
-- Result: 2008-12-22 00:00:00.000
-- SQL last week - SUN - SAT
SELECT [Last Week] = CONVERT(varchar,dateadd(day, -1, @LastMonday), 101)+ ' - ' +
CONVERT(varchar,dateadd(day, 1, @LastFriday), 101)
-- Result: 12/21/2008 - 12/27/2008
-----------------
-- Specific day calculations
------------
-- First day of current month
SELECT dateadd(month, datediff(month, 0, getdate()), 0)
-- 15th day of current month
SELECT dateadd(day,14,dateadd(month,datediff(month,0,getdate()),0))
-- First Monday of current month
SELECT dateadd(day, (9-datepart(weekday,
dateadd(month, datediff(month, 0, getdate()), 0)))%7,
dateadd(month, datediff(month, 0, getdate()), 0))
-- Last Friday of current month
SELECT dateadd(day, -7+(6-datepart(weekday,
dateadd(month, datediff(month, 0, getdate())+1, 0)))%7,
dateadd(month, datediff(month, 0, getdate())+1, 0))
-- First day of next month
SELECT dateadd(month, datediff(month, 0, getdate())+1, 0)
-- 15th of next month
SELECT dateadd(day,14, dateadd(month, datediff(month, 0, getdate())+1, 0))
-- First Monday of next month
SELECT dateadd(day, (9-datepart(weekday,
dateadd(month, datediff(month, 0, getdate())+1, 0)))%7,
dateadd(month, datediff(month, 0, getdate())+1, 0))
------------
-- SQL Last Date calculations
------------
-- Last day of prior month - Last day of previous month
SELECT convert( varchar, dateadd(dd,-1,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0)),101)
-- 01/31/2019
-- Last day of current month
SELECT convert( varchar, dateadd(dd,-1,DATEADD(mm, DATEDIFF(mm,0,getdate())+1, 0)),101)
-- 02/28/2019
-- Last day of prior quarter - Last day of previous quarter
SELECT convert( varchar, dateadd(dd,-1,DATEADD(qq, DATEDIFF(qq,0,getdate() ), 0)),101)
-- 12/31/2018
-- Last day of current quarter - Last day of current quarter
SELECT convert( varchar, dateadd(dd,-1,DATEADD(qq, DATEDIFF(qq,0,getdate())+1, 0)),101)
-- 03/31/2019
-- Last day of prior year - Last day of previous year
SELECT convert( varchar, dateadd(dd,-1,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0)),101)
-- 12/31/2018
-- Last day of current year
SELECT convert( varchar, dateadd(dd,-1,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)),101)
-- 12/31/2019
------------
------------
-- SQL Server dateformat and language setting
------------
-- T-SQL set language - String to date conversion
SET LANGUAGE us_english
SELECT CAST('2018-03-15' AS datetime)
-- 2018-03-15 00:00:00.000
SET LANGUAGE british
SELECT CAST('2018-03-15' AS datetime)
/* Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime data type resulted in
an out-of-range value.
*/
SELECT CAST('2018-15-03' AS datetime)
-- 2018-03-15 00:00:00.000
SET LANGUAGE us_english
-- SQL dateformat with language dependency
SELECT name, alias, dateformat
FROM sys.syslanguages
WHERE langid in (0,1,2,4,5,6,7,10,11,13,23,31)
GO
/*
name alias dateformat
us_english English mdy
Deutsch German dmy
Français French dmy
Dansk Danish dmy
Español Spanish dmy
Italiano Italian dmy
Nederlands Dutch dmy
Suomi Finnish dmy
Svenska Swedish ymd
magyar Hungarian ymd
British British English dmy
Arabic Arabic dmy
*/
------------
Subscribe to:
Posts (Atom)