`"Thredis is Redis + SQL + Threads. Or perhaps it's pure lunacy resulting from some mad winter hacking mixed with eggnog. Or perhaps it's the first hybrid SQL/NoSQL server. You be the judge."`
So my first question was basically: imagine a table of : tableA (createDate date, somefield text) and I was like "Just give me all the records (don't bust a nut if I should use 'rows' there ;) ) that were created in the last 7 days "
@DTest no. I only really use one machine all the time (I know, that's a terrible practice, what happens in the case of damage/loss, etc. Don't remind me, please)
@rfusca what were you expecting him to return. select somefield, Createdate from whereverthehell order by create date. or was he suppose to make up some rows?
I would like to know what exactly I am password protecting on my server when I set a database password. I am unclear as to what people are referring to when saying to password protect a database. Are they referring to a single specific database or do they mean the complete mysql database structur...
I'm doing SELECT Name WHERE Id IN (3,4,5,3,7,8,9)
where in this case the '3' Id is duplicated.
The query automatically excludes the duplicated items while for me would be important to get them all.
Is there a way to do that directly in SQL?
thanks a lot
Twice this code showed up in my Google Analytics report. I don't have a subdomain /url or /test anywhere in my directories.
This site has less than 3 months old and that's about it. I have no idea what it could possibly mean but it showed up in the main directory and a subdirectory.
Its a very difficult task for me to get a value from a table based on "Preference".
Plz help me...
I have a table "t1" including two columns "id" and "alphabet" having values "1","1","2","3","3" and "A","B","A","A","B" respectfully(It means every id has alphabet "A" by default and some also has "...
@AshishGinotra actually no, your question is not very clear. Please edit your post with additional details like table structure, sample data and desired result. — bluefeet15 secs ago
Apparently you can't do anything to prevent a carriage return from being entered into a column.
No. It's a valid character. Nothing to prevent it from being added. You could scrub it yourself. But I would do that as a one-time thing. After that, you should be much more focused on making sure only good data makes it to your DB. It's the only choice if you want to keep your DB running effectively. — Eli Gassert1 hour ago
But data entry is the responsibility of the app. The database should hold the data without losing it, not worry about the format of what the actual stored data is.
How do you know what's junk in a given field?
Had someone said to you "oh noes, there's \r and \n in the fields" what would be your first thought?
Think about a database that has multiple applications. What is the quickest way to prevent a piece of junk - let's say that has already been identified as junk by business requirements - from getting into the database? It certainly isn't to go recompile all of the apps and have a conversation with every single person having the ability to run an ad hoc query. So, no, sorry, I don't believe this is an application concern.
The database shouldn't give a crap unless it is expected to do validation (via triggers/constraints) or it itself is responsible for processing said data. Every app<>DB interaction is dofferent. Sometimes the DB is purely a store, so it's "garbage in, garbage out". Other times (DWH in lots of cases) the DB is repsonsible for ETL and will check the data etc, as it has to do lots of validation and processing
If I were really paranoid about the searches I would setup some sort of full catalog search mechanism anyways (is that the right name?)
@AaronBertrand ok, fair point for "well, we fecked it all to hell and back, and we need a fast solution" but I've never had a case where I didn't question what was valid input ...
If the database knows it's junk it shouldn't let it in. It all depends on whether this is something the database is going to be responsible for. It also depends on your surface area - if the database is, for example, also responsible for generating a CSV file of this data on a daily basis to a partner with an SSIS package, then it needs to be able to know how to handle line breaks. It will have a hard time being dumb storage AND providing the data in a particular format as one of its services
At my previous job I was responsible for the database. I did not have control over the apps. So for me to protect the database from junk that was required to be kept out of the database, I protected the database.
Then I didn't really care how messed up the apps were, it was someone else's job to fix them, and I wouldn't have to play clean-up.
Also, the example does not mention WHY SSMS has chosen a particularly infuriating path on this. It shows it as a space to avoid truncating things or making "rows" in the grid multi-line (understandable to avoid those things). However it can be misleading/confusing. I think it would be better to show it with a special glyph for those non-space whitespace characters.
Every single platform needs reliable db drivers, for every language. OSX drivers, Windows drivers, bindings for PHP, bindings for C, bindings for nodejs
@jcolebrand I don't think there is a magic one-line formula for that. If the business determines they don't want to let a user save a password with 3 repeated characters, that becomes part of what I validate.
@AaronBertrand point being: you couldn't have ever dreamt that this particular field didn't need \r or \n inline in it until someone came up with a business rule here.
I ask those questions. I realize that makes me special, but so be it.
Also remember that unless you're using this EF Code First nonsense, you design the database first. Do you really want to defer any and all constraints until the applications have been written?
@jcolebrand well, they are getting more and more popular, so you can't just shove your head in the sand and only worry about the apps where you can enforce full validation.
@jcolebrand In this particular case, Aaron's answer is the typical process anyway - you discover something, you fix it and you see if you can prevent it. The DBA or database developer has to fix the data in situ AND determine how it should be handled in the future. The choice to constrain the database or the app may not work in the database, but this one is relatively easy to fix with a CHECK constraint.
You design the database so that it will handle all the crap that comes its way from all this junk they keep pushing out there. I laugh at all the questions along the lines of "how do I do this thing in EF that is so simple in T-SQL" and the answer is typically "you can't, you'll have to wait for them to add that functionality"...
I quite often spend plenty of time in chat without ever navigating back to DBA.se.
Is there any way of making the Stack Exchange toolbar (as it appears on DBA.se) appear at the top of the chat page so that I can see notifications, rep changes etc?
@Phil that's the app developer's problem, not mine. If the app developer is aware of the business rule, then they should also be updating the app to validate it.
@Phil this has been long asked for. It isn't going to happen anytime soon.
There is a userscript I believe that adds that functionality in. It's why I pinned the site url in the room info top right. I use that link probably a dozen times a day.
@AaronBertrand this is all I'm saying. And the only way to do that is to be a good developer.
@Phil A possibility. But at least your perimeter is secure. Your database is guaranteeing its higher-level integrity. I'm not saying it started out that way with that constraint, but once it was decided that that data should be handled differently, handling it in the database is not necessarily wrong. This particular Q on SO is a good case.
@jcolebrand right, but you're suggesting that as long as the app validates it, you don't have to validate in the database? Sorry, but that is like going to Tijuana without condoms.
@Phil If it can't handle it, you can always put the constraint on once it can - obviously, like anything, you might have to coordinate client and server code releases.
So how should I interpret "This is a business rule, not necessarily a database problem. While it presents a general problem today you really should consider if this needs to be done closer to the UI and not rely on the database for this sort of validation."
@AaronBertrand don't rely on the database. Yes, enforce those constraints in the database, but if they had this flub, they probably don't even know how to put the constraint on the table.
@jcolebrand This is a problem which needs to be made easier to handle. The database needs to protect its perimeter no matter what. The user needs responsive feedback. There is currently no general way of making a good easy conduit between those two things. But relaxing the database is NOT a solution because nobody will trust the database if it doesn't make useful promises. And it needs to keep the promises it makes.
@jcolebrand and of course they don't know how to put the constraint on the table. That's why they're on the site asking, and why they had the problem in the first place. And so we tell them, constrain your table, and validate in the app. My point is that you can always do the former, you can't always do the latter (since not all DML statements come in through an app).
@Phil no, but what I'm saying is I agree with Aaron, I feel like he's just upset about what is inferred by my comment that I didn't remove and replace with new text.
@AaronBertrand hence "I've only ever seen"
I would also argue I've never worked with more than two gifted db devs
@jcolebrand Exactly. This idea of conduits of logic is not well-handled with exceptions. There needs to be a better way to reduce duplication, while still allowing the database to protect its perimeter to keep making useful promises.
Nobody is saying the database was wrong to take the garbage data. But once garbage data is identified, and that it shouldn't be in the database, the database can be updated to no longer accept such garbage. And that should be done no matter how many of the apps are immediately updated to validate.
@jcolebrand But now the right thing is different, and the expectations are that such a scenario needs to be avoided and the database can have a useful place to play in making that change (in some architectures).
@jcolebrand No, it's where a virtual disk can be mounted read/write by more than one guest simultaneously. Need it to do an Oracle RAC install. VirtualBox supports shared disks
@jcolebrand so what? If the app raises exceptions and enough people complain, it will get fixed. Having experience with lazy developers does not mean you must abandon database validation and force the developers to implement it in the app. I'm not following your logic at all.
@Phil do you happen to know the name of the thing I'm asking about?
@AaronBertrand I'm not against database validation. I think that's just as important as server-side validation, which is just as important as client-side validation. Validate early and often.
I'm against lazy code and bad requirements gathering.
@Phil So when I get my SSD if I install my Windows VMs with BootCamp and Parallels I should be able to run the same VM in either a VM or native? (in theory) or do you have any experience with that?
@jcolebrand Well, sometimes you only need it in the database. Just let the app handle it as an exception unless it's something which the app is being asked to try all the time by users. You can't anticipate everything and some things are not worth the UX effort doing anything but a quick constraint by the database.
@jcolebrand I tend to see a lot of the multi-modal database access from various apps and reporting and import/export and I subscribe to the strong perimeter and database service provider mentality
@Phil I believe the issue is there is no API call.
I encourage you to look on StackApps.com and see what shows up
stackexchange.com/filters/75595/sql-server-questions @AaronBertrand I only added three tags to this, not many, as the workflow takes some time. But this may interest you. (It's how the community team follow posts on all the metas easily)
Screenshot
Updates
Version 1.1
Added support for Notification Center (10.8 and later)
Adjusted menu icon to align better with existing menu items
Auto-update is now supported via Sparkle
Version 1.0
Option to automatically start at login
Login token now requested with no_expiry (yo...
I have a query that takes around 4 minutes to run and really need to improve on this. I'm basically pivoting the data into columns. Any idea how I can improve this query? I am mostly joining on indexes. It is the aggregates that is causing the issues
Are there better ways of pivoting the data ...
I'm doing SELECT Name WHERE Id IN (3,4,5,3,7,8,9)
where in this case the '3' Id is duplicated.
The query automatically excludes the duplicated items while for me would be important to get them all.
Is there a way to do that directly in SQL?
thanks a lot
Twice this code showed up in my Google Analytics report. I don't have a subdomain /url or /test anywhere in my directories.
This site has less than 3 months old and that's about it. I have no idea what it could possibly mean but it showed up in the main directory and a subdirectory.
I will open merchant account for you. for a small partnership fee. USA.. 
Re-installed client, problem went away.
I quite often spend plenty of time in chat without ever navigating back to DBA.se.
Is there any way of making the Stack Exchange toolbar (as it appears on DBA.se) appear at the top of the chat page so that I can see notifications, rep changes etc?
Screenshot
Updates
Version 1.1
Added support for Notification Center (10.8 and later)
Adjusted menu icon to align better with existing menu items
Auto-update is now supported via Sparkle
Version 1.0
Option to automatically start at login
Login token now requested with no_expiry (yo...
I have a query that takes around 4 minutes to run and really need to improve on this. I'm basically pivoting the data into columns. Any idea how I can improve this query? I am mostly joining on indexes. It is the aggregates that is causing the issues
Are there better ways of pivoting the data ...
