Efficiently Searching Multiple Words In A String
Feb 15, 2008
Hi,
I'd be interested in people's thoughts about the following. A user on my site will be searching for a venue name, and that could officially include a sponsor which the user might not search for. Now I am using the AutoCompleteDropdown from the AJAX Control Toolkit, so the user will start typing in a few characters and the results will be returned. I can generate the results from sql by doing a simple
LIKE '%' + @searchTerm + '%'
however, this fills me with great fear of table scans. At the moment, we'd be querying against a table of 5K records, but our application is very new.
I'm thinking one option is to split the words into another table - a one to many relationship to hold each word of the venue. The benefit of this would be that you could do a:
LIKE @term + '%'
but then I have the cost of the join. (And the added complexity which is not a major issue)
Any thoughts/tips?
Thanks!
View 1 Replies
ADVERTISEMENT
May 25, 2005
hi i am working on sql server200.I m using "LIKE" to search the records.There is freetexttable and containstable table also.just like to know the difference between them.Could anyone provide me a good link regarding this??Thanks
View 3 Replies
View Related
Dec 21, 2005
How can you search for the occurance of a whole word in a string? but not return any results that have the word as a substring.
For instance, if I search for the term 'scene' in a column. Then it will only return rows that have the word 'scene' and not those with the word 'scenery'. I've tried the following sql, but it relies on having text either side of the word as well. If the word 'scene' is on the begining or end of the cell then it is not returned.
SELECT Name, Description
FROM tblWine
WHERE Name LIKE '%[^a-zA-Z]scene[^a-zA-Z]%'
OR Description LIKE '%[^a-zA-Z]scene[^a-zA-Z]%'
Any ideas?
Goran
View 5 Replies
View Related
Oct 24, 2007
Hi,
Is it possible with SQL Server 2005 to include ignored words in a full-text search? For example, searching for "in force as of"? This gives the same results as searching for "force" only. I've tried to empty the ignored words list (noiseENG.txt), but this does not seem to have any effect.
Also we want to be able to search for strings such as "205/1305-2". Searching with punctuation characters in a query seems to be a problem.
What are the possibillities in SQL Server 2005 with regard to these problems?
View 1 Replies
View Related
Oct 21, 2005
I need to build a search function for my site. So there is a single text box for the users to type in their search string. I have been asked that I need to break the user's search string into separate words. So if the user enters: "This is my search query", I need to break it into: "This" "is" "my" "search" "query" and then search for all these words.- Can I break a string into separet words using SQL?- How do I remove funny / dangerous characters from the search string?- If I have to break the search string using a programming language, I would have to run the search query for each word. If I run the search query for each word, How do I combine the search result for the user. For instance, if I search for "my" and find some result, then search for "search" and find some results, how do I display a SINGLE search result to the user.thanks
View 1 Replies
View Related
Oct 29, 2006
Using VWD I have created a search feature using the LIKE clause. The filter expression on my SQLDataSource allows the user to search the Description field of a database and yield a result that contains the exact word or phrase entered into a textbox. Assuming that the user enters more than one word, my understanding is that the search result is limited to database rows that contain the EXACT phrase (such as found in an advanced Google search using the “with the exact phrase” option). The current filter expression is: Description LIKE '%{0}%' For example, if “John Smith” is typed into the search textbox, the results will include a row with: 1. “John Smith is my neighbor” but NOT a row with 2. “John is my neighbor. His last name is Smith”. How does one modify the filter expression so that the search result is like the Google “with all the words” search option, where the search results are limited to records in which all the words typed into the textbox are present but not necessarily in the EXACT continuous order? In the example above, BOTH Descriptions would be returned in the search results when “John Smith” in typed into the search textbox. Thanks for any help you can provide in helping me refine my search options.
View 3 Replies
View Related
Feb 17, 2014
I have french word like "Services d'organisation de minaires"..and i want to search word in SQL SERVER 2008. but the main problem is SQL SERVER give an Error because of string break.Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'organisation'.
View 1 Replies
View Related
Aug 25, 2005
-- GETALLWORDS() User-Defined Function Inserts the words from a string into the table.
-- GETALLWORDS(@cString[, @cDelimiters])
-- Parameters
-- @cString nvarchar(4000) - Specifies the string whose words will be inserted into the table @GETALLWORDS.
-- @cDelimiters nvarchar(256) - Optional. Specifies one or more optional characters used to separate words in @cString.
-- The default delimiters are space, tab, carriage return, and line feed. Note that GETALLWORDS( ) uses each of the characters in @cDelimiters as individual delimiters, not the entire string as a single delimiter.
-- Return Value table
-- Remarks GETALLWORDS() by default assumes that words are delimited by spaces or tabs. If you specify another character as delimiter, this function ignores spaces and tabs and uses only the specified character.
-- Example
-- declare @cString nvarchar(4000)
-- set @cString = 'The default delimiters are space, tab, carriage return, and line feed. If you specify another character as delimiter, this function ignores spaces and tabs and uses only the specified character.'
-- select * from dbo.GETALLWORDS(@cString, default)
-- select * from dbo.GETALLWORDS(@cString, ' ,.')
-- See Also GETWORDNUM() , GETWORDCOUNT() User-Defined Functions
CREATE function GETALLWORDS (@cSrting nvarchar(4000), @cDelimiters nvarchar(256))
returns @GETALLWORDS table (WORDNUM smallint, WORD nvarchar(4000), STARTOFWORD smallint, LENGTHOFWORD smallint)
begin
-- if no break string is specified, the function uses spaces, tabs and line feed to delimit words.
set @cDelimiters = isnull(@cDelimiters, space(1)+char(9)+char(10))
declare @k smallint, @wordcount smallint, @nEndString smallint, @BegOfWord smallint, @flag bit
select @k = 1, @wordcount = 0, @nEndString = 1 + datalength(@cSrting) /(case SQL_VARIANT_PROPERTY(@cSrting,'BaseType') when 'nvarchar' then 2 else 1 end) -- for unicode
while charindex(substring(@cSrting, @k, 1), @cDelimiters) > 0 and @nEndString > @k -- skip opening break characters, if any
set @k = @k + 1
if @k < @nEndString
begin
select @wordcount = 1, @BegOfWord = @k, @flag = 1 -- count the one we are in now count transitions from 'not in word' to 'in word'
-- if the current character is a break char, but the next one is not, we have entered a new word
while @k < @nEndString
begin
if @k +1 < @nEndString and charindex(substring(@cSrting, @k, 1), @cDelimiters) > 0
begin
if @flag = 1 and charindex(substring(@cSrting, @k-1, 1), @cDelimiters) = 0
begin
select @flag = 0
insert into @GETALLWORDS (WORDNUM, WORD, STARTOFWORD, LENGTHOFWORD) values( @wordcount, substring(@cSrting, @BegOfWord, @k-@BegOfWord), @BegOfWord, @k-@BegOfWord ) -- previous word
end
if charindex(substring(@cSrting, @k+1, 1), @cDelimiters) = 0
select @wordcount = @wordcount + 1, @k = @k + 1, @BegOfWord = @k, @flag = 1 -- Skip over the first character in the word. We know it cannot be a break character.
end
set @k = @k + 1
end
if charindex(substring(@cSrting, @k-1, 1), @cDelimiters) > 0
set @k = @k - 1
if @flag = 1
insert into @GETALLWORDS (WORDNUM, WORD, STARTOFWORD, LENGTHOFWORD) values( @wordcount, substring(@cSrting, @BegOfWord, @k-@BegOfWord), @BegOfWord, @k-@BegOfWord ) -- last word
end
return
end
GO
______________________________________________________________
Ladies and Gentlemen,
I am pleased to offer, free of charge, the following string functions Transact-SQL:
AT(): Returns the beginning numeric position of the nth occurrence of a character expression within another character expression, counting from the leftmost character.
RAT(): Returns the numeric position of the last (rightmost) occurrence of a character string within another character string.
OCCURS(): Returns the number of times a character expression occurs within another character expression (including overlaps).
OCCURS2(): Returns the number of times a character expression occurs within another character expression (excluding overlaps).
PADL(): Returns a string from an expression, padded with spaces or characters to a specified length on the left side.
PADR(): Returns a string from an expression, padded with spaces or characters to a specified length on the right side.
PADC(): Returns a string from an expression, padded with spaces or characters to a specified length on the both sides.
CHRTRAN(): Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression.
STRTRAN(): Searches a character expression for occurrences of a second character expression, and then replaces each occurrence with a third character expression. Unlike a built-in function Replace, STRTRAN has three additional parameters.
STRFILTER(): Removes all characters from a string except those specified.
GETWORDCOUNT(): Counts the words in a string.
GETWORDNUM(): Returns a specified word from a string.
GETALLWORDS(): Inserts the words from a string into the table.
PROPER(): Returns from a character expression a string capitalized as appropriate for proper names.
RCHARINDEX(): Similar to the Transact-SQL function Charindex, with a Right search.
ARABTOROMAN(): Returns the character Roman numeral equivalent of a specified numeric expression (from 1 to 3999).
ROMANTOARAB(): Returns the number equivalent of a specified character Roman numeral expression (from I to MMMCMXCIX).
AT, PADL, PADR, CHRTRAN, PROPER: Similar to the Oracle functions PL/SQL INSTR, LPAD, RPAD, TRANSLATE, INITCAP.
More than 5000 people have already downloaded my functions. I hope you will find them useful as well.
For more information about string UDFs Transact-SQL please visit the
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115
Please, download the file
http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,2,27115
With the best regards.
View 20 Replies
View Related
Feb 27, 2008
I am required to send an XML file of our clients to head office in Belgium for comparison against a database of known undesirables. The data is in a legacy system with a custom database so I have created an SSIS package that extracts the tables I need into SQL Server and have developed a program that reads from a text source and creates the XML then Secure FTPs it to Hong Kong who will handle it from there.
My problem lies in actually extracting enough data to avoid too many false positives. The scanning will check name, identity (passport number, etc.), town/city and country. We don't hold an identity number and the town/city and country are buried in free format fields. A quick analysis of the 419,000 records shows that the spelling is terribly unreliable, too. In most cases country has not been entered because the clients are local and even when they are overseas, sometimes only the city has been entered. That is often misspelt, too e.g. Kuala Lumpar or Melboure.
The addresses are held in 3 equal length fields called Address_1, Address_2 and Address_3. There's no guarantee that I will find the town/city or country in any particular one of these fields. In some cases, the street number and name are in Address_3 because the first two hold a company name and a C/O line.
So I'm not going to fret over the ones where the address information is nonsense or missing but I would like to try and extract valid country names and town/city names, where present and this is where I get stuck. I'm from a COBOL programming background and although I'm loving getting used to the power of SQL, I'm still a bit stumped when I come across a problem like this probably because I keep thinking of the solution in procedural terms.
I have a feeling that the solution will be to create two separate reference tables, one of towns/cities and the other of countries. I would then somehow search the 3 fields looking for those keywords and if found, entering them in the appropriate part of the output text file to represent town/city and/or country. I did also think about destringing to find the separate words but that doesn't help where the name consists of two words such as NEW ZEALAND.
I would love to hear from anyone who has dealt with a similar problem and has a neat solution to this using SQL.
View 4 Replies
View Related
Sep 15, 2007
Hi,
I want to search multiple words that is present in the database, e.g if i am putting "porperty in south delhi" but south word is in the data base, but result does not comes. if I use like operator like this select * from MASTERSEARCH where companyname like '" + txt_Company.Text + "%' or dealsin like '%" + txt_keywords + "%'";Here I put only south word, then result comes. but I want search criteria should be any word that is present in the database.I am using this with my web site. http://www.b2bindialinks.com
View 1 Replies
View Related
Apr 2, 2006
I have a column that has multiple word delimited by a space or comma such as:
crime horror love
I want to perform a query so that a search for one of these words returns the record. THis is what I have so far:
SELECT columnname
FROM tablename
WHERE columnname LIKE 'strSearchVar'
This only returns records with one word in the column that matches. it ignores columns with multiple words.
thanks!
View 6 Replies
View Related
Oct 6, 2005
My scenario is I have a web form with a textbox and a button.Once I enter a string and hit submit button, my stored procedure will have to return the result set.So if my search string is "text book title", then I have to execute the query like :select * from tab1 where col1 like '%text%" or col1 like '%book%" or col1 like '%title%"The problem here is I will never know how many words will be entered to search. So I have to make the statement dynamic.How can I do this in a stored procedure? Any help will be appreciated.Thanks.
View 6 Replies
View Related
May 9, 2008
I'm having problems constructing an SQL statement to find records from a user entered string searching record. a record consists of a comma separated string. for example:
user entered string: charts
record: bars, graphs, charts
i've tried:
SELECT MI_FILENAME, MI_EXT, MI_KEYWORDS
FROM MEDIA_ITEMS
WHERE (MI_KEYWORDS IN ('bars'))
Could u offer any assistance please
View 2 Replies
View Related
Jul 20, 2005
Searching through a database with data stored as text places a huge load onthe server. I've seen some applications that use some sort of word indexingthat helps speed things up.Is this the recommended strategy? And if so, how is this implemented andare there any articles that detail this?
View 1 Replies
View Related
May 30, 2006
I have a column Sports which contains a string.the string is comma delimited, so may contain e.g. 1,3,2,6,8,19 or 6,22,13 etc.What is (performance wise) the fastest way to select all the rows where the number 2 is in the Sports column.....?(I can't search on ",2," since "2" may also be at the start of the string....)
View 1 Replies
View Related
Aug 3, 2005
Hi,Does anyone have any suggestions on an approach to speed this upplease?I need to check a given column in a given table to see whether thereare any characters within any of the records that fall outside of arange of valid ASCII characters.My problem is that of speed. The approach I have taken is to write afunction that iterates the string character by character and checks theASCII value at the current position. If it is deemed invalid I drop outof the function and return True. The calling procedure then knows tostop searching as the table column has already failed.Running down a table of million rows, can take up to an hour dependingon the datatype and level of data population.If I write the same thing in Access 2003 VBA and run it via ODBC ittakes a matter of minutes.I would like to find a set-based way of doing this maybe PATINDEX orsomething else creative but am stumped.Does anyone have any brilliant ideas?Thank you!
View 5 Replies
View Related
Jul 23, 2005
hihere is a problem:i have a databes with many, many tablesthe problem is that i dont know where 'abcd' string is (but it is for surein one of that table)is there any SELECT that could help me with finding this string in database?--greets
View 1 Replies
View Related
May 13, 2015
I have a text filed in my table.I have sample data looks like <<some status>> << 3/9/2008 10:00:45 PM>> <<personname>>Im interested in searching <<some status>> and <<personname>> together by skipping date in between so my query set should return status and same person name i m looking for.
View 11 Replies
View Related
Jun 14, 2007
My problem is that i can't search a field that contains Asian characters (Korean in this case).
The table user_Access_tab have 2 keys:
access_id nVarchar(50)
user_id nVarchar(50)
The sql query below is sent through a oledbcommand to a sql server 2005 database.
"select access_id, access_right from user_Access_tab where user_id ='HQ001kimjo012007-05-07 오 11:50:323401'"
It doesn't show any hits even thogh i know there is a number of matching records.
The question doesn't generate an answer in SQL server manager studio eigher.
If i change the datatype on user_id to Varchar(50) the id is presented (in the database) as:
HQ001kimjo012007-05-07 ?? 11:50:323401
Then the question works, but why doesn't it work with nVarChar(50)?
Regards Martin Jonsson
View 1 Replies
View Related
Jul 16, 2007
Hi all:I am trying to create a website which will search and return results that are from multiple tables in different databases. I have the code to search a singular database right now, and I tried to make a connection to a second one, which worked, but I don't know how to send the proper sql commands to the second database or how to link them in the code. Does anyone have any pointers on creating a site which will search different tables in different databases with a known connection between databases? Any help would be really really really really appreciated.-Shrey
View 3 Replies
View Related
Jul 26, 2004
im in need of some help, im new at sql and am trying to seach multiple tables.
i am doing a football database with a table for each of the teams
and each table has the following
"First Name" "Last Name" "Age" "Date of Birth" etc
what i am trying to do is search for either the "first name" or "last name" in every table
if anyone can help me it would be apreciated
View 1 Replies
View Related
Nov 19, 2004
i have multiple tables that vary very little in name. what i mean by this is they are named tbleffort1, tbleffort2 etc. i need to search all the tables together. there is a large constantly changing number of tables, so i would prefer not to have to write them out one by one! any suggestions would be most appreciated!
View 4 Replies
View Related
Aug 27, 2005
Dear GroupCan anyone provide a sample query for the following scenario?Let's assume I want to search for an order someone placed which mightbe an individual or company. An individuals first name is stored incolumn FirstName And the individuls last name in column LastName of thecontact table and the company name is stored in column CompanyName ofthe company table.If a user issues a query with CName parameter how can I search allthese columns and tables at the same time to see if there's a match? Imight add that there's a column in the orders table that holds0=Contact 1=Company but ideally I don't want to use this column.Thanks very much for your help and efforts.Martin
View 2 Replies
View Related
Jun 11, 2015
I want to write a Stored Procedure which can search based on multiple condition and in default is select all.
Example :
User Name [ ALL ] Textbox
Project Name [ ALL ^] dropdown
ID [ ALL ^] dropdown
-----------------------------
Display On Gridview(Default display all records)
select one condition search one and so on.
View 5 Replies
View Related
Sep 11, 2006
Hello All,
I'm a non-programmer and an SQL newbie. I'm trying to create a printer usage report using LogParser and SQL database. I managed to export data from the print server's event log into a table in an SQL2005 database.
There are 3 main columns in the table (PrintJob) - Server (the print server name), TimeWritten (timestamp of each print job), String (eventlog message containing all the info I need). My problem is I need to split the String column which is a varchar(255) delimited by | (pipe). Example:
2|Microsoft Word - ราย?ารรับ.doc|Sukanlaya|HMb1_SD_LJ2420|IP_192.10.1.53|82720|1
The first value is the job number, which I don't need. The second value is the printed document name. The third value is the owner of the printed document. The fourth value is the printer name. The fifth value is the printer port, which I don't need. The sixth value is the size in bytes of the printed document, which I don't need. The seventh value is the number of page(s) printed.
How I can copy data in this table (PrintJob) into another table (PrinterUsage) and split the String column into 4 columns (Document, Owner, Printer, Pages) along with the Server and TimeWritten columns in the destination table?
In Excel, I would use combination of FIND(text_to_be_found, within_text, start_num) and MID(text, start_num, num_char). But CHARINDEX() in T-SQL only starts from the beginning of the string, right? I've been looking at some of the user-defind-function's and I can't find anything like Excel's FIND().
Or if anyone can think of a better "native" way to do this in T-SQL, I've be very grateful for the help or suggestion.
Thanks a bunch in advance,
Chutikorn
View 2 Replies
View Related
Apr 18, 2002
How can l rewrite this and trim the code.
CREATE Procedure Disbursements_Cats
(@startdate datetime,
@enddate datetime)
AS
Begin
SELECT Loan.loan_No AS Loan_No,
Loan.customer_No AS Customer_No,
Customer.first_name AS First_name,
Customer.second_name AS Second_name,
Customer.surname AS Surname,
Customer.initials AS Initials,
Bank.Bank_name AS Bank_name,
Branch.Branch_name AS Branch_name,
Branch.branch_code AS Branch_code ,
Bank_detail.bank_acc_type AS Bank_acc_type,
Transaction_Record.transaction_Amount AS Transaction_Amount,
Transaction_Record.transaction_Date AS Transaction_Date,
Loan.product AS Product,
Product.product_Type AS Product_Type,
Product_Type.loan_Type AS Loan_Type
FROM Transaction_Record INNER JOIN
Loan ON Transaction_Record.loan_No = Loan.loan_No INNER JOIN
Product ON Loan.product = Product.product INNER JOIN
Customer ON Loan.customer_No = Customer.customer_no INNER JOIN
Bank_detail ON Customer.customer_no = Bank_detail.customer_no INNER JOIN
Branch ON Bank_detail.Branch = Branch.Branch INNER JOIN
Bank ON Branch.Bank = Bank.Bank INNER JOIN
Product_Type ON Product.product_Type = Product_Type.product_Type
END;
GO
View 1 Replies
View Related
Jul 6, 2006
Is it possible to use wildcards with an equals statement? Such asSELECT * FROM Table WHERE City = '%' AND State='Ca'Bascially just stating where city equals anything...I know you can do it with a LIKE statement such as...SELECT * FROM Table WHERE City LIKE '%' AND State='Ca'but is that very efficient?The reason I want to do this is because I want to programmitcally set the city, so just ommiting it won't work
Also, using City LIKE '%' seems to not include NULL...is there anywayto include NULL as well as anything else?
Thanks for your help!
View 2 Replies
View Related
Mar 15, 2007
Is there a efficient way to write this query?
SELECT CASE
WHEN Population BETWEEN 0 AND 100 THEN '0-100' WHEN Population BETWEEN 101 AND 1000 THEN '101-1000' ELSE 'Greater than 1000' END AS Population_Range,
COUNT(CASE WHEN Population BETWEEN 0 AND 100 THEN '0-100' WHEN Population BETWEEN 101 AND 1000 THEN '101-1000' ELSE 'Greater than 1000' END) AS [No. Of Countries]
FROM Country
GROUP BY
CASE WHEN Population BETWEEN 0 AND 100 THEN '0-100' WHEN Population BETWEEN 101 AND 1000 THEN '101-1000' ELSE 'Greater than 1000' END
View 1 Replies
View Related
Feb 8, 2008
I hope I explain this correctly...
I'm required to allow users to order items in a field to be displayed on a page in the order they specified.
For example: A user can drag and drop items in a list to specify the order it will be displayed in.
I have my drag and drop code ready to do this.
I have an idea on how to do this but I think it’s too inefficient.
I was going to create an orderby field and populate it with a number that corresponds to the position of the item. However, as one can deduce, if a user drags and drops a record between two others, I would have to change not only its orderby number but then change all the other items orderby number.
For instances if I dropped an item with an orderby number of 3 between 6 and 7 I would have to change the 3 to a 7 and then recursively change all the other records orderby numbers up to 3 and then change everything after 7.
Well, I hope I make sense. It’s easier to visualize it on paper.
Does anyone know how to tackle this issue of user dynamic ordering?
View 2 Replies
View Related
Jul 23, 2005
My main table has the following structure:t1 (id_primary, id_secundary, name) i.e. [(1,1,"name1"), (2,1,"name2")]I want to join this table with the following second table:t2 (id_primary, id_secundary, value) i.e. [(1, NULL, "value1"),(NULL,1,"value2")]The join should first try to find a match on id_primary and only if thatfails it should find a match on id_secundary. Every row in t1 is matchedagainst a single row in t2.The following query works:selecta.name, isnull(b.value, c.value)fromt1 a left outer join t2 b on a.id_primary = b.id_primaryleft outer join t2 c on a.id_secundary = c.id_secundaryI'm wondering though if it would be possible to write a query that only usest2 once, since it actualy is quite a complex query that is calculated twicenow. Any ideas (besides using a temp table)?
View 3 Replies
View Related
May 16, 2008
I have an interesting problem:
I have an ASP.NET web application that uses a Treeview control to display what can potentially be a very large data set. In the past, I would just run a recursive stored procedure in my database that would output the XML which I would save to a file. The Treeview used the XML file as its data source. I did this because it can take so long for the stored procedure to run (10 seconds or more) that, it isn't practical to have the treeview point directly to the stored procedure. This worked well enough because the data didn't change very often.
Now, it looks as if the application will be used in a production environment, and I really need to find a way to supply up-to-date data to the treeview in a dynamic way. I have tried creating a view that would provide XML and that would be updated any time the target table is updated but, that has not worked. I have also tried creating a trigger that would output to an XML file any time an edit was made (using the xp_cmdshell functionality) but, that has proven difficult as well.
Is there a simpler solution that I am just missing? I just want an up-to-date XML representation of the data that is a result of a recursive function.
Thanks for any help you can provide.
View 7 Replies
View Related
Dec 26, 2006
We have a website that accesses our SQL databases. In the past, we used our internal employees to improve our SQL databases. However, we want to outsource the work.
There is a lot of information we would like to keep private from the outsourcing.
Is there a way to efficiently make test data throughout our database without changing our original database?
Is a way to easily update our database to the new changes?
I found this product through Google EMS Data Generator for SQL Server
http://www.sqlmanager.net/en/products/mssql/datagenerator
Would this program help us make test data?
Thanks in advance
-Devin
View 2 Replies
View Related
Oct 12, 2015
We have a SQL Server 2012 Enterprise live transactional database that is now growing over 1G per month and is becoming a size problem for us. It is currently at 23G. Character type fields are all Unicode and I have calculated a savings of 5G in space converting only 2 such fields averaging 206 characters each to non-Unicode, and almost 10G in space if we convert a few more of them from nchar and nvarchar to char and varchar types. These fields will never have a requirement to hold Unicode characters that cannot be in the SQL_Latin1_General_CP1_CI_AS collation as they come in as plain ASCII originally and will always do so per the protocol standard.
I’m the software architect and chief C# developer though only a DBA hack or I would not have designed our database to have Unicode fields for high volume tables that did not need Unicode for those fields when the database was created 3 years ago. I want to correct this mistake now before we finalize converting to an AlwaysOn environment to support with various performance and backup issues.
After downsizing these two or more fields, we would like to shrink the database one time to take advantage of the space savings for full backups, and for seeding an AlwaysOn environment.
1.What is the safest and most efficient conversion technique for downsizing columns from nchar/nvarchar to char/varchar types? Esp. when there are multiple fields in the same table to be converted. I tested doing an “add new column, set new=old, drop old, rename old to new” for both of the main two fields I want to convert from nvarchar(max) to varchar(max), and it took 81 minutes on our test server (4 virtual core, 8G memory) before running out of disk space even though there was 8G left on the disk, and the db has unlimited size set (Could not allocate space for object 'dbo.abc'.'PK_xyz' in database 'xxx' because the 'PRIMARY' filegroup is full). I did delete an old database before it finished after getting a disk warning so maybe it did not count that new space. Regardless it was too slow. And this was on just the two largest of these columns (12.6M rows) and only ran 2 to 3% CPU busy so seemed not very efficient, and indicated unacceptable downtime if we were to convert even these two fields much less any additional fields. Average field size for these two fields was only 206 characters or 412 bytes each. Another technique I plan to try is to create the new table def in a new schema, select into it from the old table, then move tables amongst schema and delete the old table. I have a FK and indexes to contend with on the table.
2.If I figure out how to do #1 efficiently within an acceptable maint window, what is the safest practice for doing a one-time shrink and end up with organized/rebuilt indexes and updated Statistics? I understand the logic of not doing regular shrinks and that sometimes it can actually increase the size.
3.Is there any third party tool that could take a backup and restore it into a new database with the modified field definitions or otherwise convert certain field types?
View 9 Replies
View Related
