How to work with Registered Servers in SQL Server Management Studio

Using Registered Servers in SQL Server Management Studio (SSMS)

SQL Server Management Studio (SSMS) offers a powerful feature called Registered Servers, which allows users to easily manage and connect to multiple SQL Server instances. This feature is especially useful for database administrators who frequently work with several servers and want to streamline query execution across them.

How to Access Registered Servers

To open the Registered Servers window in SSMS, follow one of the methods below:

• Navigate to View > Registered Servers from the main menu.
• Use the keyboard shortcut Ctrl + Alt + G.

Registered Servers window in SSMS

Creating a New Server Group

You can organize your servers into custom groups for better manageability. To create a new server group:

• Right-click on Local Server Groups within the Registered Servers window.
• Choose New Server Group and provide a meaningful name.

Creating a new server group in SSMS

Registering a New Server

To register a new server:

1. Right-click on the desired server group and select New Server Registration.
2. Enter the Server Name, choose the Authentication Type, and provide credentials if needed.
3. You also have the option to save the login information.

Registering a new server with credentials

Additional Connection Properties

While registering a server, you can configure additional properties such as:

• Default database
• Connection color for easy visual identification

Setting default database and connection color

Context Menu Options

By right-clicking on a registered server or group, you’ll find several useful options like:

• Connecting to the server
• Editing server registration
• Deleting or exporting registrations

Context menu options in the Registered Servers window

Executing Queries Across Multiple Servers

One of the key benefits of using Registered Servers is the ability to run the same query across multiple servers simultaneously. This is ideal for monitoring, health checks, or executing standard configurations across environments.

---

Further Reading:
To explore more about Registered Servers, visit the official Microsoft documentation:
Register Servers - SSMS | Microsoft Docs

SQL Server Management Studio (SSMS) Shortcuts

 

🚀 Boost Your Productivity in SQL Server Management Studio (SSMS) with These Essential Shortcuts

SQL Server Management Studio (SSMS) is a robust tool designed for database professionals. Whether you're developing, designing, or maintaining SQL databases, SSMS simplifies the entire workflow with a suite of built-in features and keyboard shortcuts that can significantly speed up your work.

In this post, we’ll explore some of the most useful SSMS shortcuts and options that every developer and database administrator should know.

---

🔄 Switch Between Databases Quickly

Shortcut: Ctrl + U
When you're in the SQL Editor, pressing Ctrl + U highlights the database dropdown in the toolbar. Use the arrow keys to switch between databases and press Esc to return to the editor.

---

🧠 Analyze Your Queries

• Estimated Execution Plan: Ctrl + L
  View the estimated execution plan of your query before running it—great for performance tuning.

• Toggle Results Pane: Ctrl + R
  Show or hide the Results pane to get a cleaner view of your query editor.

---

🧩 Code Faster with Snippets and Autocomplete

• Insert Code Snippets: Ctrl + K, Ctrl + X
  Access predefined code snippets to insert common patterns quickly.

• Activate Autocomplete: Ctrl + Space
  Trigger IntelliSense to auto-complete SQL keywords, object names, and more.

---

📋 Clipboard Ring

• Cycle Through Clipboard History: Ctrl + Shift + V
  Access previously copied text items from your clipboard history and reuse them.

---

🧭 Navigate Code Like a Pro

• Ctrl + ]: Jump to the matching parenthesis.

• Ctrl + –: Move to the last cursor location.

• Ctrl + Shift + –: Move forward to the next cursor location.

---

💬 Commenting & Uncommenting Code

• Comment Selected Text: Ctrl + K, Ctrl + C

• Uncomment Selected Text: Ctrl + K, Ctrl + U

---

📐 Block (Multi-Line) Selection

• Shortcut: Shift + Alt + Arrow Keys
  Select and edit text in a rectangular block—ideal for editing columns of code or data.

---

⚙️ Customize Query Shortcuts

Navigate to:
Tools → Options → Environment → Keyboard → Query Shortcuts

From here, you can assign your own custom SQL commands to specific keyboard shortcuts.

---

🔖 Bookmarks for Navigation

• Toggle Bookmark: Ctrl + K, Ctrl + K

• Go to Next Bookmark: Ctrl + K, Ctrl + N

---

🖥️ Full-Screen Editor Mode

• Shortcut: Shift + Alt + Enter
  Enter full-screen mode to focus solely on your code.

---

📥 Download & Resources

• Download SSMS 18.2:
  Download Link

• Official Microsoft Documentation:
  SSMS Download Guide
  SSMS Keyboard Shortcuts

---

Want more SSMS tips or tutorials? Drop a comment or share this post with your fellow DBAs and developers!

