Thursday, December 30, 2010

How to check the Authentication Mode with tSQL.

Of course you know, we've got two different Authentication Modes -
  Windows Authentication
  Mixed-mode Authentication

Where 'Mixed-Mode is both Windows Authentication and SQL Server Authentication.

Windows Authentication is the most secure, but we often have to use SQL Authentication, for the non-Windows platforms, such as Linux.  Hence, the Mixed-mode approach.

Here are a few different ways, aside from SSMS, that you can use to check your authentication mode:

1.  xp_instance_regread -
This procedure allows us to read the registry, where SQL Server stores '1' for Windows Authentication, and '2' for SQL Server / Mixed Mode Authentication.

DECLARE @AuthenticationMode INT
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', 
N'LoginMode', @AuthenticationMode OUTPUT
SELECT CASE @AuthenticationMode 
 WHEN 1 THEN 'Windows Authentication'
 WHEN 2 THEN 'Windows and SQL Server Authentication'
 ELSE 'Unknown'
END as [Authentication Mode]


2.  Server Property -
The Server Property function returns '1' for Windows Authentication, and '0' for SQL Server/Mixed-Mode Authentication.

SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
 WHEN 1 THEN 'Windows Authentication'
 WHEN 0 THEN 'Windows and SQL Server Authentication' 
END as [Authentication Mode]
NOTE:  It is not the same as what is stored in the registry, and returned from the use of xp_instance_regread.

3. xp_loginfo  -  
Returns a value of 'Windows NT Authentication' for Windows Authentication and 'Mixed' for SQL Server/Mixed-Mode Authentication.
EXEC master.sys.xp_loginconfig 'login mode'

Retrieve memory details from the OS


select 
total_physical_memory_kb,
available_physical_memory_kb,
system_cache_kb,
system_high_memory_signal_state, 
system_low_memory_signal_state,
system_memory_state_desc as system_memory_state 
from 
sys.dm_os_sys_memory


Check out BOL (Books Online), there are other details as well, but this is the one I use to get a quick look at the overall state of the system memory.

Monday, December 6, 2010

Table counts

' SELECT COUNT(*) ' on those big tables can be a little invasive.  Not only fairly timely, but the used resources can be extreme.  Here are a few different methods for using the COUNT function.  Understand, these methods are just selective counts, per table.  If you need to retrieve counts across multiple tables, possibly for comparison, you could try something like this:  Counts Across Multiple Tables
Or even this, to compare counts across databases:  Compare Two Databases

-- Just one table:
1. Return count of all records in the table
SELECT COUNT(*) FROM dbo.TABLE

2. Return count of all values for the given COLUMN, excluding NULL values:
SELECT COUNT(COLUMN) FROM dbo.TABLE

3. Return the count of distinct values for the given COLUMN:
SELECT COUNT(DISTINCT(COLUMN)) FROM dbo.TABLE

(Does not work with MSAccess.)


-- All tables:
1.  Return a count for all tables.
CREATE TABLE dbo.TABLECOUNTS
(
 TABLENAME VARCHAR(255) NOT NULL,
 RECORDCOUNT INT NOT NULL
 )
GO


EXEC sp_msforeachtable
  "INSERT INTO TABLECOUNTS SELECT '?' AS TABLENAME, COUNT(*) FROM ?"
SELECT * FROM dbo.TABLECOUNTS

-- Of course, you'll see the fully qualified table names, like this:  [dbo].[tablename].  Use the following updates, if desired, to get rid of the '[dbo].', and the ending right bracket, for each TABLENAME:

UPDATE dbo.TABLECOUNTS
SET TABLENAME = REPLACE(TABLENAME,'[dbo].[','')


UPDATE dbo.TABLECOUNTS
SET TABLENAME = REPLACE(TABLENAME,']','')


SELECT * FROM TABLECOUNTS


DROP TABLE TABLECOUNTS


2. Notably faster, but remember -- it is only a close estimate.
  The sysindexes table is not updated real time, so your numbers may be a little less than accurate.

SELECT SO.Name, SI.rows
FROM sysindexes SI, SysObjects SO
WHERE SI.id = SO.ID
AND SI.indid < 2
AND SO.Type = 'U'
ORDER BY so.name

3. Similar to #2, but statistics are updated first with DBCC UPDATEUSAGE.

DECLARE @SQL NVARCHAR(255)
SET @SQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')'
EXEC sp_executeSQL @SQL


SELECT
Schema_name(t.schema_id) AS SchemaName,
t.name AS TableName,
i.rows AS [Rows]
FROM
sys.tables AS t INNER JOIN sys.sysindexes AS i
ON t.object_id = i.id
AND i.indid < 2
ORDER BY
t.name

If you've got v2005 or v2008, you should try to steer clear from querying sysindexes.  It will work, but it is said to be removed in a future version.  Probably best to use the DMV's instead, like this:

SELECT 
o.name, 
ddps.row_count
FROM 
indexes i INNER JOIN sys.objects o
ON sys. i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats ddps
ON i.OBJECT_ID = ddps.OBJECT_ID
AND i.index_id = ddps.index_id
WHERE 
i.index_id < 2
AND o.is_ms_shipped = 0
ORDER BY 
o.NAME

Find your IDENTITY tables, sys.identity_columns

Pretty helpful... and necessary, if and when you're moving a lot of data around.  This is a real quick checker for which tables were defined with the IDENTITY property:

     -- Find IDENTITIES v2008
  SELECT
OBJECT_NAME(OBJECT_ID) AS TABLENAME,
    NAME AS COLUMNNAME,
    SEED_VALUE,
    INCREMENT_VALUE,
    LAST_VALUE,
    IS_NOT_FOR_REPLICATION
  FROM
SYS.IDENTITY_COLUMNS
  ORDER BY
    tablename




  --Find IDENTITIES v2000
  SELECT
'TableName' = o.name,
'ColumnName' = c.name
  FROM
   sysobjects o INNER JOIN syscolumns c ON o.id = c.id
  WHERE
c.status = 128
AND o.type = 'u'
AND o.status > 0
  ORDER BY
o.name


Even better, here's one that I put together very recently using sys.identity_columns, which returns a row for each column in your database that is an IDENTITY column.  I joined it to sys.sysobjects to give you a little more detail, by including the table names as well:

SELECT 
so.name [TableName],
ic.name [ColumnName],
ic.seed_value [Seed],
ic.increment_value [Increment],
ic.last_value [LastValue]
FROM 
sys.identity_columns ic JOIN sys.sysobjects so
 ON ic.object_id = so.id
WHERE 
so.name <> 'sysdiagrams'
AND so.name NOT LIKE 'queue_%'
ORDER BY 
so.name

You will see I excluded some system objects in that WHERE clause.  Feel free to include them, if desired.  And, if you've got tables with IDENTITY values, you should definitely take a look at this:  http://www.sqlfingers.com/2011/02/missing-identity-values.html

ISNULL Concatentation

If you've got NULLs in there, they are going to get you.....

We've got ten records in the Customers table, yet only three of them have middle names.  How to return all customer names in one select, with and without middle names?  There are many ways to work around NULLs, but here is a quick trick using ISNULL, which replaces NULL with the value you provide (per BOL).

For records with MiddleNames, the full name is returned like this:           Judy Lynn Smith
For records without MiddleNames, the record is returned like this:           Karen Williams


SELECT
REPLACE(FirstName + ' ' + ISNULL(MiddleName, '') + ' ' + LastName, ' ', ' ')  FullName
FROM
dbo.Customers


    FullName
    Jamie Lynn Smith
    Judy Lynn Harris
    Karen  Williams
    Marge  West
    Betsy  Simpson
    Morgan  Best
    Rhianna  Grant
    John Matthew Shaw
    Sammy  Thompson
    Caroline  Pratt

Single line result-set

I should preface this by saying that SQL Server really is not for the presentation of the data.  This could/should be done at the front end, by the countless applications available for the reporting of the data.  Having said that, more than anybody, I realize that sometimes you will have to make it pretty at the SQL layer.  Here's a real quick one for manipulating your data with self-joins, to return multiple records within a single line of results.

-- Two values, two row(s) returned:

     SELECT c.FirstName,c.LastName,c.DateOfBirth
     FROM dbo.Customers c
     WHERE c.DateOfBirth IN
          (
          SELECT temp.DateOfBirth
          FROM dbo.Customers temp
          GROUP BY temp.DateOfBirth
          HAVING (((Count(*))>1))
          )

     FirstName   LastName    DateOfBirth
     Karen         Williams       1977-03-18 00:00:00
     Betsy          Simpson       1977-03-18 00:00:00

-- Same two values, one row returned:

     SELECT
          a.FirstName, a.LastName, a.DateOfBirth,
          b.FirstName, b.LastName, b.DateOfBirth
     FROM
          dbo.Customers a INNER JOIN dbo.Customers b
             ON a.DateOfBirth=b.DateOfBirth
             AND (a.FirstName > b.FirstName)
          OR (a.FirstName=b.FirstName AND a.LastName > b.LastName)

FirstName  LastName  DateOfBirth                 FirstName   LastName   DateOfBirth
Karen        Williams     1977-03-18 00:00:00   Betsy          Simpson     1977-03-18 00:00:00


NOTE:    You want to be careful with this, given the size of the targeted result-set.


Last Record Written

In many situations you'll find yourself looking at date-specific data.  For example,
in the electronic trading industry, you may find yourself looking at 'trade date', or the datetime at which a particular trade was performed.  In the HFT (high frequency trading) world, it is all about when (and what)... the datetime attributes for the trading activity are very important for positions, trending and analysis, and many other things.

When was the last time a trade was submitted by a particular trading desk?


DECLARE @date DATETIME
SELECT @date =
                         MAX(TradeDate)
                         FROM dbo.TradeTable
                         WHERE TradingDesk = 5   --Trading desks are numerically identified.
    SELECT
          TradeDate,TradingDesk,TraderName,Exchange,Symbol,Price,Quantity,OrderID
    FROM
          dbo.TradeTable
    WHERE
          TradingDesk = 5
          AND TradeDate = @date


Ok, let's get a little more clever, and create a stored procedure with input parameters:


   IF OBJECT_ID('usp_lasttradedetails','p')>0
   DROP PROC dbo.usp_LastTradeDetails
   GO
   CREATE PROC dbo.usp_LastTradeDetails (
       @desk INT
   )
   AS
   SET NOCOUNT ON;
   /*
   Allows retrieval of the last trade written, for the given @tradingdesk.
   EXEC dbo.usp_LastTradeDetails @desk = 5
   */


  SELECT @date =
                         MAX(TradeDate)
                         FROM dbo.TradeTable
                         WHERE TradingDesk = @desk


  SELECT
     TradeDate,TradingDesk,TraderName,Exchange,Symbol,Price,Quantity,OrderID
  FROM
     dbo.TradeTable
  WHERE
     TradingDesk = @desk
     AND TradeDate = @date


SET NOCOUNT OFF;

Which indexes are missing?

Yes, yes, you can actually answer that question now... with the DMV's.  Short story, one of the wonderful new features of SQL, introduced in v2005, are the Dynamic Management Views.  There are two types, Server & Database-scope, and they provide extensive information about the current state of the SQL Server, and database(s).  This data can be very helpful to monitor, diagnose and administer your servers, reactively AND proactively.  

The DMVs are replacements for the system tables used within SQL Server 2000. 

This statement joins the missing index DMVs, in an effort to identify the most beneficial missing indexes, AND provide the statements necessary to create them.  


SET NOCOUNT ON;

SELECT 
avg_user_impact AS average_improvement_percentage, 
avg_total_user_cost AS average_cost_of_query_without_missing_index, 
'CREATE INDEX idx_' + [statement] +  
ISNULL(equality_columns, '_') +
ISNULL(inequality_columns, '_') + ' ON ' + [statement] + ' (' + ISNULL(equality_columns, ' ') + 
ISNULL(inequality_columns, ' ') + ')' + ISNULL(' INCLUDE (' + included_columns + ')', '') AS     create_missing_index_command
FROM 
sys.dm_db_missing_index_details a INNER JOIN sys.dm_db_missing_index_groups b
        ON a.index_handle = b.index_handle INNER JOIN sys.dm_db_missing_index_group_stats c
             ON b.index_group_handle = c.group_handle
WHERE 
avg_user_impact > = 40


SET NOCOUNT OFF;


NOTE:   In the CREATE INDEX statement, 'idx' is my standard index naming convention for non-clustered indexes... change as you see fit.