marrowyung
asked on
WITH XXX AS statement
Dear all,
I get the script form http://www.sqlmag.com/blog/practical-sql-server-45/sql-server-2012/disk-space-monitoring-143552 to monitor the available disk space of the disk SQL server owns:
it create SP using this:
question:
1) I don't quite understand what is the WITH XXX As is about even from this article: http://msdn.microsoft.com/en-au/library/ms175972(v=sql.105).aspx
2) I dont' see it return the result of @threshold, from where @threshold has a value ? I don't see it has a value before it goes to "WHERE AvailableMBs < @threshold"
3) "IF LEN(@list) > 3 BEGIN" , what is the LEN for ? I read http://msdn.microsoft.com/en-au/library/ms190329.aspx and it seems LEN works like parser, to return number of character returned?
I don't see how this script works.
I get the script form http://www.sqlmag.com/blog/practical-sql-server-45/sql-server-2012/disk-space-monitoring-143552 to monitor the available disk space of the disk SQL server owns:
it create SP using this:
CREATE PROC dbo.sp_drivespace_alerts
@from varchar(100),
@to varchar(200),
@subject varchar(100),
@threshold int -- number of MB under which to launch an alert
AS
SET NOCOUNT ON
DECLARE @list nvarchar(2000) = '';
WITH core AS (
SELECT DISTINCT
s.volume_mount_point [Drive],
CAST(s.available_bytes / 1048576 as decimal(12,2)) [AvailableMBs]
FROM
sys.master_files f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) s
)
SELECT @list = @list + ' ' + Drive + ', '
FROM core
WHERE AvailableMBs < @threshold
IF LEN(@list) > 3 BEGIN
DECLARE @msg varchar(500) = 'Low Disk Space Notification. The following drives are currently reporting less than '
+ CAST(@threshold as varchar(12)) + ' MB free: ' + @list
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'xxxxx',
@recipients = @to,
@subject = @subject,
@body = @msg
END
RETURN 0
GOquestion:
1) I don't quite understand what is the WITH XXX As is about even from this article: http://msdn.microsoft.com/en-au/library/ms175972(v=sql.105).aspx
2) I dont' see it return the result of @threshold, from where @threshold has a value ? I don't see it has a value before it goes to "WHERE AvailableMBs < @threshold"
3) "IF LEN(@list) > 3 BEGIN" , what is the LEN for ? I read http://msdn.microsoft.com/en-au/library/ms190329.aspx and it seems LEN works like parser, to return number of character returned?
I don't see how this script works.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So this one:
designed for SQL server earlier than SQL server 2008 R2 SP1 ?
USE master
GO
CREATE PROC dbo.sp_drivespace_alerts
@from varchar(100),
@to varchar(200),
@subject varchar(100),
@threshold int -- number of MB under which to launch an alert
AS
SET NOCOUNT ON
DECLARE @msg varchar(500)
SET @msg = 'Low Disk Space Notification. The following drives are currently reporting less than ' + CAST(@threshold as varchar(12)) + ' MB free: '
CREATE TABLE #drives (
drive char,
[free] int
)
INSERT INTO #drives
EXEC master..xp_fixeddrives
IF EXISTS (SELECT null FROM #drives WHERE [free] < @threshold) BEGIN
DECLARE @list varchar(30)
SET @list = ''
SELECT @list = @list + ' ' + drive + ',' FROM #drives WHERE [free] < @threshold
SET @list = LEFT(@list, LEN(@list) -1)
SET @msg = @msg + @list
PRINT @msg
-- send the email...
EXEC master..sp_send_cdosysmail @from, @to, @subject, @msg
END
DROP TABLE #drives
RETURN 0
GOdesigned for SQL server earlier than SQL server 2008 R2 SP1 ?
>>XXX is the temporay table ?
sort of, it's very similar, opinions differ about the comparisons of temp tables to CTEs
e.g. http://stackoverflow.com/questions/690465/which-are-more-performant-cte-or-temporary-tables
CTEs are in memory, temp tables are persisted (so can be reused within a session) but CTEs don't go to the tempdb and can outperform. It's one of those options in SQL without a definitive yes/no answer.
>>how ? in this example, it just a select !
by avoiding repetition of the base selection, the example was deliberately simple.
let's say you have an existing query that requires multiple subqueries, and several of these require "the same base data". Then 1 CTE may be created for "the same base data" , and reused in all subsequent subqueries - this way several select operations are avoided.
does this make sense now?
sort of, it's very similar, opinions differ about the comparisons of temp tables to CTEs
e.g. http://stackoverflow.com/questions/690465/which-are-more-performant-cte-or-temporary-tables
CTEs are in memory, temp tables are persisted (so can be reused within a session) but CTEs don't go to the tempdb and can outperform. It's one of those options in SQL without a definitive yes/no answer.
>>how ? in this example, it just a select !
by avoiding repetition of the base selection, the example was deliberately simple.
let's say you have an existing query that requires multiple subqueries, and several of these require "the same base data". Then 1 CTE may be created for "the same base data" , and reused in all subsequent subqueries - this way several select operations are avoided.
does this make sense now?
I used the CTE to first select the subset based of the indexed columns to first cut these tables down to a few thousand relevant rows each and then joined the CTE to my main query. This exponentially reduced the runtime of my query.quote from the above url
ASKER
forget to ask,
if we want SQL to tell me if the space left is 10GB, how I modify it? It seesm I can't as the statement is if LEN(@list) >3 then ....
"
& The thing to really know is: CTE's are mostly used help with improving query performance. "
the stuff created is much faster then using temp table ? and we don't even need to drop the "stuff" after created by using drop statement ?
if we want SQL to tell me if the space left is 10GB, how I modify it? It seesm I can't as the statement is if LEN(@list) >3 then ....
"
& The thing to really know is: CTE's are mostly used help with improving query performance. "
the stuff created is much faster then using temp table ? and we don't even need to drop the "stuff" after created by using drop statement ?
>> we don't even need to drop the "stuff" after created by using drop statement ?
correct, no need for 'drop' with a CTE
>>the stuff created is much faster then using temp table ?
I don't think one could claim "much" faster... and perhaps not even faster at all
did you read the URL provided earlier?
Opinions differ: temp tables can still be a better option "in some circumstances", particularly if you are using stored procedures and wish to pass results from one into another of these (as table variables). However, temp tables require "persistence" (i.e. I/O to write the data) plus you can add indexes (which also require I/O) but those indexes can then speed subsequent uses.
A CTE doesn't require the same I/O overheads but they exist ONLY during the life of the query that defined them.
You can use both temp tables and CTEs, they are not mutually exclusive choices.
them: just in case it isn't clear you can have multiple CTEs in one query
suggested reading:
http://www.sqllion.com/2010/08/common-table-expressions-cte/
https://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/
correct, no need for 'drop' with a CTE
>>the stuff created is much faster then using temp table ?
I don't think one could claim "much" faster... and perhaps not even faster at all
did you read the URL provided earlier?
Opinions differ: temp tables can still be a better option "in some circumstances", particularly if you are using stored procedures and wish to pass results from one into another of these (as table variables). However, temp tables require "persistence" (i.e. I/O to write the data) plus you can add indexes (which also require I/O) but those indexes can then speed subsequent uses.
A CTE doesn't require the same I/O overheads but they exist ONLY during the life of the query that defined them.
You can use both temp tables and CTEs, they are not mutually exclusive choices.
them: just in case it isn't clear you can have multiple CTEs in one query
suggested reading:
http://www.sqllion.com/2010/08/common-table-expressions-cte/
https://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/
ASKER
"by avoiding repetition of the base selection, the example was deliberately simple.
let's say you have an existing query that requires multiple subqueries, and several of these require "the same base data". Then 1 CTE may be created for "the same base data" , and reused in all subsequent subqueries - this way several select operations are avoided.
does this make sense now?
excellent and very clear, but need more example on "You can use both temp tables and CTEs, they are not mutually exclusive choices. "
so for the Reuseable query, if any index changed for the table inside that CTE, it will optimized automatically ?
let's say you have an existing query that requires multiple subqueries, and several of these require "the same base data". Then 1 CTE may be created for "the same base data" , and reused in all subsequent subqueries - this way several select operations are avoided.
does this make sense now?
excellent and very clear, but need more example on "You can use both temp tables and CTEs, they are not mutually exclusive choices. "
so for the Reuseable query, if any index changed for the table inside that CTE, it will optimized automatically ?
ASKER
it seems that CTE is going to replace VIEW, from the link:
"Stored procedures are mainly used to deal with complex logics either for security purpose or to accomplish some combined tasks, Or in most of the cases we use temporary tables or table variables to keep intermediate results which at the end tempt to be deleted when not in use. But among all these, CTE is the best as it act as a normal view only.""
but from https://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/:
"Introduced in SQL Server 2005, the common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement."
"Stored procedures are mainly used to deal with complex logics either for security purpose or to accomplish some combined tasks, Or in most of the cases we use temporary tables or table variables to keep intermediate results which at the end tempt to be deleted when not in use. But among all these, CTE is the best as it act as a normal view only.""
but from https://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/:
"Introduced in SQL Server 2005, the common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement."
no, that's not a good interpretation. Views will still exist (and do) - they have a good role to play still and this will continue.
I think use of the word of "view" is better interpreted in that quote to mean "read only".
CTEs ARE a query, with a name, inside a bigger query.
CTEs produce a "derived table" that is held in memory and that "derived table" can be re-used in the bigger query. Once the bigger query is complete the CTE "derived table" disappears (and the memory it used available for other purposes).
>>but need more example on "You can use both temp tables and CTEs, they are not mutually exclusive choices. "
sorry, not providing examples of this, I believe the statement is clear. You are able to create temp tables and you are able to use CTEs together - that is all the statement means.
>>so for the Reuseable query, if any index changed for the table inside that CTE, it will optimized automatically ?
it's not magic, CTEs don't fly about on carpets ;)
NO.
I think use of the word of "view" is better interpreted in that quote to mean "read only".
CTEs ARE a query, with a name, inside a bigger query.
CTEs produce a "derived table" that is held in memory and that "derived table" can be re-used in the bigger query. Once the bigger query is complete the CTE "derived table" disappears (and the memory it used available for other purposes).
>>but need more example on "You can use both temp tables and CTEs, they are not mutually exclusive choices. "
sorry, not providing examples of this, I believe the statement is clear. You are able to create temp tables and you are able to use CTEs together - that is all the statement means.
>>so for the Reuseable query, if any index changed for the table inside that CTE, it will optimized automatically ?
it's not magic, CTEs don't fly about on carpets ;)
NO.
ASKER
ok,
one question
1) any way you can make the subject return different server name and which driver is reaching the critical disk usage?
2) By this SP, it seems that we can specify in % of disk space free for each driver, it is XX MB left in all driver before it fire an alerts?
3) Why the condition is "IF LEN(@list) > 3 BEGIN" then email alert rise ?
one question
1) any way you can make the subject return different server name and which driver is reaching the critical disk usage?
2) By this SP, it seems that we can specify in % of disk space free for each driver, it is XX MB left in all driver before it fire an alerts?
3) Why the condition is "IF LEN(@list) > 3 BEGIN" then email alert rise ?
:) LOL one question: 1) 2) 3)
1 no idea
2 no idea
3 no idea
which part of the (many questions/comments) above are you referring to?
1 no idea
2 no idea
3 no idea
which part of the (many questions/comments) above are you referring to?
3) IF LEN(@list) > 3 BEGIN
the logic here is (see lines 21-23) before this test @list is "set" to a value IF available space is less than the given threshold: if it is set by that query (space is less than threshold) and length will be 3 or more, so proceed to send a warning.
2)
1) still no idea (more magic carpets?)
the logic here is (see lines 21-23) before this test @list is "set" to a value IF available space is less than the given threshold: if it is set by that query (space is less than threshold) and length will be 3 or more, so proceed to send a warning.
2)
EXEC master.dbo.sp_drivespace_afrom the original article please refer to it as it supplied the procedurelerts
@threshold = 12288, -- 12GB
@from = 'sqlalerts@noreply.com',
@subject = 'Low Disk Space Alerts - SERVERNAMEHERE',
@to = 'youremail@address.here,other@email. here'
1) still no idea (more magic carpets?)
ASKER
"IF LEN(@list) > 3 BEGIN
the logic here is (see lines 21-23) before this test @list is "set" to a value IF available space is less than the given threshold: if it is set by that query (space is less than threshold) and length will be 3 or more, so proceed to send a warning."
ok, I got it but the logic means it is more than 3 because there is something to return ( change it to SELECT *
FROM core
WHERE AvailableMBs < @threshold)
Then the result should be more than 4, not just 3 ,agree ?
what I don't understand is the logic is not really quite correct on this matter.
for 1)
"EXEC master.dbo.sp_drivespace_a
@threshold = 12288, -- 12GB
@from = 'sqlalerts@noreply.com',
@subject = 'Low Disk Space Alerts - SERVERNAMEHERE',
@to = 'youremail@address.here,ot
for this, this means for that for each SERVERNAMEHERE, we have to set it differently for each server !! not quite intellgence .
Ignore it if you still dont understnad what it is.
the logic here is (see lines 21-23) before this test @list is "set" to a value IF available space is less than the given threshold: if it is set by that query (space is less than threshold) and length will be 3 or more, so proceed to send a warning."
ok, I got it but the logic means it is more than 3 because there is something to return ( change it to SELECT *
FROM core
WHERE AvailableMBs < @threshold)
Then the result should be more than 4, not just 3 ,agree ?
what I don't understand is the logic is not really quite correct on this matter.
for 1)
"EXEC master.dbo.sp_drivespace_a
@threshold = 12288, -- 12GB
@from = 'sqlalerts@noreply.com',
@subject = 'Low Disk Space Alerts - SERVERNAMEHERE',
@to = 'youremail@address.here,ot
for this, this means for that for each SERVERNAMEHERE, we have to set it differently for each server !! not quite intellgence .
Ignore it if you still dont understnad what it is.
--Then the result should be more than 4, not just 3 ,agree ?
> 3 will do the job I think, but yes I think you could make that change, probably isn't needed
making this procedure more multi-server oriented is probably a good thing to do, and when you achieve that you could also locate the server name more intelligently instead of hard-coding it as it is now. Perhaps with:
SELECT SERVERPROPERTY ('InstanceName')
http://msdn.microsoft.com/en-au/library/ms174396.aspx
> 3 will do the job I think, but yes I think you could make that change, probably isn't needed
making this procedure more multi-server oriented is probably a good thing to do, and when you achieve that you could also locate the server name more intelligently instead of hard-coding it as it is now. Perhaps with:
SELECT SERVERPROPERTY ('InstanceName')
http://msdn.microsoft.com/en-au/library/ms174396.aspx
ASKER
sorry, how to add (select SERVERPROPERTY ('ServerName')) to display the server name here:
IF LEN(@list) > 3 BEGIN
DECLARE @msg varchar(500) = 'Low Disk Space Notification.
The following drives in' + (select SERVERPROPERTY ('ServerName')) + 'are currently reporting less than '
+ CAST(@threshold as varchar(12)) + ' MB free: ' + @list
It seem the format if char is not compatible .
IF LEN(@list) > 3 BEGIN
DECLARE @msg varchar(500) = 'Low Disk Space Notification.
The following drives in' + (select SERVERPROPERTY ('ServerName')) + 'are currently reporting less than '
+ CAST(@threshold as varchar(12)) + ' MB free: ' + @list
It seem the format if char is not compatible .
use @@servername
(it provides the same information)
e.g.
(it provides the same information)
e.g.
DECLARE @threshold int = 12288
DECLARE @list nvarchar(2000) = ''
DECLARE @msg varchar(500) = 'Low Disk Space Notification.
The following drives in'
+ @@servername
+ ' are currently reporting less than '
+ CAST(@threshold as varchar(12)) + ' MB free: ' + @list
select @msgASKER
ok , I get it right now !forget something.
what if I pass the server name when calling the SP:
DECLARE @servername varchar(100);
set @servername= CAST((select SERVERPROPERTY ('ServerName') ) as varchar (100));
EXEC master.dbo.sp_drivespace_a
@threshold = 204800, -- 10240, -- 10GB
@from = ' xxx' ,
@subject = 'Low Disk Space Alerts -' + @servername + '' ,
@to = 'yyy'
it seems it is complianing about "Incorrect syntax near '+'.
Am I miss something ?
what if I pass the server name when calling the SP:
DECLARE @servername varchar(100);
set @servername= CAST((select SERVERPROPERTY ('ServerName') ) as varchar (100));
EXEC master.dbo.sp_drivespace_a
@threshold = 204800, -- 10240, -- 10GB
@from = ' xxx' ,
@subject = 'Low Disk Space Alerts -' + @servername + '' ,
@to = 'yyy'
it seems it is complianing about "Incorrect syntax near '+'.
Am I miss something ?
ASKER
I use this instead:
DECLARE @list nvarchar(2000) = '',
@servername varchar(100);
set @servername= CAST((select SERVERPROPERTY ('ServerName') ) as varchar (100));
.
.
.
.
.
The following drives of ' + @servername+ ' are currently reporting less than '
+ CAST(@threshold as varchar(12)) + ' MB free: ' + @list
DECLARE @list nvarchar(2000) = '',
@servername varchar(100);
set @servername= CAST((select SERVERPROPERTY ('ServerName') ) as varchar (100));
.
.
.
.
.
The following drives of ' + @servername+ ' are currently reporting less than '
+ CAST(@threshold as varchar(12)) + ' MB free: ' + @list
If you pass a servername as parameter, then you message should include that parameter
otherwise what you have done should work (like the @@servername does).
If you require further assistance on "generalizing" this script you might want to ask a new question.
otherwise what you have done should work (like the @@servername does).
If you require further assistance on "generalizing" this script you might want to ask a new question.
ASKER
Please answer this then.
https://www.experts-exchange.com/questions/28089492/passing-server-name-as-parameter-in-MS-SQL.html
https://www.experts-exchange.com/questions/28089492/passing-server-name-as-parameter-in-MS-SQL.html
ASKER
but by this script, we still calculating using MB instead of GB.
Any convertion we can do to make the alert report using GB figure?
Any convertion we can do to make the alert report using GB figure?
?? the parameter is a number
?? the article you got this from used 12288 and stated this represented Gb
(note:I cannot guarantee the accuracy of that article or the proc)
a Gb is 1024 Mb, therefore perhaps you want
*1024
i.e. you can manipulate the number(s) by whatever factors you choose
but I'm not debugging the whole proc, I was answering your Q abut the meaning of XXX
are we done on this now?
?? the article you got this from used 12288 and stated this represented Gb
(note:I cannot guarantee the accuracy of that article or the proc)
a Gb is 1024 Mb, therefore perhaps you want
*1024
i.e. you can manipulate the number(s) by whatever factors you choose
but I'm not debugging the whole proc, I was answering your Q abut the meaning of XXX
are we done on this now?
ASKER
ok, you get bored.
ASKER
I just think I can do more by this even the article say it is in MB not in GB.
no, not bored, you really don't need my assistance in many of the points you are raising - and you will learn more by doing than on being instructed.
To decide if it is Mb or Gb and output in the desired units: I think you are more than capable of deciding this.
To decide if it is Mb or Gb and output in the desired units: I think you are more than capable of deciding this.
ASKER
ok , you are helpful like Mark_wills, the speak of EE in TED.
that's why I give you more marks.,
Please keep helping me.
but one thing, I need some check on how to convert from MB to GB (I am not from programminer backgroud), as the write of that article already say we CAN'T, but I think it is too rush.
that's why I give you more marks.,
Please keep helping me.
but one thing, I need some check on how to convert from MB to GB (I am not from programminer backgroud), as the write of that article already say we CAN'T, but I think it is too rush.
1 Gb = 1024 Mb
if you have 10 Gb then multiply by 1024 to get 10240 Mb
if you have 10240 Mb divide by 1024 to get 10 Gb
i.e.
10240 Mb / 1024 = 10 Gb
10 Gg * 1024 = 10240 Mb
This question is finished. If you have other topics you must open new questions
No offence intended; it's the way this site is meant to operate.
if you have 10 Gb then multiply by 1024 to get 10240 Mb
if you have 10240 Mb divide by 1024 to get 10 Gb
i.e.
10240 Mb / 1024 = 10 Gb
10 Gg * 1024 = 10240 Mb
This question is finished. If you have other topics you must open new questions
No offence intended; it's the way this site is meant to operate.
ASKER
"This question is finished. If you have other topics you must open new questions
No offence intended; it's the way this site is meant to operate. "
yes I knew, Mark_wills and other also say that before and I just have too much to ask to make it prefect!
you are welcome and you are coaching me.
No offence intended; it's the way this site is meant to operate. "
yes I knew, Mark_wills and other also say that before and I just have too much to ask to make it prefect!
you are welcome and you are coaching me.
ASKER
let me open a question for this:
"1 Gb = 1024 Mb
if you have 10 Gb then multiply by 1024 to get 10240 Mb
if you have 10240 Mb divide by 1024 to get 10 Gb
i.e.
10240 Mb / 1024 = 10 Gb
10 Gg * 1024 = 10240 Mb"
https://www.experts-exchange.com/questions/28090915/Converting-MB-to-GB.html
"1 Gb = 1024 Mb
if you have 10 Gb then multiply by 1024 to get 10240 Mb
if you have 10240 Mb divide by 1024 to get 10 Gb
i.e.
10240 Mb / 1024 = 10 Gb
10 Gg * 1024 = 10240 Mb"
https://www.experts-exchange.com/questions/28090915/Converting-MB-to-GB.html
ASKER
"1) WITH XXX AS (Select A from C); select x from XXX
is the same as
Select x from (Select A from C)"
then I can't see why the issuer make it so hard to read !
"2) @threshold is the 4th argument on the sp_drivespace_alerts procedure"
I know but I don't see it assigned a value. for example, before "WHERE AvailableMBs < @threshold", where we see it assigned a value before that ??
"3) LEN is the number of chars in a string (length) "
excellent, thanks. Yeah, for me, it just like what a parser do, check the length.
PortletPaul,
"When a SQL query uses "with XXX as " it is giving the name XXX to the stuff that follows inside the brackets."
XXX is the temporay table ?
"select * from xxx
union all
select * from XXX"
so it return the same time 2 times.
"CTE's are mostly used help with improving query performance."
how ? in this example, it just a select !