Looking For Table Design Pattern For Different Value Types
Jan 3, 2007
Hi,
I need to store a list of parameters in a database. Each parameter has a name, description, comment and a value. Easy so far.
However the values are of different types. Each individual parameter has a value which may be of type int, decimal, string, boolean, custom type etc.
Which table design pattern is most appropriate?
We have a heated in-house discussion and I need supporting arguments.
Options explored so far:
1) (De-)serializing the value to a string-type.
2) Adding a column for each type, using only one column at a time.
3) Adding extra value-tables, one table for each type.
The disadvantages for each option are obvious and the basis for our discussion.
Your help in this matter will be appreciated.
Regards, Tonn
View 5 Replies
ADVERTISEMENT
Jul 17, 2015
Is it possible to categorize a table by "type"; for example "Master file" or "Transaction file"?
If yes, then:
Where is this specification made on the table, and how can I interrogate the table to determine the "type" ....
View 8 Replies
View Related
Sep 15, 2005
Say I have an object that can be created by some users and not others. And, I have a method something like - function createObject(userId as Integer, objectName as String). The method calls a stored procedure using the two input arguments as parameters.Now, what I am wondering is this; I am obviously going to check in my SP to ensure that the user has the rights to create the new object before inserting the new record, and there will be an output parameter to send the new record ID back to the method, but do Ia) have a separate output parameter to report on the success of the creation (e.g. @Err Int OUTPUT)b) use the existing output (used to send the new record ID back) and send an error code (e.g. if newRecordId = -9999)c) somehow try to cause a failure of the SP that can be caught in a try/catch blockThanks in advance,Martin
View 1 Replies
View Related
Jun 19, 2015
I'm working on an application that allows users to set up scheduled time based reports. Each scheduled report creates a SQL Agent job associated with a schedule.The default time to fire these off is 8:00 AM. There are several hundred. DWH and it has no trouble running hundreds of reports all fired off at the same time.
There are several ETL processes and occasionally they don't complete before our verbal SLA of 8:00 AM.
My problem is on days where the ETL runs past 8:00 AM I wan't to hold these scheduled jobs from firing off.
View 4 Replies
View Related
May 8, 2015
I tried creating one simple bar chart report in SSRS, with background fill with pattern "UpwardDiagonal", the stripes on the column when viewed insider report viewer control is quite prominently visible, whereas when exporting the same report to PDF the lines are very thin and dense and hence makes it very difficult to differentiate between one column with pattern inside and one without. I have even tried various other pattern like "LightUpwardDiagonal" and "WideUpwardDiagonal" but all have same result when exported to PDF.why?
View 2 Replies
View Related
Aug 9, 2007
I need a table value function for extracting strings matching a particular pattern from a long string.
e.g. I have a table called cs_Posts, it has a column called FormattedBody, this value can be something like:
Code Snippet<p>this is the 1st photo <a href="http://jvcwebdev:81/cs/blogs/hllee/DSC00884.JPGhttp://jvcwebdev:81/cs/blogs/hllee/DSC00884.JPG">http://jvcwebdev:81/cs/blogs/hllee/DSC00884.JPG</A< A>>" border="0" alt="" /></a></p>
<p>this is the 2nd photo<a href="http://jvcwebdev:81/cs/blogs/hllee/scene%20photos/DSC00859.JPGhttp://jvcwebdev:81/cs/blogs/hllee/scene%20photos/DSC00859.JPG">http://jvcwebdev:81/cs/blogs/hllee/scene%20photos/DSC00859.JPG</A< A>>" border="0" alt="" /></a></p>
When this long string is input, the table value function should 2 rows:
http://jvcwebdev:81/cs/blogs/hllee/DSC00884.JPG
http://jvcwebdev:81/cs/blogs/hllee/scene%20photos/DSC00859.JPG
Any idea?
View 4 Replies
View Related
Aug 18, 2015
I would like to create a table called product. My objective is to get list of packages available for each product in data grid view column while selecting each product. Each product may have different packages type (eg:- Nos, CTN, OTR etc). Some product may have two packages and some for 3 packages etc. Quantity in each packages also may be differ ( for eg:- for some CTN may contain 12 nos or in other case 8 nos etc). Prices for each packages also will be different that also need to show. How to design the table..
Product name :
Nestle milk |
Rainbow milk
packages :
CTN,OTR, NOs |
CTN, NOs
Price:
50,20,5 |
40,6
(Remarks for your reference):CTN=10nos, OTR=4 nos
| CTN=8 Nos
View 3 Replies
View Related
May 26, 2005
SQL Server 2000, ASP.Net 1.1
I've been writing this stuff for a while, and can't seem to come to the
conclusion of how I should be retrieving data and assigning this data
to variables.
Since i'm using SQL Server, I'm convinced that I should be using the
datareaders GetSqlDouble (or whatever) function, but this would mean i
need my local variables to be one of the SQL types. The problem
with that is, that there will have to be lots of conversions done by me
to be able to use a SQL type in my application.
For instance, I have a class where i'm retrieving dates. In order
to retrieve them correctly (Null values included), I need to retrieve
them with GetSqlDateTime(), then when it comes time to display the date
in a table, i must first check for nulls, then convert to a
string. This seems to be very cumbersome. Would I be better
off just using GetDateTime(), and the .ToString method, and ignoring
Sql Types all together?
so, basically, how are you guys using your sql server data? with
the supplied sql types, and doing all of the post-processing work
manually? I feel like i'm having trouble conveying my
issue...hopefully someone knows what i mean....i'd just like some
direction to save trouble in the long run, since i feel like there's
got to be a better way...
Confused!
Thanks,
JJ
View 1 Replies
View Related
Jun 4, 2006
I need to subtract the values of the totals of two document types in a table. So far I have the following with the results as shown.
SELECT TOP 100 PERCENT { fn MONTHNAME(DOCDATE) } AS [MONTH], DOCID, SUM(DOCAMNT) AS TOTAL
FROM dbo.tblSales
WHERE (DOCID = 'RTD') OR
(DOCID = 'INV')
GROUP BY DOCID, { fn MONTHNAME(DOCDATE) }
ORDER BY DOCID DESC
This results in the following:
Month DOCID TOTAL
MAY RTD 165752.87
MAY INV 18149416.37
I need one value showing the difference of the totals.
Thanks.
View 6 Replies
View Related
Apr 13, 2006
Hi!
I have a question about creating a user defined type: I'd like to create a table of employee objects which include objects of the type employee_t. I used this structure before in Oracle 9i and would like to know how it can be done with MS SQL Server 2000 or 2005, preferably with Enteprise Manager/Management Studio. Below is an example.
CREATE TYPE employee_t AS OBJECT (
name VARCHAR(10)
jobDesc VARCHAR(15)
...
)
CREATE TABLE Employee of employee_t
Regards,
Timo
View 3 Replies
View Related
Oct 1, 2013
I consider CURSOR and TABLE as data types but some one has argued that these are database objects.
View 7 Replies
View Related
Oct 30, 2003
This is part of the codes of a stored procedure:
-- Create tables variables
DECLARE @_tab_PRNumList TABLE(
PRNum udt_PRNum
)
However, it cannot be compiled coz of this error:
Error 2715: Column or parameter #1: Cannot find data type udt_PRNum.
This user-defined data type is created in a user database only.
I tried to created the user-defined type into BOTH tempdb and master but same error.
How can i resolove it?
Or it is impossible to use user-defined datatype for table variable?
In the past,
i use this method:
CREATE TABLE #PRNumList (
PRNum udt_PRNum
)
and cerate udt_PRNum in tempdb only could solve it.
But now i want to use table variable.
It seems NOT work for my situation.
Any idea?
View 1 Replies
View Related
Jan 24, 2005
Hi,
Can anybody give me some information on Table data types of SQL2K.
How and where can it be used. Does it make the queries faster, in case there are many users using ?
Please could anybody give me these details, to make me decide whether I can use it or not ?
Thanx and Regards,
Sushma
View 1 Replies
View Related
Jul 14, 2014
I know the type of Dimension but any real time example for Dimension table
1) Conformed Dimension
2) Junk Dimension
3) Degenerated Dimension
4) Role Playing Dimension
View 3 Replies
View Related
Jun 17, 2008
I can find out the data types used in my table by navigating the SSMS and right clicking the table and selecting modify.
How do I view that same output using T-SQL?
Thank you
Goldmember
View 1 Replies
View Related
Sep 25, 2013
Our database stores vehicle data in one table, but 3 different types of data are stored in the one table. The table contains all the columns for all 3 types so when you query the table you get at least 3 rows back with null values for all the columns that don't apply to that record. The data is imported to the table when it's updates so there's a possibility that they're updated at different times so they have a different BATCH like:
BATCH TYPE ID RATING INSURANCE SAFETY
300 SAFE 123 NULL NULL A
300 INS 123 NULL YES NULL
250 RATE 123 A NULL NULL
What I'd like returned is:
ID, RATING, INSURANCE, SAFETY
123 A YES A
I'm trying to do a case statement to pull the data down, but I keep ending up with multiple rows because of all the nulls. I tried doing a SUM of the case statement with an ISNULL(SAFETY,0) but I can't SUM char values. I can probably do this with 3 temp tables to load the data that I want for each TYPE into them and then select and join them together, but is there a better way to do this?
View 2 Replies
View Related
Jul 20, 2005
Hi,Can anybody tell me if it is possible to return a table data type (asopposed to a temporary table) from a stored procedure.I am currently studying for 70-229 using the Thomas Moore book but itdoesn't really make it clear, as the text suggests you can't returnthem but some of his sample questions suggest you can.I can't make it work in Query Analyser so I am assuming that you can'treturn them. Is that correct?Thanks in advance for any help,Simon.
View 1 Replies
View Related
Jul 3, 2007
This question is around how we can get the data types and lengths populated into the flat file source columns.
In Connection Manager, you have your flat file defined. You can choose "Suggest Types...", and the minimum lengths and correct data types will be returned from within the data in the flat file.
Is there some way to automate this data type definition, but coming from the other direction (coming from the destination table that we are loading)?
For example, you have mapped the columns that will be loaded. Can you then reverse engineer the data types and lengths for the columns in the flat file from the destination table?
TIA
View 5 Replies
View Related
Nov 4, 2015
Change structure of a User Defined Table Types
field
([idConteudo] [int]
NULL) to
([idConteudo] [BigInt]
NULL)
Or really should drop and create again?
CREATE TYPE [dbo].[tbProjeto] AS TABLE(
[dsConteudo] [varchar](64) NOT NULL,
[idConteudo] [int] NULL,
[dtConteudo] [datetime] NULL DEFAULT (getdate())
)
GO
View 4 Replies
View Related
May 6, 2015
We need to Insert/Update a Fact Table from staging Table. currently we are using a SP which update Fact Table for Each region. this process is schedule, every 5 min job is run and Update fact table.but time of Insert and Update too long from staging to Fact, currently we are using merge statement for Insert and update.in my sp we are looping number how many region we need to update and at a time single Region we are updating using while loop in current SP.
View 7 Replies
View Related
Oct 7, 2015
I have a requirement of table partitioning. we have 10 years of data on a table which is 30 billion up rows on 2005 server we are upgrading it to 2014. we have to keep 7 years of data. there is no keys on table or date column. since its a huge amount of data and many users its slow down the process speed. we are thinking to do partition on 7 years for Quarterly based. but as i said there is no date column on table we have to use reference table to get date. is there a way i can do the partitioning with out adding date column on table? also does partition will make query faster?
I have think three ways to do it.
1. leave as it is.
2. 7 years partition on one server
3. 3 years partition on server1 and 4 years partition on server2 (for 4 years is snapshot better?)
View 3 Replies
View Related
Jan 24, 2008
Does anyone know if it is possible to have text in a single table cell where the first field is formatted in italics and the second is in normal?
eg: = Fields!firstname.Value(as italic) & " " & Fields!lastname.Value(as normal)?
shidot
View 5 Replies
View Related
Jul 11, 2007
I am trying to produce a matrix (crosstab) report in SQL Server 2005 Reporting Services Report Designer, where the column headers contain a binary data type storing a png image.
By just simply using the report wizard and assigning the binary (image) data value to the column headers, and then previewing the report, I get following error:
An error occurred during local report processing.An error has occurred during report processing.The Group expression used in grouping 'matrix1_COMPETITOR_EMBLEM' returned a data type that is not valid.
Is there any way to include binary data types, or images per se from the database into a matrix or even table item in a report ?
View 3 Replies
View Related
Apr 2, 2008
I have a table with a datetime field 'TheDate'. Currently dates are stored as 'mm-dd-yyyy 00:00:00'. Is there a way to get just the month, day and year parts, '01/01/2008' into the field without changing the field data type to varchar? I'm asking because when I do this:
declare @MyDate as datetime
set @MyDate = '04/02/2008 18:00:00'
select substring(convert(varchar,@MyDate,101),1,10)
I get '04/02/2008', but when I do this:
update TheTable
set TheDate = substring(convert(varchar,TheDate,101),1,10)
I'm still getting a date in the format 'mm-dd-yyyy 00:00:00' stored in the table. I'd like to be able to lose the time portion, but I'd like to be able to keep the datetime datatype for date math purposes. Can it be done?
View 4 Replies
View Related
Feb 17, 2006
Hi,
I have a indexing problem. I have a sequence that needs to has a index number. I want to use a table data type and have a working sample BUT I cannot reseed the table when needed. How do I do this.
This works only for the first ExitCoilID then I need to RESEED.
Here is my code:
DECLARE
@EntryCoilCnt AS INT,
@ExitCoilID AS INT,
@SubtractedFromEntyCoilCnt AS INT
DECLARE
@ExitCoilID_NotProcessed TABLE
(ExitCoilID int)
INSERT INTO @ExitCoilID_NotProcessed
SELECT DISTINCT ExitCoilID
FROM
dbo.TrendEXIT
where
ExitCoilID is not null and
ExitCnt is null
order by
ExitCoilID
DECLARE
@ExitCoilID_Cnt_Index TABLE
(ExitCoilID int, ExitCnt int IDENTITY (1,1))
IF @@ROWCOUNT > 0
BEGIN
DECLARE ExitCoilID_cursor CURSOR FOR
SELECT ExitCoilID FROM @ExitCoilID_NotProcessed
ORDER BY ExitCoilID
OPEN ExitCoilID_cursor
FETCH NEXT FROM ExitCoilID_cursor
INTO @ExitCoilID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @ExitCoilID_Cnt_Index
SELECT ExitCoilID
FROM dbo.TrendEXIT
WHERE
ExitCoilID = @ExitCoilID
ORDER BY
EntryCoilID, Cnt
select * from @ExitCoilID_Cnt_Index
--truncate @ExitCoilID_Cnt_Index
--DBCC CHECKIDENT ('@ExitCoilID_Cnt_Index', RESEED, 1)
FETCH NEXT FROM ExitCoilID_cursor
INTO @ExitCoilID
END
CLOSE ExitCoilID_cursor
DEALLOCATE ExitCoilID_cursor
select * from @ExitCoilID_Cnt_Index
END --IF @@ROWCOUNT <> 0
View 8 Replies
View Related
Aug 25, 2014
T-SQL script for the following request ?
Generate script for view dependencies of user defined table types ?
View 2 Replies
View Related
Apr 21, 2007
I am trying to create a clustered index on a View of a table that has an xml datatype. This indexing ran for two days and still did not complete. I tried to leave it running while continuing to use the database, but the SELECT statements where executing too slowly and the DML statements where Timing out. I there a way to control the server/cpu resources used by an indexing process. How can I determine the completion percentage or the indexing process. How can I make indexing the view with the xml data type take less time?
The table definition is displayed below.
CREATE TABLE [dbo].[AuditLogDetails](
[ID] [int] IDENTITY(1,1) NOT NULL,
[RecordID] [int] NOT NULL,
[TableName] [varchar](64) NOT NULL,
[Modifications] [xml] NOT NULL,
CONSTRAINT [PK_AuditLogDetails] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The view definition is displayed below.
ALTER VIEW [dbo].[vwAuditLogDetails] WITH SCHEMABINDING
AS
SELECT P.ID,D.RecordID, dbo.f_GetModification(D.Modifications,P.ID) AS Modifications
FROM dbo.AuditLogParent P
INNER JOIN dbo.AuditLogDetails AS D ON dbo.f_GetIfModificationExist(D.Modifications,P.ID)=1
The definition for UDF f_GetModification
ALTER function [dbo].[f_GetModification]( @Modifications xml,@PID uniqueidentifier )
returns xml
with schemabinding
as
begin
declare @pidstr varchar(100)
SET @pidstr = LOWER(CONVERT(varchar(100), @PID))
return @Modifications.query('/Modifications/modification[@ID eq sql:variable("@pidstr")]')
end
The definition for UDF f_GetIfModificationExist
ALTER function [dbo].[f_GetIfModificationExist]( @Modifications xml,@PID uniqueidentifier )
returns Bit
with schemabinding
as
begin
declare @pidstr varchar(100)
SET @pidstr = LOWER(CONVERT(varchar(100), @PID))
return @Modifications.exist('/Modifications/modification[@ID eq sql:variable("@pidstr")]')
end
The Statement to create the index is below.
CREATE UNIQUE CLUSTERED INDEX [IX_ID_RecordID] ON [dbo].[vwAuditLogDetails]
(
[ID] ASC,
[RecordID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
View 1 Replies
View Related
Jul 30, 2015
I have to tables like given below Landing table "A" (Data load will happen over here, No primary keys mentioned over here) table "B" .Now I want to move the data from A to B.I have made use of below query insert into B select * from A...Landing table "A" has huge no of records, MS SQL server is taking huge amount of time.any alternative way to make this insertion process faster?
View 6 Replies
View Related
Mar 20, 2008
Hi everyone. I'm developing a web search engine using asp and SQL Server 2000. I need to return records that matches with a string entered by users. In example, suppose my database to have this structure and it's filled like this:
ID NAME KEYWORDS
--- ------- -----------
AA025 NAME1 attached, atic, common, business, hotels
AA026 NAME2 headache, medicin, aspirins, heat, health
AA027 NAME3 at, services, music, electronics
suppose that user enters 'at'. By now, i'm using this pattern '%<input_text>%'. So in this example, pattern would be '%at%'. As i remarked in the fields above, the three fields matches, and that's not what i'm looking for. I want that the result of that query be just the last field, with 'AA027' ID.
Thanks in advance guys.
Cheers.
View 14 Replies
View Related
Jan 30, 2008
hi can any one help me to get the pattern of 70-441 exam.
i need information total number of question in the exam..
and will there be any simulation as 70-431 exam.
View 3 Replies
View Related
Mar 30, 2015
I've been trying to find and filter out data that can cause FOR XML to fail with the error:FOR XML could not serialize the data for node ?? because it contains a character (0x0006) which is not allowed in XML.I thought it would be simple, just identify the rows that don't match the XML spec URL...
However, the following doesn't work.
select fieldname
from tablename
where fieldname
like N'%[^'+nchar(0x9)+nchar(0xA)+nchar(0xD)+nchar(0x20)+N'-'+nchar(0xD7FF)+N']%'
Not only does this fail to find the offending rows, when I checked by inverted it by taking out the "^" it still returns no rows.When I reduced 0xD7FF down to 0xFF it returns data, but I cannot add the other range back in without all rows not matching.I've experimented with values and found 0x02E9 was the highest I could go without all values vanishing. Even then, numbers lower than that caused a large variation in the number of rows returned.My Field is NVarchar(100) in SQL_Latin1_General_CP1_CI_AS.
View 1 Replies
View Related
Aug 22, 2007
We have a collection of charts, some of which we use the gray scale for the palette (users only have B&W printers). We need to use the same patterns in other charts for consistency. The only option I see in Edit Chart Value-->Appearance-->Series Style-->Fill is Gradient. Is there any way to pick a fill pattern?
The pattern is the diagonal lines slanting down from left to right. It is the third column in the gray scale, so I have tried to add a dummy column as the first column, so that my column would have this pattern. However, the chart allows for space width wise for the first column, so there is a blank space on the chart. I have the Data Output checked to No.
Any other ideas, I would appreciate it!
View 2 Replies
View Related