How to stretch /partition database in SQL server old version

 How to stretch /partition a database in SQL server old version

Choosing the Right Database Architecture

These days, there are many types of storage systems, server hardware, and database technologies available on the market. Often, consultants and management teams believe that only high-end solutions like Oracle or NoSQL databases can meet their business needs — leading to expensive and sometimes unnecessary implementations.

However, the best solution truly depends on real-world exposure, experience, and actual business requirements. In this section, we’ll explore how to design a powerful and scalable database architecture that supports high performance and data availability. Database design is the heart of any business application — it must be done right.

Traditional Database Design

• High-end database server with SSD/SAN/NAS storage.
• Requires advanced configuration and high-maintenance, often demanding more attention and longer processing times.
• Backup and recovery tasks can be complex and time-consuming.

Alternative Database Design & Partitioning Concepts Using Diverse Infrastructure

All examples below are based on a primary database with multiple partitioned or distributed databases:

1. Cloud Hosting: Multiple Databases on a Single Server

• Partition databases to maximize hardware usage.
• Design based on record volume, data importance, and key columns.
• Consider storage capacity and application performance requirements.

2. Cloud Hosting: Multiple Servers with Hybrid Storage

• Utilize multiple cloud servers using a hybrid storage model.
• Designate one primary and multiple child databases based on capacity and requirements.
• Leverage dynamic processing and storage scalability.
• Use linked servers with appropriate security to connect child databases to the primary.

3. On-Premises Hosting: Multiple Databases on a Single Server

• Single on-premises server with partitioned databases across multiple disks.
• One primary database and several child databases.
• Map database files to specific storage tiers (high/mid/low).
• Link tables via views or synonyms for unified access.

4. On-Premises Hosting: Multiple Stretch Databases Across Multiple Servers

• Partition databases to leverage multiple on-premises servers efficiently.
• Organize and access data through linked servers and data access libraries.
• Distribute data according to access priority for optimal performance.

Pros

• Small databases are easier to maintain than a single large database.
• Simplifies backup, recovery, and restore operations.
• Cost-effective — databases can be allocated to different storage tiers based on importance.
• Speeds up UAT/TEST environment refresh cycles.
• Improves server productivity and overall system performance.
• Facilitates easier configuration of replication, log shipping, and mirroring.

Cons

• Improper design and misconfiguration can be detrimental.
• Requires careful monitoring and more administrative effort.

Conclusion

As discussed, the database is the core of your application. Start by identifying requirements such as expected data growth, key entities, and referential relationships. Evaluate all possible solutions with their pros and cons. Collaborate closely with your technical team before implementation. In many cases, SQL Server technology alone can satisfy your needs — provided you understand its capabilities and limitations.

All user-created statistics

 

How to Retrieve User Statistics in SQL

If you're looking to analyze user statistics in a database, this SQL script can help you retrieve detailed information about the statistics of your database tables. Here's how you can do it:

The script queries the system tables to gather essential details, such as the name of the table, statistic name, column name, data type, and column length. This is useful for understanding the structure and size of your data in the database.

SQL Script:

SELECT 
   st.[Name] AS [TableName], 
   ss.[Name] AS StatisticName,
   sc.[Name] AS [ColumnName], 
   t.[Name] AS DataType,
   CASE
      WHEN sc.max_length = -1 THEN 'varchar(max), nvarchar(max), varbinary(max) or xml'
      ELSE CAST(sc.max_length AS VARCHAR(10))
   END AS ColumnLength
FROM [sys].stats ss
   JOIN [sys].tables st ON ss.OBJECT_ID = st.OBJECT_ID
   JOIN [sys].stats_columns ssc ON ss.stats_id = ssc.stats_id AND st.OBJECT_ID = ssc.OBJECT_ID
   JOIN [sys].columns sc ON ssc.column_id = sc.column_id AND st.OBJECT_ID = sc.OBJECT_ID
   JOIN [sys].types t ON sc.system_type_id = t.system_type_id
WHERE ss.user_created = 1
ORDER BY t.[Name], st.[Name];

This query works by joining several system tables:

• sys.stats: Contains statistics data.
• sys.tables: Provides information about the tables in the database.
• sys.stats_columns: Links statistics to their respective columns.
• sys.columns: Describes the columns in the tables.
• sys.types: Contains the data types of the columns.

The script is particularly helpful for analyzing custom user-created statistics in SQL Server, and it organizes the results in an ordered fashion based on the data type and table name.

Why Choosing the Right Data Type Matters in Database Design

 

Why Choosing the Right Data Type Matters in SQL Server

