|
CONVERT(VARCHAR(20),DATE(),105)
VARCHAR is the datatype to convetr to. It should really have a length - say varchar(20) but defined as I do it just returns the correct number of characters.
DATE was your database column. For testing I tend to use getdate()
105 is the date format. There are loads of them, 101 to about 114, which return the date and/or time in different formats. Experiment to see which you like best.
Use the BOL to see the different formats.
Hope this helps.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Bob, use of most functions on the fields rather than on the parameter will inhibit the use of indexes. This could cause a fast index seek to turn into an index scan.
For this query I would use WHERE DATE >= '20080623' AND DATE < '20080624'. When you construct a date in this fashion it actually produces a datetime where the time parts are set to zero.
Also be aware that all date formats are open to interpretation based on server locale and user language, except ISO 8601 format YYYYMMDD.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Mike
I know that using functions stop indexes working, and that date formats are subject to locale etc, but thanks for pointing it out as others may not be aware - certainly judging by some of the questions on these forums 
My usual solution is to use a stored proc, pass in the date as a param, have a @fromdate and a @todate and do the >= and < as you say. As an aside, if you have used partitioned tables, partitioned on dates, have you noticed if you use a literal (eg addeddate > '23 jun 2008') it just looks at the relevant partition, but using a variable it has a quick squint in all the partitions? You would have thought it would be clever enough to avoid this overhead.
Bob
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Yeah, what Mike said, plus, the above code results in
B) string compares rather than numeric compares
C) performing a conversion on many (millions of?) values rather than one (or two)
For these reasons, never use this technique; it's a coding horror.
[Anecdote:]
Not too long ago (has it been five years already?) I had to add some functionality to a program that already took forty minutes to run.
After adding the functionality and changing the above to the form Mike describes, it took only ten minutes.
The previous guy had said, "well it ain't broke..."
|
|
|
|
|
Hi
I'm trying to use the Iif function. I've had a look on MSDN and it seems like it is valid in SQL server express but I get an error saying that there is an error near the = sign in the Iif function.
I'm using IIF(IsConsultant = -1, 'Yes', '') AS Consultant which is giving me the error.
Am I using the wrong function for SSE or am I doing something wrong. The data type for the IsConsultant field is int
Many thanks
The FoZ
|
|
|
|
|
In SQL Server, you have the CASE statement to do this:
SELECT CASE IsConsultant WHEN -1 THEN 'Yes' ELSE '' END AS Consultant
|
|
|
|
|
Thanks for that. Its something the book I've got didn't touch upon.
The FoZ
|
|
|
|
|
I'm developing a reporting system with SQL Server Reporting Services 2005.
I have created a user ("ru3") at my host server. Now, I want him to be just like administrator user, except he wont see one of the reports.
But, when I click on a field to make a drillthrough report, I get 'rsAccessDenied'.
"The permissions granted to user 'ru3' are insufficient for performing this operation. (rsAccessDenied) "
|
|
|
|
|
Hi,
I have quotes that are translated in many different languages (English, French, Arabic, etc), I want to design a database that holds these quotes, each quote has an ID.
which design is better:
1. having one table with columns (ID, English, French, Arabic, ...)
OR:
2. Having many tables like English(ID, Text), French(ID, text), ... ?
Personally I see the first design more consistent and better in saving database space, am I right or wrong? and why?
BTW: this will be a part of an extremely busy website, thousands of database requests per minute, so performance is critical.
Thanks
And ever has it been that love knows not its own depth until the hour of separation
|
|
|
|
|
Hi,
if the normal usage is having an ID and needing the text in a single language, I would
go for the "one table per language" approach. It would make it easier to add/remove
a language, and expect it to be better performance wise.
|
|
|
|
|
There are obvious tradeoffs between the two approaches and which one is better will depend on your usage. Since you said this will be an "extremely busy website, thousands of database requests per minute, so performance is critical.", then you need to look at caching the data so you don't have to continually make database hits for the same quote/language combination.
To answer the question about which one is more consistent and better conserving space...they are both consistent (since they both follow a pattern) however the first approach is more normalized so you won't have the repeated data contained in each table.
Scott Dorman Microsoft® MVP - Visual C# | MCPD
President - Tampa Bay IASA
Hey, hey, hey. Don't be mean. We don't have to be mean because, remember, no matter where you go, there you are. - Buckaroo Banzai
[ Forum Guidelines][ Articles][ Blog]
|
|
|
|
|
Thanks for your comment.
I really don't care much about space, but I care extremely about speed, I need my database requests to be served fast, no matter what the size is.
Could you please make a comment about this because I really need an extremely efficient design
Thanks
Mohammad
And ever has it been that love knows not its own depth until the hour of separation
|
|
|
|
|
Mohammad A Gdeisat wrote: I really don't care much about space, but I care extremely about speed, I need my database requests to be served fast, no matter what the size is.
Again, some of this will depend on the caching mechanisms being used. The best way to achieve performance is going to be the use of an index on the ID field and stored procedures to lookup/retrieve the quote text. On the front-end website you always want to use the ID and the users culture. The drawback is that somewhere along the call chain from the app to the database you are going to need to decide which table needs to be queried (or column needs to be retrieved) based on the culture. This means a logic block in the code or in a stored procedure.
Your requirements (at least for this part) seem simple enough, create and test both methods and measure performance. It won't be exact, but you'll get an idea of relative performance between the two. The biggest factor no matter what is going to be using the ID for everything, having a good index, and caching.
Scott Dorman Microsoft® MVP - Visual C# | MCPD
President - Tampa Bay IASA
Hey, hey, hey. Don't be mean. We don't have to be mean because, remember, no matter where you go, there you are. - Buckaroo Banzai
[ Forum Guidelines][ Articles][ Blog]
|
|
|
|
|
If you are using SQL Server 2005 have a look at partioned tables. You can then have 1 physical table partitioned by lanaguage across serveral physical disks. I haven't used it for languages, but I have used it very sucessfully to split data by time periods (such as year) with very good performance gains on large databases.
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi,
I want to Refer two tables in fuzzy lookup component.I have to map incoming Columns with more than one reference table's column. can anyone tell how can i achieve this?
Thanks in advance
shsri
|
|
|
|
|
Hi
I have a table with columns like
Table1:
1.ID as int
2.TableID as int
3.ColumnName Varchar(50)
4.ColumnValue Varchar(50)
Here TableID is not unique.I want to create a new table.
Each ColumnName value of Table1 should become Column of New
Table.
Each ColumnValue should become the row value of corresponding
column.
Table1 values:
ID TableID ColumnNAme ColumnValue
1 1 FirstNAme Javio
2 1 LastName Choprakhush
3 1 DateOfBirth 13/09/1986
4 1 Phone 6222226779
And i want to create a table like this
FirstName LastNAme DateOfBirth Phone
JAvio Choprashush 13/09/1986 6222226779
Can anyone Help ME?
Thanks in advance
sri
|
|
|
|
|
This sould put you on the right track
create table UserArea(Country varchar(20))
insert into UserAreaselect 'India'
union all
select 'USA'
union all
select 'India'
union all
select 'UK'
DECLARE @SQL nvarchar(4000)
SET @SQL=''
SELECT @SQL= @SQL +'SUM(CASE WHEN Country=''' + a.Country + ''' THEN 1 ELSE 0 END) AS [' + a.Country + '],'
FROM (select distinct Country from UserArea) as a
select @SQL = left(@SQL,len(@SQL)-1)
SET @SQL='SELECT ' + @SQL + ' FROM UserArea'EXEC(@SQL)
drop table UserArea
Bob
Ashfield Consultants Ltd
|
|
|
|
|
You can try using PIVOT if this is SQL2005.
|
|
|
|
|
Hi All,
While calling the SP which updates the TABLE(SQL SERVER 2000) thru Stored Proc,
i'm getting an error "x parameter was not supplied".
x is the datetime value. I ll pass the system time in that parameter.
I'm facing this problem only for the Datetime Values.
No probs with the code as everything works fine in my system where i developed this app. But after deploying in the client machine i'm facing this prob.
Dont ask me how this is happening, but its happening.
Can anyone help me in this issue.
--Raam
|
|
|
|
|
Without the stored proc code its hard to be certain, but I suspect your proc expects parameter X, something lke
create proc t1 (@X datetime)
as
.....
when you execute the proc it expects @X to be supplied.
If it was defined as
create proc t1 (@X datetme = getdate())
as
.....
you would not need to pass @X unless you wanted some other date.
Hop this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks Bob,
But how come its working on the other system.
--Raam
|
|
|
|
|
Well, with absolutley no code to look at, its a best bet. Post your proc & vb code and someone may be able to help
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks a lot BOB,
That was the problem due to the difference in the time between these two machines...
I changed the format... then it works...
--Raam
|
|
|
|
|
Pleased your are sorted
Bob
Ashfield Consultants Ltd
|
|
|
|
|
hi everyone,
I've been trying to make the XML Schema of my SQL Server Database and currently I've found this to generate an XSD schema,
create table Person
(
Age int not NULL check( Age > 0) ,
Height numeric(10,2) not NULL check( Height > 5),
Gender varchar(5) not null check( Gender in (’M', ‘F’, ‘O’)),
BirthDate datetime null,
)
DECLARE @schema xml
SET @schema = (SELECT * FROM Person FOR XML AUTO, ELEMENTS, XMLSCHEMA(’PersonSchema’))
select @schema
OUTPUT:
But the problem is that this is generating the schema of only one table where as I want to generate the schema of the whole Database, Can u suggest some quick solution for that?
Thanks in advance
Rocky
Success is a ladder which you can't climb with your hands in your pockets.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
