|
Thanks for the reply.
I am having an issue inserting the cast into this statement for feilds 4, 5:
use dbname
Select field1 + ' ' + ISNULL(field2,'')
+ ' ' + ISNULL(field3,'')
--+ ' ' + ISNULL(field4,'')
--+ ' ' + ISNULL(field5,'')
as column_new from table1
Regards,
Hulicat
|
|
|
|
|
Hulicat wrote: I am having an issue inserting the cast into this statement for feilds 4, 5:
I already gave you that answer...
Here it is in total since you seem to have not understood what I was talking about...
use dbname
Select field1 + ' ' + ISNULL(field2,'')
+ ' ' + ISNULL(field3,'')
+ ' ' + cast(field4 as varchar)
+ ' ' + cast(field5 as varchar)
as column_new from table1
|
|
|
|
|
When you use the cast as per damians suggestion you need to define the size of the varchar as int
ISNULL(Cast Field4 as varchar(20)),'')
|
|
|
|
|
Thanks that was jamming me up....got it thanks.
Regards,
Hulicat
|
|
|
|
|
Mycroft Holmes wrote: you need to define the size of the varchar as int
Interesting... not in SQL-2005 you don't!!
|
|
|
|
|
I am using 2008 and checked BOL before posting, I use Convert almost exclusively so I had to check, maybe the doco is out of date?
|
|
|
|
|
Maybe... I did it with SQL2005 before posting... don't have SQL2008 here...
|
|
|
|
|
I have a table containing production information that your users query frequently, They specifically use this
query most often (that is only use name to search in the where condition):
SELECT Name,Description,Vendor,Instock,Price FROM Products where Name='name'
Have a nonclustered index on this table on the Name column,but your users are complaining the query is still too
slow,what can you do to speed it up?
A、modify the index to include the Description,vendor,Instock, and price columns as nonkey columns.
B、Create a new nonclustered index on the Description,vendor,Instock, and price as nonkey columns.
C、Create a new clustered index on the Description,vendor,Instock, and price as nonkey columns.
D、You can't do anything to speed up this query.
Database is MS SQL SERVER.
Above four choices, which answer is right?please tell the reason.Thanks
|
|
|
|
|
Surely its obvious? Look at the select
SELECT Name,Description,Vendor,Instock,Price FROM Products where Name='name'
and the options:
B and C - why will creating these indices help? The search is on name only.
D - of course it can be speeded up
This leaves A. By including the other columns on the non-clustered index only the index will be read, therefore saving the read from the underlying table.
Hope this explains it, it really is a bit of a no-brainer question.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
You know it would never occur to me to add these other fields into the index, I would leave it at D. The penalty of AED on that table/index would outweigh the benefits I would have thought.
Taking that advice to the next level and you get something like - every table that is in a select query should carry an index with every field in it!
|
|
|
|
|
Mycroft Holmes wrote: The penalty of AED on that table/index would outweigh the benefits I would have thought.
Based on the OP "I have a table containing production information that your users query frequently" implies to me that there are many more reads than writes, so once the index is created there is little overhead - however, based on the info given, that is not certain so you may be correct. In real life you make a judgement call based on your knowledge of the system.
Mycroft Holmes wrote: I would leave it at D
Not if you have a load of irate users on your back 
In reality, the guy wanted to know the answer to an interview/exam question, and I would put my money on adding the other columns to the index being what they were looking for.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Ashfield wrote: In reality, the guy wanted to know the answer to an interview/exam question
It definitely feels like a theory question that has no basis in reality.
I would have trouble answering D as I know I don't know everything and I hate having to answer that to irate users, so I would be looking for the E option - more research required.
|
|
|
|
|
Thank you for your kindly help!
|
|
|
|
|
I would keep the index on Name and create another index on Name,Description,Vendor,Instock,Price
* This should solve any Bookmark problem - have you ran execution plan on your query? if this is a heavily 'write' table, you will pay the price when rebuilding indexes
|
|
|
|
|
Hi All,
I'm having some troubles working on my first stored proc using dynamic SQL and I've hit a brick wall and can't work out what I'm doing wrong. I have two tables that are always the same structure, however I wish to write a re-useable stored proc to copy records to a new table based on some value comparison. I have this in my stored proc at the moment:
ALTER PROCEDURE [dbo].[sp_CopyFileScorerChanges]
-- Add the parameters for the stored procedure here
@SourceTable NVARCHAR(100),
@TargetTable NVARCHAR(100)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @zSQL NVARCHAR(4000)
SET @zSQL = N'INSERT INTO ' + @SourceTable + '(
[FilePath],
[CurrentCategory],
[CurrentSubCategory],
[CurrentBpm],
[OldCategory],
[OldSubCategory],
[OldBpm]
)
SELECT
@FilePath,
@CurrentCategory,
@CurrentSubCategory,
@CurrentBpm,
@OldCategory,
@OldSubCategory,
@OldBpm
FROM ' + @TargetTable +
'WHERE CurrentCategory <> OldCategory'
EXEC( sp_ExecuteSQL @zSQL, N'@FilePath NVARCHAR(4000),
@CurrentCategory NVARCHAR(100),
@CurrentSubCategory NVARCHAR(100),
@CurrentBpm INT,
@OldCategory NVARCHAR(100),
@OldSubCategory NVARCHAR(100),
@OldBpm INT',
@FilePath,
@CurrentCategory,
@CurrentSubCategory,
@CurrentBpm,
@OldCategory,
@OldSubCategory,
@OldBpm
)
END
I'm currently getting a 'Incorrect syntax near 'sp_ExecuteSQL' - Expecting STRING, TEXT_LEX, VARIABLE or GLOBAL_VAR. I just want to call the stored proc with two table names, the source table and the target table.
Any help would be really appreciated.
Thanks,
|
|
|
|
|
You are trying to use a varable in dynamic sql, you cannot. Everywhere you are using @Var needs to be turned into a literal (like you do for target table).
When building dynamic sql I do a print @SQL and try and run the results, this will then give you more detailed results. It will also show that you are trying to insert @FilePath int a varchar without surrounding singles '
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Gotcha. All working now. Thank you!
|
|
|
|
|
Mycroft Holmes wrote: You are trying to use a varable in dynamic sql, you cannot. Everywhere you are using @Var needs to be turned into a literal (like you do for target table).
Wrong! sp_executesql will allow you to pass parameters into the SQL. He's using string concatenation for the table names because this is one place in SQL you cant use a variable.
|
|
|
|
|
You're right, I never use sp_execute, just execute(sql). What was wrong with his proc then?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi
I am getting following error when configuring Distributor or New Publication.I am trying to make local server as distributor and publisher.Can anyone help me to get rid of following error:
==================================
SQL Server could not configure 'WIN2K3STD' as a Distributor. (Microsoft.SqlServer.ConnectionInfo)
===================================
Property LoginSecure cannot be changed or read after a connection string has been set. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Common.ConnectionSettings.ThrowIfConnectionStringIsSet(String propertyName)
at Microsoft.SqlServer.Management.Common.ConnectionSettings.get_LoginSecure()
at Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.InstallDistributor(WizardInputs inputs, Boolean bScripting)
at Microsoft.SqlServer.Management.UI.ConfigureDistributionWizard.InstallDistributor(Boolean& anyExceptions, Boolean bScripting, ApplicationException& outerEx, StringBuilder command)
|
|
|
|
|
i want to give permission to a user how is not in my network to access my db , the user needs to use the windows authentication also mean i can't use sql logins, so is there any solution for this.
|
|
|
|
|
I may be wrong but can't you simply type in the domain/username when setting up a user. I know you would normally chase through the lists but I think you can also just type it in.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi Experts,
im new to sqlserver,my task is...i had a two country tables(t_country and p_country).i want to list the countries which are not in p_country.
thnks in advance
|
|
|
|
|
Select * from tCountry where country not in (Select country from pcountry)
|
|
|
|
|
i will not talk about Normalization here and will post only regarding your question only
try to use "NOT IN " in query
select ID from T_country where id not in (select ID from P_country)
if you dont have any keys then use the common fields exists in both the tables like in your case you can use Country Name but will not recommend you makes query so slow try to use any numeric fields
and you can also use not equal join
Best Of Regards,
SOFTDEV
Sad like books with torn pages, sad like unfinished stories ...
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
