SQL Server Date Format conversion

SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM

SELECT convert(varchar, getdate(), 101) -- mm/dd/yyyy – 10/02/2008                  

SELECT convert(varchar, getdate(), 102) -- yyyy.mm.dd – 2008.10.02           

SELECT convert(varchar, getdate(), 103) -- dd/mm/yyyy

SELECT convert(varchar, getdate(), 104) -- dd.mm.yyyy

SELECT convert(varchar, getdate(), 105) -- dd-mm-yyyy

SELECT convert(varchar, getdate(), 106) -- dd mon yyyy

SELECT convert(varchar, getdate(), 107) -- mon dd, yyyy

SELECT convert(varchar, getdate(), 108) -- hh:mm:ss

SELECT convert(varchar, getdate(), 109) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)

SELECT convert(varchar, getdate(), 110) -- mm-dd-yyyy

SELECT convert(varchar, getdate(), 111) -- yyyy/mm/dd

SELECT convert(varchar, getdate(), 112) -- yyyymmdd

SELECT convert(varchar, getdate(), 113) -- dd mon yyyy hh:mm:ss:mmm

SELECT convert(varchar, getdate(), 114) -- hh:mm:ss:mmm(24h)

SELECT convert(varchar, getdate(), 120) -- yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar, getdate(), 121) -- yyyy-mm-dd hh:mm:ss.mmm

SELECT convert(varchar, getdate(), 126) -- yyyy-mm-ddThh:mm:ss.mmm

appcmd - to list sites using wildcard mapping

You can use wildcard characters with appcmd using the following syntax:

appcmd list apppool /name:"$=*2007"

This command will list all application pools with a name ending in "2007". Using a variation on the same method, you can find all application pools running under the identify of users in a particular domain.

appcmd list apppool /processModel.userName:"$=MyDomain\*"

This will list all application pools with running with an identity of users in the "MyDomain" domain.

appcmd list site /name:"$=*cdo*"

This will list all sites with a name having "cdo".

Tip: You can find out all of the attributes that can be used with appcmd by querying for help after using an attribute. For example:

appcmd list apppools /name:DefaultAppPool -?

ERROR ( message:-name 
-queueLength 
-autoStart 
-enable32BitAppOnWin64 
-managedRuntimeVersion 
-enableConfigurationOverride 
-managedPipelineMode 
-passAnonymousToken 
-processModel.identityType 
-processModel.userName 
-processModel.password 
-processModel.loadUserProfile 
-processModel.manualGroupMembership 
-processModel.idleTimeout 
-processModel.maxProcesses 
-processModel.shutdownTimeLimit 
-processModel.startupTimeLimit 
-processModel.pingingEnabled 
-processModel.pingInterval 
-processModel.pingResponseTime 
-recycling.disallowOverlappingRotation 
-recycling.disallowRotationOnConfigChange 
-recycling.logEventOnRecycle 
-recycling.periodicRestart.memory 
-recycling.periodicRestart.privateMemory 
-recycling.periodicRestart.requests 
-recycling.periodicRestart.time 
-recycling.periodicRestart.schedule.[value='timespan'].value 
-failure.loadBalancerCapabilities 
-failure.orphanWorkerProcess 
-failure.orphanActionExe 
-failure.orphanActionParams 
-failure.rapidFailProtection 
-failure.rapidFailProtectionInterval 
-failure.rapidFailProtectionMaxCrashes 
-failure.autoShutdownExe 
-failure.autoShutdownParams 
-cpu.limit 
-cpu.action 
-cpu.resetInterval 
-cpu.smpAffinitized 
-cpu.smpProcessorAffinityMask 
-cpu.smpProcessorAffinityMask2 
)

By appending "-?" to the command above, appcmd generates an error that lists all of the attributes that can be used with application pools.

Source : https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2008-R2-and-2008/cc771280(v%3dws.10)

SSMS versus SQL Operations Studio

SSMS vs SQLOpsStudio:

SQL Operations Studio (preview) is a free lightweight database development and operations tool that runs on your desktop and is available for Windows, macOS, and Linux. SQL Operations Studio (preview) has built-in support for Azure SQL Database, Azure SQL Data Warehouse, and SQL Server running on-premises or in any cloud. 

SQL Operations Studio (preview) offers a consistent experience across databases of your choice on your favorite operating systems.

SQL Server Management Studio is a fully featured database management tool that enables most DBA’s tasks. It will continue to be the flagship tool with the data tools portfolio. 
SQL Operations Studio enables most of the routine DBA tasks for development and operations of a database but does not have all of the functionality of SSMS. 
SQL Operations Studio is meant to be light weight and easy to use for non-professional DBAs and thus will most likely always optimize usability and light weight to addressing all corner cases.

Investments in flagship Windows tools (SSMS, SSDT, PowerShell) will continue in addition to the next generation of multi-OS and multi-DB CLI and GUI tools. 

The goal is to offer customers the choice of using the tools they want on the platforms of their choice for their scenarios.

Source: https://docs.microsoft.com/en-us/sql/sql-operations-studio/faq 

Windows Batch for getting today's date

for /F "skip=1 delims=" %%F in ('

    wmic PATH Win32_LocalTime GET Day^,Month^,Year /FORMAT:TABLE

') do (

    for /F "tokens=1-3" %%L in ("%%F") do (

        set CurrDay=0%%L

        set CurrMonth=0%%M

        set CurrYear=%%N

    )

)

set dd=%CurrDay:~-2%

set mm=%CurrMonth:~-2%

set yyyy=%CurrYear%

set today=%CurrYear%%CurrMonth:~-2%%CurrDay:~-2%

SQL Logic Operator Precedence: And and Or

And has precedence over Or, so, even if a <=> a1 Or a2

Where a And b 

is not the same as

Where a1 Or a2 And b,

because that would be Executed as

Where a1 Or (a2 And b)

and what you want, to make them the same, is

 Where (a1 Or a2) And b

Here's an example to illustrate:

Declare @x tinyInt = 1
Declare @y tinyInt = 0
Declare @z tinyInt = 0

Select Case When @x=1 OR @y=1 And @z=1 Then 'T' Else 'F' End -- outputs T
Select Case When (@x=1 OR @y=1) And @z=1 Then 'T' Else 'F' End -- outputs F

How to disable Data Compression on tables in whole database in SQL Server 2008R2?

If you restore database from enterprise edition to Standard edition and you have enabled "Data compression" on table, restoration fails. Data compression is available only Enterprise edition in SQL Server 2008R2. The best way how to disable data compression is the following:

1) make a clone of your database where data compression is enabled on enterprise edition

2) then run this script on clone database

SELECT
SCHEMA_NAME(sys.objects.schema_id) AS [SchemaName]
,OBJECT_NAME(sys.objects.object_id) AS [ObjectName]
,[rows]
,[data_compression_desc]
,[index_id] as [IndexID_on_Table]
into #compr
FROM sys.partitions
INNER JOIN sys.objects
ON sys.partitions.object_id = sys.objects.object_id 
WHERE data_compression > 0
AND SCHEMA_NAME(sys.objects.schema_id) <> 'SYS'
while exists(select 1 from #compr)
begin
      declare @i varchar(200) = (select top 1 [ObjectName] from #compr)
      print @i
     exec ('ALTER INDEX ALL ON '+@i + ' REBUILD WITH (DATA_COMPRESSION = None)')
     delete from #compr where [ObjectName] = @i
end
drop table #compr

3) now you can backup clone database and restore on Standard edition