Indexing - What Is Fastest? Numeric Or Alpha Fields?

Jul 27, 2006

If I create an index on a field in SQL Server, what will be the most efficient (fastest) field type to index a field?  (This field will be a "Pointer" to a child table that will contain a list of codes, and their description.)

Would a Numeric field be quicker than a VarChar field?

VarChar would make it easier for a Human to decipher the raw records.  (For example, if I used a numeric the code would be 42 or 47, while the VarChar could be 'savings' or 'checking'.)

Basically I will have the following "Master" table: FieldType
---------
IDInt
NameVarChar
StatusInt -or- VarChar
Customer_TypeInt -or- VarChar

 If Customer_Type is a code that can be looked up in another table, and I index that field, would I want the "Code" to be an Int or VarChar?

SQL: Select *
From Master
Where Customer_Type = <42> or <'savings'>

 My Where clause would depend on the field type.

Thank you,
   Bryan

View 1 Replies


ADVERTISEMENT

Pattern Matching - Searching For Numeric Or Alpha Or Alpha-Numeric Characters In A String

Aug 18, 2006

Hi,

I was trying to find numeric characters in a field of nvarchar. I looked this up in HELP.





Wildcard
Meaning



%


Any string of zero or more characters.



_


Any single character.



[ ]


Any single character within the specified range (for example, [a-f]) or set (for example, [abcdef]).






Any single character not within the specified range (for example, [^a - f]) or set (for example, [^abcdef]).

Nowhere in the examples below it in Help was it explicitly detailed that a user could do this.

In MS Access the # can be substituted for any numeric character such that I could do a WHERE clause:

WHERE
Gift_Date NOT LIKE "####*"

After looking at the above for the [ ] wildcard, it became clear that I could subsitute [0-9] for #:

WHERE
Gift_Date NOT LIKE '[0-9][0-9][0-9][0-9]%'

using single quotes and the % wildcard instead of Access' double quotes and * wildcard.

Just putting this out there for anybody else that is new to SQL, like me.

Regards,

Patrick Briggs,
Pasadena, CA






View 1 Replies View Related

Removing Non-numeric Characters From Alpha-numeric String

Oct 24, 2007

Hi,

I have one column in which i have Alpha-numeric data like

COLUMN X
-----------------------
+91 (876) 098 6789
1-567-987-7655
.
.
.
.
so on.

I want to remove Non-numeric characters from above (space,'(',')',+,........)

i want to write something generic (suppose some function to which i pass the column)

thanks in advance,

Mandip

View 18 Replies View Related

Alpha Numeric Function

Oct 14, 2004

hi ,
can anyone tell me if there exists a function in SQL Server that help me determine if a variable is ALL Alphanumeric data or not by returning a 0 or 1 when condition fails or succeeds .
thank you

View 2 Replies View Related

Get Index Of First Alpha-numeric Character

Sep 29, 2005

In SQL I need to be able to take a varchar parameter @Area and convert it to a float.

The input values for @Area I can't control. They can range from 6300 to 6,300 SqFt to 1.2 Acres .

So to convert this value to a float I basically look through the string and remove everything that isn't a number or a period. Then I would convert this value to square feet based on how large the number is.


Code:

DECLARE @k int, @Temp VarChar(25), @SqFt Float
SELECT@Temp = @Area
select @Temp

select @k = patindex('%[^0-9. ]%', @Temp)
while @k> 0
begin
select @Temp = replace(@Temp, substring(@Temp, @k, 1), '')
select @k= patindex('%[^0-9. ]%', @Temp)
end

If @Temp = ''
BEGIN
SET @Temp = '0'
END

SELECT @SqFt = Convert(Float, @Temp)

--Distinguish if it was acres or square feet
If (@SqFt > 750.00)
BEGIN
SET @SqFt = @SqFt
END
ELSE
BEGIN
SET @SqFt = (@SqFt * Convert(Float,43560) )
END

SELECT @SqFt



This works great except for one situation, If @Area is something like 6,300 Sq.Ft. . When I run it through the part that removes all non-numeric items and periods, I end up with 6300 .. . So to get around this I want to find the first letter in the string and then remove everything after it. Then take the result and run it through part that removes everything but the numbers and period.

However I can't find away to get the index of the alpha-numeric character and remove everything after it.

Thanks in advance!

View 1 Replies View Related

Trigger To Seperate Alpha From Numeric

Nov 9, 2007

We have a table with an indesx that is varchar(7). It contains up to 5 numbers and 2 characters to denote batches. IE. 100a
105zz
1c
2
10001w

In an Access gui I need to be able to sort this field numericaly so each number shows up in its correct position. IE.
1c
2
100a
105zz
10001w

In order to accomplish this, I created a seperate field on the table named Num as varchar(5). I would like to create a trigger that updates this field any time a new batch number is entered. This way I can have my queries order by Num when returning the recordsets to the GUI.

Below is the code that I thought would work, but it is giving me an error near the keyword BEGIN after the IF(ISNUMERIC) statement. I can't seem to find an error with this code, but I thought maybe a thousand fresh sets of eyes could. Any help would be greatly appreciated. Also if you know of a better way to accomplish this task, please feel free to post here.

TIA,

Aaron

CREATE TRIGGER [InsertNum] ON [dbo].[IP_Batch_Table_Temp]
FOR INSERT, UPDATE
AS

DECLARE @Num varchar(7)
DECLARE @Num1 CHAR(7)
DECLARE @x INT

SET @Num = (SELECT IP_Batch_Number FROM inserted)
SET @x = 1
WHILE (@x < len(@Num))
BEGIN
IF (ISNUMERIC( SUBSTRING(@Num, @x, 1) ) )
BEGIN
SET @Num1 = @Num1 + SUBSTRING(@Num, @x, 1)
END
SET @x = @x + 1
END
UPDATE IP_BATCH_TABLE_TEMP SET NUMBER = ltrim(rtrim(@Num1)) WHERE IP_BATCH_NUMBER = @Num


View 5 Replies View Related

Determine If Values In A Field Are Alpha Or Numeric

Feb 28, 2008

HI,
Thanks in advance for taking your time to read this post.
I am trying to write a SQL query using MS SQL 2005 that will read the value of a field and tell if it is alpha or numeric.  I have tried the following but it does not work:
select field1 from table1 where left(field1,2)='[0-9]'
select field1 from table1 where isnumber(left(field1,2) tried with a =1 at the end and without and =1 at the end
the goal is to read through a field and format it so if a field looks like this 12xxx111xx I can change it to look like 12-xxx-111-xx.
Any help is greatly apprecaited

View 4 Replies View Related

Select Statment On Column That Can Contain Alpha And Numeric Values

Sep 13, 2007

Hi,
I have a little bit of a problem I cannot seem to figure it out. Is it possible to write a Select statement that contains a WHERE column_name > desired_numeric_value
The tricky part it that the column is of CHAR type and can contain numeric grades ranging from 0-100 or the letter I for Incomplete.
My SQL was working perfect when this column contained only numbers as soon as a record with I was added I get the following error:
Character to numeric conversion error
 This report will be used to find students who have failing grades. Thanks for any help! 

View 5 Replies View Related

Alpha Numeric Auto Increment Code In Sql Server 2000

Jan 25, 2006

Karikalan writes "We need alpha numeric auto increment code in sql server 2000.
(for eg.: ico1001, ico1002, ico1003,......)
Can any one send the code in MS sql server 2000? plz ..................
bcoz i am beginner in sqlserver 2000"

View 1 Replies View Related

Can't Load Into Numeric Fields

Feb 1, 2005

Hi,

This may seem like a simple question to be asking but I’m not the most experienced working with DTS loads and can't understand (don't know) why my load is failing.

I am trying to load in a text file comma separated into a table I have created.

It consists of only 5 entries

01 - varchar (20)
02 - smalldatetime
03 - smalldatetime
04 - numeric Scale (1)
05 - numeric Scale (1)