Storage is a crucial component in any RDBMS. To address this, Microsoft provides a variety of data types in SQL Server, each designed to optimize storage and performance. Whether you're working with OLTP or OLAP databases, how queries are processed and data is stored directly impacts performance and infrastructure costs.

---

VARCHAR vs NVARCHAR

Choosing between VARCHAR and NVARCHAR is a common decision developers face. Here are key considerations:

• Use NVARCHAR for Unicode or multilingual data.

• If your data contains only English characters and numbers, VARCHAR is generally more storage-efficient.

Important considerations before choosing:

• NVARCHAR Consumes 2 bytes per character, increasing storage requirements.

• Indexed columns use additional bytes depending on the data type.

• When using stored procedures, always match data types between parameters and variables. Mixing VARCHAR and NVARCHAR can lead to performance degradation due to implicit conversions.

---

DATE vs DATETIME (and other date/time types)

Each SQL Server date/time data type serves a different purpose and uses different storage sizes. Choosing the correct one improves both performance and clarity.

Data Type	Format	Range	Accuracy	Storage (bytes)	Fractional Seconds	Time Zone Offset
TIME	hh:mm:ss	00:00:00.0000000 – 23:59:59.9999999	100 nanoseconds	3 to 5	Yes	No
DATE	YYYY-MM-DD	0001-01-01 – 9999-12-31	1 day	3	No	No
SMALLDATETIME	YYYY-MM-DD hh:mm:ss	1900-01-01 – 2079-06-06	1 minute	4	No	No
DATETIME	YYYY-MM-DD hh:mm:ss	1753-01-01 – 9999-12-31	~3 milliseconds	8	No	No
DATETIME2	YYYY-MM-DD hh:mm:ss	0001-01-01 – 9999-12-31 23:59:59.9999999	100 nanoseconds	6 to 8	Yes	No
DATETIMEOFFSET	YYYY-MM-DD hh:mm:ss [+	-]hh:mm	0001-01-01 – 9999-12-31 (with time zone awareness)	100 nanoseconds	8 to 10	Yes

Tip: Be cautious when using date/time types in WHERE clauses. Using the wrong data type or format may yield unexpected or inefficient results.

---

Data Type Conversion in SQL Server

SQL Server allows both explicit and implicit conversions between system-defined data types such as xml, bigint, and sql_variant.

• Note: While implicit conversion to sql_variant is supported, implicit conversion from sql_variant is not.

SQL Server data type conversion chart

Reference:
Data Type Conversion - SQL Server Docs

SQL Server services

SQL Server 7

Common Name	Service Display Name	Service Name	Executable Name
Distributed Transaction Coordinator (DTC)	Distributed Transaction Coordinator	MSDTC	msdtc.exe
SQL Server	MSSQLServer	MSSQLServer	sqlservr.exe
SQL Server Agent	SQLServerAgent	SQLServerAgent	sqlagent.exe
SQL Server Analysis Services	MSSQLServerOLAPService	MSSQLServerOLAPService	msmdsrv.exe

SQL Server 2000

Common Name	Service Display Name	Service Name	Executable Name
Distributed Transaction Coordinator (DTC)	Distributed Transaction Coordinator	MSDTC	msdtc.exe
SQL Server	MSSQLServer	MSSQLServer	sqlservr.exe
SQL Server Active Directory Helper	MSSQLServerADHelper	MSSQLServerADHelper	sqladhlp.exe
SQL Server Agent	SQLServerAgent	SQLServerAgent	sqlagent.exe
SQL Server Analysis Services	MSSQLServerOLAPService	MSSQLServerOLAPService	msmdsrv.exe

SQL Server 2005

Common Name	Service Display Name	Service Name	Executable Name
Distributed Transaction Coordinator (DTC)	Distributed Transaction Coordinator	MSDTC	msdtc.exe
SQL Server	SQL Server (MSSQLSERVER)	MSSQLSERVER	sqlservr.exe
SQL Server Active Directory Helper	SQL Server Active Directory Helper	MSSQLServerADHelper	sqladhlp90.exe
SQL Server Agent	SQL Server Agent (MSSQLServer)	SQLSERVERAGENT	sqlagent.exe
SQL Server Analysis Services	SQL Server Analysis Services (MSSQLSERVER)	MSSQLServerOLAPService	msmdsrv.exe
SQL Server Browser	SQL Server Browser	SQLBrowser	sqlbrowser.exe
SQL Server Full Text Search	SQL Server FullText Search (MSSQLSERVER)	msftesql	msftesql.exe
SQL Server Integration Services	SQL Server Integration Services	MsDtsServer	MsDtsSrvr.exe
SQL Server Reporting Services	SQL Server Reporting Services (MSSQLSERVER)	ReportServer	ReportingServicesService.exe
SQL Server VSS Writer	SQL Server VSS Writer	SQLWriter	sqlwriter.exe

