Microsoft Tidbits

SQL Server Tidbits

Running Queries

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext;

select row_count,* from sys.dm_exec_requests r join sys.dm_os_tasks t on r.session_id = t.session_id where r.session_id = 70 order by task_state;
select sum(context_switches_count) from sys.dm_exec_requests r join sys.dm_os_tasks t on r.session_id = t.session_id where r.session_id = 70;

List Tables in a schema

SELECT NAME FROM SYS.all_objects WHERE schema_id IN (SELECT SCHEMA_ID FROM SYS.schemas WHERE NAME = 'ICD10');

select [name]
from dbo.sysobjects 
where  OBJECTPROPERTY(id, N'IsUserTable') = 1  -- restrict to normal user tables

Determine space used by an object

EXEC sp_spaceused N'Facts.F_MEMBER_ENROLLMENT';

Awesome undocumented procedure to loop through tables (one exists for databases too)

exec sp_MSforeachtable 'EXEC sp_spaceused [?]';

Size of individual indexes

exec sp_MSIndexSpace N'Dimensions.D_CLAIM_LINE_ITEM_T1';

Rename or Move Objects

exec sp_rename 'Dimensions.D_PROVIDER_T1.TAXONOMY_CODE','PRIMARY_TAXONOMY_CODE', N'COLUMN'
exec tempdb..sp_rename '#MY_TEMP_TABLE.COLUMN_OLD','COLUMN_NEW', N'COLUMN'

-- Move (rename) a table into a different schema (with generation):

alter schema HEDIS_2014 transfer dbo.AAB;

select 'alter schema HEDIS_2014 transfer dbo.' + name + ';'
from sys.all_objects
where schema_id = 1 and type = 'U'

Rebuild Indexes

ALTER INDEX ALL ON Dimensions.D_CLAIM_LINE_ITEM_T1
	REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON);

Rebuild statistics

UPDATE STATISTICS Dimensions.D_CLAIM_T1;

	IF NOT EXISTS (SELECT * FROM sys.stats WHERE object_id = OBJECT_ID(N'[Facts].[F_CLAIM_LINE_ITEM]') AND name = N'_stat_DCT1_DMT2_DMT1')
		CREATE STATISTICS [_stat_DCT1_DMT2_DMT1] ON [Facts].[F_CLAIM_LINE_ITEM]([CLAIM_T1_SKEY], [MEMBER_T2_SKEY], [MEMBER_T1_SKEY])

Cute query to FLOOR (round to midnight the morning beginning) a date in Transact SQL

SELECT DATEADD(day,0,FLOOR(CONVERT(float, getDate())));

or

SELECT CAST(GETDATE() AS DATE) -- Also works

List partitioned thingies

select distinct
   p.[object_id],
   TbName = OBJECT_NAME(p.[object_id]), 
   index_name = i.[name],
   index_type_desc = i.type_desc,
   partition_scheme = ps.[name],
   data_space_id = ps.data_space_id,
   function_name = pf.[name],
   function_id = ps.function_id
from sys.partitions p
inner join sys.indexes i 
   on p.[object_id] = i.[object_id] 
   and p.index_id = i.index_id
inner join sys.data_spaces ds 
   on i.data_space_id = ds.data_space_id
inner join sys.partition_schemes ps 
 on ds.data_space_id = ps.data_space_id
inner JOIN sys.partition_functions pf 
   on ps.function_id = pf.function_id
order by object_id, index_name

Recursive SQL for Gaps and Islands

