dbatools: Command for GitHub Copilot

SMO vs T-SQL Usage Guide

GUIDING PRINCIPLE: Default to using SMO (SQL Server Management Objects) first. Only use T-SQL when SMO doesn't provide the functionality or when T-SQL offers better performance or user experience.

Why SMO First

• Abstraction: SMO provides object-oriented interface that handles version differences automatically
• Type Safety: Strong typing reduces errors compared to dynamic T-SQL strings
• Built-in Methods: Common operations (Create, Drop, Alter, Script) are provided out-of-the-box
• Consistency: SMO ensures consistent behavior across SQL Server versions
• Less Code: Often requires fewer lines than equivalent T-SQL

When to Use SMO

1. Object Manipulation

Creating, dropping, altering database objects:

# PREFERRED - SMO for object manipulation
$newdb = New-Object Microsoft.SqlServer.Management.Smo.Database($server, $dbName)
$newdb.Collation = $Collation
$newdb.RecoveryModel = $RecoveryModel
$newdb.Create()

# Dropping objects
$destServer.Roles[$roleName].Drop()
$destServer.Roles.Refresh()

2. Object Scripting

Generating T-SQL from existing objects:

# PREFERRED - SMO scripting with execution via Query
$sql = $currentRole.Script() | Out-String
Write-Message -Level Debug -Message $sql
$destServer.Query($sql)

# Another example
$destServer.Query($currentEndpoint.Script()) | Out-Null

3. Object Enumeration

Accessing collections and properties:

# PREFERRED - SMO for object access
$databases = $server.Databases
$database = $server.Databases[$dbName]
$isSystemDb = $database.IsSystemObject
$members = $currentRole.EnumMemberNames()

4. Object Properties

Reading and setting object attributes:

# PREFERRED - SMO for property access
$recoveryModel = $db.RecoveryModel
$owner = $db.Owner
$lastBackup = $db.LastBackupDate
$size = $db.Size

When T-SQL is Appropriate

1. System Views and DMVs

When SMO doesn't expose the data efficiently: