Published / updated29 Aug 2026

CRUD, Filtering and Sorting

Before you start

You need: keys and constraints (Article 03).

Time: about 50 minutes, plus the practice.

Learning objective

Write every basic data operation correctly, and never run an UPDATE or DELETE that affects more rows than you intended.

Topics

• SELECT, column lists, aliases
• WHERE — operators, IN, BETWEEN, LIKE
• ORDER BY and OFFSET / FETCH
• TOP and DISTINCT
• INSERT, single and multi-row
• UPDATE — and the safety habit
• DELETE versus TRUNCATE
• Logical execution order

SELECT

SELECT   s.RollNumber,
         s.Name,
         s.ClassName + ' - ' + s.Section AS ClassSection,
         s.ParentPhone
FROM     dbo.Student AS s
WHERE    s.SchoolId = 1
  AND    s.Status = 0
ORDER BY s.ClassName, s.Section, s.Name;

AS names a column in the output or gives a table an alias. Aliases make joins readable and are effectively mandatory once more than one table is involved.

Never ship SELECT *. It sends columns nobody uses, breaks when a column is added or reordered, and — the real problem — publishes new columns automatically. Add a PasswordHash column to User and every SELECT * in the application starts returning it.

SELECT * is fine while exploring in SSMS. It is not fine in an application or a view.

WHERE

Operator	Meaning
= <> < > <= >=	Comparison
AND OR NOT	Logic
IN (...)	Matches any in a list
BETWEEN a AND b	Inclusive range
LIKE	Pattern match
IS NULL / IS NOT NULL	The only null tests

WHERE s.ClassName IN ('9th', '10th')
WHERE r.MarksObtained BETWEEN 35 AND 60
WHERE s.Name LIKE 'Ravi%'
WHERE s.Address IS NULL

AND, OR and parentheses

-- Wrong: AND binds tighter than OR, so this reads as
--   ClassName = '9th'  OR  (ClassName = '10th' AND Section = 'A')
WHERE s.ClassName = '9th' OR s.ClassName = '10th' AND s.Section = 'A'

-- Correct
WHERE (s.ClassName = '9th' OR s.ClassName = '10th') AND s.Section = 'A'

AND has higher precedence than OR. Mixing them without parentheses is a silent logic error that returns too many rows — and looks correct at a glance. Parenthesise whenever both appear.

LIKE

Pattern	Matches
'Ravi%'	Starts with Ravi
'%Kumar'	Ends with Kumar
'%ar%'	Contains ar
'_avi'	One character, then avi
'[RS]%'	Starts with R or S
'[^RS]%'	Does not start with R or S

A leading % prevents index use, forcing a scan of every row. LIKE 'Ravi%' can seek; LIKE '%Ravi%' cannot. On a large table that is the difference between instant and slow.

To search for a literal % or _, escape it:

WHERE Remarks LIKE '%50!%%' ESCAPE '!'

Without this, a search for 50% returns everything containing 50 followed by anything.

Case sensitivity

String comparison follows the column's collation. The common default, SQL_Latin1_General_CP1_CI_AS, is case-insensitive (CI) and accent-sensitive (AS), so 'ravi' = 'Ravi' is true.

Do not rely on it. A database restored with a different collation changes the behaviour of every comparison. Be explicit when it matters:

WHERE s.Name = 'Ravi Kumar' COLLATE SQL_Latin1_General_CP1_CS_AS

And avoid wrapping the column in UPPER() or LOWER() for a case-insensitive match — on a CI collation it is unnecessary, and it defeats the index.

Functions on columns kill indexes

-- Cannot seek: the function runs on every row
WHERE YEAR(p.PaidOn) = 2024
WHERE UPPER(s.Name) = 'RAVI KUMAR'
WHERE LEFT(s.RollNumber, 3) = 'NCA'

-- Can seek
WHERE p.PaidOn >= '2024-01-01' AND p.PaidOn < '2025-01-01'
WHERE s.Name = 'Ravi Kumar'
WHERE s.RollNumber LIKE 'NCA%'

A condition where the column sits alone on one side is sargable — SQL Server can use an index. Wrap the column in anything and it cannot. This single rule explains most "why is this query slow" questions at this level.

ORDER BY

ORDER BY s.ClassName, s.Section, s.Name;
ORDER BY r.MarksObtained DESC, s.Name ASC;

Without ORDER BY, row order is undefined. Not "insertion order", not "primary key order" — undefined. It may look stable for months and change when an index is added or the data grows. If order matters, say so explicitly.

Ordering by column position works but is fragile:

ORDER BY 3 DESC   -- breaks silently when the SELECT list changes

NULL ordering

NULL sorts first ascending in SQL Server. To push it last:

ORDER BY CASE WHEN s.Address IS NULL THEN 1 ELSE 0 END, s.Address;

Paging

SELECT   s.RollNumber, s.Name, s.ClassName, s.Section
FROM     dbo.Student AS s
WHERE    s.SchoolId = 1 AND s.Status = 0
ORDER BY s.ClassName, s.Section, s.Name
OFFSET   40 ROWS
FETCH    NEXT 20 ROWS ONLY;

OFFSET skips; FETCH NEXT takes. OFFSET is (pageNumber - 1) * pageSize.

ORDER BY is required — OFFSET without it is a syntax error, and rightly so: skipping 40 rows of an undefined order is meaningless.

The ordering must also be deterministic. Ordering only by ClassName when many students share a class means rows can shuffle between pages, so the same student appears on page 2 and page 3 while another is never shown. Include a unique column as the final tiebreaker:

ORDER BY s.ClassName, s.Section, s.Name, s.Id

That trailing s.Id is what makes paging correct.

TOP and DISTINCT

SELECT TOP (10) s.Name, r.MarksObtained
FROM   dbo.ExamResult AS r
JOIN   dbo.Student    AS s ON s.Id = r.StudentId
WHERE  r.ExamId = 5 AND r.IsAbsent = 0
ORDER BY r.MarksObtained DESC;

SELECT TOP (10) WITH TIES ...   -- includes everyone tied at the cut-off

TOP without ORDER BY returns an arbitrary ten rows.

SELECT DISTINCT s.ClassName, s.Section
FROM   dbo.Student AS s
WHERE  s.SchoolId = 1;

DISTINCT applies to the whole row, not one column. Needing it on a join usually means the join is producing duplicates — fix the join rather than hiding the symptom, which the joins article covers.

INSERT

-- Always name the columns
INSERT INTO dbo.Student (SchoolId, Name, RollNumber, ClassName, Section,
                         DateOfBirth, ParentName, ParentPhone)
VALUES (1, N'Sneha Patel', 'NCA-2024-0044', '9th', 'A',
        '2010-03-14', N'Mahesh Patel', '9812345670');

-- Several rows in one statement
INSERT INTO dbo.Student (SchoolId, Name, RollNumber, ClassName, Section,
                         DateOfBirth, ParentName, ParentPhone)
VALUES (1, N'Kiran Rao',    'NCA-2024-0051', '9th',  'B', '2010-07-02', N'Suresh Rao',   '9701112233'),
       (1, N'Arjun Reddy',  'NCA-2024-0031', '10th', 'B', '2009-11-20', N'Vijay Reddy',  '9701234567');

-- From a query
INSERT INTO dbo.ArchivedStudent (SchoolId, Name, RollNumber)
SELECT SchoolId, Name, RollNumber
FROM   dbo.Student
WHERE  Status = 2;

Always list the columns. INSERT INTO dbo.Student VALUES (...) depends on column order, so adding a column anywhere silently shifts every value into the wrong place.

Retrieve the generated key:

INSERT INTO dbo.Student (...) VALUES (...);
SELECT SCOPE_IDENTITY() AS NewStudentId;

Or capture more with OUTPUT:

INSERT INTO dbo.Student (SchoolId, Name, RollNumber, ClassName, Section,
                         DateOfBirth, ParentName, ParentPhone)
OUTPUT INSERTED.Id, INSERTED.PublicId
VALUES (1, N'Sneha Patel', 'NCA-2024-0044', '9th', 'A',
        '2010-03-14', N'Mahesh Patel', '9812345670');

OUTPUT works for multi-row inserts, where SCOPE_IDENTITY() gives you only the last one.

UPDATE — and the habit that prevents disasters

UPDATE dbo.Student
SET    ClassName = '11th',
       Section   = 'A'
WHERE  SchoolId = 1
  AND  RollNumber = 'NCA-2024-0012';

An UPDATE with no WHERE updates every row in the table. There is no confirmation prompt.

Build this habit and never break it:

-- 1. Write it as a SELECT first and check the row count
SELECT * FROM dbo.Student
WHERE  SchoolId = 1 AND RollNumber = 'NCA-2024-0012';

-- 2. Same WHERE, now as the UPDATE
UPDATE dbo.Student
SET    ClassName = '11th'
WHERE  SchoolId = 1 AND RollNumber = 'NCA-2024-0012';

For anything on production data, wrap it:

BEGIN TRANSACTION;

UPDATE dbo.Student
SET    Status = 3
WHERE  SchoolId = 1 AND ClassName = '12th';

SELECT @@ROWCOUNT AS RowsAffected;   -- expected 48?

-- ROLLBACK TRANSACTION;
-- COMMIT TRANSACTION;

Run it, read the count, and only then run COMMIT or ROLLBACK. If the number is not what you expected, roll back and work out why.

@@ROWCOUNT after the statement tells you how many rows changed — check it in application code too, because an UPDATE matching nothing is not an error:

IF @@ROWCOUNT = 0
    THROW 50001, 'No student was updated. The record may have been removed.', 1;

Updating from another table

UPDATE fa
SET    fa.PaidAmount = fa.PaidAmount + p.Amount
FROM   dbo.FeeAccount AS fa
JOIN   dbo.FeePayment AS p ON p.FeeAccountId = fa.Id
WHERE  p.Id = @PaymentId;

Alias the target table and use that alias after UPDATE. If the join matches several rows for one target row, SQL Server updates it once with an arbitrary matching value and reports no error — a genuine source of wrong data. Verify the join produces one row per target first.

