|
Hello,
I don't think it will allow you to change Computer column expression on the Fly... Instead add new column with new expression and name it as newColumn. Delete you old column and then rename newColumn to Original column name using below command.
sp_RENAME 'Table_First.newColumn', 'YourOriginalColumnName' , 'COLUMN'
|
|
|
|
|
If you had the opportunity to re-design a large system's DB layer, that beside pure data contains a fairly large amount (hundred of thousands every year) of files, would you use BLOBs or external storage?
The facts are known about those files:
1. Most of them are PDF, DOC(X) and image (JPEG, TIFF, BMP)
2. No search inside the files are required
3. Files are acceded mostly (95% of the time) directly
4. History of files are important
Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.
|
|
|
|
|
Blobs put heavy weight on the tables, the bigger they are the slower the operations. Also if anything corrupts on one file you'd have to restore a DB table instead of a single file or directory.
Also it would be more scalable as it would allow you to use different backend servers to store the files and have a rough load balancing/availability service without heavily involving the DB server, all at the advantage of performance.
Geek code v 3.12
GCS d--- s-/++ a- C++++ U+++ P- L- E-- W++ N++ o+ K- w+++ O? M-- V? PS+ PE- Y+ PGP t++ 5? X R++ tv-- b+ DI+++ D++ G e++>+++ h--- r++>+++ y+++*
Weapons extension: ma- k++ F+2 X
|
|
|
|
|
I would go for external storage.
You can set up a series of meta tables with locations/mappings of drives and folders and store the files outside of the database.
You could also create a series of stored procedures to check that the files are in the locations held in the database, every day or so, reporting back on discrepancies - just to catch the rare occasion where a file has been moved or deleted.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
What are you most comfortable with?
I would personally go for BLOBs.
Pros:
- You get one point of backup.
- Lowered chance of tampered or missing files.
- You only need to set permissions in one place
Cons:
- If badly implemented performance will suffer
- If your database is stored on a filesystem you will get an overhead
- No direct access to files
There's a paper[^] from MS on the subject
|
|
|
|
|
Just to confuse matters, you might also want to look at file tables: 
FileTables (SQL Server)[^]
SQL Server 2012 FileTable: My first experience[^]
Using FileTables in SQL Server 2012[^]
In other words, you can store files and documents in special tables in SQL Server called FileTables, but access them from Windows applications as if they were stored in the file system, without making any changes to your client applications.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
|
If you are using SQL SERVER 2012 then what do you say about using File Table
|
|
|
|
|
Hello,
I want to calculate the difference between 2 dates and times in seconds.
, Datediff(SECOND,
convert(datetime, [START_DATE] + ' ' + [START_TIME]),
convert(datetime, [END_DATE] + ' ' + [END_TIME])) AS M_Time_Worked
Nog problem with the function but when I come across a record where the Start_Date is empty then I get an overflow (I know that you can calculate max 68 years worth of seconds)
I've tried to use a Case statement but it wont work like this:
, CASE WHEN START_DATE IS NULL THEN START_DATE = END_DATE
ELSE
Datediff(SECOND,
convert(datetime, [START_DATE] + ' ' + [START_TIME]),
convert(datetime, [END_DATE] + ' ' + [END_TIME])) END AS M_Time_Worked
Does anyone know how I can handle empty values for a DateDiff() please?
Kind regards,
Ambertje
|
|
|
|
|
, Datediff(SECOND,
convert(datetime, [START_DATE] + ' ' + [START_TIME]),
convert(datetime, isnull([END_DATE],[START_DATE]) + ' ' + isnull([END_TIME],[START_TIME]))) AS M_Time_Worked
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
Sorry Chris,
Same error as with my query. This is an example of such an record with empty values
START_DATE| START_TIME| END_DATE | END_TIME
| | 01/18/2015| 20:12:52
I'm testing with this query:
declare @t table(START_DATE varchar(10), END_TIME_1 varchar(8), START_TIME_1 varchar(8), END_DATE varchar (10))
insert @t values('', '20:12:52', '', '01/18/2015')
select Datediff(SECOND,
convert(datetime, [START_DATE] + ' ' + [START_TIME_1]),
convert(datetime, [END_DATE] + ' ' + [END_TIME_1]))
from @t
|
|
|
|
|
That is because you are storing empty strings, not null values. You also have two empty times, which wil cause problems with the query.
You also should not store dates and times as strings - use the DateTime column type provided to hold them properly.
declare @t table(START_DATE DateTime, END_DATE DateTime)
insert @t values ('01-jan-2015 20:12:52', Null)
select Datediff(SECOND,[START_DATE], ISNULL([END_DATE],[START_DATE]))
from @t
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
Hi Chris,
I need my dates as string because I'm working with a Data warehouse DB.
, CAST(Replace(convert(date, [START_DATE], 101), '-', '') as numeric(8,0)) AS [START_DATE]
, CAST(dbo.LPAD(dbo.CONVERT_TIME_TO_NUMERIC(SUBSTRING(dbo.arKD.START_TIME,1,8)), 6, 0)AS nvarchar(6)) AS START_TIME
, CAST(Replace(convert(date, END_DATE, 101), '-', '') as numeric(8,0)) AS END_DATE
, CAST(dbo.LPAD(dbo.CONVERT_TIME_TO_NUMERIC(SUBSTRING(dbo.arKD.END_TIME,1,8)), 6, 0)AS nvarchar(6)) AS END_TIME
,Datediff(SECOND,
convert(datetime, [START_DATE] + ' ' + [START_TIME]),
convert(datetime, [END_DATE] + ' ' + [END_TIME])) AS M_Time_Worked
This is actual code where I Cast all dates to nvarchar.
So how can I change this code in a way that I can set the Start_Date (when empty) to the value of End_Date and Start_Time to the value of End_Time when empty?
I'm not an experienced developer, I'm still learning and hope you can learn me something new.
Kind regards,
Ambertje
|
|
|
|
|
,Datediff(SECOND,
convert(datetime, [START_DATE] + ' ' + [START_TIME]),
convert(datetime, CASE WHEN RTRIM([END_DATE]) = '' THEN [START_DATE] ELSE [END_DATE] END + ' ' + CASE WHEN RTRIM([END_TIME]) = '' THEN [START_TIME] ELSE [END_TIME] END)) AS M_Time_Worked
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
Still an error:
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
|
|
|
|
|
Ambertje wrote: I need my dates as string because I'm working with a Data warehouse DB.
Why would the date format be a problem in a Data Warehouse?
|
|
|
|
|
Are you looking for ISNULL(START_DATE , END_DATE) ?
|
|
|
|
|
Good morning to you all,
It's Start_Date and Start_Time that can be empty.
So I think ISNULL(Start_Date, Start_Time) is what I need.
How can I implement this in my code?
,Datediff(SECOND,
convert(datetime, [START_DATE] + ' ' + [START_TIME]),
convert(datetime, [END_DATE] + ' ' + END_TIME])) AS M_Time_Worked
Kind regards,
Ambertje
|
|
|
|
|
Ambertje wrote: So I think ISNULL(Start_Date, Start_Time) is what I need.
That is not how ISNULL works. You would need something like
ISNULL(Start_Date, EndDate) + ' ' + ISNULL(Start_Time, Endtime)
|
|
|
|
|
You are correct, but still it wont work like that.
Something was missing and after analasys I saw that the Start_Date field wasn't NULL but just empty.
So this is what I changed to make it work:
,Datediff(SECOND,
convert(datetime, ISNULL( NULLIF([START_DATE], '' ) , END_DATE) + ' ' + ISNULL( NULLIF([START_TIME], '' ) , END_TIME)),
convert(datetime, [END_DATE] + ' ' + [END_TIME])) AS M_Time_Worked
A big thank's to all for helping me and the responses where I learned from.
Kind regards,
Ambertje
|
|
|
|
|
You are correct but when I run this statement I get no values.
So I went on to try some more ways to make it work and this statement works:
, CASE WHEN [START_DATE] IS NULL OR [START_DATE] =
CAST(Replace(convert(date, END_DATE, 101),
CAST(Replace(convert(date, [START_DATE], 101),
But I can't integrate it in my DateDiff statement, can you help me please?
Ambertje
|
|
|
|
|
I have a little bit of SQL code and I am having trouble bringing it over to MSSQL. I don't even know where to being I know that the SELECT, FROM , GROUP BY and HAVING will be in the MSSSQL query.
However I know for a fact the 4th line will change into @param1 and @param2. If anyone would like to add some input into this would be nice.
Access Query
Select COUNT(') AS Result, Username AS UsernameInput, [Password]
FROM access
GROUP BY Username, [Password]
Having (COUNT(') = 1) AND (Username = ?) AND ([Password] = ?)
modified 24-Jan-15 16:04pm.
|
|
|
|
|
Change the single quote ' to an asterisk * in the count functions. eg count(*)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Asalam Alaikum Friends;
need a help of ERD Fee Record keeper of whole year for a single student plz plz plz help
|
|
|
|
|
Please do not crosspost; use one forum only.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
