Definitely go with option 1 but with a few very simple modifications:
Parts (id, part_type_id, name)
PartTypes (id, name)
Wires (id, part_id, part_type_id, lenght, diameter, material)
Contacts (id, part_id, part_type_id, description, picture)
You can then use CHECK constraints and...
I'm not a user InnoDB database but i can provide small information about Composite key
PK(client_id,address_id,date_address_from)
Combination above three columns is known as Composite Key in SQL
Primary Key holds the value -- Unique + Not Null
so above there columns holds -- Unique + Not N...
@ConcernedOfTunbridgeWells Nod but I think the param is "Application Name" or "ApplicationName". Worth insisting everyone that connects to your systems uses it, helps.
@Zane You use SQL Server logging for the package? If so, go into the package and just log everything for an execution or two of the package. There will be some slowdown from the amount of junk logged but hopefully there will be some valuable data in the events.
There's a story of a weatherman (maybe apocryphal) who was approached by a drunk farmer in a bar one day. The farmer accused him of sending a hailstorm to destroy his crops. The weatherman calmly replied something along the lines of 'yes I did, and if you don't get out of my face I'll send a tornado.' The drunk farmer left.
@JNK ProTip: The presence of a religious war is a pretty good indicator that neither side has a compelling argument. Think mac vs. PC fanbois of PC vs. console fanbois or DBMS vs. NoSQL fanbois.
@ConcernedOfTunbridgeWells I will stay out of the other arguments but I will say that my companies recent venture into using noSQL in a way it is not designed for at all has caused me must spite.
@billinkc In the late '90s my alma mater (cosc.canterbury.ac.uk) got some entry level sun workstations for their undergraduate clusters.
For the next lot someone figured out that for quite a lot less than the price of the suns they could get really high spec PCs and run Intel Solaris on them. This was in the days that Intel Solaris didn't support UDMA, so by 'high spec' I mean UW 7200RPM SCSI disks, Matrox video and really quite high spec monitors.
Had to be an upper classman for your student id to work in the room. Of course, the door was never closed (far too many funky, unwashed bodies). It was always funny to watch freshman/non CS folks sit down at a terminal log in and then just stare trying to "find windows"
@njk I buy Apple products for build quality and durability. My 2008 Mac Pro is still running like a champ, while I threw away multiple Dells within < 2 years of purchase.
@njk my 2006 MacBook Pro 17" is still in service - my sister uses it to make videos. If it had shipped with SSD at the time I'd probably still be using it myself, but I passed it down to her in 2010 when I took the new job and work bought me a new machine.
@AaronBertrand I always built my own systems. The only pre-built machine I bought was a 2010 HP laptop for $399, which I upgraded with an SSD and has worked without a hitch.
@njk my time is worth more than that to me. Plus I think Windows sucks and is only getting worse, so the odds I would build my own PC at this point in my life are about 0.
I have the following scenario.
UPDATE ATable
SET C1 = 'XValue'
WHERE C2 = 'YValue'
UPDATE ATable
SET C3 = 'MValue'
WHERE C4 = 'NValue'
For performance tuning, can I do anything to make them run under one UPDATE? Would it be better?
Thank you
@AaronBertrand i know i've told @ConcernedOfTunbridgeWells about this a few times, but i melted 3 mobos on Dells while doing ETL development in the great DTS->SSIS migration. Dell is an unfair comparison, but your point is also well-taken. @njk With the advent of virtualization, who gives a rip what your preference is.
This M6500 has been a solid work purchase. Heavy as a tank but going steady 3+ years later. My MBP from 2009 still runs like a champ though with maxed memory and an SSD
@swasheck @njk right. I state my reasons for choosing the Mac platform. I don't say people who buy Dells or build their own machines are stupid or are doing so for the wrong reasons or even try to suggest that I know their motivations. I know my decisions are sound, and I try to deflect some of the FUD that comes out now and then.
@swasheck I think I learned one new trace flag yesterday, but it's not even available yet, it's only being used to test a feature in internal builds. But that is the funny go-to. We're getting lots of NDA information on futures that I obviously can't share. :-)
@swasheck but yes, we are getting bombarded with information and it's been a lot of fun. Save for one idiot who wouldn't shut up on twitter about a first-time presenter.
@swasheck Pragmatic Works? Nah, never talked to them about a gig. I think they'd be cool if my kids were out of the house but right now, I think I'd lose the work-life balance with them.
Would like to nest a TVP
QueryWa is a User-defined table
This throws a compiler error
DECLARE @Wa AS QueryWa;
(
insert into @Wa ([sID],[wordPos])
select top 10 [sID],[wordPos] from [FTSindex] where [sID] = '2'
select * from @Wa
delete @Wa
)
Msg 156, Level 15, State 1, Line ...
@pst COALESCE is a SQL Standard and is generally recommended in these cases. also there are significant differences in how they respond to data types. perhaps you should caveat your comment with such additional information. — swasheck1 min ago
i really wanted to watch the Ozar thing today but webex is being a butthead
just like PIVOT generates the same plan as SUM(CASE WHEN a then 1 else 0 END) as SpreadingA
@bluefeet i've been reading ... which is dangerous. just got to PIVOT and UNPIVOT ... you're smarter and faster than i so you wont get any challenges from me but at least i'll be able to conceptualize your answers a bit better :)
@bluefeet: You can add links to these blogposts (by @Aaron): http://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/ and this DBA.SE question (by gbn, Aaron and Mark Storey-Smith): http://dba.stackexchange.com/questions/4274/performance-difference-for-coalesce-versus-isnull
@bluefeet Then take the exam. :) If I can pass it cold, I'll bet most SQL folks in here can do it. The biggest challenges for me was the Windowing functions and the XML syntax.
@swasheck Right. I'm more an admin/internals guy. I basically knew just enough to get the exam done. Though I think we'll need to be doing more tuning work in the future, what with cloud stuffs.
I am trying to sort the results based on a column which is of type money. The sorting doesn't seem to be working properly.
Query :
SELECT ItemID, ItemName, Total,
'$' + replace(convert(varchar,cast(Volume as money),1), '.00','') as Volume,
'$' + replace(convert(varchar,cast(Average...
@njk also, i made that first comment from a purely tongue-in-cheek standpoint. internals and admin is often confused with permissions monkey which is usually perceived as clickety-doo-da.
what i like is when my devs come to me and immediately say, "what's wrong with the server?? why is production so much slower than dev??" it's awesome because it becomes a shooting gallery for me.
I am beginning the process of putting together a design document for my companies database. I am trying to use as many formal standards as I can. Does anyone know "Is there a standards document (like IEEE 1016 1998 IEEE Recommended Practice for Software Design Descriptions) for databases"? WIK...
@njk I am a fan of outlining the basics of any database object within in them. VCS can capture how it's changed over time but I like to see "what does the author think this thing should do" so that when I get to fix it, I at least understand the intent without having to track down project documentation and read detailed specs, etc
I have two very simmilar queries:
exec sp_executesql N'SELECT TOP (1) [t0].[Production]
FROM [dbo].[T_Production] AS [t0]
WHERE [t0].[InputID] = @p0
ORDER BY [t0].[Timestamp] DESC',N'@p0 int',@p0=1161
exec sp_executesql N'SELECT TOP (1) [t0].[Production]
FROM [dbo].[T_Production] AS [t0]
WHERE ...
@swasheck the OP wants more details since you caused a commotion
I'm just aking to learn more, but using the solution @ek0nomik provided worked correctly and quickly. I am sure as there are different ways in the end to make this work, but for my particaular application, is it really so unacceptable so many comments have been made about it? Am I missing the big picture? — jenhil3424 mins ago
I'm taking over an database with a table that is growing out of control. It has transaction records for 2011, 2012, 2013 and into the future.
The table is crucial to the company's operation. But it is growing out of control with 730k records and growing with transaction being added bi-weekly.
...
@billinkc I'm not a lawyer of course... but I've known a lot of people who've broken noncompetes, but not known anyone that was taken to court for doing so...
@MichaelFredrickson Same and agreed but ya never know how litigious a company is until after you're already a member. So, I've always made it a point to read through non-competes and try to get things I don't agree with changed. Saw too many "anything you create while an employee is ours". I'm fine with not poaching their people or clients but a wholesale you can't be a db person is a no go for me
Definitely go with option 1 but with a few very simple modifications:
Parts (id, part_type_id, name)
PartTypes (id, name)
Wires (id, part_id, part_type_id, lenght, diameter, material)
Contacts (id, part_id, part_type_id, description, picture)
You can then use CHECK constraints and...
I'm not a user InnoDB database but i can provide small information about Composite key
PK(client_id,address_id,date_address_from)
Combination above three columns is known as Composite Key in SQL
Primary Key holds the value -- Unique + Not Null
so above there columns holds -- Unique + Not N...
I have the following scenario.
UPDATE ATable
SET C1 = 'XValue'
WHERE C2 = 'YValue'
UPDATE ATable
SET C3 = 'MValue'
WHERE C4 = 'NValue'
For performance tuning, can I do anything to make them run under one UPDATE? Would it be better?
Thank you
Would like to nest a TVP
QueryWa is a User-defined table
This throws a compiler error
DECLARE @Wa AS QueryWa;
(
insert into @Wa ([sID],[wordPos])
select top 10 [sID],[wordPos] from [FTSindex] where [sID] = '2'
select * from @Wa
delete @Wa
)
Msg 156, Level 15, State 1, Line ...
I am trying to sort the results based on a column which is of type money. The sorting doesn't seem to be working properly.
Query :
SELECT ItemID, ItemName, Total,
'$' + replace(convert(varchar,cast(Volume as money),1), '.00','') as Volume,
'$' + replace(convert(varchar,cast(Average...
I am beginning the process of putting together a design document for my companies database. I am trying to use as many formal standards as I can. Does anyone know "Is there a standards document (like IEEE 1016 1998 IEEE Recommended Practice for Software Design Descriptions) for databases"? WIK...
I have two very simmilar queries:
exec sp_executesql N'SELECT TOP (1) [t0].[Production]
FROM [dbo].[T_Production] AS [t0]
WHERE [t0].[InputID] = @p0
ORDER BY [t0].[Timestamp] DESC',N'@p0 int',@p0=1161
exec sp_executesql N'SELECT TOP (1) [t0].[Production]
FROM [dbo].[T_Production] AS [t0]
WHERE ...
