Float, Double, Decimal in C#

Possibly save 1 hour of your time: There are times where you have to do some calculations on your data in .NET. If the data are based on different data types such as float, double or decimal, you will get error when you do calculations and storing the result in a certain datatype.

The best is to find out which of these fields have the most precision required or basically what is the biggest number possible.

Float, Double, Decimal is the biggest from the left to right respectively.

In my case, there are some big numbers so I just store them as decimals in both .NET and SQL.

Here is a good explanation on how SQL datatypes map to .NET datatypes:

https://msdn.microsoft.com/en-us/library/system.data.sqldbtype.aspx

(SQL -> .NET)

float -> double

decimal -> decimal

 

Error: System.InvalidOperationException : The specified cast from a materialized ‘System.Double’ type to the ‘System.Decimal’ type is not valid.

This happens when my SQL datatype is float but in .NET the datatype is decimal. Since I need to do calculations using divide operator, I will change SQL datatype from float to decimal(30,15).

 

Advertisements

T-SQL – split a string value based on comma or another delimiter

Possibly save 1 hour of your time: This shows how you can take a column that has data such as a string value with comma separated value or semi-colon separated value and process each value for other purpose. In my case I want to check for foreign key not exist.

</pre>
DECLARE @MyCursor CURSOR;
SET @MyCursor = CURSOR FOR
SELECT [column_with_delimiter_separated_values]
FROM [dbo].[YourTable]

DECLARE @textString varchar(500);

OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @textString

DECLARE @tempSplitText TABLE
(
SplitString nvarchar(500)
)

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @delimiter char = ';'
DECLARE @pos INT = 0
DECLARE @len INT = 0
DECLARE @value varchar(500)

WHILE CHARINDEX(@delimiter, @textString, @pos+1)>0
BEGIN
set @len = CHARINDEX(@delimiter, @textString, @pos+1) - @pos
set @value = SUBSTRING(@textString, @pos, @len)
INSERT INTO @tempSplitText(SplitString) VALUES(RTRIM(LTRIM(@Value)))
set @pos = CHARINDEX(@delimiter, @textString, @pos+@len) +1
END

IF (LEN(@region) > @len) --deals with last element
BEGIN
set @value = SUBSTRING(@textString, @pos, LEN(@textString)-@pos+1)
INSERT INTO @tempSplitText(TextString) VALUES(RTRIM(LTRIM(@Value)))
END

FETCH NEXT FROM @MyCursor
INTO @textString
END;

CLOSE @MyCursor;
DEALLOCATE @MyCursor;

select r. from @tempSplitText r
left join [dbo].[YourTableWithTheForeignKey] c on r.SplitString = c.ForeignKey
where c.ForeignKey is null
<pre>

 

Using T-SQL cursor to loop through table records

Possibly save 1 hour of your time: There may be times you need to loop through each record in a table and process the data in the specific row or column.

DECLARE @MyCursor CURSOR;
SET @MyCursor = CURSOR FOR
  SELECT column1, column2
  FROM table
  WHERE clause

DECLARE @column1 int;
DECLARE @column2 varchar(500);

OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @column1 , @column2

WHILE @@FETCH_STATUS = 0
BEGIN
 print Cast(@column1 as varchar(10)) + '-' + @column2
 FETCH NEXT FROM @MyCursor
INTO @column1 , @column2
END

CLOSE @MyCursor;
DEALLOCATE @MyCursor;

SQL Server database – changes from SIMPLE to FULL recovery mode after dacpac deployment

Possibly save 1 hour of your time: Our SQL Server ran out of disk space and my operations team found out that one of the database is recording huge transaction logs because the database is in FULL recovery mode in our system test environment.

What causes it? We ran the following command to pinpoint which process changes the recovery mode and at what time. Based on the date and time, we figure out that this happens at similar time as our deployment.

Sp_readerrorlog 0, 1, 'recovery'

Then you can run one of the following to determine the user and time when the recovery mode was changed. id below should be replaced with actual id from above query.

sp_who id

or

sp_who2

