Below is some example code for writing the SQL Server geography data type to a table. Note by default geography data is stored in a binary format but it can be converted to a string to make it human readable.
Time cannot be summed directly in T-SQL. In order to sum two times they first need to be assigned a date. When a time data type is cast as a datetime data type, as it does not have a date element, the value defaults to the date of 1900-01-01.
As T-SQL does have the functionality to sum datetime and as the date element will be the same only the time value will be summed. This functionality allows us to sum time.
Below is example T-SQL:
This is a very common activity in the data world, i.e. there’s some data in a text string you need and the rest of the data in the string is just in your way. Some use cases might be you have a reference in a filename you need to extract, or you may need a snippet of data to create a composite key, or there’s an order number surrounded by other data that is not relevant to your needs etc.
The following is some simple T-SQL that will extract the data you want from a text string providing the data has specific delimiting characters on each side of it.
If you are exporting the results of a SQL Server query to excel typically the recipient of the file wants the dates referenced in the format “dd/mm/yyyy hh:mm:ss” or “dd/mm/yyyy” not in the usual database format yyyy-mm-dd.
The below query formats the datetime as desired. Note that the letter m representing month is capitalised. If they are not the engine will interpret the lowercase letter m as minute so you will end up with days, minutes, years.
Also not that the letter h representing the hours is also capitalised. Capitalising the h makes the time output with the 24 hour format. Lowercase h will be 12 hour format. It is highly recommended not to use the lowercase h.
If you only want the date and not time just remove the relevant text, i.e. just date dd/MM/yyyy or datetime without second dd/MM/yyyy HH:mm.
The script below will provide SQL Server Instance login details such as:
- The login name
- The role membership the login is part of such as sysadmin etc.,
- Whether or not the login is enabled
- Whether or not the login is a SQL Server Login or Windows Login etc.
- Login created date
- Login modified date
The following code will work for a remote client request to SQL 2008 and newer.
Note: The local machine address (local_net_address) is that of the SQL Server while client_net_address is the address of the remote computer you have used to make the request.
This script is for SQL Server 2005 and up. The script will return all the missing indexes for a SQL Server instance, rating their impact and provide the T-SQL to create the missing indexes.
SQL Server 2005 was the first version of SQL Server to add DMV (Database Management View) and DMO (Database Management Objects) which this script requires to function.
DMV & DMO provide useful information about SQL Server like expensive queries, wait types, missing indexes etc.
Before you create the missing indexes on the referenced tables you must consider the following essential points:
• Find and assess all the queries that are using the table referenced. If the table has a heavy amount of Data Manipulation Language (DML) operations against it (SELECT, INSERT, UPDATE, or DELETE) then you must analyse what impact adding the missing index will have before you create it on the table. INSERTs on tables are slowed down by nonclustered indexes for example.
• You need to make sure that by creating the missing indexes you are not going to end up with duplicate indexes on tables. The duplicate or unwanted indexes can kill your database performance. Search for the blog “over-indexing can hurt your SQL Server performance” for more information.
• If you find there is already an existing index that has most of the columns of the missing index highlighted you should consider adding the missing columns to the current index rather than creating another index on the table. FYI making an index wider does not mean adding all columns from a table into the current index.
So the vendors for the call system software ran the code below on the live system on a Friday night. Thanks guys. I’ve changed the name of the tables in the code for security reasons if anyone would like to use this it as an example of how not to write T-SQL code!
The vendors claim the code came from the company that developed the call system and if this is the case I think someone in head office is trying to get fired or get someone fired.
Scroll down for a review of why the code is such a mess.
Here are some of the issues with the above in no particular order.
The command is running against a 312 million row table, a 55 million row table and a 22 million row table. Although is was only planned to run once IT STILL NEEDS TO BE EFFICIENT!!!!!
It uses Select Star or (Select *) which raises the probability that SQL Server will query the whole table rather than accessing the data through indexes. Only an Id is needed to begin with.
There are subqueries used when a join would be much faster.
The subqueries are completely unnecessary when WHERE conditions could have been listed in an IN statement.
Correlated subquery!!! Literally designed to bring a server to its knees! Evaluated once for each row processed. See more on correlated sub queries here https://en.wikipedia.org/wiki/Correlated_subquery
There are EXISTS Statements used due to the poor design of the command.
A ridiculously giant case statement used! Typically the comparison data should be written to a temp table and assessed as a join when there are an excessive number of case statements.
Needless to say this query ballooned the tempdb and I’ll let you guess what happened next.
The lesson for today’s posting, trust no one!
The following script will generate a random 10 character password that meets the complexity requirement for Microsoft Windows. To generate a password just run the script in a new SQL Server Management Studio window. The logic can also be easily turned into a function.
The option of symbol characters is limited to what’s shown below as dealing with quotes and obscure characters in a password is often more trouble than it is worth. The password generated however should still be very secure as it will be 10 characters long with a guaranteed number, lowercase letter, uppercase letter and a symbol.
If you found this post helpful please like, comment and share.
If you’ve ever found yourself in the situation were a command executing against a small table is nowhere near instant there can be numerous reasons for this but the most common causes are locks and waits.
The first step in identifying the problem is to execute the script below in a new query window while the troublesome command is running.
This script will return two lists of the currently active sessions along with the stats associated with their execution. The first list will contain all the active sessions that are not running. The second list will contain all the active sessions that are running and will likely not contain the troublesome query you’re dealing with.
Identify your session based on the SqlText field. Be sure you’ve identified the session correctly as you may decide you want to kill the process later and killing the wrong one could cause you a lot of trouble.
- status : If the status is not running look to the other fields in the returned result set to help identify the problem. If the session is in the running result set but you are unhappy with the performance it is likely the T-SQL needs to be optimized to make it run faster. This is a very broad topic and there are tons of articles and guides on the internet dealing with it.
- blocking_session_id : If another session is blocking yours from executing, e.g. it has locked a table your command needs to write to, then this field will include the Id of the session causing the table to be locked. You can use
EXEC sp_who2to assess if the underlying command/query is experiencing a problem. If you are familiar with the blocking session you may know that you are able to kill the session without incurring any negative consequences. You can use the following code snippet to kill the blocking session.
KILL blocking_session_id /*replace by the actual Id*/
NOTE: Before you kill anything if it’s a command that has been running for a very long time it will likely take at least the same amount of time to roll back and unlock the table. You might be better off waiting for the session to finish on its own.
- wait_type : If no blocking session is available, then the query is waiting for something, e.g. server resources etc. More details about wait types can be found HERE
- wait_time : This stat value is measured in milliseconds. Short wait times are fine, specially in PAGEIOLATCH wait types (access to physical files) but longer wait times indicate a more serious problem.
- last_wait_type : Indicates if the last wait type was different. This is quite helpful in analyzing if the query was blocked for the same reason before.