Break it out a little more:
ORDER BY CASE WHEN @orderby = 1 THEN CONVERT(NVARCHAR(30) , ccd.CertEndDate) END ASC,
CASE WHEN @orderby = 2 THEN CONVERT(NVARCHAR(30) , ccd.CertEndDate) END DESC,
tp.lastname ASC,
tp.firstname ASC
You only need the sort order to change on the first field, so don't enclose the others in the CASE
.
It should be noted that we don't include an ELSE
for each CASE
, which means any other value will return NULL
and be discarded from the ORDER BY
.
If you really want to limit SQL Server memory, look at the Maximum server memory option.
Getting memory usage is possible, but it depends on what you really want. Do you want to see memory usage as a percent of the "Maximum server memory" option? If so, look at sys.dm_os_process_memory:
select convert(float, memory_utilization_percentage) / 100 as memory_usage
from sys.dm_os_process_memory
If you want physical memory use vs. total system memory, look in both sys.dm_os_process_memory
and sys.dm_os_sys_info:
select * from sys.dm_os_sys_info
declare @physicalMemoryInUseKB bigint
declare @totalSystemMemoryBytes bigint
select @physicalMemoryInUseKB = physical_memory_in_use_kb from sys.dm_os_process_memory
select @totalSystemMemoryBytes = physical_memory_in_bytes from sys.dm_os_sys_info
select convert(float, @physicalMemoryInUseKB) * 1024
/ convert(float, @totalSystemMemoryBytes) as memory_usage
I don't think you can get the CPU usage unless you have the Resource Governor enabled. If you do, look at sys.dm_os_performance_counters:
select
case CPUUsageBase
when 0 then 0
else convert(float, CPUUsage) / convert(float, CPUUsageBase)
end as 'cpu_usage'
from
(select cntr_value as [CPUUsage]
from sys.dm_os_performance_counters
where counter_name like 'CPU usage%'
and object_name like '%Workload Group Stats%'
and cntr_type = 537003264) Usage
cross join
(select cntr_value as [CPUUsageBase]
from sys.dm_os_performance_counters
where counter_name like 'CPU usage %'
and object_name like '%Workload Group Stats%'
and cntr_type = 1073939712) UsageBase
Best Answer
From your previous questions you use SQL Server. So you can use the & operator.
e.g. to see if the bit for 4 is on (and assuming
NULL
should returnNULL
)