A simple restriction of the domain of the SQL data type by a constraint is the most natural way. You have two options
- adding a constraint to the column of your table(s) in question
- creating a domain for each data type you want to be tailored to fit your given limitations of the programming language
An example of the first options looks like
ALTER TABLE table_name
ADD CONSTRAINT constraint_name
CHECK (column_name > -128 AND column_name < 127);
(change the table, constraint and column name as needed, and adjust the range of the checked bounds)
The second option is a two step process. First add a new domain to your DB (in this example the domain type is named c_byte8) with the command
CREATE DOMAIN c_byte8 NUMERIC CONSTRAINT c_byte8_constraint
CHECK (VALUE > -128 AND VALUE < 127);
Second create your table with the appropriate types, i.g. c_byte8 instead of NUMERIC as the attribute's type. E.g.
CREATE TABLE test_table_1 (id NUMERIC, byte_value c_byte8);
Please keep in mind that the first solution will require you to add constraints to each attribute you have defined, or you will define in the future. It makes your DB-create script less readable, because of the repetition of the same constraint condition every time you use the restricted domain. A second downside of this approach is, that you can easily miss some columns of your schema when you add those constraints to an existing schema.
An upside of the first solution is that you can extend an existing schema without much hassle.
As for the second approach, the plus is a more readable DB creation script, as is the same with the table definitions of a running production DB, but changing an existing production DB is more complicated.
First add a row_number()
value to the mix, because I'm guessing you can't rely on ID
to have no gaps.
WITH NumberedRows AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY Number, Name ORDER BY SubNumber) AS RowNum
FROM [Table]
)
--now compare RowNum to SubNumber. When the difference changes, there's been a gap in the sequence of SubNumber values.
, Diffs AS (
SELECT *, SubNumber - RowNum AS TheDiff
FROM NumberedRows
)
, Ranges AS (
SELECT Number, Name,
MIN(SubNumber) AS StartRange,
MAX(SubNumber) AS EndRange
FROM Diffs
GROUP BY Number, Name, TheDiff
)
, RangeStrings AS (
SELECT Number, Name,
CASE WHEN StartRange = EndRange
THEN CAST(StartRange AS VARCHAR(10))
ELSE CAST(StartRange AS VARCHAR(10)) + '-' + CAST(EndRange AS VARCHAR(10))
END AS RangeString
FROM Ranges
)
--And then just do your concatenation as you have already solved. :)
SELECT ...
FROM RangeStrings;
Best Answer
Maybe erical already has two columns that keep the "home score and away score as two separate int fields", as Mark and Phil already commented. Then, the remaining part of the question would be whether it is possible to create a user-defined type for a separate string column that stores the match result for faster printing.
So, the answer in this situation would be that yes, it is possible. If unsure about the syntax of the CREATE TYPE statement, it is documented at the following URL:
https://msdn.microsoft.com/en-us/library/ms175007.aspx
The following would be an example of how to do it:
Or, maybe erical was thinking about a composite user defined type. Instead of creating an alias based on an already existing type, what if it were possible to mix two already existing types into a brand new compound?
Microsoft SQL Server does not support that. Instead, Oracle comes with a RECORD type. An example would be the following: