Tags » T-SQL

DISTINCT and GROUP BY

DISTINCT和GROUP BY有不同的semantics(語意),也用在不同的情境之下。DISTINCT是用來移除duplicated data,而GROUP BY通常用來分組後和aggregate function (e.g. SUM, AVG, MAX, …)一起使用。 133 more words

Deleting a duplicate record

Recently a friend of mine asked me about deleting a duplicate record from a table without deleting both records. Having been in a situation I drafted up a sample script and emailed it to him. 163 more words

SQL Server

List all Indexes with fragmentation percent in a Database - TSQL

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(‘DB_name’), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id… 30 more words

T-SQL

SQL SERVER - Using 20 Logical Processors Based on SQL Server Licensing

It is second to human nature to utilize every bit of juice for what we pay. You buy something and you would like to get the best out of it right? 532 more words

SQL Authority

List all tables with row-count and size on a database.

SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8/1024 AS TotalSpaceMB,
SUM(a.used_pages) * 8/1024 AS UsedSpaceMB,
(SUM(a.total_pages) – SUM(a.used_pages)) * 8/1024 AS UnusedSpaceMB… 78 more words

T-SQL

Could not Execute 'sp_MSadd_replcmds' - Fun with SQL Replication

Today I was working on a Microsoft SQL Server 2008 Replication system that’s being implemented at the moment and ran a build against a system. Immediately two of the subscriptions failed with the ominous message below: 341 more words