Many people have asked me what the 't' is for, in 'tSQL'. Transact-SQL (T-SQL) is Microsoft's and Sybase's proprietary extension to SQL. Transact-SQL is central to using SQL Server. All applications that communicate with SQL Server do so by sending Transact-SQL statements to the server, regardless of the user interface, or the application.
Per Wikipedia: http://en.wikipedia.org/wiki/Transact-SQLI have banged my head against the tSQL wall on more than one occasion. Here I've posted a few tips, tricks... and workarounds. Hopefully you will find something useful. Please let me know if you have any questions.
Also remember that SQL Server is for the storing and administering the data, not for making it pretty. We can do some pretty clever manipulations within tSQL, but this really should be done on the front end. Coding the presentation in the backend can be very costly in terms of performance.
Helpful tSQL
- SQL Server BACKUP tSQL statements for different types of BACKUPs
- SQL Server RESTORE tSQL statements for different RESTORE methods
- Monitor transaction log usage Using DBCC SQLPERF(logspace)
- DBCC SQLPERF(logspace) tricks Use it with conditions
- SQL Server Statistics What are Statistics? How do you use them? Why?
- Which tables are not being used? Quickly query which objects are in use.
- How long is that checkdb going to take? Query est. completion time for your checkdb
- DBCC CHECKDB History This is a GOOD one.
- Who dropped that table?! No explanation needed here. :)
- Where is your SQL Error Log? Quick query for location and error log file sizes.
- How to read the SQL Error Log? Use xp_readerrorlog to read the SQL Server Error Log
- Resize or Relocate TempDB Sometimes you need to do it.
- List all SQL instances on your network Super fast, easy way to do it
- Use CMS to get version details from many servers Fast query across many servers
- ISNULL Concatenation Those NULLs are going to get you......
- EXISTS vs IN Which is better? Are they really different?
- Create your own Activity Monitor Use some DMVs to monitor current server process
- Database File Size (MB) Quickly list each file per database, in MB
- How long did that last backup take? Quick way to see the duration of your backup
- Is Your Database Recoverable? Don't wait until there is a problem
- Is your Database configured for Replication? Quick query to confirm replication.
- DBCC's Last Run Date Check when DBCC's were last ran. Please.
- What are SQL Server statistics ? What are they for, how are they used -- and why?
- Last Record Written Identify the last record written
- What tables are being locked and blocked? Query table locking and blocking details.
- Missing Indexes Which Indexes Are Missing?
- Find Your Identity Quickly list of all of your IDENTITY tables
- Missing IDENTITY values Identify the holes, or gaps in your IDENTITY values
- IDENTITY Values Reseed or check your IDENTITY values
- Table Counts Different uses of the COUNT function
- PIVOT, static or dynamic Nice way to report quarterly or annual sales
- Clean Up the White Space Remove empty spaces from your data values
- DATETIME, and then some Many different DATE-related constructs
- String searching Searching objects for a given @string
- Single Line Result Set Return multiple records in a single line result-set
- Another Single Line Result Set Two distinct record counts, in one line
- Another single line result set... Return multiple SUMs back in one line
- Which filegroups do your objects reside upon? sys.filegroups JOIN sys.indexes
- Add a new CONSTRAINT Use this to add a GETDATE() DEFAULT constraint
- Database File Location Default MDF/LDF directory, or existing file(s) location
- Manage SQL Server Agent Remotely Start an Agent job on another instance
- Change Maintenance Plan Ownership Change owner for Agent Job or Maint Plan
- DATETIME Functions Return day name & first day of the week functions
- Day of Week Functions. Day of week name, or get 1st day of the week
- Comma Separated Lists Multiple values in a single, comma separated list
- Tablediff Utility Reconcile two tables?!
- Find the most current .bak files Nifty little piece to find most current .bak/.trn files
- EXEC sp_spaceused on ALL tables Retrieve space details for ALL tables
- SSIS File System Task Process multiple files in a Foreach Loop Container
- Concatenate values with FOR XML PATH Quickly create a comma separated string
- LOWERCASE values Differentiate upper & lowercase values, case-insensitive
- Disable Index DISABLE and ENABLE an index with tSQL
- Move Table to New Filegroup Create a new filegroup & move an existing table
- Filegroup Manipulations Add/Remove Filegroups and Files
- Add New Column with Constraint Add new column with constraint, or just a constraint
- sys.dm_os_memory_clerks Where is SQL's memory going?
- TCP Port & IP Address Retrieve the TCP Port and IP Address for your SQL Server
- Fragmented Indexes Check fragmented indexes with sys.dm_db_index_physical_stats
- Compare Two Databases Check table counts across two databases
- Counts Across Multiple Instances Comparing table counts in more than one instance
- Copy Object Permissions Return permissions per object, with GRANT statement
- List All Object Permissions CTE to output all object permissions
- Insert Large Batches Stress test, or simulate production data flow
- Active SQL Server Connections Return active connection details
- Forced Parameterization Dynamically proceduralize the ad-hoc!! (sort of)
- Aggregate Performance Statistics Use sys.dm_exec_query_stats for cached query plans
- Uninstall SQL Server Reporting Services Yes, it can be done. No reboot required.
- Table & Column CURSOR Use this example to cursor through table columns
- Remove duplicates with CTE Remove duplicates from table without PRIMARY KEY
- TRUNCATE_ONLY in SQL 2008 How to truncate the log in v2008
- Restore Backup from Another Server Restore Production Backup to Dev Server
- Calculate a different date 1st day of the month/year, difference between two dates
- Table Dependencies Quickly reference any table dependencies
- Rename a Filegroup How to change the logical name of a filegroup
- Rename objects Rename database, table, columns, indexes or constraints
- Backup All Databases Quick script to backup multiple databases via cursor
- When the procedure was last executed Using sys.dm_exec_query_stats
- When database objects were last modified Find last modified date per object
- Return parameters for a procedure or function Quick return of proc/function with parms
- Query for all backup details When did it run last? Where's the bak file?
- What is your DB Mirror status? Query to return the state of your mirroring.
- When was your last tran log backup? Query details for last tran log dump, all dbs
- Rename the SQL Server database files How to rename the physical filenames
- Find your most accessed objects Use this query to see what IS or IS NOT being used.
- SQL Server CPUs -- physical, virtual and core count Using master..xp_msver
- SQL Server Virtual Log Files Return a VLF count for each database
- Which tables have XML columns? Use INFORMATION_SCHEMA.COLUMNS.
- Parse delimited list into separate values Without using a function!!
- SSMS crash? Need to recover your scripts? It can be done!
- Add new user defined error messages Use of sp_addmessage
- How to patch Mirrored servers Use 'Rolling upgrade' to apply service pack to Mirrored instance
- Find all SQL Servers on your network Super, super easy and FAST !!
- Can you find the index creation date? um.... yes. But not as easily as you'd think
- Query SQL Server Maintenance Plan details sysmaintplan_plans and sysmaintplan_subplans
- When as your server rebooted? Quick cmd prompt to see your last server bounce.
- How to KILL all database connections Fast way to kick everybody out but yourself
I am continually working to add to the site, and I would love to know if any of this has been useful. Please send me an email at rebecca@sqlfingers.com. Let me know if you have any questions, or if there is something specific that I can help with. I offer different types of consulting; for one or two problems at a time, or even managed service agreements through which I can provide regular, ongoing database administration, monitoring, and more. Give me a call, we can customize an agreement to meet your needs.
-Rebecca
rebecca@sqlfingers.com