4 added 7 characters in body
source | link

Community wiki answer:

It also makes it harder to demonstrate to students that data is not automatically ordered.

This is a demo I like:

CREATE TABLE dbo.Example
(
    [data]  integer NOT NULL,
    padding character(8000) NOT NULL DEFAULT ''
);
GO
-- Add 50 rows with [data] numbered from 1 to 50
INSERT dbo.Example 
    ([data]) 
SELECT SV.number
FROM master.dbo.spt_values AS SV
WHERE SV.number BETWEEN 1 AND 50
AND SV.[type] = N'P';
GO
-- Add a nonclustered primary key on [data]
ALTER TABLE dbo.Example
ADD CONSTRAINT [PK dbo.Example data]
PRIMARY KEY NONCLUSTERED ([data]);

The following query starts with a cold cache, and is forced to use the PK:

-- Flush dirty pages to disk
CHECKPOINT;
GO
-- Drop clean buffers from memory
DBCC DROPCLEANBUFFERS;
GO
-- Query forced to use the PK ordered by [data]
SELECT TOP (15)
    E.[data],
    E.padding
FROM dbo.Example AS E 
    WITH (INDEX([PK dbo.Example data]))
WHERE
    E.[data] > 0;

The execution plan shows the index being used in a seek:

plan

We might expect rows ordered by [data] but the output typically varies with each execution:

output

The reason being that the nested loops join uses unordered prefetching.

 

For example GROUP BY this,that ends up with the data in order of that, which is weird, especially if there is no index on it.

When you (or a DBMS) want to do GROUP BY a,b, you can do it by:

  • sorting (in a,b order) ;or
  • sorting in b,a order ;or
  • hashing the combination a,b

MySQL can only do it the first way, so it will always produce results in the order of a,b - unless you add a different ORDER BY.

SQL Server's optimizer has all 3 options available - and possibly more - so you can see different results, depending on indexes, sizes of tables, joins, etc. The (coincidental) order of the results may simply be a leftover from the sorting needed to do the grouping.

If you want to demonstrate to your students, try the same query with different indexes available. Or try both GROUP BY a,b and GROUP BY b,a. Will you get results in same or different ordering? Ask your students before running the query!

Community wiki answer:

It also makes it harder to demonstrate to students that data is not automatically ordered.

This is a demo I like:

CREATE TABLE dbo.Example
(
    [data]  integer NOT NULL,
    padding character(8000) NOT NULL DEFAULT ''
);
GO
-- Add 50 rows with [data] numbered from 1 to 50
INSERT dbo.Example 
    ([data]) 
SELECT SV.number
FROM master.dbo.spt_values AS SV
WHERE SV.number BETWEEN 1 AND 50
AND SV.[type] = N'P';
GO
-- Add a nonclustered primary key on [data]
ALTER TABLE dbo.Example
ADD CONSTRAINT [PK dbo.Example data]
PRIMARY KEY NONCLUSTERED ([data]);

The following query starts with a cold cache, and is forced to use the PK:

-- Flush dirty pages to disk
CHECKPOINT;
GO
-- Drop clean buffers from memory
DBCC DROPCLEANBUFFERS;
GO
-- Query forced to use the PK ordered by [data]
SELECT TOP (15)
    E.[data],
    E.padding
FROM dbo.Example AS E 
    WITH (INDEX([PK dbo.Example data]))
WHERE
    E.[data] > 0;

The execution plan shows the index being used in a seek:

plan

We might expect rows ordered by [data] but the output typically varies with each execution:

output

The reason being that the nested loops join uses unordered prefetching.

For example GROUP BY this,that ends up with the data in order of that, which is weird, especially if there is no index on it.

When you (or a DBMS) want to do GROUP BY a,b, you can do it by:

  • sorting (in a,b order) ;or
  • sorting in b,a order ;or
  • hashing the combination a,b

MySQL can only do it the first way, so it will always produce results in the order of a,b - unless you add a different ORDER BY.

SQL Server's optimizer has all 3 options available - and possibly more - so you can see different results, depending on indexes, sizes of tables, joins, etc. The (coincidental) order of the results may simply be a leftover from the sorting needed to do the grouping.

If you want to demonstrate to your students, try the same query with different indexes available. Or try both GROUP BY a,b and GROUP BY b,a. Will you get results in same or different ordering? Ask your students before running the query!

Community wiki answer:

It also makes it harder to demonstrate to students that data is not automatically ordered.

This is a demo I like:

CREATE TABLE dbo.Example
(
    [data]  integer NOT NULL,
    padding character(8000) NOT NULL DEFAULT ''
);
GO
-- Add 50 rows with [data] numbered from 1 to 50
INSERT dbo.Example 
    ([data]) 
SELECT SV.number
FROM master.dbo.spt_values AS SV
WHERE SV.number BETWEEN 1 AND 50
AND SV.[type] = N'P';
GO
-- Add a nonclustered primary key on [data]
ALTER TABLE dbo.Example
ADD CONSTRAINT [PK dbo.Example data]
PRIMARY KEY NONCLUSTERED ([data]);

The following query starts with a cold cache, and is forced to use the PK:

-- Flush dirty pages to disk
CHECKPOINT;
GO
-- Drop clean buffers from memory
DBCC DROPCLEANBUFFERS;
GO
-- Query forced to use the PK ordered by [data]
SELECT TOP (15)
    E.[data],
    E.padding
FROM dbo.Example AS E 
    WITH (INDEX([PK dbo.Example data]))
WHERE
    E.[data] > 0;

The execution plan shows the index being used in a seek:

plan

We might expect rows ordered by [data] but the output typically varies with each execution:

output

The reason being that the nested loops join uses unordered prefetching.

 

For example GROUP BY this,that ends up with the data in order of that, which is weird, especially if there is no index on it.

When you (or a DBMS) want to do GROUP BY a,b, you can do it by:

  • sorting (in a,b order) ;or
  • sorting in b,a order ;or
  • hashing the combination a,b

MySQL can only do it the first way, so it will always produce results in the order of a,b - unless you add a different ORDER BY.

SQL Server's optimizer has all 3 options available - and possibly more - so you can see different results, depending on indexes, sizes of tables, joins, etc. The (coincidental) order of the results may simply be a leftover from the sorting needed to do the grouping.

If you want to demonstrate to your students, try the same query with different indexes available. Or try both GROUP BY a,b and GROUP BY b,a. Will you get results in same or different ordering? Ask your students before running the query!

3 Added example
source | link

Community wiki answer:

It also makes it harder to demonstrate to students that data is not automatically ordered.

This is a demo I like:

CREATE TABLE dbo.Example
(
    [data]  integer NOT NULL,
    padding character(8000) NOT NULL DEFAULT ''
);
GO
-- Add 50 rows with [data] numbered from 1 to 50
INSERT dbo.Example 
    ([data]) 
SELECT SV.number
FROM master.dbo.spt_values AS SV
WHERE SV.number BETWEEN 1 AND 50
AND SV.[type] = N'P';
GO
-- Add a nonclustered primary key on [data]
ALTER TABLE dbo.Example
ADD CONSTRAINT [PK dbo.Example data]
PRIMARY KEY NONCLUSTERED ([data]);

The following query starts with a cold cache, and is forced to use the PK:

-- Flush dirty pages to disk
CHECKPOINT;
GO
-- Drop clean buffers from memory
DBCC DROPCLEANBUFFERS;
GO
-- Query forced to use the PK ordered by [data]
SELECT TOP (15)
    E.[data],
    E.padding
FROM dbo.Example AS E 
    WITH (INDEX([PK dbo.Example data]))
WHERE
    E.[data] > 0;

The execution plan shows the index being used in a seek:

plan

We might expect rows ordered by [data] but the output typically varies with each execution:

output

The reason being that the nested loops join uses unordered prefetching.

For example GROUP BY this,that ends up with the data in order of that, which is weird, especially if there is no index on it.

When you (or a DBMS) want to do GROUP BY a,b, you can do it by:

  • sorting (in a,b order) ;or
  • sorting in b,a order ;or
  • hashing the combination a,b

MySQL can only do it the first way, so it will always produce results in the order of a,b - unless you add a different ORDER BY.

SQL Server's optimizer has all 3 options available - and possibly more - so you can see different results, depending on indexes, sizes of tables, joins, etc. The (coincidental) order of the results may simply be a leftover from the sorting needed to do the grouping.

If you want to demonstrate to your students, try the same query with different indexes available. Or try both GROUP BY a,b and GROUP BY b,a. Will you get results in same or different ordering? Ask your students before running the query!

Community wiki answer:

For example GROUP BY this,that ends up with the data in order of that, which is weird, especially if there is no index on it.

When you (or a DBMS) want to do GROUP BY a,b, you can do it by:

  • sorting (in a,b order) ;or
  • sorting in b,a order ;or
  • hashing the combination a,b

MySQL can only do it the first way, so it will always produce results in the order of a,b - unless you add a different ORDER BY.

SQL Server's optimizer has all 3 options available - and possibly more - so you can see different results, depending on indexes, sizes of tables, joins, etc. The (coincidental) order of the results may simply be a leftover from the sorting needed to do the grouping.

If you want to demonstrate to your students, try the same query with different indexes available. Or try both GROUP BY a,b and GROUP BY b,a. Will you get results in same or different ordering? Ask your students before running the query!

Community wiki answer:

It also makes it harder to demonstrate to students that data is not automatically ordered.

This is a demo I like:

CREATE TABLE dbo.Example
(
    [data]  integer NOT NULL,
    padding character(8000) NOT NULL DEFAULT ''
);
GO
-- Add 50 rows with [data] numbered from 1 to 50
INSERT dbo.Example 
    ([data]) 
SELECT SV.number
FROM master.dbo.spt_values AS SV
WHERE SV.number BETWEEN 1 AND 50
AND SV.[type] = N'P';
GO
-- Add a nonclustered primary key on [data]
ALTER TABLE dbo.Example
ADD CONSTRAINT [PK dbo.Example data]
PRIMARY KEY NONCLUSTERED ([data]);

The following query starts with a cold cache, and is forced to use the PK:

-- Flush dirty pages to disk
CHECKPOINT;
GO
-- Drop clean buffers from memory
DBCC DROPCLEANBUFFERS;
GO
-- Query forced to use the PK ordered by [data]
SELECT TOP (15)
    E.[data],
    E.padding
FROM dbo.Example AS E 
    WITH (INDEX([PK dbo.Example data]))
WHERE
    E.[data] > 0;

The execution plan shows the index being used in a seek:

plan

We might expect rows ordered by [data] but the output typically varies with each execution:

output

The reason being that the nested loops join uses unordered prefetching.

For example GROUP BY this,that ends up with the data in order of that, which is weird, especially if there is no index on it.

When you (or a DBMS) want to do GROUP BY a,b, you can do it by:

  • sorting (in a,b order) ;or
  • sorting in b,a order ;or
  • hashing the combination a,b

MySQL can only do it the first way, so it will always produce results in the order of a,b - unless you add a different ORDER BY.

SQL Server's optimizer has all 3 options available - and possibly more - so you can see different results, depending on indexes, sizes of tables, joins, etc. The (coincidental) order of the results may simply be a leftover from the sorting needed to do the grouping.

If you want to demonstrate to your students, try the same query with different indexes available. Or try both GROUP BY a,b and GROUP BY b,a. Will you get results in same or different ordering? Ask your students before running the query!

2 First revision based on a comments left by https://dba.stackexchange.com/users/993/ypercube%e1%b5%80%e1%b4%b9
source | link

Community wiki answer:

For example GROUP BY this,that ends up with the data in order of that, which is weird, especially if there is no index on it.

When you (or a DBMS) want to do GROUP BY a,b, you can do it by:

  • sorting (in a,b order) ;or
  • sorting in b,a order ;or
  • hashing the combination a,b

MySQL can only do it the first way, so it will always produce results in the order of a,b - unless you add a different ORDER BY.

SQL Server's optimizer has all 3 options available - and possibly more - so you can see different results, depending on indexes, sizes of tables, joins, etc. The (coincidental) order of the results may simply be a leftover from the sorting needed to do the grouping.

If you want to demonstrate to your students, try the same query with different indexes available. Or try both GROUP BY a,b and GROUP BY b,a. Will you get results in same or different ordering? Ask your students before running the query!

For example GROUP BY this,that ends up with the data in order of that, which is weird, especially if there is no index on it.

When you (or a DBMS) want to do GROUP BY a,b, you can do it by:

  • sorting (in a,b order) ;or
  • sorting in b,a order ;or
  • hashing the combination a,b

MySQL can only do it the first way, so it will always produce results in the order of a,b - unless you add a different ORDER BY.

SQL Server's optimizer has all 3 options available - and possibly more - so you can see different results, depending on indexes, sizes of tables, joins, etc. The (coincidental) order of the results may simply be a leftover from the sorting needed to do the grouping.

If you want to demonstrate to your students, try the same query with different indexes available. Or try both GROUP BY a,b and GROUP BY b,a. Will you get results in same or different ordering? Ask your students before running the query!

Community wiki answer:

For example GROUP BY this,that ends up with the data in order of that, which is weird, especially if there is no index on it.

When you (or a DBMS) want to do GROUP BY a,b, you can do it by:

  • sorting (in a,b order) ;or
  • sorting in b,a order ;or
  • hashing the combination a,b

MySQL can only do it the first way, so it will always produce results in the order of a,b - unless you add a different ORDER BY.

SQL Server's optimizer has all 3 options available - and possibly more - so you can see different results, depending on indexes, sizes of tables, joins, etc. The (coincidental) order of the results may simply be a leftover from the sorting needed to do the grouping.

If you want to demonstrate to your students, try the same query with different indexes available. Or try both GROUP BY a,b and GROUP BY b,a. Will you get results in same or different ordering? Ask your students before running the query!

1
source | link
    Post Made Community Wiki by user126897