How to change the collation of a database in SQL Server?

Have you ever wondered how to change the collation for a database in SQL Server?

Well the other day I received a database from a third party supplier and the first step was to change the Collation of the database. My questioning about why they would supply the database in the incorrect collation setting was met with a wall of silence. Therefore I had to put in my documentation how to change the Collation of the database.

I did it the quick way by right click on database > Select properties > options > Select appropriate collation from dropdown menu next to Collation.

Database properties
Database properties

That was a good enough solution at the time but for my documentation I would always add the T-SQL so that anyone could follow the guide without making mistakes.

Below is an example of how to change the Collation via T-SQL

ALTER DATABASE AdventureWorks2012 COLLATE SQL_Latin1_General_CP850_BIN

Please let me know whether you found this guide useful and leave a message in the comments.

Statistical Aggregate functions in SQL Server

Have you ever wondered how to do program or perform simple statistical function within SQL Server? Well the kind people at Microsoft have enabled us to all to save time by giving us a few inbuilt functions which save us from having to solve them programmatically.
I have used the unit Price column in the SalesOrderDetail table within the Adventureworks database to illustrate the in built functions

 

SELECT --UnitPrice,<br />
COUNT(UnitPrice) AS 'Count of UnitPrice',<br />
AVG(UnitPrice) AS 'Average UnitPrice',<br />
MIN(UnitPrice) AS 'Minimum UnitPrice',<br />
MAX(UnitPrice) AS 'Maximum UnitPrice',<br />
SUM(UnitPrice) AS 'Total UnitPrice',<br />
VAR(UnitPrice) AS 'Variance of UnitPrice',<br />
STDEV(UnitPrice) AS 'Standard deviation of UnitPrice'<br />
FROM [Sales].[SalesOrderDetail] 

1
The COUNT function counts the number of values within the column.
The AVG function returns the average unit price column.
The MIN function returns the smallest value within the column.
The MAX function returns the largest value within the column.
The SUM function returns the sum total of all the values within the column.
The VAR function returns the variance of all the values within the column.
The STDEV function returns the standard of all the values within the column.

 

I am sure that I have probably missed out a few functions so please let me know via the comments below.

Change a database state in SQL

Have you ever wanted to change a databases state from read only to read write or vice versa? Well I am going to show you how using some easy to remember T-SQL which you will be using repeatedly over time.

 

We received a database from a client the other day and noticed one of my System Admin was struggling with the restore. He thought he was doing something wrong with the restore and I said he hadn’t done anything wrong and that the database was likely put into that state prior to being backed up and sent to us.

 

I showed him the below T-SQL which enabled him to change the state to read write and then change it to read only if required.

 

Make Database Read Only

USE [master]

GO

ALTER DATABASE [YourDBName] SET READ_ONLY WITH NO_WAIT

GO

 

Or

 

USE [master]

GO

ALTER DATABASE [YourDBName] SET READ_ONLY

GO

 

Make Database Read/Write

USE [master]

GO

ALTER DATABASE [YourDBName] SET READ_WRITE WITH NO_WAIT

GO

 

Or

 

USE [master]

GO

ALTER DATABASE [YourDBName] SET READ_WRITE

GO

 

List of DBCC commands in SQL Server

Have you wanted a list of DBCC commands to enable you to do a deep dive? Well I am going to list them out for you.

Below is a list which complied after a discussion with a fellow DBA who said there were only about twenty whilst I was sure there were way more. We were both disappointed when I found only 39 commands but I will be using this to further my knowledge of the DBCC commands. Please note these are the documented functions as we always risk using undocumented functions on Production servers.

DBCC ALLOC CHECK

DBCC ALLOC REPAIR

DBCC CHECKALLOC

DBCC CHECKCATALOG

DBCC CHECKCONSTRAINTS

DBCC CHECKDB

DBCC CHECKFILEGROUP

DBCC CHECKIDENT

DBCC CHECKTABLE

DBCC CLEANTABLE

DBCC DBREINDEX

DBCC dllname (FREE)

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

DBCC FREESESSIONCACHE

DBCC FREESYSTEMCACHE

DBCC HELP

DBCC INDEXDEFRAG

DBCC INPUTBUFFER

DBCC IVIEW CHECK

DBCC OPENTRAN

DBCC OUTPUTBUFFER

DBCC PROCCACHE

DBCC SHOW_STATISTICS

DBCC SHOWCONTIG

DBCC SHRINKDATABASE

DBCC SHRINKFILE

DBCC SQLPERF

DBCC SSB CHECK

DBCC SYS CHECK

DBCC SYS REPAIR

DBCC TABLE CHECK

DBCC TABLE REPAIR

DBCC TRACEOFF

DBCC TRACEON

DBCC TRACESTATUS

DBCC UPDATEUSAGE

DBCC USEROPTIONS

SQL Server DMVs and DMFs categorised to make it easier to find information.

Have you wanted to find a DMV which would help give information about AlwaysOn Availability Group ,           Change Data Capture , Change Tracking ,Common Language Runtime , Database , Database Mirroring , Execution , Extended Events , Filestream, Full-Text Search and Semantic Search ,        I/O , Indexes , Object s, Query Notifications , Replication , Resource Governor , Security , Service Broker , SQL Server Operating System and Transactions.

Well I have tried to categorise them and have put them into their relevant category.

AlwaysOn Availability Group sys.dm_hadr_auto_page_repair
sys.dm_hadr_availability_group_states
sys.dm_hadr_availability_replica_cluster_nodes
sys.dm_hadr_availability_replica_cluster_states
sys.dm_hadr_availability_replica_states
sys.dm_hadr_cluster
sys.dm_hadr_cluster_members
sys.dm_hadr_cluster_networks
sys.dm_hadr_database_replica_cluster_states
sys.dm_hadr_database_replica_states
sys.dm_hadr_instance_node_map
sys.dm_hadr_name_id_map
sys.dm_tcp_listener_states
Change Data Capture sys.dm_cdc_errors
sys.dm_cdc_log_scan_sessions
sys.dm_repl_traninfo
Change Tracking sys.dm_tran_commit_table .
Common Language Runtime sys.dm_clr_appdomains
sys.dm_clr_loaded_assemblies
sys.dm_clr_properties
sys.dm_clr_tasks
Database Mirroring sys.dm_db_mirroring_auto_page_repair
sys.dm_db_mirroring_connections
Database sys.dm_db_file_space_usage
sys.dm_db_fts_index_physical_stats
sys.dm_db_partition_stats
sys.dm_db_persisted_sku_features
sys.dm_db_session_space_usage
sys.dm_db_task_space_usage
sys.dm_db_uncontained_entities
Execution sys.dm_exec_background_job_queue
sys.dm_exec_background_job_queue
sys.dm_exec_background_job_queue_stats
sys.dm_exec_cached_plan_dependent_objects
sys.dm_exec_cached_plans
sys.dm_exec_connections
sys.dm_exec_cursors
sys.dm_exec_describe_first_result_set
sys.dm_exec_describe_first_result_set_for_object
sys.dm_exec_plan_attributes
sys.dm_exec_procedure_stats
sys.dm_exec_query_memory_grants
sys.dm_exec_query_optimizer_info
sys.dm_exec_query_plan
sys.dm_exec_query_resource_semaphores
sys.dm_exec_query_stats
sys.dm_exec_requests
sys.dm_exec_sessions
sys.dm_exec_sql_text
sys.dm_exec_text_query_plan
sys.dm_exec_trigger_stats
sys.dm_exec_xml_handles
Extended Events sys.dm_xe_map_values
sys.dm_xe_object_columns
sys.dm_xe_objects
sys.dm_xe_packages
sys.dm_xe_session_event_actions
sys.dm_xe_session_events
sys.dm_xe_session_object_columns
sys.dm_xe_session_targets
sys.dm_xe_sessions
Filestream and FileTable sys.dm_filestream_file_io_handles
sys.dm_filestream_file_io_requests
Full-Text Search and Semantic Search sys.dm_fts_active_catalogs
sys.dm_fts_fdhosts
sys.dm_fts_index_keywords
sys.dm_fts_index_keywords_by_document
sys.dm_fts_index_keywords_by_property
sys.dm_fts_index_population
sys.dm_fts_memory_buffers
sys.dm_fts_memory_pools
sys.dm_fts_outstanding_batches
sys.dm_fts_parser
sys.dm_fts_population_ranges
Index sys.dm_db_index_operational_stats
sys.dm_db_index_physical_stats
sys.dm_db_index_usage_stats
sys.dm_db_missing_index_columns
sys.dm_db_missing_index_details
sys.dm_db_missing_index_group_stats
sys.dm_db_missing_index_groups
I/O sys.dm_io_backup_tapes
sys.dm_io_cluster_shared_drives
sys.dm_io_pending_io_requests
sys.dm_io_virtual_file_stats
Object sys.dm_sql_referenced_entities
sys.dm_sql_referencing_entities
sys.dm_db_stats_properties
Query Notifications sys.dm_qn_subscriptions
Replication sys.dm_repl_articles
sys.dm_repl_schemas
sys.dm_repl_tranhash
sys.dm_repl_traninfo
Resource Governor sys.dm_resource_governor_configuration
sys.dm_resource_governor_resource_pools
sys.dm_resource_governor_workload_groups
Security sys.dm_audit_actions
sys.dm_audit_class_type_map
sys.dm_cryptographic_provider_algorithms
sys.dm_cryptographic_provider_keys
sys.dm_cryptographic_provider_properties
sys.dm_cryptographic_provider_sessions
sys.dm_database_encryption_keys
sys.dm_server_audit_status
Service Broker sys.dm_broker_activated_tasks
sys.dm_broker_connections
sys.dm_broker_forwarded_messages
sys.dm_broker_queue_monitors
SQL Server Operating System sys.dm_os_buffer_descriptors
sys.dm_os_child_instances
sys.dm_os_cluster_nodes
sys.dm_os_dispatcher_pools
sys.dm_os_hosts
sys.dm_os_latch_stats
sys.dm_os_loaded_modules
sys.dm_os_memory_brokers
sys.dm_os_memory_cache_clock_hands
sys.dm_os_memory_cache_counters
sys.dm_os_memory_cache_entries
sys.dm_os_memory_cache_hash_tables
sys.dm_os_memory_clerks
sys.dm_os_memory_nodes
sys.dm_os_memory_objects
sys.dm_os_memory_pools
sys.dm_os_nodes
sys.dm_os_performance_counters
sys.dm_os_process_memory
sys.dm_os_schedulers
sys.dm_os_stacks
sys.dm_os_sys_info
sys.dm_os_sys_memory
sys.dm_os_tasks
sys.dm_os_threads
sys.dm_os_virtual_address_dump
sys.dm_os_volume_stats
sys.dm_os_wait_stats
sys.dm_os_waiting_tasks
sys.dm_os_windows_info
sys.dm_os_workers
Transaction sys.dm_tran_active_snapshot_database_transactions
sys.dm_tran_active_transactions
sys.dm_tran_current_snapshot
sys.dm_tran_current_transaction
sys.dm_tran_database_transactions
sys.dm_tran_locks
sys.dm_tran_session_transactions
sys.dm_tran_top_version_generators
sys.dm_tran_transactions_snapshot
sys.dm_tran_version_store

How to list all SQL DMVs (Dynamic management views) s and DMFs (Dynamic management functions ) in SQL Server?

Have you ever wondered how many DMVs are in SQL? Wondered whether a DMV exists which help diagnose your problems? Well I am going to show you how to list all the DMVs and DMFs so that you can do a deeper dive into them or await my series where I will be exploring each every DMV and DMF.

Before you run the script it is important to understand that Dynamic Management Views and Functions are used to return information about the status of SQL Server so that you can performance tune issues, check and diagnose for problems.

</p>
<p>SELECT * FROM SYS.ALL_OBJECTS</p>
<p>WHERE [name] LIKE '%dm_%'</p>
<p>AND [type] IN ('V', 'TF', 'IF')</p>
<p>ORDER BY [name]</p>
<p>

This script will only run in SQL2005 ( SQL 2000 does not include these features)  and above.

In SQL 2008R2 there are 141 DMVs and DMFs and in 2012 there are 171.

Easily Check Database Integrity (DBCC CHECKDB) For All Databases On A Server

Today I was doing my daily checks on one of my biggest SQL instances which has over 1,000 databases and noticed that SQL Agent was stopped. I couldn’t see any errors and was able to simply start it without any problems but I was concerned that the daily integrity check jobs had not run and I am way too cautious to get caught by not doing it manually.

Initially I wasn’t  too sure how to do it very quickly without creating a script using DBCC CHECKDB, querying sys.databases  with some dynamic T-SQL but decided to give the below script a try and luckily it worked.

 EXEC sp_MSforeachDB ‘DBCC CHECKDB (?)’

What are the SQL Server System databases?

SQL Server System databases

SQL Server has 5 very default system databases which are

  1. Master
  2. MSDB
  3. Model
  4. TempDB
  5. Resource

Master

As the name implies this is one of the most important databases on the system and without it SQL Server will not start. It stores all the configuration of the instance and all other databases which are subsequently created. The information is stored into tables known as system tables.

MSDB

The MSDB database is used by SQL Agent and contains data about jobs, job schedules, alerts, log shipping, database mail, recovery of damaged pages….. This information is retrievable through the tables stored within this database.

Model

This is basically a template database for the creation of new databases.

TempDB

TempDB is recreated every time SQL is restarted and is basically used for storing of information temporarily when SQL Server doesn’t have sufficient memory available. This could be a sorting query, temporary tables, holding variables…..

Resource

This is a hidden read-only database which contains information on all objects within SQL server. If you look in the directory of your SQL Server installation you will find two files named mssqlsystemresource.mdf and mssqlsystemresource.ldf which contain the resource database. The hidden nature of the database makes it easy to upgrade via hot-fixes and service packs.

How can I find out what SQL Server version is running?

I was asked by someone which SQL Server version a server was running and was surprised when my DBA colleague said to run a stored procedure I had never heard of.  I had not thought that there would be more than one way as the usual method I used below was so simple I didn’t think that there would be a need for another method.  I then did a deep dive of all the different ways I could find out the version of SQL Server I was running and have included them below.

Method 1

Go to Help > About

1

Method 2

Use the following script .

SELECT @@VERSION

2

That is the script which I would use and I think is pretty much the most commonly used method within the SQL Server community.

Method 3

EXEC sp_MSgetversion

3

Method 4

EXEC xp_msver

4

Method 5

SELECT SERVERPROPERTY(‘productversion‘), SERVERPROPERTY (‘productlevel‘), SERVERPROPERTY (‘edition‘)

5

Method 6

Within the SQL logs the current version will be listed when the log was last cycled.

6