Tag Info

New answers tagged

0

Zip codes should be stored as text, as some start with 0 (screwing up formatting/sorting) and there is no reason to do math on them. Also, if you want to store global postal codes, they often contain letters. Phone numbers are a maybe for text, especially if there might be extensions. Or you want to store numbers like 1-800-GOT-JUNK. There's no reason to do ...


2

Your colleague is correct that it is easier to simply not think about it and just store everything as a varchar. But this comes at a large cost in terms of space requirements, performance, flexibility in querying data, and most importantly, lack of data integrity. This is not just a one-time cost; it is paid repeatedly over the lifecycle of the ...


2

There are several situations in which it's better to represent numbers using some kind of numeric data-type. It's a little more efficient, but that's just the beginning. You get support for built-in arithmetic using SQL operators without performing type conversions at run time. Not only do type conversions slow things down, but they can result in numerous ...


2

Size is one consideration. An int can hold up to -2,147,483,648 in four bytes. A char will need 11 bytes to hold the same value. There are built-in functions to manipulate the various data types. DATEADD() and DATEDIFF() are two examples. This will not be possible with date-stored-as-text. Constantly CASTing back and forth will not make for efficient ...


0

If you simply include the column which holds these values into an order by clause they will come out in the sequence you want. For example select ThisColumn from MyTable order by ThisColumn This will work if your values genuinely are all form shown i.e. two digits, a slash and one digit. If you have other formats the more involved solutions will be ...


1

A way to order such string is Step1: Create Split Function CREATE FUNCTION [dbo].[fnSplit] ( @List VARCHAR(8000), @Delimiter CHAR(1) = ',' ) RETURNS @Temp1 TABLE ( ItemId INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, Item VARCHAR(8000) NULL ) AS BEGIN DECLARE @item VARCHAR(4000), @iPos INT SET @Delimiter = ISNULL(@Delimiter, ',') SET @List = ...


0

There are different data types in sql to store the data. if the all data are same then varchar is good to store. but in the future, you need the operation on the data, you can't because of limited functionality. better to go with the data type of data.


0

First you find the position of your separating character ( / ). Then you order your table by left side of separator and right side of separator. SELECT field ,CHARINDEX('/', field) as [Position of /] ,LEFT(field, CHARINDEX('/', field)-1) as Left_Sort ,RIGHT(field, LEN(field) - CHARINDEX('/', field)) as Right_Sort from #string order by ...


0

The Shipping Address is a property of each and every order, not of the customer. Even if it was a property of the Customer, it would be necessary to know where past orders were shipped to if a customer relocated. Therefore all that can be stored as a Customer property are the Default Shipping Address (and Default Billing Address), for pre-populating each ...


2

Do you really need ROW_NUMBER and OUTPUT? How about a nice set-based DELETE, eg something like this: USE tempdb SET XACT_ABORT ON BEGIN TRAN SELECT * INTO #o FROM sys.objects -- Identify records to keep SELECT TOP 2 object_id INTO #keep FROM #o ORDER BY object_id -- Delete others DELETE o FROM #o o ...


0

Test the inner query to see how well it performs. If you are missing an index on the foreign key, performance may be very slow. The size of your result set can cause performance issues with inner joins. The inner join may need to be run once for each row in your result set. Not having enough memory can cause an issue as data may need to be re-read from ...


10

