Tag Info

New answers tagged

0

You should use ID as bigint instead of numeric(18,0) if state_id is more "selective" then region_id, you should make index on (state_id, region_id).


0

I like simply catching the unique_violation exception as proposed here: Optimal way to ignore duplicate inserts? Ignore Duplicates #1 Create a transaction that catches unique constraint violations, taking no action: BEGIN INSERT INTO db_table (tbl_column) VALUES (v_tbl_column); EXCEPTION WHEN unique_violation THEN -- Ignore duplicate ...


1

Instead of SSIS I think you might want to look into SQL Server Service Broker. This lets you send messages asynchronously. See here or here for a start. MSMQ or RabbitMQ may also be options you should consider.


0

Nothing you would not normally do. 4 million records is TINY - this is 2015, not 1990, when Foxpro was introducnig the capabillity to work with millions of rows comfortable. Doing a query on suburb or street name is fine, as soon as i do a query on region_id it takes minutes This is not optimization ,it is basic db design that is missing, likely ...


2

What you need to do is use the LEAD windowing function to look at one row down, two rows, three rows, etc for however long your pattern is. Example: LEAD(gradeissued,1) over (partition by studentid order by whenissued) as one_row. Put that in a CTE or subquery and then in the next query do a where criteria on your pattern (one_row = 'D' and two_row = 'C' and ...


5

One way of doing it would be to use LEAD. WITH T AS ( SELECT StudentID, GradeIssued AS g1, LEAD(GradeIssued, 1) OVER (PARTITION BY StudentID ORDER BY WhenIssued) AS g2, LEAD(GradeIssued, 2) OVER (PARTITION BY StudentID ORDER BY WhenIssued) AS g3, LEAD(GradeIssued, 3) OVER (PARTITION BY StudentID ORDER BY WhenIssued) AS g4 ...


0

I assume there is a UNIQUE index on the column with "Dave" and "Bob"? Without that, how do you know what is "new"? Let's call the column name (distinct from id). Does some other table depend on Dave=5? If so, the following will not suffice, and the solution is much messier. Are there changes to the columns? Or is it only "new" rows to be copied from A ...


0

There are two ways: first - using regexp_replace like here - Eliminate duplicates in ListAgg (Oracle), second - use distinct inside a subquery: select fk_proddes_id,fk_id_cod_deseu, listagg(to_char(cod_eliminare), ',') within group (order by cod_eliminare desc) from (select distinct fk_proddes_id,fk_id_cod_deseu, cod_eliminare from ...


0

MySQL has a messy way of doing subqueries that are mixed with DML. I mentioned why in my answer to Problem with MySQL subquery @ypercube also suggested something in that same post. Perhaps you could delete by auto_increment primary key (like id) create temporary table delete_keys engine=MyISAM select id from table1 A inner join table2 B on A.column1 = ...


0

I don't know which data access technology you are using, but certainly in plain ADO.NET it's definitely a supported feature to have queries that return multiple resultsets. For example, if you have a SqlDataReader, you step through the resultsets with NextResult. It would then be a straightforward matter to read each of the three resultsets in turn and see ...


0

SELECT [UserName] = ulogin.[name], [DatabaseUserName] = princ.[name], [Role] = null, [PermissionType] = perm.[permission_name], [PermissionState] = perm.[state_desc], [ObjectType] = CASE perm.[class] WHEN 1 THEN obj.type_desc -- Schema-contained ...


0

The INDEX has been applied to every fields on ON-CLAUSE and AND-CLAUSE. Please learn about "compound indexes". Please provide EXPLAIN SELECT .... AND wi_training.trn_deleted = 0 AND wi_training.trn_beneficiary_type = 2 AND wi_training.trn_start_date <= '2015-06-30' Begs for the compound INDEX(trn_beneficiary_type, trn_deleted, trn_start_date). A ...


1

Use: A foreign data wrapper like odbc_fdw or tds_fdw (see foreign data wrappers list, foreign data wrapper documentation); or dbi-link - see github repo


1

If you want to change the current\default database to a different one try: alter login <loginname> with default_database = <dbname>; Now, create a user for above login created use <dbname>; create user <username> from login <loginname>; And now you can assign roles to the above create user for the login as below: use ...


3

USE msdb; GO CREATE USER shims FROM LOGIN shims; GO ALTER ROLE SqlAgentUserRole ADD MEMBER shims; GO Also, for future reference, any time you know how to do something in the UI but not in a script, this is what the Script option on most dialogs is for - it will show you what script SSMS would have executed:


2

Would you like to try this? DECLARE @cmd1 nvarchar(2000) SET @cmd1 = 'IF ''?'' NOT IN(''Name of your database'')' + 'BEGIN ' + 'drop DATABASE [?] ' + 'END' EXEC sp_MSForEachdb @command1 = @cmd1 GO it worked here. PAY ATTENTION TO NOT DROP MASTER AND ETC. Put then on that list.


3

How about something like below : change your output in SSMS to text and once you are happy run the generated sql statements in another query window : use master go select 'Alter database '+ quotename(name) + char(10) +'set single user with rollback immediate' +char(10)+ 'go'+ char(10)+ 'Drop database '+quotename(name) + char(10)+ 'go' from ...


2

After setting the db to singleuser you have to switch back to master. I added a use master statement but I don't want to test this.... EXEC sp_MSforeachdb ' USE [?] IF (DB_ID(''?'') > 4 AND DB_NAME()!=''ABC'') BEGIN ALTER DATABASE ''?'' SET SINGLE_USER WITH ROLLBACK IMMEDIATE **use master;** DROP DATABASE ''?'' END


0

select rap.DENUMIRE,a3.ID, op.FK_ID, count(*) over (partition by op.FK_ID) AS count from ANEXA3_TABEL1_ROWS a3 inner join RAPORTORI_AMB rap on rap.id = ch.FK_ID_RAPORTOR inner join ANEXA3_TABEL1_OP op on op.FK_ID = a3.ID


2

If the database is in full recovery mode and you are doing periodic log backups then you can find the login that deleted the data, and the exact moment the data was deleted. Read How to read and interpret the SQL Server log. Also, with a proper backup strategy in place, it will be trivial to recover the deleted data as well. On the other hand, if you do not ...


0

object_id() function is your friend: declare @capture_instance sysname select @capture_instance = capture_instance from cdc.change_tables where source_object_id = object_id('dbo.myTable)


2

Check out Drupal and RDF. Check out what StackExhange (etc) uses. And study how GIT works. I recommend keeping the current value in the primary table, and put the history in other table(s). That is, plan for efficient access to the current value, at the expense of history. If you are talking about "articles" of, say, 1K characters, then compress them. ...


1

One option would be if you had a server side trace running while that incident happened or you have to dig into the transaction logs using fn_dblog (this is undocumented and unsupported). Below query might help if the data is still persistent in DMV: --- who did what ?? SELECT cr.DatabaseName ,s.session_id ,s.host_name ,s.program_name ...


0

One way to understand what's going on with normalization is to find out what normalization buys you. In other words, what's going to happen if you depart from any given normal form? For normalization forms second through fifth normal forms, the answer is "update anomalies". That is, when you insert, update, or delete rows, can you end up leaving things ...


-1

SELECT GROUP_ID, Group_Name, SUM(Price) FROM ProductsTable GROUP BY GROUP_ID, Group_Name


2

To count how many contributors have contributed 5 images or more: SELECT COUNT(*) AS number_of_contributors FROM ( SELECT 1 FROM images GROUP BY contributor_id HAVING COUNT(*) >= 5 ) AS t ; It could be written without the derived table but it's obfuscated: SELECT COUNT(*) OVER () AS number_of_contributors FROM images GROUP BY ...


0

a SUM is an aggregation of data, when working with aggregations you can specify the clause GROUP BY to show results grouped by what you need. SELECT Group_Name, SUM(Price) FROM Table GROUP BY Group_Name


0

Multiple aspects needs to be considered when creating an index. An index on columns A,B is not the same as an index on B,A. the performance of a query which filters on both column A and B will be different depending on the order of the columns in the index. Indexes slow down DML operations and increase the size of the database. You can refer to the ...


8

Update apotable set price1 = 0, price2 = 0, price3 = 0 where po_number IN ('x', 'y', 'z'); Note that numbers should not be enclosed in single quotes 0 is a number, '0.00' is a character literal (aka "string"). There is no need to search the web for the syntax of a SQL statement. That is all documented in the manual: ...


1

1st case You seem to forget the valid_during range. As your third case suggests, there can be multiple entries per (rec_id, val), so you must select the right one: UPDATE master m SET valid_on = f_array_sort(m.valid_on || u.valid_on) -- sorted array, see below FROM updates u WHERE m.rec_id = u.rec_id AND m.valid_during @> u.valid_on -- ...


2

There is absolutely nothing about this on the Internet - even the Oracle docs regarding the %_ASSEMBLIES views don't actually state what they show. However, if you go digging around in $ORACLE_HOME/rdbms/admin you'll see the following reference to the data dictionary views (which sit on assembly$: remark remark FAMILY "ASSEMBLIES" remark remark Views for ...


0

I think this page has what you are looking for - Assembly: Assembly is Microsoft's term for the module that is created when a DLL or .EXE is complied by a .NET compiler. If I understand it correctly it is the .NET equivalent for Java's "class files". Starting 11g, you can develop and deploy .NET procedures and functions in Oracle database. Here ...


1

(Can post this as comment due to lack of rep) This link could be helpful to you: http://www.mssqltips.com/sqlservertip/2531/sql-server-compressed-backups-disk-space-needs/


5

You need to use a second CTE for the INSERT: WITH upsert AS ( UPDATE tbl SET a = 2 WHERE a = 1 RETURNING tbl.* ), inserted AS ( INSERT INTO tbl (a) SELECT 1 WHERE NOT EXISTS( SELECT * FROM upsert ) RETURNING * ) select * from upsert union all select * from inserted


1

Simply include in your model a choice table for each type of choice that your Your field choice_type_id is essentially a kind of partitioning attribute. It indicates what type of child table is applicable. So far you have three tables: MULTIPLE_CHOICE, BINARY_CHOICE, and FREE_TEXT (although you might consider binary choice to be a special case of multiple ...


3

Use this one: DROP INDEX SYSTEM."IX_isbn_send_h.D_STAT"; Note, when you use quotes, then names are case-sensitive in Oracle.


2

A check in the HAVING clause would do the trick: SELECT author_id, count(*) AS i_count FROM images GROUP BY author_id HAVING max(date_added) > min(date_added) + interval '30 days' ORDER BY i_count DESC;


2

One method is to introduce a PersonType attribute. Using this as a composite key along with a check constraint and foreign key in the Student and Teacher tables will ensure a row for a given person exists in only one of those entities and in the proper table. I can't speak to how well this implementation will get along with EF. There are similar ...


2

Add a student/teacher attribute to Person. Since this attribute is dependent of the key in Person (whatever that is), no Person can be both a Teacher and a Student. Now it is a matter of guaranteeing that a person whose type is student is not added to Teacher etc. For DBMS:s that support queries in check constraints you can do something like: ALTER TABLE ...


2

The amount of details logged is controlled by log_error_verbosity. Setting it to TERSE will exclude DETAIL, HINT, QUERY, and CONTEXT error information: ALTER DATABASE db_name SET log_error_verbosity to 'TERSE';


1

The question would have been a better candidate for stackoverflow. Anyways, the problem with your SQL is that the GROUP BY should only be on the author name and not on author name + book title. From the GROUP BY on author name, get the max(number_of_pages) and wrap it inside another SELECT to to get the books by that author that have that those number of ...


3

Assuming MySQL, if you execute EXPLAIN EXTENDED on both queries, you get: mysql> EXPLAIN EXTENDED SELECT S.ID,S2.Value FROM dbo.Sample AS S -> INNER JOIN dbo.Sample2 AS S2 ON -> S.ID = S2.ID; +----+-------------+-------+--------+---------------+------+---------+----------+------+----------+-------------+ | id | select_type | table | type ...


1

This would be very simple in SQL. As far as I can read the BigQuery Query reference, it supports all (GROUP BY clause, COUNT() and SUM() functions, CASE expressions): SELECT custID, InteractionDate, 1.0 * COUNT(CASE WHEN Purchased = 'T' THEN 1 END) / COUNT(*) AS Success, SUM(Sales) AS Sales FROM tableName GROUP BY custID, ...


2

You can use this one: SELECT custID, InteractionDate, IFNULL(ROUND((yes)/(yes+NO), 2),0) AS Success, sales FROM (SELECT custID, InteractionDate, sum(if(Purchased=='T',1,0)) AS yes, sum(if(Purchased!='T',1,0)) AS NO, sum(sales) AS sales FROM (SELECT 1 AS custID, 20150312 AS ...


0

SQL is a real (but **DECLARATIVE**, not imperative) programming language! This has its pros and cons, see here. But without the sarky comment from @RickJames :-), I was going to say the same thing. You'll always get problems like this when you don't properly normalise (you have what are called repeating groups - a breach of Codd's Rule no. 2 - scalar ...


2

A query takes some combination of CPU and I/O. It might be 100% and 0% if everything it needs is cached in RAM, or it might be 1% and 99% if nothing is cached. Or anywhere in between. Only one "core" of the CPU will be used for one connection in MySQL. Multiple connections could use all the cores; but I rarely see that. Another issue is "locks" or ...


3

As your administrator I'd like to confirm that the problem has been caused by firewall restriction. SQL port is opened so you can complete linked server setup. Cheers, mate


1

There are many ways. Here is one (should be a fast variant to retrieve all rows): SELECT t1.*, t2.* FROM table1 t1 JOIN ( SELECT DISTINCT table_1_id, table_2_id FROM table1_2 ) t1_2 ON t1_2.table_1_id = t1.id JOIN table2 t2 ON t2.id = t1_2.table_2_id; Why not remove the duplicates permanently? And add a UNIQUE constraint ...


0

The tables can be queried in a few different ways depending on what you want to do. If you wish to (Example 2) extract all the Reviews with a particular Category you can create a procedure like this: SELECT review_id, professional_rating, efficiency_rating, referral_rating... FROM Review r INNER JOIN Review_Category rc ON r.review_id = rc.review_id INNER ...


1

You'd need to use the UCA (Unicode Collating Algorithm): the COLLATE value would be something like UCA500R1_LPO_CU for Polish and uppercase first. More info here. Note that you cannot change the collating setting of an existing database; you would have to create a new one with the required collation and move your data.



Top 50 recent answers are included