Today, I’m continuing the discussion on the variety of data types supported by SQL Server. I’ll round out the discussion by looking at the operators that are used with the numeric data types. Here are the operators:
Some of these are fairly obvious but even then, they can have hidden complexity.
We use the plus sign + to add numbers, and we use the minus sign (or dash) - to subtract numbers. No surprise there.
2019-10-14
One thing that I quite like about Snowflake is the way it cleanly works with a wide variety of file formats.
Obviously this changes over time but at the time of writing, you could COPY from the following source file formats:
There are also quite a number of options for configuring how these are used. Apart from the obvious options like record and field delimiters, skipping rows, etc, one of the most important of these options is compression. You can currently choose these options for compression:
2019-10-11
There was an interesting question in the forums the other day. The poster wanted to be able to put entries in a table to determine who could access data in the other tables in the database.
There are two basic ways to do this. If you want an error thrown, you’d be best just using GRANT/DENY/REVOKE as permissions and not using your own table to control it. However, if you want no error, but just no data, then the Row Level Security (RLS) added in SQL Server 2016 could do the job. Let’s take a look:
2019-10-10
This post relates to another question I got on Stack Overflow recently.
The poster was saying that he was having no luck inserting data from teh Gujarati language, even though he was using nvarchar as a data type.
The most common problem that I see when people aren’t getting the outcome they want when inserting into an nvarchar column is that they aren’t putting N in front of their string values. (N is National Characters Set). Imagine a table like this:
2019-10-10
Lately, I’ve been needing to write queries against the SQL Server Reporting Services catalog. And if you’ve ever tried that, you’ll find that items in the catalog have a type, but there’s no table or view that turns that type (a number) into a name.
So, in our free SDU Tools for developers and DBAs, we added a simple view that does just that. It’s called RSCatalogTypes.
You can see it in action in the main image above, and in the video here:
2019-10-09
Knocking Access was a popular sport over the last decade or more. Many data professionals saw Access as a real problem. Lots of silos of unmanaged data grew up across organizations and things could get out of hand pretty quickly. I saw all the expected problems that come from a lack of centralized management of data.
Some issues were quite nasty. I remember doing work for a company that did aircraft maintenance and had depots all over the country. Every depot had a copy of an Access database, but every single one had then modified it in different, and in many cases, conflicting ways. Then they decided to centralize the data, and oh what a pain.
2019-10-08
Today, I’m continuing the discussion on the variety of data types supported by SQL Server. Last time I mentioned exact decimal numbers, but there are also inexact (or approximate) decimal numbers. Here are the available types:
These two data types float and real are approximate data types.
You probably realize that in decimal, there are values we can’t store exactly like 1/3. No matter how many 3’s we write when we write 0.33333333, we are still never going to have the exact value.
2019-10-07
Snowflake has the normal options in its SQL language for using INSERT statements to add data to a table. But it also has bulk options for rapidly adding a lot of data to a table via the COPY command. The same command can be used for bulk export as well.
A curious aspect of this though, is that because it’s a cloud-only database system, you can’t just use COPY to get data to/from a local file system. COPY works to/from what’s called a stage.
2019-10-04
I answered an interesting question the other day on Stack Overflow. What the person who posted was after was a way to create all missing stored procedures on one database that were present on another database on another server.
Here’s an example of how to do that:
USE tempdb;
GO
-- Copying from [GREGT580] to local server
SET NOCOUNT ON;
DECLARE @MissingProcedures TABLE
(
MissingProcedureID int IDENTITY(1,1) PRIMARY KEY,
SchemaName sysname,
ProcedureName sysname,
ProcedureDefinition nvarchar(max)
);
INSERT @MissingProcedures
(
SchemaName, ProcedureName, ProcedureDefinition
)
SELECT s.[name], p.[name], sm.definition
FROM [GREGT580].AdventureWorks.sys.procedures AS p
INNER JOIN [GREGT580].AdventureWorks.sys.schemas AS s
ON p.schema_id = s.schema_id
INNER JOIN [GREGT580].AdventureWorks.sys.sql_modules AS sm
ON sm.object_id = p.object_id
WHERE NOT EXISTS (SELECT 1
FROM sys.procedures AS pl
INNER JOIN sys.schemas AS sl
ON sl.schema_id = pl.schema_id
AND sl.[name] = s.[name] COLLATE DATABASE_DEFAULT
AND pl.[name] = p.[name] COLLATE DATABASE_DEFAULT);
DECLARE @SchemaName sysname;
DECLARE @ProcedureName sysname;
DECLARE @ProcedureDefinition nvarchar(max);
DECLARE @Counter int = 1;
WHILE @Counter < (SELECT MAX(MissingProcedureID) FROM @MissingProcedures AS mp)
BEGIN
SELECT @SchemaName = mp.SchemaName,
@ProcedureName = mp.ProcedureName,
@ProcedureDefinition = mp.ProcedureDefinition
FROM @MissingProcedures AS mp
WHERE mp.MissingProcedureID = @Counter;
EXEC SDU_Tools.ExecuteOrPrint @ProcedureDefinition; -- Change to EXEC (@ProcedureDefinition) to create
SET @Counter += 1;
END;
I start by querying sys.procedures, sys.schemas, and sys.sql_modules on the remote database to find the ones that aren’t present on the local system. I needed sys.sql_modules to get the definition of the procedure.
2019-10-03
If you used computers much in the 1980’s or 1990’s you’ll remember jokes that circulated all the time, but encoded in ROT13. It was a type of Caesar Cypher or Shift Cypher where the letters in the original words were shifted by a fixed number of positions in the alphabet to create the cyphertext.
ROT13 was a specific type of shift cypher where, for our alphabet of 26 characters, a 13 character shift is applied. That means that the same function can be used to encode text and then to decode the same text.
2019-10-02