Tutorial by Examples

Data Manipulation Language (DML for short) includes operations such as INSERT, UPDATE and DELETE: -- Create a table HelloWorld CREATE TABLE HelloWorld ( Id INT IDENTITY, Description VARCHAR(1000) ) -- DML Operation INSERT, inserting a row into the table INSERT INTO HelloWorld (D...

Display a message to the output console. Using SQL Server Management Studio, this will be displayed in the messages tab, rather than the results tab: PRINT 'Hello World!';

Syntax: SELECT * FROM table_name Using the asterisk operator * serves as a shortcut for selecting all the columns in the table. All rows will also be selected because this SELECT statement does not have a WHERE clause, to specify any filtering criteria. This would also work the same way if you...

Generally, the syntax is: SELECT <column names> FROM <table name> WHERE <condition> For example: SELECT FirstName, Age FROM Users WHERE LastName = 'Smith' Conditions can be complex: SELECT FirstName, Age FROM Users WHERE LastName = 'Smith' AND (City = 'New York' OR C...

UPDATE HelloWorlds SET HelloWorld = 'HELLO WORLD!!!' WHERE Id = 5 The above code updates the value of the field "HelloWorld" with "HELLO WORLD!!!" for the record where "Id = 5" in HelloWorlds table. Note: In an update statement, It is advised to use a "where&...

A simple form of updating is incrementing all the values in a given field of the table. In order to do so, we need to define the field and the increment value The following is an example that increments the Score field by 1 (in all rows): UPDATE Scores SET score = score + 1 This can be dang...

Transact-SQL supports two forms of comment writing. Comments are ignored by the database engine, and are meant for people to read. Comments are preceded by -- and are ignored until a new line is encountered: -- This is a comment SELECT * FROM MyTable -- This is another comment WHERE Id = 1; ...

SELECT @@VERSION Returns the version of MS SQL Server running on the instance. SELECT @@SERVERNAME Returns the name of the MS SQL Server instance. SELECT @@SERVICENAME Returns the name of the Windows service MS SQL Server is running as. SELECT serverproperty('ComputerNamePhysicalNetBIOS'...

Whenever you change data, in a Data Manipulation Language(DML) command, you can wrap your changes in a transaction. DML includes UPDATE, TRUNCATE, INSERT and DELETE. One of the ways that you can make sure that you're changing the right data would be to use a transaction. DML changes will take a loc...

DELETE FROM Helloworlds This will delete all the data from the table. The table will contain no rows after you run this code. Unlike DROP TABLE, this preserves the table itself and its structure and you can continue to insert new rows into that table. Another way to delete all rows in table is ...

TRUNCATE TABLE Helloworlds This code will delete all the data from the table Helloworlds. Truncate table is almost similar to Delete from Table code. The difference is that you can not use where clauses with Truncate. Truncate table is considered better than delete because it uses less transacti...

SELECT * INTO NewTable FROM OldTable Creates a new table with structure of old table and inserts all rows into the new table. Some Restrictions You cannot specify a table variable or table-valued parameter as the new table. You cannot use SELECT…INTO to create a partitioned table, even whe...

The following example can be used to find the total row count for a specific table in a database if table_name is replaced by the the table you wish to query: SELECT COUNT(*) AS [TotalRowCount] FROM table_name; It is also possible to get the row count for all tables by joining back to the table'...