This is expected behaviour at the moment the function gets evaluated on the DELETE stream. So it actually behaves like this (pseudo code) DELETE k OUTPUT Deleted.name, ROW_NUMBER() OVER (ORDER BY Deleted.object_id) as r FROM ( SELECT k.*, ROW_NUMBER() OVER (ORDER BY object_id) r FROM #o k ) k WHERE r <> 1 --OUTPUT returns rows ...


1

Try this instead: UPDATE student SET [Status] = CASE WHEN struc.Mode IS NOT NULL THEN 'Cleared' ELSE 'Not Cleared' END FROM FeeStudent student LEFT JOIN FeeStructure struc ON student.Mode = struc.Mode AND student.[Level] = struc.[Level] AND student.SEM = struc.Semester AND student.Paid = struc.Amount


0

If the company owns a database and has no DBA, somebody has to be capable of auditing that database directly, without using an application program as a go-between. Either that or the company is at the mercy of whatever enterprise supplies them with their applications. That person could be an in-house member of the IT staff. It could be the company's ...


0

If you need all users data who commented then inner join is enough as you have already implemented. SELECT com_des,com_doc,usr_name FROM tbl_user JOIN tbl_comment ON usr_id=stud_id above query will return only those records from tbl_user table where these users have records in tbl_comment. If a user in tbl_user has no corresponding record in tbl_comments ...


0

You need to design a webpage that will have a form as shown. Now on submit button click, you need use any server-side language like PHP/C#/Python...etc to convert those form data in a sql query. Something like INSERT INTO TABLE..... you can have some idea from these links PHP Form ASP.NET Form


2

I suggest your first option, with two improvements and some simplifications. ( SELECT 1 -- irrelevant what you select here FROM client_category_price WHERE sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460' LIMIT 1 -- may be redundant ) UNION ALL -- not just UNION ... UNION ALL ( SELECT 1 FROM work_order_item WHERE sellable_id = ...


0

I can understand that you feel some pieces that you are used to are missing. But that is only because they are missing. Nonetheless, SQL Server was being successfully used when Foreign Keys were just a concept (which we implemented through triggers in those days), not a physical implementation such as a constraint. Declarative Referential Integrity was ...


0

I suppose the data type of the result of the function SUBSTR(PARAM, ...) has the same length as the column PARAM itself. Since you have two of those in your select list, if the length of PARAM is large enough it's possible that the combined length of two values in the select list exceeds some sort of internal limit. Try explicitly casting the values to ...


0

First thing you should do is add a distribute for the tables on for the join on and organize on the where clause.


0

Your data is cached, this is why you are noticing a performance difference. Assuming you are in a development environment, issue these commands before running your query: dbcc freeproccache dbcc dropcleanbuffers Run the above commands to clear the cache, then run your query before indexes. Apply your indexes, run the two commands again, then run your ...


-2

I am guesisng you are creating a table and you are maxing out the column/row size. break apart into more tables. if that is true.


2

Try making a temp table with just the data you need and doing a join to that. For each self join do another temp table. I would start with one at a time and check the performance.


1

Since this old question has been dug up anyway, I'll mention that you can use the built-in XQuery support in DB2 for regular expression matching, something along the lines of select whatever from users where xmlcast( xmlquery('fn:matches($USER_NAME,"^a[aofdmep][a-z][a-z0-9]{4}[sidbfkfpo]")') as integer) = 1 XMLQUERY above calls the XQuery ...


0

Building on Leigh Riffel and Joe's answers, you might consider using LIKE when you have a long list of individual characters, or when you have multiple character ranges. SELECT * FROM (SELECT 'afr923zs' MyString FROM SYSIBM.SYSDUMMY1) T WHERE substr(MyString,1,1) = 'a' AND 'aofdmep' like '%'||substr(MyString,2,1)||'%' AND substr(MyString,3,1) ...


0

Since you have dropped your index and probably stats aren't up to date I'd suggest running ANALYZE against a table to get valid execution plan. MariaDB - ANALYZE TABLE


2

Using PIVOT: WITH combined AS ( SELECT o.customer_no, EXTRACT(month FROM o.entry_date) AS order_month, EXTRACT(year FROM o.entry_date) AS order_year, t.order_final_tot FROM orders o JOIN order_totals t ON (o.order_no = t.order_no) ) SELECT * FROM combined PIVOT ( SUM(order_final_tot) AS ...


0

Assuming: The record with the max tranno for a case also has the max date/time Combination of tranno and caseno is unique We can use the following query: SELECT a.caseno , a.date , a.time , a.tranno , b.pcode , c.pdesc , a.user FROM ( SELECT aa.caseno , MAX(aa.tranno) AS tranno FROM tablea aa WHERE ...


0

If (and only if) order_no is the primary (or a unique) key of order_totals, then you could use a similar CASE expression as in your first query, to get the order values: SELECT TO_CHAR(o.entry_date, 'MM') AS month, -- count orders SUM(CASE WHEN EXTRACT(year FROM entry_date) = 2013 THEN 1 ELSE 0 ...


1

Most likely the statistics on the field were out of date, adding the index create/updated statistics with a full table scan. More info on statistics https://mariadb.com/kb/en/mariadb/documentation/optimization-and-tuning/engine-independent-table-statistics/


0

Use CASE statement as following SELECT CASE WHEN ref_id = 'RIDERS' and subcod = 'DISABILITY' THEN description ELSE NULL END AS DISABILITY, CASE WHEN ref_id = 'RIDERS' and subcod = 'DISMEMBERMENT' THEN description ELSE NULL END AS DISMEMBERMENT, CASE WHEN ref_id = 'RIDERS' and subcod = 'MEDICAL' THEN description ELSE NULL END AS MEDICAL, CASE WHEN ...


2

SQL Server optimizer does constant folding, when possible. But is not a black-or-white issue, there are many shades of gray. See Compute Scalars, Expressions and Execution Plan Performance or Troubleshooting Poor Query Performance: Constant Folding and Expression Evaluation During Cardinality Estimation. You also need to read Conor vs. Runtime Constant ...


5

This is a typical "greatest N per group" problem which is usually solved using window functions: select caseno, date, time, tranno, pcode, pdesc, user from ( select a.caseno,a.date,a.time,a.tranno,b.pcode,c.pdesc,a.user, row_number() over (partition by a.caseno order by a.tranno desc) as rn from tablea a right join tableb b on ...


0

Unfortunately, there are no settings in SQL Profiler that will display the values the way you want them displayed with your insert .. values statement or any other similar statements. But, by using SP:StmtCompleted and SQL:StmtCompleted you should be able to see when the values are being assigned to the parameters. So you'll have an entry something like ...


0

One of Codd's principles was that a relational database should be able to look after itself using relational techniques. Following from this there has to be a table (or table-like thing) that holds the columns for a table and their relative ordering. This requires a definite data type to be declared for your columns' metadata and hence an upper limit on ...


0

mmmmmpie offers you a very viable way to do it. However, especially if you want to get your test environment as close to possible as prod for testing purposes, I highly recommend you look into RMAN cloning. You will end up with a block-for-block recreation of your production environment, which IMO is worth its weight in gold in regard to performance tuning ...


3

No, your case does not block on Oracle, nor should it. Concurrency is one the the major reasons to use an RDBMS instead of say, Excel. But just to make it interesting, if you insert the same value in both sessions, it will then block. Session #1 CREATE TABLE t1 (c1 NUMBER PRIMARY KEY); INSERT INTO t1 VALUES (1); -- do not commit transaction Session #2 ...


0

PROPOSED SOLUTION SET @currhash = MD5('dummy'); SELECT A.id,B.deptname,B.hours,A.rank FROM (SELECT (@prevhash := @currhash), (@currhash := MD5(depttype)), (@rnk:=IF(@prevhash=@currhash,@rnk+1,1)) rank, id,depttype FROM (SELECT id,LEFT(deptname,LEAST( IF(LOCATE('0',deptname)>0,LOCATE('0',deptname),99), ...


0

Create date tables with your ranges and do the join query. Assuming SQL Server as the database, my sample solution is given below. Declare @Range1Begin date = '10/16/2014', @Range1End date = '10/28/2014', @Range2Begin date = '9/22/2014', @Range2End date = '10/21/2014'; Declare @DateTable1 table (Date1 date); Insert into ...


0

Head First SQL was very helpful for me in terms of SQL. I also found Beginning Database Design From Novice to Professional to be very useful for understanding the Relational Model. The website SQL Zoo is also very useful for learning SQL. To be honest I wouldn't get so many books just on SQL - it isn't that hard, and Head First SQL explains it very ...


1

Just looking at your definitions (thank you very much for that) I see: No index on to support WHERE dbo.EntryTypes.EntryType = 'Error'. (But this may not be needed if it is a small table with just a few entries.) Your fk_* columns in MAIN have check constraints, but no indexes. You should create some indexes since they are used to join to several ...


2

I found the course at Standford to be helpful - if you sign up, you can view past lectures on many of these topics. https://class.stanford.edu/courses/Engineering/db/2014_1/info


3

Maybe something like this: with ranked_visits as ( SELECT w.website_id, v.visitor_id, count(wv.visit_id) as visits, row_number() over (partition by w.website_id order by count(wv.visit_id) desc) as rnk FROM website_visits wv JOIN websites w ON wv.website_id = w.website_id JOIN visitors v ON wv.visitor_id = ...


0

MySQL should not have trouble handling a large table like that. You have it indexed (because of the foreign keys) so most queries against the table should use one of the indexes and be fairly fast even if there are a huge number of rows in the table.


0

You could use MERGE command, something like this (this a dummy code, ok?): MERGE YourTale AS pi USING (SELECT col1,col2 FROM YourTable) AS src (col1,col2) WHEN MATCHED THEN UPDATE WHEN NOT MATCHED THEN DELETE You can learn more about MERGE command here: http://msdn.microsoft.com/pt-br/library/bb510625.aspx


1

From what I can see you have two problems. The first is the doubling. At a guess it is because of the SELECT statement in your INSERT statement. SELECT @NewMetricID, NULL, sm.sortorder, ISNULL(sm.MetricOrder, 1), ISNULL(sm.CategoryOrder, 1), sm.RptCurrentGroup, 'System', ...


0

Proposed Solution #1 ALTER TABLE cities ADD UNIQUE INDEX idx_city_state (city,state_code); ALTER TABLE cities_extended ADD UNIQUE INDEX idx_city_state (city,state_code); CREATE TABLE cities_new SELECT A.city,A.state_code FROM cities A LEFT JOIN cities_extended B USING (city,state_code) WHERE B.city IS NULL ; INSERT INTO cities_extended ...


2

If you are going to use custom collations for specific databases then yes, you'll need to make the collations match whenever you are joining or unioning data from the two databases. In fact you will need to do this with many metadata queries anyway. Just look at catalog views like sys.tables: SELECT c.name, c.collation_name FROM sys.all_columns AS c INNER ...


1

From Books Online database_default - Causes the COLLATE clause to inherit the collation of the current database. If you are executing your second query from the common database then the value from master.dbo.sysdatabases is being coerced into common's collation, not the other way around as you suppose.



Top 50 recent answers are included