-- Recursive SQL Query Example to combine D_MEMBER_T2 segments into a continuous segment of current eligibility
-- Takes about 30 seconds to run
with enrolled_segments (MEMBER_T1_SKEY, MEMBER_ID, MEM_ELIGIBILITY_STATUS_FLAG, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, LEVEL)
AS
(
	-- Select all members that are effective today
	SELECT MEMBER_T1_SKEY, MEMBER_ID, MEM_ELIGIBILITY_STATUS_FLAG, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, 1 AS LEVEL
	FROM Dimensions.D_MEMBER_T2
	WHERE CAST(GETDATE() AS DATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
		AND MEM_ELIGIBILITY_STATUS_FLAG = 'Y'

	UNION ALL

	-- And iterate selecting the next D_MEMBER_T2 segment as long as MEM_ELIGIBILITY_STATUS_FLAG = 'Y'
	SELECT DMT2.MEMBER_T1_SKEY, DMT2.MEMBER_ID, DMT2.MEM_ELIGIBILITY_STATUS_FLAG, DMT2.EFFECTIVE_START_DATE, DMT2.EFFECTIVE_END_DATE, ES.LEVEL+1 AS LEVEL
	FROM Dimensions.D_MEMBER_T2 DMT2
		INNER JOIN enrolled_segments ES ON
			DMT2.MEMBER_T1_SKEY = ES.MEMBER_T1_SKEY AND
			DMT2.EFFECTIVE_END_DATE = DATEADD(DAY, -1, CASE WHEN ES.EFFECTIVE_START_DATE = '1753-01-01 00:00:00.000' THEN '1753-01-02 00:00:00.000'
				ELSE ES.EFFECTIVE_START_DATE end) AND 
			DMT2.MEM_ELIGIBILITY_STATUS_FLAG = 'Y'
)
-- Then select the earliest start date and latest end date -- this will be the complete enrollment period for that person that covers today.
-- The "Level" that results is the number of consecutive eligible segments adjacent to the one covering today (only counting backwards in time)
SELECT MEMBER_T1_SKEY, MEMBER_ID, MEM_ELIGIBILITY_STATUS_FLAG,
	MIN(EFFECTIVE_START_DATE) AS SEGMENT_START_DATE,
	MAX(EFFECTIVE_END_DATE) AS SEGMENT_END_DATE,
	MAX(LEVEL) AS LEVEL
FROM enrolled_segments
group by MEMBER_T1_SKEY, MEMBER_ID, MEM_ELIGIBILITY_STATUS_FLAG

Reasons Microsoft Vista Sucks

• "search in files" is gone. Yes, people THINK it's there, but it's not. I can't get the search to find a simple string in an .aspx file (which I have ridiculously associated with Notepad, just in case Vista is only looking for files it knows how to read). I've changed a boatload of settings, and read some other pages that seem to indicate that people got this working, but following their instruction does nothing. Is it the file type? WTF?
• Really there are a lot of these, but given how lazy I've been updating the rest of the site you probably won't see them here unless they REALLY bother me... like whatever I have listed so far

Reasons Microsoft Access (2007) Sucks

If you have a query that constantly asks you for a prompt for no reason, it could be that you've accidentally sorted by a column that you later renamed or removed. Access 2007 doesn't quite pick up on the change, so you have to do the following:

• Open the Query in Design View (or SQL View)
• Open the Properties Sheet (which by default is open and docked on the right -- if not, right click somewhere and highlight "Properties")
• See if there's anything in the "Order By" Property that shouldn't be there
• Delete it

Of course, it could be something else. ANY name change to a dependant query or table could cause grief -- they matriculate properly about half the time, judging from the last 12 hours. Changing the "Total" type of a field in a query for example will rename a field MaxOfxxxx without asking, and in some cases without propagating to other queries that reference the field. It's enough to drive one mad!

Tips and Tricks for Windows XP Professional

• NB: This used to be available at: http://www.microsoft.com/technet/prodtechnol/winxppro/tips/default.mspx but it's apparently gone, so I copied it here.

Working with Files

• To rename several files at once, select all of them in any folder view, or in the Windows Explorer, rename the FIRST one in the list, and the rest will follow.
• To turn off filenames, hold down the Shift key when you click to open a folder in Windows Explorer or when you switch into thumbnail view. This will turn of the file names, giving more space for the thumbnails. Doing it again turns them back on.
• Try grouping your files. In the top menu bar select View – Arrange icons by… - Type, and then View – Arrange icons by… - Show in groups.
• You can change the size and quality of the shell's thumbnails. Start regedit, expand HKEY_CURRENT_USER (only for you) or HKEY_LOCAL_MACHINE (for all the users in this computer) and in Software – Microsoft – Windows – CurrentVersion – Explorer, add or modify the following entries:
• ThumbnailSize – Double Word, decimal value between 32 and 256
• ThumbnailQuality – Double Word, decimal value between 50 and 100

Internet Explorer

• You can use the Search Companion in Internet Explorer to perform many Internet related actions. Try:
• go msft
• Go yp cheesecake factory

Interface

• Get rid of balloon tips: Start regedit, expand HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\Advanced Set EnableBalloonTips=0x00000000
• Disable the Search companion to go back to the traditional search view: Start regedit, expand HKEY_CURRENT_USER and in Software – Microsoft – Windows – CurrentVersion – Explorer – CabinetState, set the value of Use Search Asst to "no"
• Turn off the low disk space notification: Start regedit, expand HKEY_CURRENT_USER and in Software – Microsoft – Windows – CurrentVersion – Policies - Explorer, and set the value of NoLowDiskSpaceChecks to DWORD = 1. (This modification requires a logoff or reboot to work)
• You can force your taskbar to group windows. Any application that has any number you decide of windows open will automatically cluster, regardless of how full your taskbar is. To set this: Start regedit, expand HKEY_CURRENT_USER and in Software – Microsoft – Windows – CurrentVersion – Explorer - Advanced, and create a REG_DWORD with the name TaskbarGroupSize and the value you want to reach before the windows are clustered together on the taskbar. (This modification requires a logoff to work). Top of pageTop of page

Keyboard

• windowskey + Break opens the Control Panel System applet.
• windowskey + Tab moves through the taskbar buttons
• windowskey + D shows the Desktop
• windowskey + L shows the Welcome screen, or locks the machine if Fast User Switching is disabled.