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.
Below is a T-SQL example that will pick a random number between 1 and 50.
That’s a bit boring though.
What about parameters?
What about a use case?
Where’s the familiar glamour of coding with T-SQL?
I hear ya.
Below is a T-SQL example that could make you a multimillionaire!
This T-SQL code will pick random numbers for the Euromillions lottery.
If you found this post helpful please like/share/subscribe.
The following script will create a job that will run every minute to test if database mail can be sent from a job scheduled to run by the Sql Server Agent.
Simply find and replace the email address below with the email address you want to target:
Then run the script.
The operator ‘Test Operator’ and job ‘MailTest’ will be created.
The job is disabled by default, enable it to begin testing.
When you are finished run the commented out section at the bottom of the script to remove the test operator and job.
If you have just setup database mail for the first time the SQL Server Agent will need to be restarted.
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.
Maybe you get emails from time to time saying something like “Hey can you grant so-and-so SELECT permission on” and then they list a few dozen tables.
There’s a couple of issues with this.
Firstly you shouldn’t be getting that as a simple email, it should come in as a formal access request.
Secondly User access should be defined in roles (or group logins if you want to manage access at an Active Directory level) that tie back to departments and seniority. Different roles have different permissions on different objects. This makes the subject of access more manageable and easily auditable. The access request should be “can you add so-and-so to this role” and ideally people should only exist in one role.
Thirdly this would be really annoying and, depending on the length of the table list, take too long to do via the SSMS GUI.
So if you are getting emails like the above try move your organisation along with regards the first two points. But to help you action the email I’ve created the Excel file DbaScripts_GrantSelect which can be downloaded here.
The DbaScripts_GrantSelect file allows you to enter the Login (user name), Database name and Schema name in the first three columns. You can then copy and paste the table names into the fourth column called Table. Drag the first three columns down for as many table name entries there are. Then drag the SQL Command formula column down for as many table name entries there are and this will create the commands to grant SELECTs on the tables for the user specified.
If you can’t download the file above you can recreate it.
In an empty Excel sheet write the following into the cells as directed.
E1: SQL Command
In E2 paste the following formula:
=”GRANT SELECT ON [“&B2&”].[“&C2&”].[“&D2&”] TO [“&A2&”];”
GDPR compliance has given people working in the DBA space the exciting opportunity to drop tables! Tables once considered gold mines are now being treated like live grenades management want rid of fast. This is a prudent stance because if a table contains personal data and it’s not being used for some vital business process why keep it around now? If somehow the wrong person gained access to the data it could have severe reputational and financial consequences. Of course a business should have never kept unneeded personal data but in truth most companies have gathered as much data as they could up until this point even if it wasn’t used as the assumption has been it might be needed later.
Before dropping tables though it is still good practice to rename the table first for a period of time to make sure nothing breaks. Once a sufficient amount of time has passed and you are confident the tables can be dropped without adverse effects the script below can help drop the newly unwanted tables.
If you’ve followed a standard naming convention for renaming unwanted tables, for example prefixing all the targeted tables with “_DropThis_” or something to that effect, this script will provide commands to:
- Count the number of rows in each targeted table
- Drop each targeted table
- Confirm each targeted table has been dropped.
Simply find & replace the text “DatabaseName” with the name of the database that contains the tables to be dropped and “TextTarget” with the text each table name should contain and run the script.
Running the script will produce two tables. The first table will contain the commands to count the number of rows for each targeted table. The second table will contain the commands to drop each targeted table. Open new windows in SSMS, referencing the database to run the commands against, and copy and paste the scripts of each table into the windows. Run the count script first obviously before the drop script. Once the drop script has been run you can run the commented out query at the end of the script below to confirm the tables have been dropped.
You can take the screen shots and/or copy and paste the results of the commands (and the commands themselves) into an email or text document as a simple report to confirm the number of rows dropped and that the tables have been dropped.
This post provides you with a script that will generate a restore script for a database using the latest full backup file that exists in a directory. (No need to state the filename explicitly)
You need to provide the following at the start of the script:
- The target database i.e. the database you will restore to
- The directory where the backup file is saved
If you are using the excellent Ola Hallengren maintenance solution (see link) the directory path will look something like below. If you’re not using Ola’s solution, you should be.
This restore script is designed to work with Ola’s solution as it segregates the backup directory structure such that each database has an allocated folder and each full backup file is named with the date and time of the file creation.
The restore script determines which backup file is the latest backup file based on the max name. So for the script to work it is assumed you have an appropriate backup strategy (i.e. using Ola’s solution) were backup types are segregated into different folders, backup names have a date reference and the backup location is dedicated to backups and nothing else, i.e. no trash files in the location.
Some use cases for this solution might be:
- Restoring a nightly backup to another instance for reporting purposes
- Restoring backups to a development environment
- Restoring backups to another server to test the backups
You can use the logic in a stored procedure or as the T-SQL in a job step and schedule accordingly.