The cause of this was from a setting I was not aware of when creating the dacpac deployment file. To change this setting, right click on your database project and choose Properties. Under Project Settings, click on Database Settings. Then Operational. Change Recovery to SIMPLE or FULL as desired.

SIMPLE recovery mode

Rename failed for Database Microsoft SqlServer. The database could not be exclusively locked to perform the operation

Possibly save 1 hour of your time: You want to rename a database that is shared with other developer and you receive this error.

Solution:

</pre>
DECLARE @DbName nvarchar(50)
SET @DbName = N'replace_your_database_name'

DECLARE @EXECSQL varchar(max)
SET @EXECSQL = ''

SELECT @EXECSQL = @EXECSQL + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DbName) AND loginame not like '%replace_your_login_name%'

EXEC(@EXECSQL)
<pre>

The above will remove other people’s connections. Once you close your query window, you can try to rename the database now.

However, I still get message that you cannot kill your own process.  Then you need to run the above SELECT statement without checking for your loginname to determine what other sessions you have. Then you can manually run EXEC(‘Kill 78;’). The number here should be your process id.

T-SQL – Arithmetic overflow error converting numeric to data type numeric.

Possibly save 1 hour of your time: When you have a float data value operation in T-SQL and assign it to a variable with less precision, you may get this error.

The following will give you this error since float cannot be converted to decimal.

Set PercentValue (decimal(5,2)) = Value (float) / AnotherValue (float)

Solution:

Increase the decimal data type to allow more values and precision like decimal (10, 4).

 

 

 

 

 

 

Visual Studio database project schema compare (.scmp) show delete all objects

Possibly save 1 hour of your time: If you are using schema compare in your database solution project within Visual Studio, the initial compare shows nothing on the project vs  your target database. It tells you the difference is to delete all objects such as tables and stored procedures.

The problem was that the scripts (.sql) files are added to the folder outside of Visual Studio. You need to right click on the folder and choose Add >> New Item. Then you can select under each SQL Server menu sub category and the SQL object.

sqlobject.png

Book Microsoft SQL Server 2016: A Beginner’s Guide, Sixth Edition

Sql Server database project template missing in Visual Studio

Possibly save 1 hour of your time: There is a good way to manage your database objects. You can create a solution project for your database as follows.

In Visual Studio, go to File >> New >> Project. You should see the template under Installed >> Templates >> Other Languages >> SQL Server >> SQL Server Database Project.

database project

If you cannot see the above template in Visual Studio 2015, then you need to get SSDT as follows.

Open Visual Studio (mine is 2015) >> choose Tools >> Extensions. Update Updates >> Product updates >> you may find the Microsoft SQL Server Data Tools (SSDT) update that contains the database project template. Select it and Update.

tools and extensions.png

You can see it is installing Microsoft SQL Server Data Tools v14.0.61707.300.

data tools.png

Alternatively, you can also download the tools from

https://msdn.microsoft.com/en-us/mt186501

Book: Microsoft SQL Server 2016: A Beginner’s Guide, Sixth Edition

Truncate local SQL Server database log file to save disk space

Possibly save 1 hour of your time: I notice my machine disk space is running low. So where can I save some space? Database log is usually a big one.

First, download a free program call WindirStat. This program will tell you in a very nice visual way of where most of your space are taken up.

So I found out from my machine there are two database log files in which both took up over 50 GB.

Solution: I backup the database first. Then I ran the following command on the specific database in SQL Server Management Studio 2012.

select * from sys.database_files --find the name of the log file

ALTER DATABASE your_database_name SET RECOVERY SIMPLE
DBCC SHRINKFILE('name_of_log', 0, TRUNCATEONLY)
ALTER DATABASE your_database_name SET RECOVERY FULL

Murach’s SQL Server 2016 for Developers

sql server seed identity reset

Possibly save 1 hour of your time: Sometimes after deleting some reference data in a table one want to change the identity to reseed at 1 or a certain value. The next time you insert a new record it will reseed starting at 1.

DBCC CHECKIDENT ('YourTableName', RESEED, 0);
GO

Introducing Microsoft SQL Server 2016: Mission-Critical Applications, Deeper Insights, Hyperscale Cloud