SQL Server: List table columns names instead of using SELECT *

Smart and Easy - @SeniorDBA

In SQL Server, and in any common SQL language, the asterisk ( * ) tells the database engine to return all columns within a table. Using “SELECT *” in your queries is a bad idea, and the reasons why we don’t recommend this is:

    • Security – If an unauthorized user gets access to your query and it returns * (meaning all columns)  it could leave every column available for misuse.
    • Performance – It should always be faster to return the data just the columns you need than for all available columns. When using “SELECT *” the column list is resolved each time you run the statement, so it is almost always true that it is faster to return just the columns you need instead of all columns.
    • Confusion – As things change over time, you really can’t be sure that a query written today using “SELECT *” will return results easily digestible by the program or report that must consume the data in the future. Columns may be added or removed to a table over time, and if you specify columns in your SELECT queries you know exactly which ones will be returned.

We know the reason must people use “SELECT *” in a query is because it is easy and fast. So how can we make using the column names easy and fast?There are a few examples that make it extremely easy to list the column names from a table.

Continue reading “SQL Server: List table columns names instead of using SELECT *”
Advertisements

Free Download: SQL Server Management Studio 17.6

SQL Server

SQL Server Management Studio (SSMS) is an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. SSMS combines a broad group of graphical tools with a number of rich script editors to provide developers and administrators of all skill levels access to SQL Server.

Microsoft has announced the latest release of SQL Server Management Studio (SSMS) in October. This release features improved compatibility with previous versions of SQL Server and a stand-alone web installer.

Get it here:

Continue reading “Free Download: SQL Server Management Studio 17.6”

Free Download: SQL Server Management Studio 17.4

SQL Server

SQL Server Management Studio (SSMS) is an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. SSMS combines a broad group of graphical tools with a number of rich script editors to provide developers and administrators of all skill levels access to SQL Server.

Microsoft has announced the latest release of SQL Server Management Studio (SSMS) in October. This release features improved compatibility with previous versions of SQL Server and a stand-alone web installer.

Get it here:

Continue reading “Free Download: SQL Server Management Studio 17.4”

Free Download: SQL Server Management Studio 17.3

SQL Server

SQL Server Management Studio (SSMS) is an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. SSMS combines a broad group of graphical tools with a number of rich script editors to provide developers and administrators of all skill levels access to SQL Server.

Microsoft has announced the latest release of SQL Server Management Studio (SSMS) in October. This release features improved compatibility with previous versions of SQL Server and a stand-alone web installer.

Get it here:

Continue reading “Free Download: SQL Server Management Studio 17.3”

SQL Server Hall of Shame

Shame - @SeniorDBA

In a recent post by Adam Machanic, he asked his followers to send him the items they thought were the worst features of SQL Server. The list he developed is called the “SQL Hall of Shame”. He put together the following list:

  • In-Memory OLTP
  • English Query
  • Data Quality Services (DQS)
  • Master Data Services (MDS)
  • Auto-Shrink
  • Auto-Close
  • Auto-Grow
  • Notification Services (SSNS)
  • Query Notifications
  • Buffer Pool Extension (BPE)
  • Management Data Warehouse (MDW) / Data Collector
  • Lightweight Pooling / Fiber Mode
  • JSON
  • SQL Server Management Studio (SSMS)
  • Connect to SSIS from SQL Server Management Studio
  • DROP DATABASE IF EXISTS
  • Columnsets (and Sparse Columns in general)
  • SQLCLR
  • Utility Control Point (UCP)
  • Raw Partitions
  • Uninstall
  • Service Broker (SSB)
  • Not Freeing Allocated Memory Except Under Pressure
  • Database Engine Tuning Advisor (née Index Tuning Wizard)
  • DBCC PINTABLE
  • Virtual Interface Adaptor (VIA) Network Protocols
  • Mirrored Backups
  • SQL_VARIANT

If you read the article by Adam Machanic, you’ll get the detail for each item on the list.

Free Download: SQL Server Management Studio 17

SQL Server

SQL Server Management Studio (SSMS) is an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. SSMS combines a broad group of graphical tools with a number of rich script editors to provide developers and administrators of all skill levels access to SQL Server.

Microsoft has announced the latest release of SQL Server Management Studio (SSMS) in May. This release features improved compatibility with previous versions of SQL Server, a stand-alone web installer, and toast notifications within SSMS when new releases become available.

Get it here:

Download – The version number for the latest release is 14.0.17099.0

New in this release

  • Over 100 connect issues fixed
  • SQL Server PowerShell module moved out of SSMS and into the PowerShell gallery
  • Icons updated to support high-DPI display modes
  • Numerous performance improvements in Object Explorer
  • WSUS support for upgrading to future 17.X versions
  • Improved Multi-factor authentication support
  • Enhancements to Availability Group functionality (Support for SQL Server on Linux, direct seeding, endpoint URL handling, and more)
  • Showplan enhancements including new feature to analyze actual existing showplan to help diagnose issues
  • New DAX Query Windows
  • Numerous other enhancements for Power Query support

Query SQL Server Using PowerShell

PowerShell - SeniorDBA

The ability for you to query a SQL Server instance from PowerShell might actually be helpful, especially if you don’t have access to an instance of SQL Server Management Studio.

In this article by Grant Fritchey we see a simple way to connect to and execute a SQL Server query using PowerShell.

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
# Get the connection
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = 'Server=WIN-3SRG45GBF97\DOJO;Database=WideWorldImporters;trusted_connection=true'

# Retrieve test data
$BillToCustomerCmd = New-Object System.Data.SqlClient.SqlCommand
$BillToCustomerCmd.CommandText = "SELECT DISTINCT i.BillToCustomerID
FROM Sales.Invoices as i;"
$BillToCustomerCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $BillToCustomerCmd
$BillToCustomerList = New-Object System.Data.DataSet
$SqlAdapter.Fill($BillToCustomerList)

# Set up test query
$SQLCmd = New-Object System.Data.SqlClient.SqlCommand
$SQLCmd.Connection = $SqlConnection
$SQLCmd.CommandText = "DECLARE @sqlquery NVARCHAR(MAX);
SET @sqlquery
= N'SELECT si.StockItemName,
i.InvoiceDate,
i.SalespersonPersonID
FROM Sales.Invoices AS i
JOIN Sales.InvoiceLines AS il
ON il.InvoiceID = i.InvoiceID
JOIN Warehouse.StockItems AS si
ON si.StockItemID = il.StockItemID
WHERE i.BillToCustomerID = @BillToCustomerID;';

DECLARE @parms NVARCHAR(MAX);
SET @parms = '@BillToCustomerID int';

EXEC sys.sp_executesql @stmt = @sqlquery,
@params = @parms,
@BillToCustomerID = @btc;"
$SQLCmd.Parameters.Add("@btc",[System.Data.SqlDbType]"Int")

# Run the tests
foreach($row in $BillToCustomerList.Tables[0])
{
$SqlConnection.Open()
$SQLCmd.Parameters["@btc"].Value = $row[0]
$SQLCmd.ExecuteNonQuery() | Out-Null
$sqlconnection.Close()
}

Read the entire article here.