SQL Server 2008 and SQL Server 2008 R2

Common Name	Service Display Name	Service Name	Executable Name
Distributed Transaction Coordinator (DTC)	Distributed Transaction Coordinator	MSDTC	msdtc.exe
SQL Server	SQL Server (MSSQLSERVER)	MSSQLSERVER	sqlservr.exe
SQL Server Active Directory Helper	SQL Active Directory Helper Service	MSSQLServerADHelper100	SQLADHLP.EXE
SQL Server Agent	SQL Server Agent (MSSQLSERVER)	SQLSERVERAGENT	SQLAGENT.EXE
SQL Server Analysis Services	SQL Server Analysis Services (MSSQLSERVER)	MSSQLServerOLAPService	msmdsrv.exe
SQL Server Browser	SQL Server Browser	SQLBrowser	sqlbrowser.exe
SQL Server Full Text Search	SQL Full-text Filter Daemon Launcher (MSSQLSERVER)	MSSQLFDLauncher	fdlauncher.exe
SQL Server Integration Services	SQL Server Integration Services 10.0	MsDtsServer100	MsDtsSrvr.exe
SQL Server Reporting Services	SQL Server Reporting Services (MSSQLSERVER)	ReportServer	ReportingServicesService.exe
SQL Server VSS Writer	SQL Server VSS Writer	SQLWriter	sqlwriter.exe

 SQL Server Version and code name

Version	Year	Release name	Code name	Internal database version
1.0 (OS/2)	1989	SQL Server 1.0 (16-bit)	Filipi	-
1.1 (OS/2)	1990	SQL Server 1.1 (16-bit)	Pietro	-
4.2A (OS/2)	1992	SQL Server 4.2A (16-bit)	-	-
4.2B (OS/2)	1993	SQL Server 4.2B (16-bit)	-	-
4.21a (WinNT)	1993	SQL Server 4.21a	SQLNT	-
6	1995	SQL Server 6.0	SQL95	406
6.5	1996	SQL Server 6.5	Hydra	408
7	1998	SQL Server 7.0	Sphinx	515
-	1999	SQL Server 7.0 OLAP Tools	Plato	-
8	2000	SQL Server 2000	Shiloh	539
8	2003	SQL Server 2000 64-bit Edition	Liberty	539
9	2005	SQL Server 2005	Yukon	611/612
10	2008	SQL Server 2008	Katmai	655
10.25	2010	Azure SQL database (initial release)	Cloud database or CloudDB
10.5	2010	SQL Server 2008 R2	Kilimanjaro (aka KJ)	661
11	2012	SQL Server 2012	Denali	706
12	2014	SQL Server 2014	SQL14	782
13	2016	SQL Server 2016	SQL16	852
14	2017	SQL Server 2017	Helsinki	869
Not yet released	2019	SQL Server 2019 CTP	Seattle	895

Link: https://sqlserverbuilds.blogspot.com/

For more details on determining SQL Server version and edition, visit: How to determine the version, edition, and update level of SQL Server

Know about SQL Server edition

Enterprise

The Enterprise edition of SQL Server is designed for large-scale environments, capable of managing databases up to 524 petabytes. It can address up to 12 terabytes of memory and supports up to 640 logical processors (CPU cores), making it ideal for mission-critical applications and high-demand workloads.

Standard

The Standard edition differs from the Enterprise edition in several key areas. It supports fewer active instances (the number of nodes in a cluster) and lacks some of the advanced high-availability features, such as hot-add memory. This edition is limited to a maximum of 4 sockets or 24 cores with 128 GB of memory, making it suitable for small to medium-sized businesses or less demanding workloads compared to the Enterprise edition.

Web

The SQL Server Web edition is a cost-effective solution designed for web hosting environments. It offers a low total-cost-of-ownership option with support for a maximum of 4 sockets or 16 cores and 64 GB of memory. This edition is ideal for small to medium-sized web applications with lower resource demands.

Express

SQL Server Express is a free, lightweight edition of SQL Server, ideal for lightweight applications, smaller databases, or learning purposes. It is limited to 1 socket or 4 cores with 1410 MB of memory, making it suitable for smaller applications or development environments that don’t require advanced features or extensive scalability.

Developer

SQL Server Developer edition provides developers with all the features of the Enterprise edition but is licensed for use solely as a development and test system. It allows developers to build and test applications on top of SQL Server, making it an excellent choice for those creating applications for production environments. This edition includes all the Enterprise features, but it is not licensed for production use.

For more detailed information on SQL Server editions and components, visit the official documentation: SQL Server Editions and Components.