He is talking about the older types, datetime and smalldatetime. They consist of both the date and the time. So it just wouldn't make sense if YYYYMMDD was unambiguous for those types but YYYYMMDD hh:mm:ss[.nnn] was not.
Anyway, it is easy to verify using Aaron's method (SET LANGUAGE), which I've just done :)
The reason he didn't include the YYYYMMDD format with the time part was probably because he was talking about dates specifically. YYYYMMDD is unambiguous regardless of whether you are specifying a time along with it or not. In contrast, YYYY-MM-DD is unambiguous only as part of the YYYY-MM-DDThh:mm:ss[.nnn] format
You shouldn't take my word for it, though, even if I were Aaron. Just try for yourself:
SET LANGUAGE FRENCH;
GO
SELECT CONVERT(DATETIME, '2009-10-13');
GO
SELECT CONVERT(DATETIME, '20091013');
GO
SELECT CONVERT(DATETIME, '20091013 11:12:14');
GO
SET LANGUAGE ENGLISH;
Well, the edit seemed unnecessary. But it may have made things better by replacing two conversions with one, and there's no sign that the rest of the code particularly relies on the previous format, so... I'd probably leave it to avoid making changes to the thread (and thus bumping the question to the top of the queue) again. But there may be other opinions.
Great, I'm back to zero when it comes to the role permissions in Postgres... Do I have to make it a User to give it access to the db it's the owner of?
@RoyiNamir Not sure but perhaps OR @Type = 'D' would suffice, because in an AFTER INSERT trigger, the result of COLUMNS_UPDATED() would have all bits set to 1.
(And thus the first condition, SUBSTRING..., would work for an insert.)
A big task for me. I have a few questions. You guys already helped me so much, but I get one difficulty after another and progressing unbelievably slowly. These questions will better describe my tasks.
I have an aspx page with the following controls: TextBox, CheckBoxList, DropDownList.
They are...
@RoyiNamir To clarify what I said, the IF is supposed to verify that there are things to log in the audit table.
The SUBSTRING predicate checks if the column in the current iteration of the loop is affected by the trigger operation. If it is not affected, the server goes on to presume that the operation may be either an INSERT or a DELETE and checks whether it is so (using the IN predicate). But, as I said before, the inserts should be caught by SUBSTRING as well, so the other condition could be just @Type = 'D'.
The task is to audit the current change. It can be any of the three operations. If it's an update, the trigger checks each column whether it is updated, so that it doesn't log a column that isn't updated.
If it's a delete, the SUBSTRING condition will return false, but the change should still be logged. That's what the OR @Type... is for.
SELECT x.*
FROM ( SELECT DISTINCT cellId FROM table1 ) c
CROSS APPLY (
SELECT DISTINCT TOP 10 cellId,COL2,COL3,COL4
FROM table2 t
WHERE c.cellId = t.cellId ) x
That's the source. It lines up with the graph, so that might be the metric used for the banks.
I don't think all of that debt is just the bailout though, there's probably a big chunk of overnight rate lending from the reserve and assorted frigging with short-term securities in that.
The Federal Reserve claims they only lent $1.7 Trillion to the big banks. Why the huge difference in totals? Because the Fed only counts the most outstanding at any one time.
from that link
indeed overnight loans which have been settled i guess
I am having trouble populating my second table in my SQL Server Management Studio.
My first table ServerList contains all information in the screenshot below. The second table ServerDrives should then contain all the information regarding hard drive specs (free, used, letter, and which server ar...
"Unfortunately, the enemy is “change” and the DBAs have about as much chance of winning as cocker spaniel does in a fight against a pack of velociraptors. It really is just a matter of time."
@mmarie The enemy isn't virtualisation as such - HW support from modern CPUs makes this quite efficient these days. The enemy is poorly thought out storage consolidation and the centralisation of storage taking control of this away from the DBA and putting it into the hands of the sort of people who can relate to SAN administration jobs.
Infrastructure: Yeah, we can put your 1.5TB EDW onto our shared NetApp. No worries.
EDW Team: Now the ETL process won't fit into the batch window.
Infrastructure: Must be your fault. Our new setup is much better than the old one.
EDW Team: The old one used to fit into the batch window on the 8 year old 32 bit server we first built the system on - on SQL Server 2000.
Infrastructure: Our NetApp is much faster than the old hardware.
EDW Team: It's the same code base and same database schema.
EDW Team: It used to run on our old 32 bit server.
@mmarie I saw this problem in six gigs on the trot. On two of those the EDW was migrated from really crusty old 32 bit hardware, and on two others I could show the same ETL process running faster on my PC.
@mmarie It's not so much couldn't understand as having a vested interest in not admitting that the £500,000 SAN they spent a year lobbying for isn't quite as good as they made out. However, at least they've got a VMWare consolidation project on their CVs now so they're good.
"but since the hosts doesn't have enough ram, we're going to scale down that sql box to 64Gb ram, that certainly won't be a problem because our all-ssd 3PAR is soo much faster than the DAS in the physical box, we'll just put vhd's on one big volume and you'll see how much faster this is on the new hardware"
3 weeks later: "things seem to be running a bit slow on the new setup, whatcha think the problem is, should I add more vCPU's?"
being the prick that I am I told them "hard to diagnose over the phone, but you can try"
now they are exactly in that situation, having to explain a 250K€ investment brought down performance
At one point I used to work in the same building as a major storage vendor (you wouldn't have to be Einstein to work out who it was) and got somewhat friendly with a couple of their sales reps. Off the record, they would quite happily say that a lot of their DW customers use direct attach storage for the data warehouse systems as it's much faster than a SAN.
For streaming workloads (i.e. DW style table scans) you can get at least an order of magnitude better cost/performance with direct attach storage. It's quite easy to tune an old-school host-based RAID controller to pull 1GB+ a second of a single 2.5" shelf.
Show me an actual SAN in production where you can get that.
I'm pretty sure that SAN can do better than what it's doing now though
prob is the thing is installed by some regular "hey i do citrix, exchange, AD, hardware and stuffs" sysadmin
probably clicked next next next through the whole setup, created one large volume, threw a bunch of vhds or vmdks on the thing and thought "well it's SSD it's gonna fly"
The conventional wisdom is that for a DW workload you will get about 2-3x better performance if you tune your disks right - as compared to a system configured for a typical consolidation workload.
They do data virtualization rather than database virtualization. They gave me a whole bunch of marketing fluff at a conference a couple weeks ago, but the concept is interesting to me.
@mmarie Fibre Channel and iScsi are just packet switched protocols behind the scenes. If you wrote a driver that tunneled something over I.P. you could mount any old shit over the interwebs.
Subject to how much bandwidth and latency you had to their servers.
@mmarie Saves having to buy a SAN if it's fast enough.
So, an iSCSI target is just an application you run on a computer that emulates a SCSI disk. With suitable drivers you can mount it as a block storage device.
In principle, a SAN controller is just a computer that imitates a block storage device while fronting some disks behind the scenes. It's the same principle.
Native Fibre Channel has a bunch of features for supporting resiliency such multiple routes between hosts and targets. Protocols like FCOE do something similar if you have switches that support the additional FCOE features. FCOE just lets you use ethernet cabling and modified switches, which are quite a bit cheaper than trad Fibre Channel.
@swasheck Reading between the lines I presume it's sort of iSCSI-as-a-service type of architecture with some feature to replicate block level storage onto their servers.
@ConcernedOfTunbridgeWells The place I work now has such a SAN, but it's flash-based and not used enough to be continually saturated before it hits the NAND yet. We can do about 1-1.7 GBips scans over iSCSI with 2 10gbps NICs on each target. All the other SANs I've worked with topped out somewhere below 500 MiBps though.
And of course you could still get much faster performance for less hardware cost with local PCIe flash.
@JamesLupolt One of my quiet hopes is that flash-based SANs will alleviate the worst of the sins of incompetent storage admins. With a bit of luck it should be possible to implement a reasonably quick EDW platform on shared flash storage.
@JamesLupolt Short stroking isn't a big deal for DW workloads as you're aiming to optimise for streaming I/O.
Basically you're after: o Large stripe size (>=256k) o Aggressive read-ahead policy. o (Maybe) tweak the cache segmentation and read-ahead parameters on the disks.
In practice, the vendor probably wouldn't let you do the last one yourself. A vendor with a modicum of technical savvy in its pre-sales staff should be able to arrange it for you, though.
I will say that I've tiddled with mode page tweaks on F/C JBODs in the past and you can get some performance improvement by doing it. The same would apply to SAS or SATA for much the same reasons.
@JamesLupolt Also, I'm not sure why 'MPP appliance' sits at the 'Cheap' end of the scale. Teradata and Netezza are proprietary (and therefore buttock-clenchingly expensive) big iron.
@JamesLupolt I guess it's possible that OP is thinking of a Hadoop cluster on commodity hardware, but I don't see that as being relevant to data warehousing.
@ConcernedOfTunbridgeWells not sure if we discussed this with you and @JackDouglas at the drinkies or with @PaulWhite here but Parallel queries are planned to be added in Postgres for 9.6 version (release end of 2016)
@ypercube Have you heard much about the implementation? When I looked into that a few years ago it sounded like there were plans to do it with process forking.