DELETE and TRUNCATE

DELETE FROM dbo.ExamResult
WHERE  ExamId = 5 AND StudentId = 12;

Same rule: no WHERE means every row. Same habit: SELECT first, transaction on production.

	DELETE	TRUNCATE TABLE
WHERE clause	Yes	No — all rows
Logging	Per row	Minimal
Speed on a large table	Slow	Very fast
Resets IDENTITY	No	Yes
Fires triggers	Yes	No
Blocked by a foreign key reference	Per row	Always, if any FK points at the table
Rollback in a transaction	Yes	Yes

TRUNCATE is for emptying a staging table. It resets the identity seed, which means new rows reuse ids that old data may still reference elsewhere.

Prefer soft delete

-- Not this, for a student with history
DELETE FROM dbo.Student WHERE Id = 12;

-- This
UPDATE dbo.Student
SET    Status = 1              -- Inactive
WHERE  SchoolId = 1 AND Id = 12;

A student has exam results, attendance, fee accounts, and payments. A hard delete is either blocked by foreign keys — correctly — or, with cascades, destroys records the school must retain.

Soft delete adds one obligation: every query must filter it out.

WHERE s.SchoolId = 1 AND s.Status <> 1

Forget that filter in one report and "deleted" students reappear. That is the standard soft-delete bug, and it is reported as "delete does not work".

Logical execution order

SQL is written in one order and evaluated in another:

Written:   SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY
Evaluated: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

Two consequences you will hit immediately:

-- Fails: the alias does not exist yet when WHERE runs
SELECT s.Name, s.ClassName + '-' + s.Section AS ClassSection
FROM   dbo.Student AS s
WHERE  ClassSection = '10th-A';

-- Works: ORDER BY runs after SELECT
SELECT s.Name, s.ClassName + '-' + s.Section AS ClassSection
FROM   dbo.Student AS s
ORDER BY ClassSection;

To filter on a computed value, repeat the expression in WHERE, or use a CTE or subquery — covered in the grouping article.

Errors you will hit

Message	Cause	Fix
Invalid column name 'Nmae'	Typo in a column name	Check the spelling in Object Explorer
An UPDATE changes every row	WHERE clause missing	Always SELECT first with the same WHERE
WHERE Section = NULL returns nothing	NULL is never equal to anything	IS NULL
WHERE MarksObtained <> 0 skips absentees	NULL fails every comparison	Handle NULL explicitly
Query is slow after adding WHERE YEAR(PaidOn) = 2024	A function on the column defeats the index	Use a date range instead
Rows come back in a different order each run	No ORDER BY	Order explicitly if order matters

Run the SELECT before the UPDATE. Same WHERE, same table — if the SELECT returns 800 rows, the UPDATE was about to change 800 rows.

Common mistakes

• SELECT * in an application or a view
• Mixing AND and OR without parentheses
• UPDATE or DELETE with no WHERE
• Not checking @@ROWCOUNT
• No ORDER BY, then relying on the order returned
• OFFSET paging with a non-deterministic ORDER BY, so rows repeat across pages
• Wrapping a column in a function in WHERE, defeating the index
• LIKE '%term%' on a large table where LIKE 'term%' would do
• INSERT without a column list
• Forgetting the soft-delete filter
• TRUNCATE on a table whose identity values are referenced elsewhere
• Using a SELECT alias in WHERE

Practice

Using the School Management System schema, write and run the course exercise — filtered CRUD queries:

1. All active students in class 10th, ordered by section then name.
2. Students whose name starts with Ra, and separately those containing ar. Compare execution times on a table with a few thousand rows.
3. Students in classes 9th or 10th and section A. Write it once without parentheses and once with, and compare the row counts.
4. Page 3 of the student list at 20 per page, with a deterministic order.
5. Top 5 scorers in one exam, excluding absentees, including ties.
6. Insert three students in a single statement, returning the generated ids with OUTPUT.
7. Promote every 10th-class student to 11th, inside a transaction. Check @@ROWCOUNT before committing.
8. Soft-delete one student, then run your list query from step 1 and confirm they are gone.
9. Remove the Status <> 1 filter from that query and confirm they reappear. This is the soft-delete bug, on purpose.

Then run the safety drill: in a scratch copy of the database, run UPDATE dbo.Student SET Section = 'Z'; with no WHERE, inside a transaction. Read @@ROWCOUNT, then ROLLBACK. Doing this once, deliberately, in a safe place is what builds the habit of writing the WHERE clause first.

You can now

• Write SELECT, INSERT, UPDATE and DELETE correctly
• Always test a WHERE with a SELECT first
• Handle NULL with IS NULL, not =
• Keep filters sargable so indexes still work
• Say why results without ORDER BY have no guaranteed order

Review questions

1. Why does WHERE ClassName = '9th' OR ClassName = '10th' AND Section = 'A' return too many rows?
2. Why must OFFSET paging use a deterministic ORDER BY?
3. Why does WHERE YEAR(PaidOn) = 2024 perform worse than a range comparison?
4. What are three differences between DELETE and TRUNCATE TABLE?

Next: Functions and expressions