Database Design - One table for different types of transactions Considering a procurement system as an example. Would it make sense to have one single table for the following: Quote, Order, Invoice and Credit Memo?

There will be some details that are different. For example each list will have its 5 special fields but the other say 50 fields would be exactly the same. Would it make sense in this case to join all tables in a single one. And some fields are unused for some types.

I can see that Navision system does something like that in its database. It uses one table, but then splits into separate tables once the items are posted. I think this may be for performance reasons because the posted items can tend to become very large. & splitting into separate tables would speed up queries. But this is just my thinking.

So my main dilemma is ... what is the recommended approach in such case: 1 table or separate tables? what are the pros and cons of each? What are the business considerations to take into account?

Thanks for your help!

There are essentially three options (with the possibility of complexity via combination of the three options):

Keep each table separate
Bring all of the tables together as one major table
Use an associative relationship:  a base table with the common columns, and individual tables for the non-common columns

Here are some of the factors to consider:

Do these really represent the same entity?  If so, what is that entity?  Based on your examples (Quote, Order, Invoice, and Credit Memo), these are separate entities to me.  They are 'related' but are not the same or subtypes of a master entity (such as having an Employee table and ExemptEmployee and NonExemptEmployee subtables which have fundamentally different attributes).  This tilts in favor of keeping tables separate.
Do you need to query against Quote UNION Order UNION Invoice?  Note that this is different from joining Quotes to Orders (to get, say, a percentage of successful sales).  If you do need to do those types of queries often, that argues in favor of a single table.  I could see doing this if you're looking at "the life of an order."  On the other hand, that's still a simple query even when split out into several tables, so it's a minor consideration.
Are there columns which are not common to all of the types?  You mentioned that there are several special fields per type.  This means you probably do not want to go with option #2 in any case, because option #3 re-introduces relatively simple integrity constraints.  Suppose that, in your big table, you have three columns which are required if you are doing an Order, but should be NULL otherwise.  Doing this is trivial with associative tables, but requires check constraints with the One Big Table approach.  And if column X for an Order needs to be numeric but for an Invoice needs to be text, that just makes validation way more difficult.
Do you ever change types?  Not just copying Quote data into an Order, but actually changing the Quote itself to an Order.  If so, that's an argument in favor of one big table.  But I don't think that's a realistic business process.
How many orders do you normally get?  The more orders you get, the more you want to split out those tables.  A scan against X pages is still better than a scan against 4X pages, and separate tables increases the likelihood that you'll be able to do seeks instead of scans because of finer-grained control of indexes.

Based on these considerations, I would personally keep the tables separate.  Even though they may have similar attributes, they are fundamentally different entities.

What I would try to do, however, is look at those sets of common attributes and see if they really belong on the Quote/Order/Invoice/Credit Memo, or if normalizing those out into their own tables would be a better idea.  For example, if you have customer first name, customer last name, customer street 1, etc., splitting that out into a customer key (or customer and address keys, depending upon circumstances) could cut down on the total number of columns in each of the four major entities, reducing repetition and gaining the other benefits of normalization.

It's possible that you've already normalized everything out to the fullest extent and that each of those entities does, in fact, legitimately have 50 attributes which are in common.  In that case, I'd still keep separate tables because you're talking about fundamentally different things which happen to relate to one another.


Heavy load of one of shards in MongoDB I have a problem with one of our shards. For some reason I'm getting the next usage:

  15:38:42 up 4 days, 10:28,  1 user,  load average: 3.67, 3.50, 3.56

In the mean time all other shards are ok. Their average load approx:

  16:49:58 up 11 days, 56 min,  1 user,  load average: 0.03, 0.03, 0.05

I have 3config, 2 query and 7 shards (all of them are primary replicaset. don't ask why).

I have 2 databases: 20Gb and 30gb. All servers are the same in their configuration. Also, in each database only 1 collection. In MMS control panel I see:

Maybe a problem here?
How I can investigate this issue and fix it?

As I'm new with MongoDB I have used the next article to configure my shards:

https://stackoverflow.com/questions/19672302/how-to-programatically-pre-split-a-guid-based-shard-key-with-mongodb

Please check 1st accepted answer. Or how can I sheck my shard key myself?

The problem explained

As per your comment, your shard key is the _id field of the document. This field is monotonically increasing, basically like an incremented integer.

Put simply, sharding works this way: documents are stored in chunks. Those chunks are spread over the cluster based on ranges of the shard key. Let's look at a simple example:

s1: chunks holding id 0-50
s2: chunks holding id 51-100
s3: chunks above 101-?

And here is where the problem occurs: there is one shard were all new documents go to. What happens then is that new chunks are created all the time (as chunks have fixed size). So not only are all writes focussed on one server, this server also has to do some overhead work. After a certain threshold is met, the cluster will start to move chunks to the other shards, which only will balance out disk space, though. In out example, it might look like this:

s1: chunks holding id 0-100
s2: chunks holding id 101-200
s3: chunks holding id 201-?

Obviously, all write operations will still go to s3.

What went wrong?

You chose the wrong shard key. Monotonically increasing shard keys lead to the problem explained above. Altough ObjectIds look like hash sums, but they aren't. They are monotonically increasing.

What can be done?

You need a better shard key. Since a shard key can not be changed after a collection is sharded, migrating to a new shard key is not a simple thing to do. However, there is a quite detailed explanation in mongoDB's ticket system. Basically, it works like this:

Remove all but one shard from the cluster
Wait for the chunk migrations to be finished
Shut down the remaining shard
Change the shard key through a mongos instance
Restart the last shard
Add the other shards to the cluster


Unable to locate database.mdf in production server 1) I want to attach a database from my development server to my production server.

The database ESpiceHerbs from my development server is located at:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA

2) When I log in using my production server credentials and right click on the Databases folder in Object Explorer and select Attach Databases and click Add,  I cannot find it there! :(

3) I am really confused as to why it cannot be located although its pointing to the same folder.

Could it be because the development server is running on 32 bit and the production server is running on 64 bit?

Might be a rights issue. Have a look at the security settings on the physical .mdf file and make sure the user you are using for SSMS has access to that .mdf file.

Most probably, as you are supplying credentials, you are logging on with your Windows user id, so make sure that you have read-write priviliges on the .mdf file.

To get to the security settings:
Explore -> Locate your file -> Right click -> Properties -> Security Tab

Your user group or your user should be in that list.


How to access a SQL Server database using another user with permissions in the same intranet While I'm waiting for my user to get access permissions, I'm stuck using a colleague's, and whenever I try to run basic SQL queries I have to switch from my Windows user to hers.

Hopefully this is clear enough: I want to access a SQL Server for which another user on the same intranet has permissions while I only have her credentials, and that the server utilizes Integrated Security.

You can open SSMS as her Windows user account by doing either of the following:

Right click the SSMS shortcut while holding the SHIFT key. Click on Open as another user and enter the other users credentials.
Create a shortcut in the following format:

  C:\Windows\System32\runas.exe /savecred /user:DOMAIN\USERNAME
  "C:\Program Files (x86)\Microsoft SQL
  Server\140\Tools\Binn\ManagementStudio\Ssms.exe"

You'll need to replace the path to SSMS.exe with your version.

Once you open the shortcut and enter the password for the account, you can then simply double click the shortcut in the future as it will store your credentials for later use.


SQL Server 'ignores' drop table command I have a 500+ lines stored proccedure which I am writting and testing using the Microsoft SQL Management studio. Today I encounter the following 'strange' case. My query (in pseudocode) look like the following

create table #TempTable1 (Name varchar(30))

-- Some queries involving #TempTable1

drop table #TempTable1

-- Some queries

declare MyCursor cursor fast_forward for
select ......

open MyCursor

fetch next from MyCursor into @CursorValue
while @@fetch_status = 0
   begin

       create table #TempTable1 (Name varchar(30))

       -- Some queries involving #TempTable1

       drop table #TempTable1

       fetch next from MyCursor into @CursorValue

    end

close MyCursor
deallocate MyCursor

My query fails with an error in the second create table

  There is already an object named '#TempTable1' in the database.

I know that due to query optimizations the commands are not running in the sequence I am typing them. However this error still seems strange to me. I can put some go commands to force the execution, but this will create some other problems in my code e.g. I will have to redeclare some variables e.t.c. Renaming the tables will solve the problem but it will harm a little the readabillity of the code. So it is more 'curiocity' than an 'actual problem'.

Any suggestions on why this is happening and how I can avoid it?

You can see you are creating temptable with same name.You cannot create temp table with same name in single query or batch this is documented as per BOL document. From Bol document

  If more than one temporary table is created inside a single stored procedure or batch, they must have different names.

You should use below logic it will just check before creating and deleting

IF OBJECT_ID('tempdb..#Temptable1') IS NOT NULL

   DROP TABLE #Temptable1

CREATE TABLE #Temptable1(name nvarchar(30))

Which would make your query like

IF OBJECT_ID('tempdb..#Temptable1') IS NOT NULL

       DROP TABLE #Temptable1

    CREATE TABLE #Temptable1(name nvarchar(30))

-- Some queries involving #TempTable1

   IF OBJECT_ID('tempdb..#Temptable1') IS NOT NULL

       DROP TABLE #Temptable1

-- Some queries

declare MyCursor cursor fast_forward for
select ......

open MyCursor

fetch next from MyCursor into @CursorValue
while @@fetch_status = 0
   begin

          IF OBJECT_ID('tempdb..#Temptable2') IS NOT NULL--new temp table

       DROP TABLE #Temptable2

    CREATE TABLE #Temptable1(name nvarchar(30))

       -- Some queries involving #TempTable1

        IF OBJECT_ID('tempdb..#Temptable2') IS NOT NULL

       DROP TABLE #Temptable2

       fetch next from MyCursor into @CursorValue

    end

close MyCursor
deallocate MyCursor


What are the pros and cons of using CLIENT_RESULT_CACHE_SIZE and RESULT_CACHE_MODE I have multiple questions

For Oracle Database 11g Release 11.2.0.1.0

what are the benefits of using CLIENT_RESULT_CACHE_SIZE and RESULT_CACHE_MODE.
How will the CLIENT side cache be kept sync if data changes at server side.

Do we have a AUTO mode for RESULT_CACHE_MODE? What is the recommended mode for this?

If i execute a single query 10 times with CLIENT_RESULT_CACHE enabled will the query be run 10 times at the server?

Please point me towards any documentation regarding this.Thanks in advance

What are the benefits? - Faster data access with less disk access.

How will the client side cache be kept in sync?  From the Oracle Call Interface Programmer's Guide:

  The client cache transparently keeps the result set consistent with
  any session state or database changes that can affect its cached
  result sets.

  When a transaction modifies the data or metadata of any of the
  database objects used to construct that cached result, invalidation is
  sent to the OCI client on its subsequent round-trip to the server. If
  the OCI application does no database calls for a period of time, then
  the client cache lag setting forces the next OCIStmtExecute() call to
  make a database call to check for such invalidations.

  The cached result sets relevant to database invalidations are
  immediately invalidated, and no subsequent OCIStmtExecute() calls can
  match such result sets. The OCI statement handles currently fetching
  from these cached result sets, at the time such invalidations are
  received, can continue fetching from this (invalidated) cached result
  set.

  The next OCIStmtExecute() call by the process may cache the new result
  set if there is space available in the cache. The OCI client result
  cache periodically reclaims unused memory.

  If a session has a transaction open, OCI ensures that its queries that
  reference database objects changed in this transaction go to the
  server instead of the client cache.

  This consistency mechanism ensures that the OCI cache is always close
  to committed database changes. If the OCI application has relatively
  frequent calls involving database round-trips due to queries that
  cannot be cached, (such as DMLs, OCILob calls, and so on) then these
  calls transparently keep the client cache up-to-date with database
  changes.

  Note that sometimes when a table is modified, a trigger can cause
  another table to be modified. OCI client result cache is sensitive to
  all such changes.

  When the session state is altered, for example, if NLS session
  parameters are modified, this can cause the query results to be
  different. The OCI result cache is sensitive to such changes and on
  subsequent query executions, returns the correct query result set. The
  current cached result sets are kept (and not invalidated) for any
  other session in the process to match; otherwise, these result sets
  get "Ruled" after a while. There are new result sets cached
  corresponding to the new session state.

  If the application must keep track of all such database and session
  changes it can be cumbersome and prone to errors. Hence, OCI result
  cache transparently keeps the result sets consistent with any database
  or session changes.

  The OCI client result cache does not require thread support in the
  client.

*Do we have an auto mode for RESULT_CACHE_MODE?* - The Oracle Database Reference shows that the two options for this setting are MANUAL and FORCE.

What is the recommended mode for this? - This would depend on what your needs are.

To understand the benefits of the result cache, look at the Validation of the Client Result Cache of the Oracle Call Interface Programmer's Guide referenced earlier.


How to combine "SELECT" and "REPLACE INTO" when combining two MySQL tables into another? I've got data with measurements split between two different tables, but the data doesn't need to be present in both:

mysql> SELECT * FROM t1;          mysql> SELECT * FROM t2;
+----------------+-------+        +----------------+-------+
| measurement_id | value |        | measurement_id | value |
+----------------+-------+        +----------------+-------+
| 1              | data1 |        | 1              | NULL  |
| 2              | data1 |        | 2              | data2 |
| 3              | NULL  |        | 4              | data2 |
| 5              | data1 |        | 8              | data2 |
| 8              | data1 |        | 9              | data2 |
| 9              | NULL  |        +----------------+-------+
+----------------+-------+

My users need to be able to combine the two tables into a third table, depending on their choice of whether data1 should take precedence over data2, whether NULL data should be copied, etc.

An example of one such possible user-requirement combination is "fill t3 with data from t1 unless there is non-NULL data from t2 to replace NULL or non-existing data from t1."  The resulting table would be

mysql> SELECT * FROM t3;
+----------------+-------+
| measurement_id | value |
+----------------+-------+
| 1              | data1 |
| 2              | data1 |
| 3              | NULL  |
| 4              | data2 |
| 5              | data1 |
| 8              | data1 |
| 9              | data2 |
+----------------+-------+

The way I implement that condition is the following SQL commands:

INSERT INTO t3 SELECT * FROM t1;
REPLACE INTO t3 SELECT * FROM t2 WHERE t2.measurement_id NOT IN (SELECT measurement_id FROM t1) OR t2.measurement_id IN (SELECT measurement_id FROM t1 WHERE value IS NULL);

There are other example, more simple or more complicated, I can give, but they all rely on first copying all data from one table into another, then doing a REPLACE INTO to overwrite data on conditions.

My question is: How can I combine the above INSERT and REPLACE INTO commands into a single command so I'm not copying data from t1 that will just be replaced in the second command?

as variant:

INSERT INTO t3

SELECT t1.measurement_id,
CASE WHEN t1.value IS NULL THEN
     t2.value
ELSE
     t1.value
END as value

FROM t1 LEFT JOIN t2 ON t2.measurement_id = t1.measurement_id AND t1.value IS NULL

UNION ALL

SELECT
     t2.measurement_id,
     t2.value
FROM t2 WHERE t2.measurement_id NOT IN (SELECT measurement_id FROM t1)

as benefits - no REPLACE


Hard Parse on Non-Identical Statements? Let's say I have this query :

SELECT X FROM Y

If I run it for the first time then it would do a hard parse.

After that, I run this query :

select x from y

With just a difference in alphabetical upper and lower case, does it mean they have to do the hard parse again?

Thank You.

Yes, those are two different queries. They'll both be parsed and saved in the SQL area independently.

SQL> create table share_test (foobar number);
Table created.

SQL> select foobar from share_test;
no rows selected

SQL> SELECT foobar FROM SHARE_TEST;
no rows selected

SQL> select sql_id, sql_text from v$sqlarea where sql_text like '%foobar%';
SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
g9cdv3mpuhd8d
select sql_id, sql_text from v$sqlarea where sql_text like '%foobar%'

d2v4u39j41y6k
select foobar from share_test

fdsqytc1y3f4u
SELECT foobar FROM SHARE_TEST

The criteria for SQL sharing are in the Configuring and Using Memory - SQL Sharing Criteria docs. Among others:

  The text of the SQL statements or PL/SQL blocks must be identical, character for character, including spaces, case, and comments.


mysql load data local infile is skipping the 1st column of the 1st row We have a PHP script that has been running from the command line on a WAMP server for a few years. I am now converting it to run on Linux (AWS EC2 AMI instance). The script basically loads data from a csv into a mysql table. It is almost working but for some reason it skips the data in the 1st column of the 1st row. Here is the sql:

LOAD DATA LOCAL INFILE '/var/scripts/_output/ref_dtl_output.csv'
REPLACE INTO TABLE  MY_TABLE.ref_dtl

FIELDS TERMINATED BY ','
ENCLOSED BY ''

LINES TERMINATED BY '\n'

The files it's loading are anywhere from a few hundred bytes to > 100K bytes. In every case, whether it's getting thousands of rows, or 1 row, the 1st column of the 1st row is always "0" or an empty string. I have checked every csv, and there is data there. This never happened on the Windows server.

I tried running the SQL above from within the PHP script, and I also tried using HeidiSQL to import it and got the same results.

CREATE TABLE `ref_dtl` (
`site_id` INT(11) NOT NULL,
    `business_date` DATE NOT NULL,
    `trans_seq` INT(10) NOT NULL,
    `dtl_seq` INT(10) NOT NULL,
    `parent_trans_seq` INT(10) NOT NULL,
    `parent_dtl_seq` INT(10) NOT NULL,
    `ref` CHAR(20) NOT NULL,
    `ref_type` CHAR(3) NOT NULL,
    PRIMARY KEY (`site_id`, `business_date`, `trans_seq`, `dtl_seq`, `parent_trans_seq`, `parent_dtl_seq`),
    INDEX `site_id` (`site_id`),
    INDEX `business_date` (`business_date`),
    INDEX `trans_seq` (`trans_seq`),
    INDEX `dtl_seq` (`dtl_seq`),
    INDEX `parent_trans_seq` (`parent_trans_seq`),
    INDEX `parent_dtl_seq` (`parent_dtl_seq`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

Any ideas for how to fix?

Depending on the source of the text file, the csv file here, it may have the byte of order (BOM) character.

In this case, the very first column from fist row will be an invalid integer, and will be saved as 0 in the DB.

To check if the file has BOM letters at its beginning, refer to this link.


Why we design distributed availability group? In my existing environment, there is a design proposed for using distributed AG. Can you please let me know few my queries regarding this?

What is the distributed high availability benefit which we can not leverage with traditional AG? If you can explain one scenario of failover which we can not avail in traditional AG?
In which scenario the design of distributed AG is preferable?

What is the distributed high availability benefit which we can not leverage with traditional AG? If you can explain one scenario of failover which we can not avail in traditional AG?

Distributed availability groups are a new feature introduced in SQL Server 2016, as a variation of the existing Always On Availability Groups feature. No announcements have been made as to whether future versions of SQL Server will allow previous versions to participate in the same distributed availability group. If that scenario were enabled, it would allow distributed availability groups to be part of a SQL Server version upgrade plan.

For your understanding I have just copy and paste the appropriate stanza articles from the documentation for Distributed availability groups:

  A distributed availability group is a special type of availability group that spans two separate availability groups. The underlying availability groups are configured on two different Windows Server Failover Clustering (WSFC) clusters. The availability groups that participate in a distributed availability group do not need to be in the same location. They can be physical, virtual, on-premises, in the public cloud, or anywhere that supports an availability-group deployment. As long as two availability groups can communicate, you can configure a distributed availability group with them.

  The following figure shows a high-level view of a distributed availability group that spans two availability groups (AG 1 and AG 2), each configured on its own WSFC cluster. The distributed availability group has a total of four replicas, with two in each availability group. Each availability group can support up to the maximum number of replicas, so a distributed availability group based on Standard Edition can have up to four replicas, and one based on Enterprise Edition can have up to 18 total replicas.

  The individual WSFC clusters and their corresponding availability groups follow traditional rules. That is, they can be joined to a domain or not joined to a domain (Windows Server 2016 or later). When two different availability groups are combined in a single distributed availability group, there are four scenarios:

  Both WSFC clusters are joined to the same domain.
  Each WSFC cluster is joined to a different domain.
  One WSFC cluster is joined to a domain, and one WSFC cluster is not joined to a domain.
  Neither WSFC cluster is joined to a domain.

Your second question:

  In which scenario the design of distributed AG is preferable?

Again, quoting from the same documentation:

  Here are the three main usage scenarios for a distributed availability group:

  Disaster recovery and easier multi-site configurations
  Migration to new hardware or configurations, which might include using new hardware or changing the underlying operating systems
  Increasing the number of readable replicas beyond eight in a single availability group by spanning multiple availability groups


using string concatenation to reference a table For a database systems project which tracks entities histories, I stumbled acros the following problem.

Assume we have the table "entity"

CREATE TABLE entity (
    eID SERIAL PRIMARY KEY,
    eName VARCHAR(255) NOT NULL,
    eValue1 INTEGER,
    eValue2 INTEGER
);

as well as "entity_history"

CREATE TABLE entity_history (
    eID_FK INTEGER REFERENCES entity(eID),
    time TIMESTAMP DEFAULT current_timestamp,
    type VARCHAR(6) CHECK(type IN ('Value1','Value2')),
    val INTEGER,
    importantHistoricalEntryNotInMainTable TEXT
    UNIQUE(eID_FK, time)
);

Usually, the idea is that the history gets an entry on update/insert of the main table.
I have to do it the other way around (due to the entitys history being created based on external website information which is not stored in the main table upon insert), so whenever an entry is added to the history, it should update the current value of the field in the main table.
Thus I tried using

CREATE RULE update_main_table AS ON INSERT TO entity_history
DO
UPDATE entity SET ("e" || NEW.type) = NEW.val;

wwhich yields a syntax error.

You can't use variables for columns. So you would need to do something like this:

CREATE RULE update_main_table AS ON INSERT TO entity_history
DO
UPDATE entity
   SET eValue1 = case
                    when new.type = 'Value1' then NEW.val
                    else eValue1
                 end,
       eValue2 = case
                    when new.type = 'Value2' then NEW.val
                    else eValue2
                 end;

Now I guess you didn't show us the whole picture and you have more than just two columns in the entity table (which will make the above update statement very inefficient and cumbersome).

The whole thing looks like a variation of the (anti) pattern named "entity-attribute-value". As you are using Postgres, I would highly recommend using the hstore data type to store key value pairs.

At least in the entity table you could do that:

CREATE TABLE entity (
    eID     SERIAL PRIMARY KEY,
    eName   VARCHAR(255) NOT NULL,
    value   hstore
);

Then you could just do:

CREATE RULE update_main_table AS ON INSERT TO entity_history
DO
UPDATE entity
   SET value = value || hstore(NEW.type, NEW.val::text)
WHERE eid = new.eID_FK;

Additionally this will automatically adjust to new "value types" without the need to rewrite the statement.

Maybe you can even restructure the entity_history table to also use a hstore column.


How to use a avg expression in the WHERE between clause? I want to compare two value with avg rating of user

select * from dt_tutor_comments where avg(rating_value) BETWEEN 1 and 2 GROUP by u.id

Can you try this instead? Instead of having it in the WHERE clause, use it in HAVING clause.

SELECT u.id, AVG(rating_value) avg_rate
FROM dt_tutor_comments u
GROUP by u.id
HAVING AVG(rating_value) BETWEEN 1 AND 2


a SP to strip nulls from a table I think I'm close, but the apostrophes are killing me:

    create procedure denull (@database varchar(255), @table varchar(255))

    as

    declare @sql nvarchar(max)

    set @sql = ' declare @name varchar(255), @column varchar(255))

    declare c cursor local for

    select st.name, sc.name from ' + @database + '.sys.tables st
    inner join sys.columns sc on st.object_id = sc.object_id
    where st.name = ''' + @table + '''

    update ''''@name'''' set ''''@column'''' = '''' where ''''@column is null''

    fetch next from st.name, sc.name

    close c

    deallocate c
    '
    print @sql

    exec sp_executesql @sql

it prints out to:

     declare @name varchar(255), @column varchar(255))

    declare c cursor local for

    select st.name, sc.name from livendbmirror.sys.tables st
    inner join sys.columns sc on st.object_id = sc.object_id
    where st.name = 'CMS_27_Psych_results'

    update ''@name'' set ''@column'' = '' where ''@column is null'

    fetch next from st.name, sc.name

    close c

    deallocate c
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ')'.
    Msg 102, Level 15, State 1, Line 9
    Incorrect syntax near ''.
    Msg 105, Level 15, State 1, Line 9
    Unclosed quotation mark after the character string '

    fetch next from st.name, sc.name

    close c

    deallocate c
    '.

Question: How can I dynamically remove null values and replace them with '' for fussy end users?

I'd think twice about carte-blanche modifying data the way you are proposing unless you are doing this one-time as part of a data-purity effort.

I'd write code to do the modification using non-dynamic T-SQL first, then convert that into dynamic T-SQL, and finally add it to a stored procedure for easy re-use.

For instance, we'll create a table in tempdb, then update a column that has null values:

USE tempdb;

DROP TABLE dbo.test_table;
CREATE TABLE dbo.test_table (i varchar(3) null);
INSERT INTO dbo.test_table(i) VALUES (null)

UPDATE [dbo].[test_table]
SET [i] = ''
WHERE [i] IS NULL;

Next, we'll add this code to a framework that uses dynamic T-SQL so we don't need to write individual statements for every column we want to update:

DECLARE @cmd nvarchar(max);
DECLARE @schema sysname;
DECLARE @table sysname;
DECLARE @column sysname;
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT s.name
    , t.name
    , c.name
FROM sys.schemas s
    INNER JOIN sys.tables t ON s.schema_id = t.schema_id
    INNER JOIN sys.columns c ON t.object_id = c.object_id
    INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id
WHERE c.is_nullable = 1
    AND ty.name IN (
          'varchar'
        , 'char'
        , 'nvarchar'
        , 'nchar'
    )
    AND s.name = 'dbo'
    AND t.name = 'test_table'
ORDER BY s.name
    , t.name
    , c.name;

OPEN cur;
FETCH NEXT FROM cur INTO @schema, @table, @column;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cmd = 'UPDATE ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + '
SET ' + QUOTENAME(@column) + ' = ''''
WHERE ' + QUOTENAME(@column) + ' IS NULL;';
    PRINT @cmd;
    FETCH NEXT FROM cur INTO @schema, @table, @column;
END
CLOSE cur;
DEALLOCATE cur;

It uses a cursor to iterate over all the varchar, nvarchar, char, and nchar columns in the given table where the column value is null.

Next, we'll create a stored procedure to run the code.  The stored proc has a paramter, @debug, which allows us to see the UPDATE statement, or to actually run the UPDATE statement.

IF OBJECT_ID(N'dbo.RemoveNulls', N'P') IS NOT NULL
DROP PROCEDURE dbo.RemoveNulls;
GO
CREATE PROCEDURE dbo.RemoveNulls
(
    @schema_to_change sysname
    , @table_to_change sysname
    , @debug bit = 1
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @cmd nvarchar(max);
    DECLARE @schema sysname;
    DECLARE @table sysname;
    DECLARE @column sysname;
    DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
    FOR
    SELECT s.name
        , t.name
        , c.name
    FROM sys.schemas s
        INNER JOIN sys.tables t ON s.schema_id = t.schema_id
        INNER JOIN sys.columns c ON t.object_id = c.object_id
        INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id
    WHERE c.is_nullable = 1
        AND ty.name IN (
              'varchar'
            , 'char'
            , 'nvarchar'
            , 'nchar'
        )
        AND s.name = @schema_to_change
        AND t.name = @table_to_change
    ORDER BY s.name
        , t.name
        , c.name;

    OPEN cur;
    FETCH NEXT FROM cur INTO @schema, @table, @column;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @cmd = 'UPDATE ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + '
SET ' + QUOTENAME(@column) + ' = ''''
WHERE ' + QUOTENAME(@column) + ' IS NULL;';
        PRINT @cmd;
        IF @debug = 0
        BEGIN
            EXEC sys.sp_executesql @cmd;
        END
        FETCH NEXT FROM cur INTO @schema, @table, @column;
    END
    CLOSE cur;
    DEALLOCATE cur;
END
GO

Run the stored procedure like this, which won't actually modify data since we've left the @debug parameter set to its default value.:

EXEC dbo.RemoveNulls @schema_to_change = 'dbo'
    , @table_to_change = 'test_table';

If we want to actually modify the data, run the stored procedure like this:

EXEC dbo.RemoveNulls @schema_to_change = 'dbo'
    , @table_to_change = 'test_table'
    , @debug = 0;


What to do with a table with too many indexes? I'm the new DBA for this company.  I'm seeing some of their tables have a LOT of indexes; for instance some have over 50 or 60.

Is this a good thing? From my research, it seems this is not good for performance.

How would you manage this?

I would like to delete all the indexes and start from 0.  I think the developers have been using Tuning Advisor without really understanding indexes.

Probably some of those indexes are used, so deleting them all is not a good idea.

You can review index usage stats and find bad NC indexes with this query (taken from Glenn Berry's Diagnostic Queries):

-- Possible Bad NC Indexes (writes > reads)  (Query 47) (Bad NC Indexes)
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id,
    i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor,
    user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],
    user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
    ON s.[object_id] = i.[object_id]
    AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
    AND s.database_id = DB_ID()
    AND user_updates > (user_seeks + user_scans + user_lookups)
    AND i.index_id > 1
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);

You can also find recommended missing indexes using this query (again from Glenn Berry's diagnostic queries):

-- Missing Indexes for current database by Index Advantage  (Query 48) (Missing Indexes)
SELECT DISTINCT CONVERT(decimal(18,2), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) AS [index_advantage],
    migs.last_user_seek, mid.[statement] AS [Database.Schema.Table],
    mid.equality_columns, mid.inequality_columns, mid.included_columns,
    migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact,
    OBJECT_NAME(mid.[object_id]) AS [Table Name], p.rows AS [Table Rows]
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
    ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
    ON mig.index_handle = mid.index_handle
INNER JOIN sys.partitions AS p WITH (NOLOCK)
    ON p.[object_id] = mid.[object_id]
WHERE mid.database_id = DB_ID()
ORDER BY index_advantage DESC OPTION (RECOMPILE);

Instead of creating the recommended missing indexes as suggested, try to combine them with existing indexes and find a balance to keep the number of indexes low.


How do I insert records into database with foreign keys into SQL Server? I'm writing a database in SQL Server, I have this schema

Basing on this, I've prepared UML diagram

I have written SQL code to create tables and alter them with foreign keys

CREATE TABLE Employee
(
    Fname VARCHAR(20),
    Minit CHAR(1),
    Lname VARCHAR(30),
    Ssn VARCHAR(9) CONSTRAINT pk_Employee PRIMARY KEY, -- pk key
    Bdate DATE,
    Address VARCHAR(30),
    Sex CHAR(1),
    Salary INT,
    Super_ssn VARCHAR(9),
    Dno INT,
    CONSTRAINT chk_Employee_Ssn CHECK (LEN(Ssn)=9)
);

CREATE TABLE Department
(
    Dname VARCHAR(30),
    Dnumber INT CONSTRAINT pk_Department PRIMARY KEY, -- pk key
    Mgr_ssn VARCHAR(9),
    Mgr_start_date DATE,
    CONSTRAINT chk_Department_MgrSsn CHECK (LEN(Mgr_ssn)=9)
);

CREATE TABLE Dept_locations
(
    Dnumber INT,
    Dlocation VARCHAR(30),
    CONSTRAINT pk_Dept_locations PRIMARY KEY (Dnumber,Dlocation) -- pk key
);

CREATE TABLE Project
(
    Pname VARCHAR(20),
    Pnumber INT CONSTRAINT pk_Project PRIMARY KEY, -- pk key
    Plocation VARCHAR(30),
    Dnum INT
);

CREATE TABLE Works_on
(
    Essn VARCHAR(9),
    Pno INT,
    Hours DECIMAL(7,2),
    CONSTRAINT pk_WorksOn PRIMARY KEY (Essn,Pno),   -- pk key
    CONSTRAINT chk_WorksOn_Essn CHECK (LEN(Essn)=9)
);

CREATE TABLE Dependent
(
    Essn VARCHAR(9) ,
    Dependent_name VARCHAR(20),
    Sex CHAR(1),
    Bdate DATE,
    Relationship VARCHAR(15),
    CONSTRAINT pk_Dependent PRIMARY KEY (Essn,Dependent_name), -- pk key
    CONSTRAINT chk_Dependent_Essn CHECK (LEN(Essn)=9)
);

ALTER TABLE Dependent ADD
    CONSTRAINT fk_Dependent_Essn FOREIGN KEY (Essn) REFERENCES Employee(Ssn)

ALTER TABLE Department ADD
    CONSTRAINT fk_Department_MgrSsn FOREIGN KEY (Mgr_ssn) REFERENCES Employee(Ssn)

ALTER TABLE Employee ADD
    CONSTRAINT fk_Employee_SuperSsn FOREIGN KEY (Super_ssn) REFERENCES Employee(Ssn),
    CONSTRAINT fk_Employee_Dno FOREIGN KEY (Dno) REFERENCES Department(Dnumber)

ALTER TABLE Dept_locations ADD
    CONSTRAINT fk_DeptLocations_MgrSsn FOREIGN KEY (Dnumber) REFERENCES Department(Dnumber)

ALTER TABLE Project ADD
    CONSTRAINT fk_Project  FOREIGN KEY (Dnum) REFERENCES Department(Dnumber)

ALTER TABLE Works_on ADD
    CONSTRAINT fk_WorksOn_Essn  FOREIGN KEY (Essn) REFERENCES Employee(ssn),
    CONSTRAINT fk_WorksOn_Pno  FOREIGN KEY (Pno) REFERENCES Project(Pnumber)

Now when I'm trying to insert some data into those tables,

example:

INSERT INTO Employee(Fname, Minit, Lname, SSn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
VALUES ('John', 'B', 'Smith', '123456789', '1965-01-09', 'Lazy Town', 'M', 30000, '333445555', 5);

I realize that most of those tables requires foreign keys to any other tables, which makes the insertion very weird. To insert something, I must already have other data in the database. It's like vicious circle.

I have tried to insert some data for any of those tables, using those commends (one after another, not all at once). Every time it failed, because foreign keys can't find records in other tables :<

INSERT INTO Employee(Fname,Minit,Lname,SSn,Bdate,Address,Sex,Salary,Super_ssn,Dno) VALUES
('John','B','Smith','123456789', '1965-01-09', 'Lazy Town', 'M', 30000, '333445555',5);

INSERT INTO Dept_locations(Dnumber,Dlocation) VALUES
    (1, 'Houston');

INSERT INTO Department(Dname,Dnumber,Mgr_ssn,Mgr_start_date) VALUES
    ('Research', 5, '333445555', '1988-05-22');

INSERT INTO Dependent(Essn,Dependent_name,Sex,Bdate,Relationship) VALUES
    ('333445555', 'Alice', 'F', '1986-04-05', 'Daughter');

INSERT INTO Works_on(Essn,Pno,Hours) VALUES
    ('333445555', 3, 10);

INSERT INTO PROJECT(Pname,Pnumber,Plocation,Dnum) VALUES
    ('ProductX', 1, 'Bellaire', 5);

My question: is there any method to insert records in effective and safe way, without errors caused by non existing records?

Since Department.Mgr_ssn is nullable, I would insert the departments without Mgr_ssn.

Then insert the employees as you did before. Start with the employee having no Super_ssn (the big boss, I guess). Then the emps whose super is the big boss, etc.

Finally update the departments to set the Mgr_ssn.

UPDATE Department
SET Mgr_ssn = '333445555'
WHERE Dnumber = 5

The rule is to first populate tables with a lookup character, i.e. tables containing constants. Departments are kind of constant, where as employees can change.


Oracle 12c recovery without system file Scenario:

Deleted system files (dbf files); and trying to recover database back to normal using available control files, and some redo logs.
Backup is not available, nor are archived redo log files

System information:
OS - RHEL Server Release 7.5 (Maipo)
Oracle Linux Server Release 7.5 (12c)
How did we land up in this situation:

rm  *.DBF in DBF_FILES/ folder; this deleted system01.dbf,  system02.dbf (one file being of system tablespace and one sysaux)

two instances of oracle were running and we were removing files of other instance

restarted ec2 instance

Current Situation:

database is starting in mounted mode only
I have a AMI backup of the server after the deletion and restart of instance
All data files are intact
Control file is intact
Redo archive logs not available; all we have is some redo logs (thought of using redo logs to re-create the entire thing; in line with what postgreSQL offers with WriteAheadLog); but could not find correct resource maybe. (more on this in approaches taken)
A one year old backup of the same database is available which is working but there have been many changes from that time; unluckily the dev does not have any record of what he changed!

Approaches I've tried:

Tried recovering the folder with extundelete, foremost, testdisk (sysadmin type approaches)
The files removed were available on an old backup and I tried copying the same and starting the database
I also created a trace file from the control file and removed the deleted files from trace file and tried starting the database.
Also tried RECOVER DATAFILE '<empty_file.dbf>' by creating empty files of the same name

Happy to connect over video/audio/screen-share if need be.

OS backups of the data files will only be valid if the database was shutdown at the time the backup was made, otherwise they most likely will not work because they will not account for the state of any data that was cached being written to disk when the backup was made. The system tablespace in particular is problematic because it contains the entire data dictionary for the database. Without it, the other data files are largely useless.
All data files must be synced to the same system change number that is in the control files before you'll be able to open the database; if you don't have archived redo logs to reapply missing transactions then you'd have to restore all data files and control files from the same cold/offline backup and accept whatever data loss that implied.
I would advise opening a ticket with Oracle Support, but without an RMAN backup or archived redo log files I don't think you're going to have many options. Maybe a recent datapump export of the data that you could use to populate a new db instance?


Does MySQL 8 ASCII vs utf8mb4_0900_ai_ci size differ when only using ASCII characters? If I only use only ASCII characters, will VARCHAR (255) with utf8mb4_0900_ai_ci be larger on disk than VARCHAR (255) using ASCII?

Unless MySQL does something strange, using only ASCII characters (i.e. only values 0 - 127) should be the exact same encoding, and hence the exact same size, between ASCII, UTF-8, and many other 8-bit code pages. It's only when you hit code points above 127 (or 0x7F) that UTF-8 starts to require additional space (though technically speaking, standard ASCII only includes values 0 - 127, thus there are no code points above 127, thus all ASCII code points are encoded identically in UTF-8, which after all, was the design goal of UTF-8: full ASCII compatibility).


MySQL - LOAD DATA INFILE - Handling unescaped NULL in INFILE or creating INFILE with escaped NULL I am selecting data from a database on server A, dumping the output into a file locally, and then doing a LOAD DATA INFILE on my local database.   The bash command looks something like this:

mysql -e "SELECT * FROM items WHERE created_at > '2011-05-01';" > items

I was told this was the correct way when moving data from an external server to a local database.  I think I would have normally chosen to use mysqldump, but I didn't want to fuss with curling the dumped file from the server to my local machine.

The data that is dumped to the items file with the command line code above contains a lot of NULLs.  Those NULLs, when read by the LOAD DATA INFILE command, run locally, ends up loading a bunch of strings of "NULL" into my table.  I want those to be properly translated into NULL without having to go into the items file and find/replacing "NULL" with "\N"

I need a way to do one of two things:

1) When selecting data from a mysql table, I would like the output to escape NULLS into the standard \N string that is read by LOAD DATA INFILE.  In the interest succinctness and ease of maintenance, I want to avoid the type of code below.

SELECT IFNULL(x, '\N' ) AS x, IFNULL(y, '\N') AS y ...

2) When running a LOAD DATA INFILE, I would like it to treat the string NULL as null.

(SELECT * FROM items WHERE created_at > '2011-01-01')

Any thoughts or alternate methods are greatly appreciated.

Many Thanks,
Chad

Have you looked into the SELECT INTO OUTFILE syntax?

Here is a quote on the NULL string issue:

  If the FIELDS ESCAPED BY character is empty, no characters are escaped and NULL is output as NULL, not \N. It is probably not a good idea to specify an empty escape character, particularly if field values in your data contain any of the characters in the list just given.

So make sure you indicate a FIELDS ESCAPED BY clause to get NULLs written as \N

Hope this helps


is there a way to connect to mysql using oracle sqlplus instant client i am stuck while trying to connect to mysql using sqlplus client and i have already asked this question at askubuntu.com as

https://askubuntu.com/questions/674871/not-able-to-connect-to-mysql-database-from-oracle-sqlplus-instant-client-in-ubun

please any one help me.. trying to solve it from last 5 hours :(

The answer to your question is, no, you cannot connect to MySQL using the current oracle instant client.


modelling grocery orders, that can be split into multiple short orders I'm trying to figure out the best way of modelling a basic grocery picking system and something has stumped me. In short: how do I represent a single order that can be split into multiple parts / short orders, to be picked by various pickers?

A little more detail:

For those of you in the UK, think groceries via tesco.com, that's exactly what I'm trying to model, albeit far more simple of course. Here’s how it works (massively simplified) from a dotcom back room in Tesco:

A customer places an order: products, quantities, delivery date.

Customer orders are split into trips (around the store or warehouse) based on various criteria, therefore a single order can be a single trip (rare) or many trips.

A member of staff picks an order (or part of) via a trip. Another member could be picking for the exact same order, just a different part of it. When picking a trip, you can be picking for multiple orders at once, one huge order, two normal orders and a larger one. Real world example: Six trays on a picking trolley, each tray is a single customer order (or part of) and it’s possible for more than one tray to be part of the same order, so items can be mixed between the two. You could have six different customers, or 1 customer across 5 trays and another separate customer. Any mix.

I can handle the makeshift splitting rules, what I can’t wrap my head around, is how to represent the split orders in a simple database. I've attached an image of what I think it should be like.

This is all for fun on my part. I'm just trying to build a mock ’enterprise’ .NET app as a learning experience. There’s a million ideas I have that I can add to this that would be great to show off, alas database design is a massive slog for me.

Any ideas how I would tackle this? I've googled warehouse picking, online shopping and all the rest. It's either not what I need, or way too complex. I really want to start writing some code.

Your model is almost there for your stated purposes, so as I suggested in comments Picks doesn't need too many fields to capture the basic information you need to both accomplish splitting an order to different people, and tracking when/how it was completed.

While I mentioned the basics above, what you also really need are tracking/audit fields. With these you can actually monitor the status and recover history of who picked what and when. I'd suggest process audits and system audits. With both you can track issues with the software solution and your business process (allows some basic support for things like connectivity loss of scanners in a warehouse, system down etc.).

OrderID INT NOT NULL
StaffID INT NOT NULL
ProductID INT NOT NULL
ItemRetrievedFlag bit NOT NULL
ItemPickDateTime DATETIME NULL
InsertSystemID INT NULL --something like a barcode scanner device ID
AuditInsertDate DATETIME NULL
AuditUpdateDate DATETIME NULL
UpdateSystemID INT NULL

With a table like this you can retrieve order fulfillment history as simply as:

SELECT *
FROM Picks
WHERE OrderID = X

The query above gives you all recorded activity for the order, but it may not tell you what state the order as a whole is in. You should also have some kind of an OrderTracking table(s).

OrderID INT NOT NULL
StatusID INT NOT NULL
StatusDateTime DATETIME NULL
OrderTotalRetrievedItems INT NULL
OrderTotalItems INT NULL
AuditInsertDatetime DATETIME NULL
AuditUpdateDateTime DATETIME NULL
AuditUpdateSystemID INT NULL

You may find that with a consistent order fulfillment process you can instead dedicate columns to the status, but this pushes you into an update pattern instead of inserts as orders move through the process. If you keep a single row per status change you can more fully Denormalize your design.


move blob data to new filegroup short version: what options do i have to move a table that has blob data over to a new filegroup, without blowing out the txlog?

details:
we have an extremely over-allocated filegroup ("DA1"). the plan is: move all the objects to a new (right-sized) filegroup("DA2"), shrink/delete DA1 to reclaim all that disk space*. Easy, right? So, I already moved most all the objects to DA2, most all were pretty small. This last table has blob data, it's about 250GB. prod is in FULL recovery. What we've tried in dev: 1) tried a shrink, took over 11 hours before we killed it. 2) i tried SELECT INTO a new table on DA2 so I could delete&rename, however, the log grew too large and consumed the log drive (constraints here too :(  I do have large maintenance windows on the weekends, but cannot change the recovery model. Any other ideas I can test out?

*we're talking about 2TB to reclaim, and thus has created problems/pressure on restoring this db to lower environments because it's just_too_big.

You can try moving the data by rebuilding indexes. There are some limitations to this, for example, your clustered index rebuild will have to be done OFFLINE (a limitation of ONLINE rebuilds) and any non-clustered indexes can be rebuilt online as long as the LOB data is not part of the index key or as an include column.

Also, during the index build operation, the transaction log can be backed up and truncated, provided you do not run the index operation in an explicit transaction.

Microsoft has a guide on calculating the space required for here. They also have some general recommendations such as switching to BULK-LOGGED recovery to reduce the amount of transaction log data. See here for guidelines from Microsoft.

One of the advantages to this is you can move an index at a time, so you can do this over multiple maintenance windows if need be.


How do I create a user-defined aggregate function in MySQL (redux)? OK, I'm probably going to get banned for asking the same question a second time, but I'm an incredible newbie here and I was told I don't have enough of a reputation to comment on the original question.

I agree with the OP's (Matt Fenwick's) comment to answer 5 in How do I create a user-defined aggregate function? in that it gives a fish for a day, but the real question is learning how to fish.

I need, among other things, a GROUP BY aggregate function like the MIN function, but on call SMALL(t.value, 2) it return the second smallest data element, on call SMALL(t.value, 3) it return the third smallest data element, and so on. (This actually works with LibreOffice Calc's SMALL function, BTW.)

The problem for my particular reason for asking this problem is I would like to convert my grading spreadsheet for a college course to a MySQL database, and I need to drop the two lowest attendance score, the two lowest homework scores, and the two lowest quiz scores for each students to compute their adjusted attendance percentage, adjusted homework average, and adjusted quiz average, among other adjustments to the data.

The tblQuizzes looks something like

mysql> select * from tblQuizzes LIMIT 12;
+------------+---------+-----------+
| StudentKey | QuizKey | QuizGrade |
+------------+---------+-----------+
|          1 |       1 |     0.123 |
|          2 |       1 |     0.456 |
|          3 |       1 |     0.789 |
|          4 |       1 |     0.890 |
|          5 |       1 |     0.123 |
|          6 |       1 |     1.000 |
|          1 |       2 |     0.789 |
|          2 |       2 |     0.123 |
|          3 |       2 |     0.456 |
|          4 |       2 |     0.789 |
|          5 |       2 |     0.123 |
|          6 |       2 |     1.000 |
+------------+---------+-----------+

I would need a query like

SELECT
tblQuizzes.StudentKey,
(SUM(tblQuizzes.QuizGrade) - MIN(tblQuizzes.QuizGrade) - UDF_SMALL(tblQuizzes.QuizGrade, 2))/(COUNT(tblQuizzes.QuizGrade) - 2) AS AdjQuizAverage
FROM
tblQuizzes
GROUP BY
tblQuizzes.StudentKey

to drop the lowest 2 quiz scores and average the remaining quizzes.

So, keeping in mind that I don't just want "a fish for a day" and know how to get the second lowest quiz score, but instead am interested in "learning how to fish" and write a MIN(table.value, offset) GROUP BY aggregate function (in C/C++ or PHP if necessary), can anyone provide some hints/complete answers with links and examples for this "fishing problem"?

Thanks much in advance for any assistance you can provide; I'm sorry if I seem rude in my "demands"/requests, I've just learned that vague requests result in workaround, "fish for a day" replies.

OK, it's almost working; the only thing that is wrong is I evidently don't know how to coerce the constant argument args->args[1] to (any) type of int value. If I code target_depth to a fixed integer, the program works. It's only when I try to read it in from the query that I get the "ERROR 2013 (HY000): Lost connection to MySQL server during query" error. Any help with reading in the target depth for UDF_SMALL (2nd lowest value, 3rd lowest value, etc.) would be greatly appreciated.

/******************************************************************************
 ** udf_small.c
 **
 ** This MySQL user-defined function (UDF) sorts through an aggregate dataset,
 ** then returns the ith smallest element each group in the GROUP BY statement.
 **
 ** Author:        Jeffrey Rolland
 ** Creation Date: 12/07/2014
 ** Modifications: None
 ******************************************************************************/

/******************************************************************************
** A dynamically loadable file should be compiled shared.
** (something like: gcc -shared -o my_func.so -I /usr/includes/mysql/ my_func.c).
** You can easily get all switches right by doing:
** cd sql ; make udf_example.o
** Take the compile line that make writes, remove the '-c' near the end of
** the line and add -shared -o udf_example.so to the end of the compile line.
** The resulting library (udf_example.so) should be copied to some dir
** searched by ld. (/usr/lib/mysql/plugin/ ?)
** If you are using gcc, then you should be able to create the udf_example.so
** by simply doing 'make udf_example.so'.
**
** After the library is made one must notify mysqld about the new
** functions with the commands:
**
** CREATE FUNCTION metaphon RETURNS STRING SONAME "udf_example.so";
** CREATE FUNCTION myfunc_double RETURNS REAL SONAME "udf_example.so";
** CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "udf_example.so";
** CREATE FUNCTION sequence RETURNS INTEGER SONAME "udf_example.so";
** CREATE FUNCTION lookup RETURNS STRING SONAME "udf_example.so";
** CREATE FUNCTION reverse_lookup RETURNS STRING SONAME "udf_example.so";
** CREATE AGGREGATE FUNCTION avgcost RETURNS REAL SONAME "udf_example.so";
** CREATE FUNCTION myfunc_argument_name RETURNS STRING SONAME "udf_example.so";
**
** After this the functions will work exactly like native MySQL functions.
** Functions should be created only once.
**
** The functions can be deleted by:
**
** DROP FUNCTION metaphon;
** DROP FUNCTION myfunc_double;
** DROP FUNCTION myfunc_int;
** DROP FUNCTION lookup;
** DROP FUNCTION reverse_lookup;
** DROP FUNCTION avgcost;
** DROP FUNCTION myfunc_argument_name;
**
** The CREATE FUNCTION and DROP FUNCTION update the func@mysql table. All
** Active function will be reloaded on every restart of server
** (if --skip-grant-tables is not given)
**
** If you ge problems with undefined symbols when loading the shared
** library, you should verify that mysqld is compiled with the -rdynamic
** option.
**
** If you can't get AGGREGATES to work, check that you have the column
** 'type' in the mysql.func table. If not, run 'mysql_upgrade'.
**
**************************************************************************/

/*************************************************************************
** Syntax for the new aggregate commands are:
** CREATE AGGREGATE FUNCTION <function_name> RETURNS {STRING|REAL|INTEGER}
** SONAME <name_of_shared_library>
**
** Syntax for avgcost: AVGCOST( t.quantity, t.price )
** with t.quantity=integer, t.price=double
**
** (This example is provided by Andreas F. Bobak <bobak@relog.ch>)
**************************************************************************/

#ifdef STANDARD
    /* STANDARD is defined, don't use any mysql functions */
    #include <stdlib.h>
    #include <stdio.h>
    #include <string.h>
    #ifdef __WIN__
        typedef unsigned __int64 ulonglong; /* Microsofts 64 bit types */
        typedef __int64 longlong;
    #else
        typedef unsigned long long ulonglong;
        typedef long long longlong;
    #endif /*__WIN__*/
#else
    #include <my_global.h>
    #include <my_sys.h>
    #if defined(MYSQL_SERVER)
        #include <m_string.h>   /* To get strmov() */
    #else
        /* when compiled as standalone */
        #include <string.h>
        #define strmov(a,b) stpcpy(a,b)
        #define bzero(a,b) memset(a,0,b)
    #endif
#endif
#include <mysql.h>
#include <ctype.h>
#ifdef _WIN32
    /* inet_aton needs winsock library */
    #pragma comment(lib, "ws2_32")
#endif
#ifdef HAVE_DLOPEN
    #if !defined(HAVE_GETHOSTBYADDR_R) || !defined(HAVE_SOLARIS_STYLE_GETHOST)
        static pthread_mutex_t LOCK_hostname;
    #endif
#endif
#include <stdbool.h>

my_bool udf_small_init( UDF_INIT* initid, UDF_ARGS* args, char* message );
void udf_small_deinit( UDF_INIT* initid );
void udf_small_reset( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error );
void udf_small_clear( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error );
void udf_small_add( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error );
double udf_small( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error );

struct SortArray
{
    ulonglong length;
    double *data;
};

/*************************************************************************
** Example of init function
** Arguments:
** my_bool maybe_null 1 if function can return NULL
** Default value is 1 if any of the arguments
** is declared maybe_null
**
**
** initid Points to a structure that the init function should fill.
** This argument is given to all other functions.
**
** unsigned int decimals Number of decimals.
** Default value is max decimals in any of the
** arguments.
**
** unsigned int max_length Length of string result.
** The default value for integer functions is 21
** The default value for real functions is 13+
** default number of decimals.
** The default value for string functions is
** the longest string argument.
**
** char *ptr; A pointer that the function can use.
**
**
** args Points to a structure which contains:
** unsigned int arg_count Number of arguments
**
** enum Item_result *arg_type Types for each argument.
** Types are STRING_RESULT, REAL_RESULT
** and INT_RESULT.
**
** char **args Pointer to constant arguments.
** Contains 0 for not constant argument.
**
** unsigned long *lengths; max string length for each argument
** char *maybe_null Information of which arguments
** may be NULL
**
**
** message Error message that should be passed to the user on fail.
** The message buffer is MYSQL_ERRMSG_SIZE big, but one should
** try to keep the error message less than 80 bytes long!
**
** This function should return 1 if something goes wrong. In this case
** message should contain something usefull!
**************************************************************************/
my_bool udf_small_init( UDF_INIT* initid, UDF_ARGS* args, char* message )
{
    struct SortArray *sort = NULL;
    ulonglong target_depth;
    ulonglong i;

    if (args->arg_count != 2)
    {
        strcpy(message, "Wrong number of arguments: SMALL() requires two arguments");
        return 1;
    }
    if ((args->arg_type[0] != REAL_RESULT) || (args->arg_type[1] != INT_RESULT) )
    {
        strcpy(message, "Wrong argument type: SMALL() requires an REAL and a INT");
        return 1;
    }
    if (*((longlong*)args->args[1]) < 1)
    {
        strcpy(message, "Wrong argument value: SMALL() requires second parameter that is positive");
        return 1;
    }

    initid->maybe_null = 0; /* The result may be null */
    initid->decimals = 10; /* We want 10 decimals in the result */
    /* initid->max_length = 10; */ /* 10 digits + . + 10 decimals */

    target_depth = *((longlong*)args->args[1]);

    sort = malloc((sizeof(struct SortArray)));

    if(sort == NULL)
    {
        strcpy(message, "Couldn't allocate memory");
        return 1;
    }

    initid->ptr = (char*)sort;

    sort->length = 0;

    sort->data = malloc((target_depth)*(sizeof(double)));

    if(sort->data == NULL)
    {
        strcpy(message, "Couldn't allocate memory");
        return 1;
    }

    for(i = 0; i < target_depth; i++)
    {
        sort->data[i] = 999999999;
    }

    return 0;
}

/****************************************************************************
** Deinit function. This should free all resources allocated by
** this function.
**
** Arguments:
** initid Return value from xxxx_init
****************************************************************************/
void udf_small_deinit( UDF_INIT* initid )
{
    free(initid->ptr);
}

/****************************************************************************
** Small Aggregate Function.
**
** There are 3 extra functions for an aggregate function, xxx_reset,
** xxx_clear, and xxx_add
****************************************************************************/

/****************************************************************************
** xxx_reset
**
** Arguments:
** initid Structure filled by xxx_init
**
** args The same structure as to xxx_init. This structure
** contains values for all parameters.
** Note that the functions MUST check and convert all
** to the type it wants! Null values are represented by
** a NULL pointer
**
** is_null If the result is null, one should store 1 here.
**
** message Error message that should be passed to the user on fail.
** The message buffer is MYSQL_ERRMSG_SIZE big, but one should
** try to keep the error message less than 80 bytes long!.
**
** xxx_reset gets done every time we begin processing the records for a new
** group. It calls xxx_clear and xxx_add
****************************************************************************/

/* This is only for MySQL 4.0 compability */
void udf_small_reset(UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* message)
{
    udf_small_clear(initid, args, is_null, message);
    udf_small_add(initid, args, is_null, message);
}

/****************************************************************************
** xxx_clear
**
** Arguments:
** initid Structure filled by xxx_init
**
** args The same structure as to xxx_init. This structure
** contains values for all parameters.
** Note that the functions MUST check and convert all
** to the type it wants! Null values are represented by
** a NULL pointer
**
** is_null If the result is null, one should store 1 here.
**
** message Error message that should be passed to the user on fail.
** The message buffer is MYSQL_ERRMSG_SIZE big, but one should
** try to keep the error message less than 80 bytes long!.
**
** xxx_clear resets the processing variables to their initial values for a
** new group
****************************************************************************/

/* This is needed to get things to work in MySQL 4.1.1 and above */
void udf_small_clear(UDF_INIT* initid, UDF_ARGS* args, char* is_null __attribute__((unused)), char* message __attribute__((unused)))
{
    struct SortArray *sort = NULL;
    ulonglong target_depth;
    ulonglong i;

    sort = (struct SortArray*)initid->ptr;

    target_depth = *((longlong*)args->args[1]);

    /* if(sort == NULL)
    {
        strcpy(message, "Initid Pointer is NULL");
        exit(1);
    }

    if(sort->data == NULL)
    {
        strcpy(message, "Couldn't allocate memory");
        exit(1);
    } */

    sort->length = 0;

    for(i = 0; i < target_depth; i++)
    {
        sort->data[i] = 999999999;
    }
}

/****************************************************************************
** xxx_add
**
** Arguments:
** initid Structure filled by xxx_init
**
** args The same structure as to xxx_init. This structure
** contains values for all parameters.
** Note that the functions MUST check and convert all
** to the type it wants! Null values are represented by
** a NULL pointer
**
** is_null If the result is null, one should store 1 here.
**
** message Error message that should be passed to the user on fail.
** The message buffer is MYSQL_ERRMSG_SIZE big, but one should
** try to keep the error message less than 80 bytes long!.
**
** xxx_add is the main processing workhorse of a anggregate UDF. It processes
** each new record in the group.
****************************************************************************/

void udf_small_add(UDF_INIT* initid, UDF_ARGS* args, char* is_null __attribute__((unused)), char* message __attribute__((unused)))
{
    if (args->args[0] && args->args[1])
    {
        struct SortArray *sort = NULL;
        ulonglong target_depth;
        double col_value;
        ulonglong counter;
        ulonglong i, j;
        bool done;

        sort = (struct SortArray*)initid->ptr;

        target_depth = *((longlong*)args->args[1]);

        col_value = *((double*)args->args[0]);

        /* if(sort == NULL)
        {
            strcpy(message, "Initid pointer is NULL");
            exit(1);
        } */

        i = target_depth;
        counter = 1;
        done = FALSE;

        /* sort->data[0] = col_value + 1.0;
        sort->data[1] = col_value;

        col_value = 0.05; */

        while ((!(done)) && (i > 0))
        {
            if(col_value <= sort->data[i-1])
            {
                for(j = 0; j < i-1; j++)
                {
                    sort->data[j] = sort->data[j+1];
                }
                sort->data[i-1] = col_value;
                done = TRUE;
            }
            else
            {
                i--;
                counter++;
            }
        }

        /* if(counter >= sort->length)
        {
            sort->length = counter;
        } */

    }
}

/***************************************************************************
** UDF double function.
**
** Arguments:
** initid Structure filled by xxx_init
**
** args The same structure as to xxx_init. This structure
** contains values for all parameters.
** Note that the functions MUST check and convert all
** to the type it wants! Null values are represented by
** a NULL pointer
**
** is_null If the result is null, one should store 1 here.
**
** error If something goes fatally wrong one should store 1 here.
**
** This function should return a double. It returns a value at the end of
** each group.
***************************************************************************/

double udf_small( UDF_INIT* initid, UDF_ARGS* args __attribute__((unused)), char* is_null, char* error __attribute__((unused)))
{
    struct SortArray *sort = NULL;
    ulonglong target_depth;

    sort = (struct SortArray*)initid->ptr;
    target_depth = *((longlong*)args->args[1]);

    sort->length = target_depth; /* Kill me once working */

    if(sort != NULL)
    {
        if(sort->length == target_depth)
        {
            *is_null = 0;
            return sort->data[0];
        }
    }
    else
    {
        *is_null = 1;
        return  10.0;
    }
}


Update and Insert data from a updated table I need help to update (and insert) data in table1 with newer data from table2.

I am in a very hard situation imagine that i will need to do it for 40 tables in a database of more than 300 tables running in production, if i want to do for all tables, i will just delete the database and restore the dump but this is not my case. I am trying to imagine how i can write a function to do this, using a loop for example, checking if the data is newer(update) or inexistent(insert). So i will need a very generic production or to program a very smart function.

Requirements are not clear on insert.
If the ID are not correct then how would you know a non-matching usercode is not already present?

Update:
What this does is if t2 is null then it will not replace a value in t1

update t1
set t1.col1 = insull(t2.col1, t1.col2) ....
from table1 t1
join table2 t2
on t2.usercode = t1.usercode


Making a Database Online I am so confused with this issue,  I have vb.net application and it has a sql database .

Now, I want this application to access the sql server from anywhere , because there are many clients sharing the same database from many regions.

I don't want to make my current sql server available online by opening ports and allowing TCP remote connections, I am looking for a hosting service or solution that provides sql server database hosting.
I just found web hosting services, and they do not allow users to use thier sql databases remotely for storage purposes.

Another words, I want to access it anywhere by some IP or domain or shared server, not by letting my computer working 24 hours to serve clients connections.

Thanks

Your requirement of having a database accessible from many remote locations is not uncommon.

If I were to do this, I would build a Windows Communication Framework (WCF) service that provides access to the database indirectly.  The WCF Service typically resides on a web server, where it operates as a proxy for the SQL Server.  The WCF service could provide security for the database by various authentication mechanisms; that is beyond the scope of this answer.

For information on WCF, check these documents:

http://msdn.microsoft.com/en-us/library/ms731082%28v=vs.110%29.aspx

http://www.codeproject.com/Articles/42643/Creating-and-Consuming-Your-First-WCF-Service

http://code.msdn.microsoft.com/Create-a-WCF-Service-c3486993


move postgresql 9.4 cluster to new server I have a redmine postgresql folder backup (no dump) from postgresql 9.4
and want to move it to a new server with debian 10 (buster) with postgres 11.

On my debian buster server I tried to get this old database running, so I installed the old postgresql-9.4 there with this tutorial: https://wiki.postgresql.org/wiki/Apt#Quickstart

When I tried to start the old cluster it said:

$ pg_createcluster 9.4 main --start
Configuring already existing cluster (configuration: /etc/postgresql/9.4/main, data: /var/lib/postgresql/9.4/main, owner: 117:120)
Error: move_conffile: required configuration file /var/lib/postgresql/9.4/main/postgresql.conf does not exist

I managed to start a new cluster2 with this instruction: https://stackoverflow.com/a/48624460/1069083

But that one is empty. My old main cluster contains all my data.

How do I start the old main cluster?

I solved it like this:

I had to restore the old config too in

/etc/postgresql/9.4/main

Then I could start the old main cluster with

pg_ctlcluster 9.4 main start

Then I had to update redmine:

cd /usr/share/redmine

and start

bundle exec rake db:migrate RAILS_ENV=production

check if redmine is working correctly in your browser, Now you can upgrade postgresql to 11. first dump the database with

sudo -u postgres pg_dumpall > /root/full_dump.sql

remove postgres 9.4 and install postgres 11, then

sudo -u postgres psql -d postgres < /root/full_dump.sql
/etc/init.d/postgresql restart

check /var/log/redmine/default/production.log for any errors, for example check in /etc/redmine/default/database.yml if redmine is still trying to connect on port 5433


Mysql How to insert a variable as field value in a table I have created a variable named hash as a password hash. How can this variable be inserted a a field value in a table?

hash = pbkdf2_sha256.encrypt("password", rounds=200000, salt_size=16)
cur.execute("insert into meanu(username,pwdhash1) values('BV2',hash)")
produces following error:
mysql_exceptions.OperationalError: (1054, "Unknown column 'hash' in 'field list'")
mysql_exceptions.OperationalError: (1054, "Unknown column 'hash' in 'field list'")

cur.execute("insert into meanu(username,pwdhash1) values('BV2','hash')")
inserts the literal "hash" as the field value.

You should insert hash as a parameter following this example:
http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html

Something like this

insert_stmt=(
       "insert into meanu (username,pwdhash1)"
       " values('BV2',%s)"
        )
data=(hash)
cursor.execute(insert_stmt, data)


Stored procedure working when run manually, not running in SQL Agent Job When I execute a Stored Procedure through a SQL Agent Job, I get the following error message:

  Executed as user: NT AUTHORITY\SYSTEM.
  XML parsing: line 10, character
   33, unexpected end of input [SQLSTATE 42000] (Error 9400).
  The step failed.
  Sql Severity 16, Sql Message ID 9400

However, when I run it manually, it is successful.

This is the procedure which I am executing:

decalre @URL VARCHAR(max)
 set @URL='http://www.spa.gov.sa/english/rss.xml'

 declare  @xmlT TABLE ( yourXML XML )
DECLARE @Response nvarchar(max)
DECLARE @XML xml
DECLARE @Obj int
DECLARE @Result int
DECLARE @HTTPStatus int
DECLARE @ErrorMsg nvarchar(MAX)

EXEC @Result = sp_OACreate 'MSXML2.XMLHttp', @Obj OUT

EXEC @Result = sp_OAMethod @Obj, 'open', NULL, 'GET', @URL, false
EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
EXEC @Result = sp_OAMethod @Obj, send, NULL, ''
EXEC @Result = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT

INSERT @xmlT ( yourXML )
EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml'--, @Response OUT

    INSERT into Tlb(discp , tit ,   datee,linkk)
    SELECT   N.C.value('description[1]', 'nvarchar(max)') discp,   N.C.value('title[1]', 'varchar(999)') tit,
    N.C.value('pubDate[1]', 'varchar(99)') datee,N.C.value('link[1]', 'varchar(999)') linkk
    FROM @xmlT CROSS APPLY yourXML.nodes('//channel/item') N(C)

I had this same issue when google geocoding and parsing the XML result.  Seems that when run via an SQL job the XML result was being truncated.

Found the solution here: http://www.progtown.com/topic376715-a-problem-with-spoagetproperty-p2.html

Had to put:

SET TEXTSIZE 2147483647;

at the start of my stored proc.


Built-in XML functions I am trying to use built-in XML functions, but I'm getting an %s: invalid identifier error. Selects works fine without the XML functions.

Any ideas where is the mistake?

select id,full_name from users where id in(
with sample_data as
(
 (select pm_id
 from pm  where p_id=30369)
)
select cast(t.column_value.extract('//text()') as varchar2(40)) val
  from sample_data,
  table( xmlsequence( xmltype(
    '<x><x>' || replace(pm_id, '#@#', '</x><x>') || '</x></x>'
  ).extract('//x/*'))) t)

@elimerv,

Are you sure you are not having something else in place of "single-quote" that is expected there? Also, you don't tell what kind of data does the "pm" table has in the pm_id column (BTW, your latest SQL doesn't appear to be correct as your sample_data subquery has an "id" column whereas your main query refers to "pm_id" column).
This is what I get on 11.2.0.1
First, your latest query, as it is (except for tables used):

with sample_data as (
   select '123#@#' id
   from dual
  )
  select sysdate from dual where dummy  in(
    select cast(t.column_value.extract('//text()') as varchar2(400)) val
    from sample_data,
         table(xmlsequence(xmltype('<x><x>'||replace(pm_id,'#@#','</x><x>')||'</x></x>').extract('//x/*'))) t
  )
 10  /
         table(xmlsequence(xmltype('<x><x>'||replace(pm_id,'#@#','</x><x>')||'</x></x>').extract('//x/*'))) t
                                                     *
ERROR at line 8:
ORA-00904: "PM_ID": invalid identifier

Now, with correct column name used:

with sample_data as (
   select '123#@#' pm_id
   from dual
  )
  select sysdate from dual where dummy  in(
    select cast(t.column_value.extract('//text()') as varchar2(400)) val
    from sample_data,
         table(xmlsequence(xmltype('<x><x>'||replace(pm_id,'#@#','</x><x>')||'</x></x>').extract('//x/*'))) t
  )
 10  /

no rows selected

And, when I replaced one of the "single-quotes" with a different (but similar looking) character :

with sample_data as (
   select '123#@#' pm_id
   from dual
  )
  select sysdate from dual where dummy  in(
    select cast(t.column_value.extract('//text()') as varchar2(400)) val
    from sample_data,
         table(xmlsequence(xmltype('<x><x>'||replace(pm_id,'#@#','</x><x>')||'</x></x>').extract(`//x/*'))) t
  )
 10  /
         table(xmlsequence(xmltype('<x><x>'||replace(pm_id,'#@#','</x><x>')||'</x></x>').extract(`//x/*'))) t
                                                                                *
ERROR at line 8:
ORA-00911: invalid character

Does this give you any idea?
BTW, are you sure you need XML processing to solve your problem ?


mysql - How to remove duplicate rows in parent table and leave one row with the lowest ID, and replace foreign key in child's table with this ID? I have the situation where I have three tables:

hgn_modern_sources
+----+--------------+
| id |    source    |
+----+--------------+
| 17 | Something... |
+----+--------------+

junc_modern_source_has_reference
+----+------------------+------------------------+
| id | modern_source_id | location_within_source |
+----+------------------+------------------------+
| 52 |               17 | 1, 1                   |
| 89 |               17 | 1, 1                   |
| 99 |               17 | 1, 1                   |
+----+------------------+------------------------+

hgn_picture_has_reference
+----+---------------------+-------------------+
| id | modern_reference_id | picture_link      |
+----+---------------------+--------------------+
| 45 | 89                  | /images/image1.png |
| 75 | 99                  | /images/image2.png |
+----+---------------------+--------------------+

My question is how to delete duplicates from junc_modern_source_has_reference (id = 89 and id = 99), leave one row with the lowest ID (id = 52), and replace foreign keys in hgn_picture_has_reference table (column modern_reference_id) with one left (id = 52)?

The solution can be only MySQL based, or PHP + MySQL (which I prefer more).

DROP TEMPORARY TABLE IF EXISTS tempTable;
CREATE TEMPORARY TABLE tempTable
(id int
,minId int
PRIMARY KEY (id)
);

-- filling temporary table with list of duplicate ids
-- and corresponding minimal id within each group of duplicates
WITH CTE AS (
SELECT id
    ,MIN(id) OVER (PARTITION BY modern_source_id, location_within_source ) AS minId
    ,ROW_NUMBER OVER (PARTITION BY modern_source_id, location_within_source ORDER BY id) AS rn
FROM junc_modern_source_has_reference
)
INSERT INTO tempTable (id, minId)
SELECT id, minId FROM CTE WHERE rn > 1;

UPDATE hgn_picture_has_reference AS t1
JOIN tempTable AS t2 ON t1.id = t2.id
SET t1.modern_reference_id  = t2.minId;

DELETE t1
FROM junc_modern_source_has_reference AS t1
JOIN tempTable AS t2 ON t1.id = t2.id;

DROP TEMPORARY TABLE tempTable;

If you use MySQL version older than 8.0 you can't use window functions and you need to modify part with CTE this way:

INSERT INTO tempTable (id, minId)
SELECT id, minId
FROM
    (SELECT t1.id
            ,t2.minId
    FROM junc_modern_source_has_reference AS t1
    JOIN (
        SELECT MIN(id) AS minId
            ,modern_source_id
            ,location_within_source
        FROM junc_modern_source_has_reference
        GROUP BY modern_source_id, location_within_source
        HAVING COUNT(1) > 1
        ) AS t2 ON t1.modern_source_id = t2.modern_source_id
                   AND t1.location_within_source = t2.location_within_source
                   AND t1.id <> t2.minId
    ) AS CTE


When are computed columns computed? When are the values for computed columns determined?

When the value is retrieved?
When the value is changed?
Some other time?

I'm guessing this is a novice question since I'm not finding anything in my searches.

This is very easy to prove on your own. We can create a table with a computed column that uses a scalar user-defined function, and then check plans and function stats before and after both an update and select, and see when an execution gets recorded.
Let's say we have this function:
CREATE FUNCTION dbo.mask(@x varchar(32))
RETURNS varchar(32) WITH SCHEMABINDING
AS
BEGIN
  RETURN (SELECT 'XX' + SUBSTRING(@x, 3, LEN(@x)-4) + 'XXXX');
END
GO

And this table:
CREATE TABLE dbo.Floobs
(
  FloobID int IDENTITY(1,1),
  Name varchar(32),
  MaskedName AS CONVERT(varchar(32), dbo.mask(Name)),
  CONSTRAINT pk_Floobs PRIMARY KEY(FloobID),
  CONSTRAINT ck_Name CHECK (LEN(Name)>=8)
);
GO

Let's check sys.dm_exec_function_stats (new in SQL Server 2016 and Azure SQL Database) before and after an insert, and then after a select:
SELECT o.name, s.execution_count
FROM sys.dm_exec_function_stats AS s
INNER JOIN sys.objects AS o
ON o.[object_id] = s.[object_id]
WHERE s.database_id = DB_ID();

INSERT dbo.Floobs(Name) VALUES('FrankieC');

SELECT o.name, s.execution_count
FROM sys.dm_exec_function_stats AS s
INNER JOIN sys.objects AS o
ON o.[object_id] = s.[object_id]
WHERE s.database_id = DB_ID();

SELECT * FROM dbo.Floobs;

SELECT o.name, s.execution_count
FROM sys.dm_exec_function_stats AS s
INNER JOIN sys.objects AS o
ON o.[object_id] = s.[object_id]
WHERE s.database_id = DB_ID();

I see no function call on the insert, only on the select.
Now, drop the tables and do it again, this time changing the column to PERSISTED:
DROP TABLE dbo.Floobs;
GO
DROP FUNCTION dbo.mask;
GO

...
  MaskedName AS CONVERT(varchar(32), dbo.mask(Name)) PERSISTED,
...

And I see the opposite happening: I get an execution logged on the insert, but not on the select.
Don't have a modern enough version of SQL Server to use sys.dm_exec_function_stats? No worries, this is captured in the execution plans, too.
For the non-persisted version, we can see the function referenced only in the select:

While the persisted version only shows the computation happening on insert:

Now, Martin brings up a great point in a comment: this isn't always going to be true. Let's create an index that doesn't cover the persisted computed column, and run a query that uses that index, and see if the lookup gets the data from the existing persisted data, or computes the data at runtime (drop and re-create function and table here):
CREATE INDEX x ON dbo.Floobs(Name);
GO

INSERT dbo.Floobs(name)
  SELECT LEFT(name, 32)
  FROM sys.all_columns
  WHERE LEN(name) >= 8;

Now, we'll run a query that uses the index (actually it uses the index by default in this specific case anyway, even without a where clause):
SELECT * FROM dbo.Floobs WITH (INDEX(x))
  WHERE Name LIKE 'S%';

I see additional executions in function stats, and the plan doesn't lie:

So, the answer is IT DEPENDS. In this case, SQL Server thought it would be cheaper to re-compute the values than to perform lookups. This could change due to a variety of factors, so don't rely on it. And this can happen in either direction whether or not a user-defined function is used; I only used it here because it made it that much easier to illustrate.


How to dump to csv valid json with Postgresql? I am trying to dump some data as json with:

\copy (SELECT json_build_object('areaSqft',area_sqft, 'titleNos',title_nos, 'buildingIds',building_ids, 'geometry',geom_json) FROM my_data) to my_data.csv with delimiter ',' csv header

what I am expecting is a valid json per row, but what I get is:

"{""areaSqft"": 214.394254595041, ""geometry"": {""type"": ""MultiPolygon"", ""coordinates"": [[[[0.000015, 51.449107], [0.000154, 51.441108], [0.000238, 51.44111], [0.00024, 51.441052], [0.000137, 51.441051], [0.000041, 51.441049], [0.000015, 51.441107]]]]}, ""titleNos"": [""ZB78669""], ""buildingIds"": [7521141, 9530393, 7530394]}"

There are extra " as first and last character and "" around instead of single ".

How can I get a valid json stripping unnecessary quotes?

The quotes around fields and inside fields are part of the CSV format, and they're required here, because, according the CSV spec:

  Fields containing line breaks (CRLF), double quotes, and commas should
  be enclosed in double-quotes
  If double-quotes are used to enclose fields, then a double-quote
     appearing inside a field must be escaped by preceding it with
     another double quote

I think that you don't want or need CSV in your case. Just take the output of SELECT, with the unaligned format of psql

=# \pset format unaligned
Output format is unaligned.

=# select json_build_object('foo', 1, bar, 2) AS myjson
   from (values (E'xy\zt'), ('ab,cd')) as b(bar);

myjson
{"foo" : 1, "xyzt" : 2}
{"foo" : 1, "ab,cd" : 2}
(2 rows)

You may also use \g output.json instead of the semi-colon at the end of the query to have psql redirect the results of that query to a file, and \pset tuples_only to remove headers and footers.


aggregate queries optimisation I need to aggregate all sales by days to build graph.

I have such a table

CREATE TABLE `transaction` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` varchar(255) NOT NULL,
  `purchase_date` int(10) unsigned NOT NULL DEFAULT '0',
  `expires_date` int(10) unsigned NOT NULL DEFAULT '0',
  `expires_date_calculated` int(10) unsigned NOT NULL DEFAULT '0',
  `payment_account_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `payment_account_id` (`payment_account_id`),
  KEY `expires_date` (`expires_date`)
);

with the following data

INSERT INTO transaction
 (product_id, purchase_date, expires_date, expires_date_calculated, payment_account_id)
VALUES
 ('a', 1164357246, 0, 1264357246, 2),
 ('a', 1345117819, 0, 1435413246, 1),
 ('b', 1345117907, 0, 1440683646, 2),
 ('c', 1345119400, 1464357246, 0, 5),
 ('b', 1345119625, 0, 1464357246, 10),
 ('a', 1345120085, 0, 1464357246, 2),
 ('b', 1345121092, 0, 1464357246, 5),
 ('a', 1464357246, 0, 1564357246, 2) ;

And I have a function which triggers the following query 32 times with different date values

SELECT COUNT(DISTINCT payment_account_id) AS cnt
FROM transaction
WHERE (purchase_date < 1345120085
       AND (expires_date > 1345120085 OR expires_date_calculated > 1345120085))
  AND (
    `product_id` IN
    (
      'a',
      'b'
    )
  );

And it works very slow. I want to replace it by one big query with 32 SUM(IF())

SELECT
  sum(IF((purchase_date < 1345120085
          AND (expires_date > 1345120085 OR expires_date_calculated > 1345120085)),
        1, 0)) AS day1
#...
FROM (
       SELECT
         payment_account_id,
         (purchase_date)           AS purchase_date,
         (expires_date)            AS expires_date,
         (expires_date_calculated) AS expires_date_calculated
       FROM transaction
       WHERE
         `product_id` IN
         (
           'a',
           'b'
         )
         AND payment_account_id IS NOT NULL
        GROUP BY payment_account_id
     ) AS tmp;

SQL Fiddle with example

But I get wrong result. What I am doing wrong?

Ok. I found the solution.

SELECT
  SUM(IF(day1 >0, 1, 0))
  #...
  FROM(
      SELECT
      payment_account_id, sum(day1) AS day1
      #...
        FROM (
           SELECT
             payment_account_id,
             IF((purchase_date < 1345120085 AND (expires_date > 1345120085 OR expires_date_calculated > 1345120085)), 1, 0) AS day1
            #...
           FROM transaction
           WHERE
             `product_id` IN
             (
               'a',
               'b'
             )
             AND payment_account_id IS NOT NULL
          ) AS tmp
      GROUP BY payment_account_id
     )
     AS tmp1;


Backup & Restore Error in MS SQL Server 2016 I have a backup from SQL Server 2016. I want to restore it in my pc but I am getting this error.

  The database was backed up on a server running version 13.00.1400.
  That version is incompatible with this server, which is running
  version 11.00.5343.

I have searched in Google and have try to upload some patches but it's still the same.How can I fix it.

The error message

  The database was backed up on a server running version 13.00.1400. That version is incompatible with this server, which is running  version 11.00.5343.

means that you are having backup of sql server 2016 RC3 --> 13.00.1400 and you are trying to restore it on your PC which is running 11.00.5343 --> sql server 2012 SP2 + GDR 3045321 fix.

Restoring the database from higher version to lower version is not supported using backup restore or attach/detach methods.

A RESTORE HEADERONLY FROM DISK = 'X:\yourBackupFile.bak' will give you the major, minor and software version build numbers that you can confirm.

Downgrading database is a pain !

Best is to install FREE DEV sql server 2016 RTM + CU1 on your PC machine and restore the backup.


Use Multiple Expressions In Select List I am attempting to run this syntax

Select
[StoreName] = (Select *
              FROM OPENQUERY(192.168.5.812,'Select
                                    storename
                                    FROM stores') x
              WHERE x.storename = y.storename)
[StoreManager] = y.storemanager
FROM managerinfo y

But this gives me an error of

  Msg 116, Level 16, State 1, Line 28
  Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Is it possible to do this while using OPENQUERY?  How can I achieve my desired result?

Try changing your * with just the column name. Although it means the same, now it should be clear to SQL Server:

SELECT
    [StoreName] = (SELECT storename
                     FROM OPENQUERY(192.168.5.812, 'SELECT storename
                                    FROM stores') x
                    WHERE x.storename = y.storename)
    [StoreManager] = y.storemanager
FROM
    managerinfo y ;


SQL Server 2008 R2 Remote Connection issue I have installed SQL Server 2008 R2 in mixed mode, but currently issue is I can't use SQL through ip nor ip with port, even I have enabled TCP/IP and open port in firewall too.

I tried to telnet 127.0.0.1 1433 to check whether its working or not but its not connecting
I tried almost every possible things but failed in each.

How do I get this to work?

By default Sql server may not be listening for TCP/IP connections.

Did you check this stackoverflow question?

Also check start the "sql server agent" service is started.


looking for a lighter alternative to cognos express I have a client that has about 10 staff. They want to add business intelligence services to an existing MySql database.

It is a database containing purchasing and readership data for publications over the past 30+ years. They are an Audit Bureau, and thus, on a yearly basis, they audit the numbers that publishers report so that these numbers are they sent to advertisers and can be used to justify the cost of ads in their publications.

I need a small- to mid-sized OLAP cube option. Cognos Express is the first one I'm trialing. It feels more like a mid-sized one than a smaller end one so I'm wondering if anyone knows of any alternatives.

I have deployed  Logi Reports at a client and the free version is just fine for the users where I am.  You can pay for the upgraded version which offers OLAP-like abilities. 2016 Update Logi no longer issues a free version. The paid version now has dashboard and analytic tools.

This product was mentioned in another post iccube as a free OLAP but I have not used it.  It looks to me like it tries to emulate the built in features that Oracle has on a Java platform.

You don't mention the infrastructure or technical skills of the client.  These are important considerations.  A tool that is "too complicated" will only results in calls to you to dumb it down.

The features I recommend considering are:

how many brands of database can it connect to?  Does it work with the versions you have?
How easy is to configure access and permissions with the client's infrastructure? Active Directory is a standard for many places and should be an option.
How fast is the rendering engine or how much patience does your client have to wait for results?  (Of course database structure has a much bigger effect on performance than the reporting engine)
Do you want a product with wizards that make it easy to deploy reports?
Can you craft a query in SQL and then drop it into the report?  This is incredibly handy for testing and development.


Ola Hallengren DataBaseBackup to URL results in un-equal file sizes causing IO Device Error we have been using Ola's Maintenance Solution and Database backup direct to URL since we migrated our system to Azure IAAS.  We have multi terabyte DB's (approx 40% Filestream data) and therefore split the backups over multiple files to keep them below 195GB each.
We had no issues until the last couple of weeks where the Full backup started to fail with an IO Device Error, telling me that the file size was greater than the 195GB limit so I increased the @NumberOfFiles parameter.
The first week this worked, the next week it failed again even though the DB had only grown a few GB in size.
I ended up having to add 10 to the number of files parameter and the backup completed successfully.
The issue is, most files written to are approx 90GB and 4 are approx 180GB.
Does anyone know the reason for the un-equal file sizes and a way to prevent this?
EXECUTE [dbo].[DatabaseBackup]
@Databases = '<dbname>',
@URL = '<storage account>',
@BackupType = 'FULL',
@Compress = 'Y',
@Verify = 'Y',
@CheckSum = 'Y',
@LogToTable = 'Y',
@MaxTransferSize = 4194304,
@Blocksize = 65536,
@NumberOfFiles = 36

Thanks in advance

All Ola's procedure does is to call the BACKUP command. I.e., the fact that you are using Ola's procedure is irrelevant. If you want to determine this with 100% certainty, then just pick up the backup command executed (should be in the CommandLog table methinks, or worst case using a trace) and execute this directly (possibly using an Agent job).
My guess is that you are unfortunate combined with using backup compression. I.e., some backup thread just happen to run into data that doesn't compress well, and because of that the size of that file becomes larger than the other thread's files.
You can verify this by turning off compression, and see that you get a much more even file size. I understand that this might not be practical because of the database size, but perhaps you have a smaller database with same symptom for which you can do this test? I've asked around and we'll see if I can get a confirmation on my theory.
I tried this myself and did indeed see a bigger file size variation with compression.
FWIW, in 7.0 (when striping was introduced) the backup thread pushed as much data into the destination (file or tape) as the destination could consume. This gave best backup throughput, but uneven file size if one tape device (or file destination) happened to be much slower than the other. The algorithm changed in 2000 to spread the data evenly across the backup files. My assumption is that this determination of how the data is to be spread is done before compression.
SQL Server pre-allocates the backup file for performance reasons. When you use compression it has to guess what size you end up with. You can end up in a situation where it tries to pre-allocate more than what it would eventually need, causing the backup to fail. Trace flag 3042 changes this. It causes SQL Server to allocate storage on-the-fly. I very much doubt this will change anything for you, but thought I'd mention this so you can be aware of what the trace flag does and don't get your hopes up too much in case you read about the trace flag and decide to test it.


How can Amazon Redshift be built based on PostgreSQL if Postgres is row-based? According to Amazon, Redshift is based on PostgreSQL, and is a column-oriented database management system. This looks to me as a very self-conflicting statement, isn't Postgres a row-oriented database?

A Postgres server is a lot of things, not just row-oriented access methods:

(The image above is from the Postgres documentation.)
Postgres source code is available for anyone to use under a very permissive license. To implement a DBMS that is "based on PostgreSQL" you don't have to rewrite it from scratch. Say, if you want to introduce a different layout for storing your data you can likely reuse with little or no changes the main server process, client APIs, query parser and rewriter, most of utility and security functions. You might need to modify the plan generator and executor, while adding new access methods and replacing parts of the page storage manager. Given the resources at Amazon's disposal, this doesn't look like an impossible undertaking.
Amazon basically say as much:

the specialized data storage schema and query execution engine that Amazon Redshift uses are completely different from the PostgreSQL implementation. [...] Amazon Redshift stores data in columns, using specialized data compression encodings for optimum memory usage and disk I/O. Some PostgreSQL features that are suited to smaller-scale OLTP processing, such as secondary indexes and efficient single-row data manipulation operations, have been omitted [...]