Are the posted questions getting worse?

Question

Are the posted questions getting worse?

Viewing 15 posts - 21,391 through 21,405 (of 66,738 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

CirquedeSQLeil (11/11/2010)
I have a few articles that I am finishing up currently. Is there anybody that would like to give them a look-over before I submit them?

Sure, fire any of them my way.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 2

WayneS (11/11/2010)
Well, I'm off to give my first presentation of "Comparing Tables Variables and Temporary Tables" to my UG tonight. Wish me good tidings, smooth talk, and all the other stuff you need for public speaking.

Well, I think it went pretty well. In a similar vein to Gail's "statistics" 5 times in a minute, I did get a little bit tongue-tied saying "tempdb" and "temporary tables" in the same sentence. I ran the PowerPoint presentation in presenter mode, but then ran into some minor issues flipping back and forth with SSMS and my virtual machine back to the slide. I might try setting up the monitor in slave mode the next time.

I started the presentation asking questions. Everyone thought that table variables were 1. memory-only, 2. did no logging, and 3. had no indexes. Now, about 20 more people know better.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

Congratulations, Wayne. Applause! Thunderous applause

Let me ask what logging takes place with table variables, since they are not affected by rollbacks.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

The Dixie Flatline (11/11/2010)
I'd be happy to Jason, if you will include a picture of what you look like.

http://www.sqlservercentral.com/blogs/

Look in the featured blogger section.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Paul White SSC Guru Points: 150468 More actions · Answer 5

The Dixie Flatline (11/11/2010)
Let me ask what logging takes place with table variables, since they are not affected by rollbacks.

Try it for yourself:

-- Guaranteed to be in SIMPLE recovery

USE tempdb;

GO

-- Clear log

CHECKPOINT;

GO

SELECT L.[Current LSN],

L.Operation,

L.Context,

L.[Log Record Length],

L.AllocUnitName,

L.[Page ID],

L.[Slot ID],

L.PartitionId,

L.[Description]

FROM sys.fn_dblog(NULL, NULL) L;

GO

DECLARE @T TABLE (col1 INT NOT NULL);

-- Show the #temp table associated with the table variable

SELECT * FROM sys.tables

INSERT @T (col1) VALUES (37);

GO

-- Log generated by the table variable operations

SELECT L.[Current LSN],

L.Operation,

L.Context,

L.[Log Record Length],

L.AllocUnitName,

L.[Page ID],

L.[Slot ID],

L.PartitionId,

L.[Description]

FROM sys.fn_dblog(NULL, NULL) L;

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Paul White SSC Guru Points: 150468 More actions · Answer 6

WayneS (11/11/2010)
I started the presentation asking questions. Everyone thought that table variables were 1. memory-only, 2. did no logging, and 3. had no indexes. Now, about 20 more people know better.

Well done. It's amazing how few database people know the basics of table variables.

Did you also bust the myth that table variables are always estimated as containing one row?

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

WayneS (11/11/2010)
WayneS (11/11/2010)
Well, I'm off to give my first presentation of "Comparing Tables Variables and Temporary Tables" to my UG tonight. Wish me good tidings, smooth talk, and all the other stuff you need for public speaking.
Well, I think it went pretty well. In a similar vein to Gail's "statistics" 5 times in a minute, I did get a little bit tongue-tied saying "tempdb" and "temporary tables" in the same sentence. I ran the PowerPoint presentation in presenter mode, but then ran into some minor issues flipping back and forth with SSMS and my virtual machine back to the slide. I might try setting up the monitor in slave mode the next time.
I started the presentation asking questions. Everyone thought that table variables were 1. memory-only, 2. did no logging, and 3. had no indexes. Now, about 20 more people know better.

Congrats and good job.

When can I book you to speak for my User Group - same topic?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 8

You're very tiny! (j/k)

Nice shades though...

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 9

Paul, I ran the code and saw the log entries, but I wasn't doubting his word.

Let me rephrase the question: What is the purpose of having log entries for a table variable? or How are they used?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Paul White SSC Guru Points: 150468 More actions · Answer 10

The Dixie Flatline (11/11/2010)
What is the purpose of having log entries for a table variable? or How are they used?

The first part of the answer is that the principle of write-ahead logging requires that log records detailing any change are written to disk before any modified data pages.

The second part of the answer is that some operations on table variables might need to be undone, e.g.

DECLARE @T TABLE (col1 INT NOT NULL PRIMARY KEY);

INSERT @T (col1)

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 1;

If you look at the log records, you'll see three LOP_INSERT_ROWS records for the new rows with values (1,2,3)...then we hit a primary key violation when the fourth value (1) is encountered. SQL Server then needs to undo the changes it has already made. You'll see three LOP_DELETE_ROWS entries (with a description of 'COMPENSATION') followed by LOP_ABORT_XACT.

See http://sqlinthewild.co.za/index.php/2010/10/12/a-trio-of-table-variables/

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

WayneS (11/11/2010)
Everyone thought that table variables were 1. memory-only, 2. did no logging, and 3. had no indexes. Now, about 20 more people know better.

And I thought the 'no logging' myth was a rare one. Glad I did spend some (lots of) time debunking that one.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

CirquedeSQLeil (11/11/2010)
I have a few articles that I am finishing up currently. Is there anybody that would like to give them a look-over before I submit them?

Go for it Jason. Got a short day today and a quiet day tomorrow.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 13

another one....

http://www.sqlservercentral.com/Forums/Topic1019796-1550-1.aspx?Update=1

oh and I reached a 1000 point...small milestone 😀

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

CirquedeSQLeil (11/11/2010)
I have a few articles that I am finishing up currently.

Topics?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 15

Henrico Bekker (11/12/2010)
another one....
http://www.sqlservercentral.com/Forums/Topic1019796-1550-1.aspx?Update=1

What's wrong with that question?

It's far more than just putting the server name in. There's all the VPN tools and remote access permissions to install and configure. All the time I worked at the bank I never had remote access to the network. Simply putting the server name into management studio would have resulted in a long delay and a 'SQL Server does not exist' error.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass