- 1 SQL Server Tidbits
- 1.1 Running Queries
- 1.2 List Tables in a schema
- 1.3 Determine space used by an object
- 1.4 Awesome undocumented procedure to loop through tables (one exists for databases too)
- 1.5 Size of individual indexes
- 1.6 Rename or Move Objects
- 1.7 Rebuild Indexes
- 1.8 Rebuild statistics
- 1.9 Cute query to FLOOR (round to midnight the morning beginning) a date in Transact SQL
- 1.10 List partitioned thingies
- 2 Recursive SQL for Gaps and Islands
- 3 Reasons Microsoft Vista Sucks
- 4 Reasons Microsoft Access (2007) Sucks
- 5 Tips and Tricks for Windows XP Professional
SQL Server Tidbits
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'
ALTER INDEX ALL ON Dimensions.D_CLAIM_LINE_ITEM_T1 REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON);
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())));
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
- You can use the Search Companion in Internet Explorer to perform many Internet related actions. Try:
- go msft
- Go yp cheesecake factory
- 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
- 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.