SQL Server Arithmetic Operators (%)

(%) modulo operator is used to get the remainder of one number.

Select 47 / 7 AS IntValue, 47 % 7 AS RemValue;

Result: 6, 5

SQL Server Arithmetic Operators (/)

(/) division operator is used to divide two expressions.

Select id, name, someColumn / 10

SQL Server Arithmetic Operators (*)

(*) multiplication operator is used to multiply two operators.

Select 10 * 7

Result = 70

SQL Server Arithmetic Operators (-)

(-) subtraction operator is used to subtract two numbers, also subtracts a number from date as number of days.

DECLARE @someNumber int; SET @someNumber = 191;
SELECT '200' - @someNumber;

Result = 9 

DECLARE @initialdate datetime, @someNumber int;
SET @initialdate = ''June 26, 1982 07:33 AM';
SET @someNumber = 3;
SELECT @initialdate - @someNumber AS 'Final Date';
Result = 1982-06-23 07:33:00.000

SQL Server Arithmetic Operators (+)

(+) addition arithmetic operator is used to add two numbers, also adds a number either in days or in a date.

DECLARE @someNumber int; SET @someNumber = 191;
SELECT '200' + @someNumber;

Result = 391 

DECLARE @initialdate datetime, @someNumber int;
SET @initialdate = ''June 26, 1982 07:33 AM';
SET @someNumber = 3;
SELECT @initialdate + @someNumber AS 'Final Date';
Result = 1982-06-29 07:33:00.000

Version history in your database using Microsoft Visual Studio 2010

In this blog post you will find a little technique that we use in order to store a version history of our deployments. Please check that this requires a working knowledge of datadude so if you don’t know what the terms SQLCMD variables, Post-Deployment script, msbuild & Continuous Integration means to then this blog post may not be referred to for you :)

First we Need a table

First we need a table for the storage of our version history, in this case lets called it BuildVersion and it definitely looks like the following

CREATE TABLE dbo.[BuildVersion]
(
BuildVersion NVARCHAR(20) NOT NULL,
Deployed DATETIME NOT NULL
)

Second we Need a project variable

Here we have a SQLCMD variable declared as part of our project that is meant to store a version number. By default in datadude SQLCMD variables are declared in a file that is called Database.sqlcmdvars:

we have a variable called ‘BuildVersion’ that are default to the value of “Unknown”

Here we Need to populate the table

We are using the value in our ‘BuildVersion’ variable to fill our BuildVersion table and we do that within a Post-Deployment script here by using the following code sample that you may see what happen next :)

INSERT [dbo].[BuildVersion]([BuildVersion],

[Deployed])
VALUES ('$(BuildVersion)',

GETDATE())

--$(Here BuildVersion is a SQLCMD variable declared within this project)

And here we need to tell our mechanism what the build number really is

First we need to make sure that $(BuildVersion) has a value in it. As we are deploying our database as part of a continuous integration build (leveraging msbuild) then we can pass in a value from our msbuild script. Find the code that we use to do that:

<Exec Command="..\VSDBCMD\vsdbcmd.exe /Action:Deploy /ConnectionString:&quot;Data Source=$(Server);Integrated Security=True" /DeployToDatabase:+ /ManifestFile:"..\MyDB.deploymanifest"/p:BuildVersion="$(BuildLabel)"" />

The important bit for what we are discussing here is that last part (underlined):

/p:BuildVersion="$(BuildLabel)"

The /p directive is used to populate a named variable (in our case it is BuildVersion) with a value. In our case than value is grabbed from an msbuild property which in this case, is referred to by $(BuildLabel). [Please note that managing msbuild properties is outside the scope of this article.]

Lets Wrap-up

That’s really what you need. If it all remains together correctly then [BuildVersion] will surely contain a nice history of all your deployments

I hope this is useful for you. I guess the same technique will also work in earlier versions of datadude but I don’t have any to hand so can’t about it.

Technology in the line of Extinction

We have heard about the extinction of dinosaurs, mammoths etc. happened billions of years ago but have you ever noticed that you are also a witness of some extinction activities...

Following are few technologies that are on the edge of extinction or some already extinct :)

1. Landline phones

2. Floppy disks

3. Wristwatches

4. VHS tape and VCRs

5. Film cameras

6. Typewriters

7. The Walkman, Discman and MiniDisc player

8. Dial-up Internet access

9. CDs & DVDs

10. Fax Machines

11. Tube TVs & Plasma TVs

12. Beepers

13. Credit Cards

14. E-Book Readers

15. iPods

16. Steam Engines

17. Instant Cameras

18. Computer Monitors