Here are the first two lines from my file,

ABCDEFGHIJKLMNOPQRS1,02/02/2005,05/02/2005,0,1
ABCDEFGHIJKLMNOPQRS2,01/02/2005/06/02/2005,1,1

As far as I can se it should be working but it gives me the error:

The number of failing rows exceeds the maximum specified.
TransformCopy 'DTSTransformation_4'conversion error: General conversion failure on column pair (source column 'Col004(DBTYPE_STR), destination column 'Result1' (DBTYPE_NUMERIC)).

Help.

Can't figure out what’s going on so any ideas would be useful.

Cheers.

View 1 Replies View Related

Decimal/numeric Fields

Sep 19, 2007

Hi,

Database: MS SQL
Backend: Visual FoxPro ver 9

I have a table on SQL with numeric field (18,3), when create a cursoradaptor the become the a numeric field accept only int Value not allow to accept decimal. How to enable the field to accept decimal.

I am already:-
set fixed on
set decimal to 3

Best regards

View 3 Replies View Related

Concatenating Numeric Fields

Mar 28, 2006

Friends,

I am attempting to concatenate two numeric type fields together with character data and the query is adding them together. I am assuming I need to convert the ints to a string type but would appreciate some info on the best way to do this...I am sure it's something simple but am not finding much on the web about it.

SELECT vehFacID + '-' + vehID AS vehNew FROM Vehicles

Returns the sum of vehFacID & vehID. Doh!

J.H.

View 7 Replies View Related

ASP Only Shows Fields Which Contain Numeric Data

Jan 4, 2004

Hi

I'm using an SQL server database to run a website with ASP.

A new page I'm making will only display the contents on fields containing numeric data and it won't display text.

Other pages work fine. Does anyone have any ideas as to why this is happening - I've never seen in before.

Dave

http://pink-football.com/gossip.asp

View 2 Replies View Related

Numeric Concatenation Failure On 4 Fields

Aug 27, 2007

I have a table:
batch_date numeric (8,0)
batch_nmbr numeric (5,0)
batch_ctr numeric (4,0)
col4...
col5...
col6...

I am trying to select data using something like:
select col1, col2
FROM
tablename
WHERE
(BATCH_DATE +
BATCH_NMBR +
BATCH_CTR) >= (:BATCH-DATE
+ :BATCH-NMBR
+ :BATCH-CTR)

this is a mssql cursor embedded in a microfocus cobol program.

I do not get predictable results.

View 1 Replies View Related

Reporting Services And Numeric Fields

Aug 31, 2007

Hi

I've generated a report using Reporting Services, and some of my fields are numeric with the value zero. On generating the report this is shown as 0.00 and not 0. Is this something that is easily fixed?

View 1 Replies View Related

Summing And Grouping Numeric Fields

Nov 6, 2006

Hello again

How do I sum up and group numeric values into one output column in SSIS?

I have data that looks like this:

Refno date credit amount

11675 1987-04-23 7.03 13501.27


and I want to add the credit and amount columns together and output them as one column, In T-SQL, i'd do this:

SELECT Refno, SUM(Credit + Amount)

FROM Transfer

GROUP BY Refno, Date

I could add this SQL into my SSIS package easily enough but I wan't to learn how to do it the SSIS way







View 4 Replies View Related

Converting Varchar To Numeric And Date Fields...

Jan 21, 2008



Hi All,

I have 2 varchar fields on MS 2005 table
First field is date and format is 080118(YYMMDD)
and second is salary field like 00002000(positive) and 00002000- (negative)
how can I move them to date and numeric fields on another table....

thanks

View 1 Replies View Related

Data Mining :: Isnull Function On Numeric Fields

Aug 10, 2015

I'm attempting to use the isnull function to convert null values in a column to a blank. It works on the alpha field but not the numeric and I was wondering what can be done for numeric field conversion.

View 6 Replies View Related

FLAT File Indexing Vs RDBMS Indexing

Sep 22, 2006

Hi

I want to know is a flat file faster than a RDBMS for indexing for example a search engine indexing would a flat file be better in terms of performance, scalability etc than a RDBMS?

View 14 Replies View Related

Online Re-indexing Vs Offline Re-indexing

Sep 10, 2007

Hi,

The other day we tried online re-indexing feature of SQL 2005 and it€™s performing faster than offline re-indexing. Could you please validate if it€™s supposed to do be this way? I always thought offline should be faster than online.


Thanks,
Ritesh

View 5 Replies View Related

T-SQL (SS2K8) :: Arithmetic Overflow Error Converting Numeric To Data Type Numeric

Jun 10, 2014

when I run below query I got Error of Arithmetic overflow error converting numeric to data type numeric
declare @a numeric(16,4)

set @a=99362600999900.0000

The 99362600999900 value before numeric is 14 and variable that i declared is of 16 length. Then why this error is coming ? When I set Length 18 then error removed.

View 2 Replies View Related

Arithmetic Overflow Error Converting Numeric To Data Type Numeric

Mar 21, 2006

Guys

I'm getting the above when trying to populate a variable. The values in question are :
@N = 21
@SumXY = -1303765191530058.2251000000
@SumXSumY = -5338556963168643.7875000000

When I run, SELECT (@N * @SumXY) - (@SumXSumY * @SumXSumY) in QA I get the result OK which is -28500190448996439680147097583285.072256 ie 32 places to left of decimal and 6 to the right
When I try the following ie to populate a variable with that value I get the error -
SELECT R2Top = (@N * @SumXY) - (@SumXSumY * @SumXSumY)@R2Top is NUMERIC (38, 10)



Any ideas ??

View 6 Replies View Related

TSQL Function To Return Numeric Value Of Non Numeric Field

Jul 20, 2006

I need to replace Access Val() functions with similiar function in sql.

i.e. Return 123 from the statement: SELECT functionname(123mls)

Return 4.56 from the satement: SELECT functionname(4.56tonnes)

Any one with ideas please

Thanks

George


View 1 Replies View Related

Certain Numeric Fields Not Read From The Excel File When Using A Excel File Source.

Jul 20, 2006

I have the Excel Connection Manager and Source to read the contents from an Excel file. For some reason couple of numeric fields from the Excel worksheet are brought over as nulls even though they have a value of 300 and 150. I am not sure why this is happening. I looked into the format of the fields and they are set to General in Excel, I tried setting them to numeric and that did not help.

All the other content from the excel file is coming thru except for the 2 numeric fields.

I tried to bring the contents from the excel source to a text file in csv format and for some reason the 2 numeric fields came out as blank.

Any inputs on getting this addressed will be much appreciated.

Thanks,

Manisha

View 5 Replies View Related

User NOT LIKE (alpha)

Jan 19, 2007

Hi

for MS SQL 2000

I am searching users for each letter

WHERE users.name LIKE ('A%')

how can i search For users.name not starting by A to Z ? all except letters

thank you

View 2 Replies View Related

Alpha Prefix - Can It Be Done In SQL?

Jun 14, 2007

Good Morning Db forum.


I am working on a query that I need to pull all fields that contain 3 alpha characters. for example BCB001, MCR001, CHP001 and so forth.

Is there a SQL alpha wildcard that I could use to pull all records that have the three alpha chars?

View 3 Replies View Related

Migrate Alpha Sql 6.5 To Intel Platform

Jan 7, 2002

Hello:

Our company needs to (restore/migrate) our Alpha Nt4sp4 sql 6.5sp5a databases to an Intel hardware platform. For the time being the new intel server will be runing Nt4sp4 sql 6.5 sp5a. I understand that if we were to upgrade to sql 7 we would be able to restore the databases without any problems. However upgrading to sql 7 is not an option right now. I have tried to restore a backup from the alpha to an intel based machine, and it fails because the processor types are different.

I have already search technet, but found nothing, except upgrade to sql 7.

I am hoping someone out there could point me in the right direction.

Thank inadvance for your help, I appreciate it very much.

Cindy

View 1 Replies View Related

SQL Transfer From Alpha To Intel Platform

Oct 19, 2000

please please help?

For the last couple of days i've been trying to transfer a SQL6.5 database from an Alpha platform to an Intel one.

At one point i loaded SQL7 on the Intel platform and tried to perform an upgrade (using the wizard), across the network - but it just did not want to play.

Using Veritas Backup Exec, with SQL6.5 on both machines, i've tried doing a backup of the database on the Alpha platform and then restore to the Intel Platform, but this fails stating that the processors are incompatible

Am i trying something that is impossible?

If anyone could point me in the right direction i'd be truely grateful.

Sharpy

View 1 Replies View Related

Migrating Sqlserver 6.5 From Alpha To Intel

Jun 20, 2000

Hello,

I am looking for ways to migrate Sqlserver 6.5 from Alpha to Intel plat form. The only method I heard was Database/Object transfer.
Is there any other way?

Thanks,
yi

View 2 Replies View Related

Backup From Alpha, Restore On Intel??

Dec 10, 1999

Hi,

I'm trying to move a database over from an Alpha to an Intel-base PC. The SQL Server database is on an Alpha server. The 2nd machine is an Intel. When I make a backup of the database from Alpha, copy over the file to the 2nd PC (Intel) and then try to restore it from the 2nd PC, it doesn't sure up on the list of restores when I click on 'Add Device'

The only other way I can get this to work successfully is with a Database Object Transfer, which takes too long (several hours) and limits me to being on site. I need to do this every few weeks so I need a better way to transfer the database over ?

Anyone have any experience with SQL Server on Alpha??

Joyce

View 2 Replies View Related

Can I Install SQL Server 6.5 On Alpha System With Fx!32

Dec 23, 1998

Hello,

We want to port our Nt application to Alpha system with Fx!32. Is it possible to have SQl Server on this system.

Is there any SQL server available specifically for Alpha system..? Because there are VB and VC compilers available for Alpha systems.

ThanX
__Vijay VM

View 2 Replies View Related

Left Most Character Is An Alpha Or Number?

Feb 23, 2006

Hi,

How do I do a conditional check for the left most character if it is an alpha or number in a Derived Column Transformation? Something like

ISNUMERIC(newseq) ? "ABC" : "DEF"

Thanks

View 2 Replies View Related

Removing Non-alpha Characters && Spaces Script...

Dec 6, 2006

Hi I am trying to strip out any non-alpha characters from a field.

i.e. Field = ABC"_IT8*$ should return: ABCIT8

I am writing a loop to do this for all values of a field. The script runs, but hangs....please could somebody advise on the code below...:

I run the script but it doesn't seem to finish. Can anybody see any issues with the code:

DECLARE @Index SMALLINT,
@MATCH_Supplier_name varchar(500),
@Counter numeric,
@Max numeric
-- @sqlstring varchar(500)

SET @Counter = 1
SET @Max = (SELECT Max(DTect_Supplier_SRN) FROM SUPPLIER_TABLE_TEST)

WHILE @Counter <@Max
BEGIN
SET @MATCH_Supplier_name = (SELECT Match_Supplier_Name FROM SUPPLIER_TABLE_TEST WHERE @Counter = DTect_Supplier_SRN)
SET @Index = LEN(@MATCH_Supplier_name)
WHILE @Index > = 1
SET @MATCH_Supplier_name = CASE
WHEN SUBSTRING(@MATCH_Supplier_name, @Index, 1) LIKE '[a-zA-Z]' TH EN SUBSTRING(@MATCH_Supplier_name, @Index, 1)
WHEN SUBSTRING(@MATCH_Supplier_name, @Index, 1) LIKE '[0-9]' THEN SUBSTRING(@MATCH_Supplier_name, @Index, 1)
ELSE ''
END + @MATCH_Supplier_name
SET @Index = @Index - 1
--PRINT @MATCH_Supplier_name
SET @Counter = @Counter + 1
END

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved