SQL and ML Spaces

float is always not accurate…so is real….

use dt_numeric or dt_decimal for accurate decimal points.

precision & scale:

precision – total number of digits in the number
scale – number of digits minus the digits after the decimal

— use DT_NUMERIC with accruate precision & scale to avoid rounding issues in SSIS
— SSIS datatypes – http://msdn.microsoft.com/en-us/library/ms141036.aspx

Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For example, the number 123.45 has a precision of 5 and a scale of 2.

DT_DECIMAL

An exact numeric value with a fixed precision and a fixed scale. This data type is a 12-byte unsigned integer with a separate sign, a scale of 0 to 28, and a maximum precision of 29.

DT_NUMERIC

An exact numeric value with a fixed precision and scale. This data type is a 16-byte unsigned integer with a separate sign, a scale of 0 – 38, and a maximum precision of 38.
In SQL Server, the default maximum precision of numeric and decimal data types is 38

SQL Server evaluates an expression that depends on cost estimation plan which doesnt always make sense with other programming languages.

declare @a int = 1, @b int = 0
print 'if @a = @b + 1 or @a/@b = 2'
if @a = @b + 1 or @a/@b = 2
print 'No Error'
print 'if @a/@b = 2 or @a = @b + 1'
if @a/@b = 2 or @a = @b + 1
print ' Error'
go

-- we should have got divide by zero error as parent_object_id is 0 but alas NO
select *
from sys.tables
select *
from sys.tables
where schema_id / parent_object_id >10
and object_id < 10

Because of PARALLELISM
for actual sql handle look for ecid = 0

grant a permission
revoke to undo the permissions given by grant

deny – to make the data invisible to the user, cannot be given access by grant again..kinda permanent change

first fire sp_helpfile to get the name and location of the files

use master
GO
sp_helpfile

ALTER DATABASE TEMPDB MODIFY FILE (
NAME = tempdev, FILENAME = 'E:\TEMPDB\tempdev.mdf'
)
ALTER DATABASE TEMPDB MODIFY FILE (
NAME = templog, FILENAME = 'E:\TEMPDB\templog.ldf'
)


It will only be reflected after SQL Server is restarted. Then you can delete those old tempdev.mdf and templod.ldf files to gain space.