Cacher is the code snippet organizer for pro developers

/************************************
@@ROWCOUNT
************************************/

/*
@@ROWCOUNT returns the number of rows affected by the last statement executed. It's common to use
this to check if the previous statement affected any rows by checking that the funciton's result is
zero or greater than zero.

As an exmaple, this SELECTs a statement that will not return any rows. If this is the case (which
it is) then it will SELECT 'No Conribution Found'.
*/

SELECT cont_dt from T_CONTRIBUTION where customer_no = 3021111111

IF @@ROWCOUNT = 0
	SELECT 'No Contribution Found' AS 'Error Message';
	
/* Results:

cont_dt
-----------------------

(0 rows affected)

Error Message
---------------------
No Contribution Found

(1 row affected)

*/

/************************************
IDENTITY
************************************/
	
/* IDENTITY - property of a column which generates sequential numbers automatically for insertion 
into table. There are a few ways to query what the last identity created is:

@@IDENTITY - the last identity generated in the current session. Be careful, this will pull 
the last IDENTITY created in any table. If another IDENTITY has been created subsequently in 
that session, then it'll pull that IDENTITY and not the one you're expecting.

SCOPE_IDENTITY() - the last identity generated in the current scope. This will make sure 
you're pulling the IDENTITY that you created, but be careful if there were multiple IDENTITies 
updated in your batch, you might not get the value you're expecting.

IDENT_CURRENT('table_name') - the last identity inserted into a table, regardless of what 
session or scope it was created in.

*/

INSERT INTO Sales.Orders (CustomerID, Amount)
VALUES (12, 2056);

SELECT SCOPE_IDENTITY() AS OrderID;

-- Overriding IDENTITY - you can use SET INDENTITY_INSERT to ON in order to allow you to override an IDENTITY restriction and set the number yourself. You probably want to turn this on to make you change, and then turn it off again so that the IDENTITY constraint continues to keep creating unique values.
SET IDENTITY_INSERT SalesLT.CallLog ON;

-- CallID has an IDENTITY property so you can't insert that number, bu with SET IDENTITY_INSERT ON you can force it
INSERT INTO SalesLT.CallLog (CallID, SalesPerson, CustomerID, PhoneNumber)
VALUES (9, 'adventureworks, 11, '926-3456')

/************************************
SEQUENCE
************************************/
/* CREATE SEQUENCE - object that creates a sequential value, but are not tied to a single table. 
For instance, you can have sequential values created for OrderID in your web orders table 
and also in your retails orders table, therefore creating unique values for OrderID regardless 
of what table the order lands into. Once CREATE SEQUENCE command is used, every time you need a new value
you invoke the NEXT VALUE FOR <sequence name>.

*/
CREATE SEQUENCE Sales.OrderNumbers AS INT
STARTS WITH 1 INCREMENT BY 1;

...
SELECT NEXT VALUE FOR Sales.OrderNumbers;

/************************************
NEWID, UNIQUEIDENTIFIER, GUID
************************************/

/*
To create a unique global identifier, you use NEWID(). This creates a random identifier - each time
you run it, you will get a new number.

If you want a GUIDs that always increase within the machine, use NEWSEQUENTIALID(), which can
only be used as default for column of the 
type uniqueidentifier.
*/

/* Results:

myguid
AA5E3564-E744-4B6D-9271-58994107B273

*/