How can I get individual rowcounts like SSMS? I have a client c# program that is executing stored procedures via ExectueNonQuery, including catching the PRINT and Error output with InfoMessage events.  It works fine, but I have noticed something odd.

When I execute a stored procedure from SSMS, it displays rowcounts for each individual SQL statement that is executed in the Messages tab (as though it were coming from the InfoMessages).  However my program never sees these messages, though it does catch all of the same other output.  Instead, it just returns the rows affected in the ExecuteNonQuery function result that is the sum of all of the individual rowcounts (which is kind of useless).

For example, this procedure:

use [tempdb]
go

SELECT  *
INTO    MyCols
FROM    sys.columns
go

CREATE PROC foo As

    UPDATE  MyCols
    SET     name = name + N''
-- SSMS shows (662 row(s) affected)

    UPDATE  MyCols
    SET     name = name + N''
    WHERE   name like '%x%'
-- SSMS shows (59 row(s) affected)

PRINT 'bar'
-- both SSMS and ExecuteNonQuery get this

-- ExecuteNonQuery returns 721 rows affected
GO

When the foo proc is run, SSMS displays rowcounts of 662 and 59, but ExecuteNonQuery only returns the total of 721.

So, how can I get the same information that SSMS is getting?

Just to be clear here: I am not interested in how to change the stored procedures to add PRINT @@ROWCOUNTs after every SQL statement.  I know how to do that and it's not an option most of the time for a variety of reasons.

I am asking how to do what SSMS is doing here.  I can change the client code all I want at this point (for right now, anyway) and I would like to do it right.

The SqlCommand.StatementCompleted event will fire after each statement in a batch, and one of the properties of the event (well, pretty much the only property) is the number of rows affected by the statement that fired the event.

Some notes:

A requirement of getting this info is that you did not specify SET NOCOUNT ON;, or conversely, you did specify SET NOCOUNT OFF;.
All events fire at the completion of each Execute___(), not during the execution.
The StatementCompletedEventArgs.RecordCount includes row counts from SELECT statements, whereas the SqlDataReader.RecordsAffected property only reports row counts from DML statements (INSERT, UPDATE, DELETE, etc).
The StatementCompleted event does not include the individual SQL statement from the batch that fired the event. However, the event handler is sent the sender as an input parameter and this is the SqlCommand of the query batch, and you can see that batch by casting sender to SqlCommand and then looking at the CommandText property (this is shown in the example below).

The documentation is very sparse on this so I have worked up an example that shows this event firing for both ExecuteNonQuery and ExecuteScalar, as well as for both ad hoc queries and stored procedures (i.e. SqlCommand.CommandType of Text vs StoredProcedure):

using System;
using System.Data;
using System.Data.SqlClient;

namespace StatementCompletedFiring
{
    class Program
    {
        static void Main(string[] args)
        {
            using (SqlConnection _Connection =
                          new SqlConnection("Integrated Security = True;"))
            {
                using (SqlCommand _Command = new SqlCommand(@"
SET NOCOUNT OFF; --  ensures that the 'StatementCompleted' event fires

EXEC('
CREATE PROCEDURE #TestProc
AS
SELECT * FROM sys.objects;

SELECT * FROM sys.tables;
');

SELECT * FROM sys.objects;
", _Connection))
                {

                    _Command.StatementCompleted += _Command_StatementCompleted;

                    try
                    {
                        _Connection.Open();

                        _Command.ExecuteNonQuery();

                        _Command.CommandText = @"
SELECT 123 AS [Bob];

WAITFOR DELAY '00:00:05.000'; --5 second pause to shows when the events fire

SELECT 2 AS [Sally]
UNION ALL
SELECT 5;
";
                        Console.WriteLine("\n\t");
                        Console.WriteLine(_Command.ExecuteScalar().ToString());
                        Console.WriteLine("\n");

                        _Command.CommandType = CommandType.StoredProcedure;
                        _Command.CommandText = "#TestProc";
                        _Command.ExecuteNonQuery();
                    }
                    catch (Exception _Exception)
                    {
                        throw new Exception(_Exception.Message);
                    }
                }
            }
        }

        static void _Command_StatementCompleted(object sender,
                                                StatementCompletedEventArgs e)
        {
            Console.ForegroundColor = ConsoleColor.Red;
            Console.Write("\nQuery Batch: ");
            Console.ForegroundColor = ConsoleColor.White;
            Console.WriteLine(((SqlCommand)sender).CommandText);

            Console.ForegroundColor = ConsoleColor.Red;
            Console.Write("Row(s) affected: ");
            Console.ForegroundColor = ConsoleColor.White;
            Console.WriteLine(e.RecordCount.ToString() + "\n");

            Console.ResetColor();
        }
    }
}

OUTPUT:

  Query Batch:
  SET NOCOUNT OFF; --  ensures that the
  'StatementCompleted' event fires

  EXEC(' CREATE PROCEDURE #TestProc AS SELECT * FROM sys.objects;

  SELECT * FROM sys.tables; ');

  SELECT * FROM sys.objects;

  Row(s) affected: 453

  Query Batch:
  SELECT 123 AS [Bob];

  WAITFOR DELAY '00:00:05.000'; --5 second pause

  SELECT 2 AS [Sally] UNION ALL SELECT 5;

  Row(s) affected: 1

  Query Batch:
  SELECT 123 AS [Bob];

  WAITFOR DELAY '00:00:05.000'; --5 second pause

  SELECT 2 AS [Sally] UNION ALL SELECT 5;

  Row(s) affected: 2

  123

  Query Batch: #TestProc
  Row(s) affected: 453

  Query Batch: #TestProc
  Row(s) affected: 17


Doing a comparison on two csv files with no Primary Key I'm wondering if in SSIS there is a way to compare two csv files(with the same structure) with no reference key to make a record unique.

To give you a better picture from what I mean; In initial load we load data from a csv file into a table, in the subsequent loads, we will get another csv file (same format) that can have different records. We should compare what we have in the table vs. what exist in the new version of the csv file and only load the changed sub set. If there is a new records, it should be inserted, if update, it should be updated, if record deleted in the source, it should be marked as inactive in the table.

Now my questions are:

Is there any way to compare two csv files in SSIS, while there is no key to make the
records unique?
How can we compare two tables in SQL Server, while there is no key to make
records unique?

The volume of data in the csv file is quite high, more than 20 millions records!

Any idea is appreciated.

Thank you,

Nazila

If you have your first CSV loaded into a table, you can just as easily load the other one into a staging table (presumably with the same structure as the 'real' one).  Then you can get the new rows by

SELECT * FROM staging_table
EXCEPT
SELECT * FROM real_table
;

Rows missing from the new CSV can be get reversing the two sides around EXCEPT.  However, given the lack of a key on the staging table (and hopefully not on the real one - it's not clear from your question), deleting rows based on this comparison can be painful, especially with so many rows.

You can drop the staging table once you have finished.

(As far as I see, this approach will work in any RDBMS.)


Why MySQL doesn't utilize leftmost Index field (UK - col1, col2, col3) in select query? I have a department table with the following structure

Create Table

CREATE TABLE `department` (
  `id` binary(16) NOT NULL,
  `name` varchar(255) NOT NULL,
  `type` int(11) NOT NULL,
  `status` tinyint(4) NOT NULL,  -- Possible values are 0,1,2 only
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_2xsp2nild3xbgkg4pln7cviib` (`status`,`type`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Following query doesn't utilise the status index as it is the left most column in unique constraint.

EXPLAIN SELECT * FROM department d WHERE d.status = 1;

    id  select_type  table   partitions  type    possible_keys                 key     key_len  ref       rows  filtered  Extra
------  -----------  ------  ----------  ------  ----------------------------  ------  -------  ------  ------  --------  -------------
     1  SIMPLE       d       (NULL)      ALL     UK_2xsp2nild3xbgkg4pln7cviib  (NULL)  (NULL)   (NULL)       4    100.00  Using where

But when querying on another table which has unique constraint on a single column, it uses the index.

CREATE TABLE `account_unit` (
  `id` binary(16) NOT NULL,
  `status` tinyint(4) NOT NULL,
  `unit_name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_2x8b55sxftahu8cjbqld9sw9b` (`unit_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

EXPLAIN SELECT * FROM account_unit au WHERE au.`unit_name` = 'AU1'

    id  select_type  table   partitions  type    possible_keys                 key                           key_len  ref       rows  filtered  Extra
------  -----------  ------  ----------  ------  ----------------------------  ----------------------------  -------  ------  ------  --------  --------
     1  SIMPLE       au      (NULL)      const   UK_2x8b55sxftahu8cjbqld9sw9b  UK_2x8b55sxftahu8cjbqld9sw9b  767      const        1    100.00  (NULL)

As per my understanding, left most column on a multi column constraint will be utilized  as index on select queries. Enlighten me if I'm wrong.

It's almost certainly due to the low cardinality of the index on the first column.  Since there are only 3 values, the optimizer decides that it is better do do a full scan on the table.

Using the index on the first column will point to about one third of the rows.  Looking up that many rows based on the primary key will likely lead to practically all of the primary key pages being pulled into the buffer along with a large portion of the secondary index pages, leading to more memory usage and potentially disk traffic.  Overall, it is more efficient and usually about the same speed to just do a full scan on the data if a large portion of the data is likely to be selected based on a secondary index lookup.


how to upgrade and migrate postgres dbs to new server machine? my production databases run on a centos 5.5 virtual machine. This server has 2 postgres 8.4 clusters installed on 2 different partitions; now my idea is to upgrade both db version and server os (centos 6) in order to set up a newer machine with also much more disk space. In this way i will be covered for many years.
I read postgres documentation and pg_upgrade seems to be a very useful tool but,if i understood well, it can be used only on same machine and on same mounting point. So how can i get my goal? pgdumpall command? note that clusters' sizes are (at the moment) 10gb and 1gb ,but cluster 1 grows up quite fast.

thanks

The simplest approach is to simply use pg_dumpall -f cluster.dump, then restore with psql -f cluster.dump on the new host.

Slightly more sophisticated is to dump globals, then dump each DB separately:

pg_dumpall --globals-only > globals.sql
psql -qAt -c 'SELECT datname FROM pg_database WHERE NOT datistemplate;' -0 |\
  xargs -0 -i pg_dump -Fc -f "{}".pgbackup "{}"

Restore with something like:

psql -f globals.sql
for f in *.pgbackup; do
  pg_restore -f "$f" -C -d postgres
done

In all these cases you may need to run as the postgres user.

A 10GB dump is quite reasonable.

If you need to do a low downtime upgrade, there are a few more sophisticated options:

Set up slony-I or Londiste to replicate changes from the old server to the new one; or
Install the same version of PostgreSQL on the new server. Set up streaming replication. Fail over to the new server, then pg_upgrade to a current PostgreSQL version.


Receiving error while creating stored procedure to insert Excel files into a table I was wondering if you could tell me what is wrong with my query?

The parameter @TableName is an input parameter.

Why do I have to declare it again?

CREATE PROCEDURE [dbo].[OpenExcel]
    @FileName nvarchar(2000),
    @TableName varchar(2000)

AS
    BEGIN
        DECLARE @Provider nvarchar(2000)
            SET @Provider = 'Microsoft.ACE.OLEDB.12.0'

        DECLARE @etValue nvarchar(4000)
            SET @RetValue = 'select *
                             from openrowset(''' + @Provider +
                             ''',''Excel 8.0;Database=' +
                             @FileName + ';hdr=no' + ''',
                             ''select " from [Sheet1$]'' )'

        DELETE FROM @TableName

        INSERT INTO @TableName

        EXEC(@RetValue)

    END

An image of the stored procedure

The error message I receive is:

  Msg 1087, Level 16, State 1, Procedure OpenExcel, Line 26
  Must declare the table variable "@TableName".

Well I'm trying to pass the Excel path (@FileName) and table name (@TableName; in which the data should be inserted) to the stored procedure.

How can I achieve that?

I have different tables with different structures. Please help me with this issue.

Here is your solution:

Create Procedure [dbo].[OpenExcel]

 @FileName nvarchar(2000),

  @TableName varchar(2000)

AS Begin

    declare @Provider nvarchar(2000);

    set @Provider = 'Microsoft.ACE.OLEDB.12.0';

    declare @RetValue nvarchar(4000) ;

    set @RetValue='select * from openrowset("' + @Provider + ''',''Excel 8.0;Database=' + @FileName +';hdr=no'+''', ''select * from [Sheet1$]" )'

    declare @Bufr nvarchar(4000)

    set @Bufr='delete from '+@TableName+';'

    set @Bufr=@Bufr+'insert into '+@TableName  exec(@RetValue)

End


Do I need to `mysql_upgrade --verbose -u root -p` each time when `source backup.sql`? I am upgrading mysql 5.1 to mysql 5.5 in centos 7 server.

As time limit,I only mysqldump and source several small database to mysql 5.5,and then mysql_upgrade --verbose -u root -p,everything is ok.

Several days later, I import a big database db3 to mysql 5.5.

[root@localhost ~]# mysql -u root -p < db3.sql
Enter password:
[root@localhost ~]# mysql_upgrade --verbose -u root -p
Enter password:
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
This installation of MySQL is already upgraded to 5.5.60-MariaDB, use --force if you still need to run mysql_upgrade
[root@localhost ~]# mysql_upgrade --verbose --force -u root -p
Enter password:
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Phase 1/4: Fixing views

Pls notice This installation of MySQL is already upgraded to 5.5.60-MariaDB, use --force if you still need to run mysql_upgrade.
Do I need to run mysql_upgrade --verbose -u root -p again?

mysql_upgrade may or may not be needed.  It is useful after you upgrade the server version.

In some cases (I don't remember 5.1 to 5.5), there were changes to datatypes that necessitated "fixing" the schema and the data in table(s) affected by such.

If you are dumping from the same major version that you are loading into, there should be no need for running mysql_upgrade.

If you have clues of what it changed, we can discuss further.

(Note:  Even 5.5 is getting rather "old".  I hope you will be moving forward soon.)

As I understand it, mysql_upgrade leaves a file behind after it runs.  The file tells it "I have upgraded to X.YY.ZZ, so I don't need to run again".

Apparently, that file (on your machine) says that it has upgrated to 5.5, so it should not be needed again.

Do you have any symptoms of things not working 'right'?  If so, let's approach the question from there.


In which form Microsoft SQL password should be given when creating a user? I am creating a Microsoft SQL server user with the guidance of this MSDN article and giving the system the following commands:

  CREATE LOGIN MyName WITH PASSWORD = 'MyPass';
  USE My$DatabaseName;
  CREATE USER MyName FOR LOGIN MyName;
  GO

I use SQL Studio to look again the password by clicking the name on Logins list. There I see too many stars on the password that it could represent my given password.

My question is that if I gave the password according to that I wrote (in plain text), did I give it correctly? I have problems to login to the database and I don't know now is the password there ok or not.

(The link on MSDN does not say anything about encrypting, but I cannot say if the example passwords follow some encrypting rule or not.)

Yes, it is there correctly. You have run CREATE LOGIN correctly.

The number of stars in the SSMS box is just a mask and is unrelated to your actual password. SSMS doesn't know or try to read your password from the server

Note that SQL Server doesn't store your password so it can't relate to the **** in SSMS anyway. SQL Server hashes it into the password_hash column of sys.server_principals


Restrict executeQuery in Java from data manipulation in an Oracle database I am developing a query browser in Java where I want to restrict users from manipulating data. So I am using executeQuery as I searched many questions and all are answered as executeQuery is used to select and does not allow data manipulation as this on link.

It's working just fine with MySQL but when its comes to Oracle I am getting the error

  ORA 00900: invalid SQL statement

but the real problem is, in database the statement is updating the record. I am getting the same error for update, delete, insert and drop but all commands are manipulating the data in database.

Now I have the option to check whether my query string start with data manipulation keywords but I am trying to avoid that checking. It's working absolutely fine in MySSQL, so I amnot getting what is the issue with Oracle.

Below is the code sample:

Connection conn = null;
Statement stmt = null;
ResultSet query_set = null;
try {
    String query = "insert into users values(1,'name')";
    Class.forName ("oracleDriver"); //Oracle driver as our database is Oracle.
    conn = DriverManager.getConnection("oracleDbUrl", "dbUsername", "dbchecksum"); //login credentials to the database
    stmt = conn.createStatement();
    query_set = stmt.executeQuery(query);
} catch(Exception e) {
    e.printStackTrace();
}

Kindly suggest how I can restrict users from data manipulation without applying check on the query string and what is the reason behind this Oracle behavior.

The JDBC standard does not specify the executeQuery() method behaviour, apart from requiring it to throw an exception when the statement returns something other than a single result set. The Oracle driver seems to comply with this requirement. The method is intended to be used with queries, but nowhere in the the documentation is it stated that it should prevent you from using it with DML statements.

You can choose to handle this exception and roll back the transaction, if you want to prevent changes to your data. Obviously, you will have to disable autocommit for this to work.

The correct way to achieve what you want would be to grant only select privileges to the user ID your application connects with.


School database design I am trying to design a database management system with my PHP and intermediate MySQL knowledge, keeping normalization in mind.

These are the assumptions:

The school has many students from grade 1 to grade 6.
Each grade is divided into classes (e.g. we have grade 1 A, grade 1 B, grade 1 C) due to the class size.
Grade 1 to grade 3 offer the same subjects, but from grade 4 to grade 6 users can select which courses to offer.
Many teachers teach ONLY one subject BUT can teach MORE THAN one grade.

As an intermediate programmer, it all got twisted.
I was able to create the following tables and columns

students{(id),(name),(name),(parent_id)} -student details

parents{(id),(name),(email),(phone),(address)} - parent details

teachers{(id),(name),(email),(subject_id)} - teachers details

subjects{(id),(name),(description)} - subject details

attendance{(date),(status),(student_id)} - attendance of student with id of student_id,
                                           status in boolean TRUE = present, FALSE=absent

This is how far I have gone.

Now I want to show a table where a student can be linked to all the subjects he/she is taking and a teacher linked to all the grades (no need to specify classes) he is teaching. How can I do that?

I will appreciate it if I can be pointed in the right direction.

Homework questions like this will generally not elicit answers with code. Stack Exchange is not a place where you can come to get others to do your homework for you. However you have only asked for pointers of where to look, so to that end, I will inform you that what you are looking for is called a many-to-many relationship, and requires a third table that maps table 1 to table 2. Usually, this third table just has two columns, each containing the primary key of one of the main tables, and the primary key of this table covers both columns.

Read up:

http://en.tekstenuitleg.net/articles/software/database-design-tutorial/many-to-many.html
http://www.tomjewett.com/dbdesign/dbdesign.php?page=manymany.php


Innovative solution required - Remotely create stored procedure I have a local stored procedure that I want to distribute out to many other SQL Servers without having to log onto each one manually.

All servers can access the same network share if I want to go down the .sql and SQLCMD route.

Alternatively all servers are linked to a "Central" server using a the same login credentials.

Essentially I want to avoid using lengthy OPEN QUERY's because the syntax becomes a management nightmare when working with lengthy stored procedures.

Thoughts welcome/appreciated

You could simply create a Group wihtin the registered servers pane in SSMS and add all servers to that group. Then you are able to run a query against the whole group. Just run the Create procedure (or whatever you need) there and it will be executed on all servers in the group (that could have connected with success).

Be aware that select queries ran against a group come back with results in a merged resultset that basically shows all results from all servers merged together with a ServerName column added for the origin (that does not allow any filtering by servers). This can be configured more detailed in SSMS at

  Tools -> Options -> Query Results -> SqlServer -> Multiserver Results

In case the tables or views on the different servers have a different definition, the merging of the result will fail (in case you use select *). In that case use a query with defined columnnames that allow SSMS to merge the result.


MySQL Function Error In an inherited database I have two tables of current interest, records and logins.  login tells me the location of the user and where and when his login occured (in and out times included).

The fun part is that there is no correlating mark between the login session and the applications ran.  To solve the issue I'm creating a new table login_apps and am trying to use a function to combine the two tables.

So far I have:

drop function if exists ltop;
delimiter $$

create function ltop(id int, u varchar(10), s datetime, e datetime)
    returns text
    language sql
begin
    declare pid varchar(40);
    declare pname varchar(63);
    declare my_return varchar(100);
    declare cur1 cursor for (select usageProgramID
                                from records
                                where usageUser=u
                                    and usageWhen>=s
                                    and usageWhen<=e
                                    and usageProgramID!="");
    open cur1;
    read_loop: loop
        fetch cur1 into pid;
        set pname = (select programName from Programs where programID=pid);
        insert into login_apps(`sid`, `programName`) values(id, pname);
    end loop read_loop;

    close cur1;

    return "finished";
end;$$
delimiter ;

It enters into the system without issue and the insert is working as expected on a test.  The issue I'm coming up with is that when it's run as:

select ltop("265548", "user", "2013-02-21 13:54:27", "2013-02-21 14:32:18");

I get the error:

ERROR 1329 (02000): No data - zero rows fetched, selected, or processed

Shouldn't the data be returned be "finished"?

Secondly, I'm not really sure what would be considered the best way to run this on every value of logins any pointers for going that route?

ERROR 1329 (02000): No data - zero rows fetched, selected, or processed

That error is not about your RETURN; it's this:

fetch cur1 into pid;

When the cursor runs out of data, this error is thrown.  This could be because the SELECT query declared in the cursor didn't find any rows, or because it did find rows but as you iterated through it, you read past the last row.  If nothing happened when the cursor ran out of data, how would you ever break out of read_loop?

You need to trap the error with a handler, which will prevent execution of the procedure from terminating, and set a variable you can use to break out of the loop.

At the top, with the other variables:

DECLARE done TINYINT DEFAULT 0;

Then, after declaring but before opening the cursor:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

Inside your loop:

fetch cur1 into pid;
IF done = 1 THEN LEAVE read_loop; END IF;

This should solve your immediate problem.

Other observations:

Your function modifies the database, so at the top, you should have this:

returns text
MODIFIES SQL DATA
language sql

...although, really, this would be more appropriately written as a stored procedure rather than a function.  MySQL supports non-deterministic stored functions that modify the database, you there are potential issues with the binary log used for point-in-time recovery or replication that can pop up.

To process all of the data rather than one value at a time, you could use nested cursors -- the outer cursor fetching whatever data you're feeding into this function and the inner cursor -- inside an additional BEGIN/END block doing the logic you've got, above with values fetched in the outer cursor.  This requires resetting of the done value back to 0 after each iteration of the inner block so that the other block doesn't stop prematurely.

The inner subquery is unnecessary, since the cursor could be declared with...

SELECT p.programName
  FROM records r JOIN Programs p ON p.programID = r.usageProgramID
 WHERE ...

...but, in fact, this entire function seems like it may be unnecessary.

In SQL, it's typically best if you you tell the database what needs to be done ("declarative"), as opposed to how to do it ("procedural").

Thinking too procedurally is an SQL antipattern.

I don't know your column names in the 'login' table, so you'll need to correct those, but consider the following query.  As far as I can tell, it does exactly what the function does, for all logins and all matching records.

INSERT INTO login_apps(`sid`, `programName`)
SELECT l.session_id, p.programName
  FROM logins l
  JOIN records r ON r.usageUser = l.user_name
                AND r.usageWhen >= l.start_time
                AND r.usageWhen <= l.end_time
                AND r.usageProgramID != ""
  JOIN Programs p ON p.programID = r.usageProgramID;

Done.

For future maintenance of the "login_apps" table, a trigger on the "records" table could look up the related login and write the new entries into "login_apps" every time "records" has an insert or an update... or when the stop time is logged on the "login" table, an update trigger could read "records" and insert into "login_apps".

Final thought, it would probably be better design if you stored the program ID rather the program name in the login_apps table... unless the "records" table actually has an auto_increment primary key, in which case, that would be the value you should really store in login_apps.


Aggregating Multiple COTS Databases into a Data Warehouse or similar for Reporting I am sure this is a very basic question, so I apologize, but I've read lots of articles and done a lot of google searches and have not found any leads for this particular challenge I am looking to address.  I appreciate any guidance that can be provided.

I have an enterprise environment where we need to have multiple instances of a COTS application spread geographically around the world.  These applications are using SQL Server Enterprise Editions 2008, 2012, and 2014 (different versions of the COTS software) and have to remain in their geographic isolation.  I have created an SSRS server to facilitate being able to give the customers access to their specific geographically isolated COTS database data.  However, the demand has now grown where we want the users to be hitting a data warehouse for the SSRS data so we don't risk impacting our production systems.  Additionally, my team would like to be able to aggregate the data for some enterprise reporting.
 I have read a bunch on data warehousing but I can't seem to find any solutions to bringing 14 different instances of a single COTS application into a single SQL server for reporting against.  Ideally we'd like to be able to track the changes as well.  The 3 databases per COTS instance are all named the same regardless of the version.  Two of the databases are relatively static (maybe 200 updates per day each), but one is very active (70k+ writes per day).  The solution doesn't have to be real time, but does need to at least offer a daily load.

Database 1 ~ 600 MB and 200 writes/day

Database 2 ~ 1.4 GB and 200 writes/day

Database 3 ~ 35-120 GB (depending on location) and 70k writes/day (all application events and audit) [old entries are never modified so would only need to send the delta]

So how can I bring in 42 Vendor Databases into a single data warehouse?  Is there an approach better than the data warehouse to pursue?  Thank you very much in advance, and if there is anything else that is needed to offer a solution, please let me know.

It appears that you just want a copy of all your databases centrally located, which could then be used to run the same report for different regions, just by changing the datasource?

You could acheive this with a single Server and a single instance.

While I wouldn't call this a data warehouse (per se), but it might be the simplest solution to solve your requirements.

With this approach you would end up with 42 databases on your instance and you could name them anything thing you like, but I would suggest including the source location in the name of the database

- for example
   [Source Server].SourceDB    -->      [Dest Server].DestDB
  [Sydney.xyz.com].Database1   -->  [Central.xyz.com].Sydney_Database1
  [Sydney.xyz.com].Database2   -->  [Central.xyz.com].Sydney_Database2
[Brisbane.xyz.com].Database1   -->  [Central.xyz.com].Brisbane_Database1
[Brisbane.xyz.com].Database2   -->  [Central.xyz.com].Brisbane_Database2
etc...

You could achieve this via

Replication ,or
Log Shipping

Both of these solutions are relatively simple (assuming you have network access between all your nodes), and replication you only need to replicate speific objects (tables, procs etc) that you use in your reports (whereas log shipping will be the whole database), but replication can be problematic to troubleshoot.


Why does this logon trigger work in one server but not another? I created the below trigger in the same manner in the SYS schema on two different Oracle 11g servers, without any errors.

CREATE OR REPLACE TRIGGER LOGON_TRG
  AFTER LOGON ON DATABASE
BEGIN
    if (user in ('mytestro', 'mytestrw')) then
      EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = mytest';
    end if;
exception
  when others
    then null; -- prevent a login failure due to an exception
END logon_trg;
/

On one server I can tell it is working since I can select any table in mytest as soon as I logon with either account.  On the other server it says the table does not exist (ORA-00942).  If I issue ALTER SESSION SET CURRENT_SCHEMA = mytest; manually I can access the table, so it seems the trigger is not firing.

Assuming your trigger is enabled, I am surprised at your trigger working at all.

Usernames are case-insensitive by default and treated as uppercase strings internally. If you create a user called mytestro or mytestrw, the actual usernames will be MYTESTRO and MYTESTRW. Normally, a condition like the one in your if statement, will be false.

Sure, it is possible to create case-sensivite, lowercase usernames, but that is quite rare and I consider it bad practice, because it has no benefits, it just makes administration cumbersome.

Instead of:

create user mytestro ...

You can use:

create user "mytestro" ...

The former creates a user called MYTESTRO. The latter creates a user mytestro. These are not identical.

The first one behaves as:

SQL> show user
USER is "MYTESTRO"

While the second one behaves as:

SQL> show user
USER is "mytestro"

You can use the above to check the actual usernames when logged in (or select user from dual if your client does not support show). Your trigger in its current form does not work with the first user above, and does work with the second user above.


Restore SQL Server 2012 backup to a SQL Server 2008 database? Is there a way to restore a SQL Server 2012 database backup to a SQL Server 2008?

I tried to attach file, it does not work.

You have couple of options :

Option A : Script out database in compatibility mode using Generate script option :

Note : If you script out database with schema and data, depending on your data size, the script will be massive and wont be handled by SSMS, sqlcmd or osql (might be in GB as well).

Option B:

First script out tables first with all Indexes, FK's, etc and create blank tables in the destination database - option with SCHEMA ONLY (No data).

Use BCP to insert data

bcp out the data using below script. set SSMS in Text Mode and copy the output generated by below script in a bat file.

-- save below output in a bat file by executing below in SSMS in TEXT mode

-- clean up: create a bat file with this command --> del D:\BCP\*.dat

select '"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" ' /* path to BCP.exe */
    +  QUOTENAME(DB_NAME())+ '.' /* Current Database */
    +  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.'
    +  QUOTENAME(name)
    +  ' out D:\BCP\'  /* Path where BCP out files will be stored */
    +  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'
    +  REPLACE(name,' ','')
    + '.dat -T -E -SServerName\Instance -n' /* ServerName, -E will take care of Identity, -n is for Native Format */
from sys.tables
where is_ms_shipped = 0 and name <> 'sysdiagrams'                       /* sysdiagrams is classified my MS as UserTable and we dont want it */
/*and schema_name(schema_id) <> 'unwantedschema'    */                             /* Optional to exclude any schema  */
order by schema_name(schema_id)

Run the bat file that will generate the .dat files in the folder that you have specified.
Run below script on the destination server with SSMS in text mode again.

--- Execute this on the destination server.database from SSMS.

--- Make sure the change the @Destdbname and the bcp out path as per your environment.

declare @Destdbname sysname
set @Destdbname = 'destinationDB' /* Destination Database Name where you want to Bulk Insert in */
select 'BULK INSERT '
/*Remember Tables must be present on destination database */
+ QUOTENAME(@Destdbname) + '.'
+ QUOTENAME(SCHEMA_NAME(SCHEMA_ID))
+ '.' + QUOTENAME(name)
+ ' from ''D:\BCP\' /* Change here for bcp out path */
+ REPLACE(SCHEMA_NAME(schema_id), ' ', '') + '_' + REPLACE(name, ' ', '')
+ '.dat'' with ( KEEPIDENTITY, DATAFILETYPE = ''native'', TABLOCK )'
+ char(10)
+ 'print ''Bulk insert for ' + REPLACE(SCHEMA_NAME(schema_id), ' ', '') + '_' + REPLACE(name, ' ', '') + ' is done... '''
+ char(10) + 'go'
   from sys.tables
   where is_ms_shipped = 0
and name <> 'sysdiagrams' /* sysdiagrams is classified my MS as UserTable and we dont want it */
and schema_name(schema_id) <> 'unwantedschema' /* Optional to exclude any schema */
    order by schema_name(schema_id)

Run the output using SSMS to insert data back in the tables.

This is very fast bcp method as it uses Native mode.


attaching locally stored mdf to SSMS Sorry if this question does not make sense, I have no experience with databases, I just stumbled upon this task.

I need to attach an mdf to a database. I have the mdf file stored in my local computer. When I click attach in SSMS, and then add, I can only browse the disks and folders of the SQL Server, while the file is in my local computer. How can I move the mdf file to the sql server?

As Aaron Bertrand says in the comments, having a .mdf file as the method for adding a database to a server is unusual.  Normally you should have a .bak backup file.  If at all possible, contact your source and ask for backup to restore.

If you have to use an .mdf You need to copy it to the folder on the server, where it will become the .mdf of the database when it is live. Normally you would do this with an RDP or share connection to the folder. Be aware there are several issue you may encounter doing this.

See these related posts for the answer to questions you have not asked yet, but probably will.

Attaching MDF without LDF
How to attach an .mdf file that was not properly detached?
Is is possible to recover a database from the 'raw' files (.mdf and .ldf) of the old server to a new SQL Server instance on a new server?


Will my query pick up where it stopped due to connection timeout? I am doing a rather large query that hits MySQL connection timeout. I know how to change the timeout parameter, but I'm also query from a laptop that can't be left running for long.

The database admin told me that I could send the results to a table instead. After searching around, it seems like INSERT INTO dst_tbl SELECT * FROM src_tbl; is basically the idea (source article).

But even if my results are saved, will the query picked up from where it stopped when I reconnected? Is there a way to check that I'm not missing / duplicating records?

More generally, I'd appreciate any recommendation about how to do large query under timeout constraint.

MySQL is an ACID database. A in this term stands for Atomicity. This basically mean:

  Atomicity requires that each transaction is "all or nothing": if one part of the transaction fails, the entire transaction fails, and the database state is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors, and crashes. To the outside world, a committed transaction appears (by its effects on the database) to be indivisible ("atomic"), and an aborted transaction does not happen.

So, you should not worry at all. If your transaction timed out nothing will be written to database. Re-run your transaction


Allow refresh of lower environment from production when using a HSM We currently use an Oracle Wallet for Transparent Data Encryption of certain critical columns and are migrating to a HSM for compliance reasons.  Our issue is how to handle encryption on the development systems that get refreshed frequently from the production system.  These refreshes are completely automated and rely on image file backups of our production system.  The development systems use the same Oracle Wallet and the refresh process handles manipulating the data in the sensitive columns so that the data does not have to be encrypted, however, we leave the columns encrypted so we can get similar behavior to production.  Here are some options I am aware of.

Have the development systems use the same HSM Partition as production.  This would be the most obvious solutions, but Oracle does not recommend it and this could potentially allow development to affect production.
Drop encrypted columns or drop them and add them back.  - This would be relatively fast, but leaves the columns empty, preventing processes that use the data from being tested.  It also changes the column order, which could be problematic.
Upgrade to 12c and use the reverse migrate command to move back to a wallet from an HSM. - Same problem as #1, albeit for a shorter time, but also requires an upgrade which we cannot do.  As I understand it, this functionality is not available in 11g.
Drop/recreate the encrypted columns and populate them using queries from production as part of the data manipulation part of the refresh.
Drop the tables and import them from an export of production.  Rather time consuming.

Are there other options I am missing or things that would make one of these options more desirable?

A method we found to be viable was to convert production to use the HSM and then clone the partition containing the key for production to development.  This needs to be repeated with each clone of the database and then a post clone step can modify the key for the lower environment using normal key change processes.


How to get information about the sequence behind the new Identity Column in Postgres 10? (GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY) Postgres 10 brings an implementation of the SQL standard GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY feature, loosely known as identity column. This feature supplants Postgres’ own SERIAL pseudo-type, eliminating its issues/problems. For more info, see here and here.

One of the main reasons for GENERATED… being superior to SERIAL is that the sequence number generator is handled in the background transparently. We can add the column, adjust the current sequence number, and drop the column, all without referring explicitly to the sequence.

CREATE TABLE tbl (
    col  BIGINT
         GENERATED ALWAYS AS IDENTITY
         PRIMARY KEY ,
    col2 text
)
;

And…

ALTER TABLE tbl
ALTER COLUMN col
RESTART WITH 1000
;

And…

ALTER TABLE tbl
ALTER COLUMN col
DROP IDENTITY IF EXISTS
;

Or…

ALTER TABLE tbl
ALTER COLUMN col
DROP COLUMN IF EXISTS
;

Note how in none of that example code did we refer to a specific sequence-generating object.

I presume this feature is implemented using the existing Sequence feature in Postgres.

➠ How do we query this background sequence? How do we ask what the last/next value generated will be? What is the equivalent of the Sequence manipulation functions?

It appears you use the same functions as before:

https://www.postgresql.org/docs/10/static/functions-info.html

  pg_get_serial_sequence returns the name of the sequence associated
  with a column, or NULL if no sequence is associated with the column.
  If the column is an identity column, the associated sequence is the
  sequence internally created for the identity column. For columns
  created using one of the serial types (serial, smallserial,
  bigserial), it is the sequence created for that serial column
  definition. In the latter case, this association can be modified or
  removed with ALTER SEQUENCE OWNED BY. (The function probably should
  have been called pg_get_owned_sequence; its current name reflects the
  fact that it has typically been used with serial or bigserial
  columns.) The first input parameter is a table name with optional
  schema, and the second parameter is a column name. Because the first
  parameter is potentially a schema and table, it is not treated as a
  double-quoted identifier, meaning it is lower cased by default, while
  the second parameter, being just a column name, is treated as
  double-quoted and has its case preserved. The function returns a value
  suitably formatted for passing to sequence functions (see Section
  9.16). A typical use is in reading the current value of a sequence for an identity or serial column, for example:

  SELECT currval(pg_get_serial_sequence('sometable', 'id'));


Tool to generate large datasets of test data Many times when trying to come up with an efficient database design the best course of action is to build two sample databases, fill them with data, and run some queries against them to see which one performs better.

Is there a tool that will generate (ideally straight into the database) large (~10,000 records) sets of test data relatively quickly?  I'm looking for something that at least works with MySQL.

The best tool (if you can find it) is DataFactory. (Sadly out of print). I've generated absolutely delightful (and quite authentic-looking) datasets from it.

Generatedata.com is... acceptable, but doesn't scale very well.

DataGenerator is something to keep an eye on.

And while DTM Data Generator is clunky and a poor substitute for DataFactory, it exists and is being sold, and I've used it to generate mildly acceptable data.


MySQL HAVING Causes Extra Increment of Session Variable I'm writing a select statement to get the top 3 most recent items for a list of users. An example can be seen in this fiddle: http://sqlfiddle.com/#!9/cb25a4/12

The rows are getting ordered correctly using sessions variable which are set to 0 in a dummy FROM statement so as to not mess up future session requests. However, when I want to limit rows in the outer SELECT by row_num < 4, the increments I have in the SELECT clause are hit twice... or something.

Is the HAVING clause causing these variables to increments twice then? That's what it looks like. If so, so there any way I can get around this? Thanks.

Found a way to do this, though it feels like a hack and I hope somebody is able to find a different/more elegant way. The sessions variables are incremented once in WHERE and HAVING clauses, so I just added a conditional to see whether or not the column has been incremented yet. Now it's only getting incremented in the HAVING clause. Example of my hacky fix: http://sqlfiddle.com/#!9/cb25a4/35


Best index for column where values are mostly the same We have an integer column that currently consists only of 0 or 1 values. This column has now been used by a developer to store a unique 32-bit identifier on some occasions, and we need to be able to efficiently pull out rows containing any one of these identifiers.

Given the value will be 0 or 1 say (I don't have figures yet) 99% of the time, how might it best be indexed to query against the minority case? Am I even right in thinking the volume of common values will be an issue?

           Column           |  Type   |     Modifiers
----------------------------+---------+--------------------
 event_value                | integer | not null

There are currently no indexes on this column. And I don't envisage the need to regularly select just the 0 or 1 values.

The table is of a reasonable size, currently 30 million rows and growing fast.

I appreciate this isn't the best use of the column, but that can't change in the short term.

First off, like you said yourself, not the best use of the column. Should be a separate boolean and an integer column for your "32-bit identifiers". If that's NULL 99% of the time, that is no problem. NULL storage is very cheap.

Either way, you should definitely use a partial index. (That's the proper term as used in the manual.) Excluding 99 % of the rows from the index makes it massively smaller, which matters for performance with millions of rows.

However, if you have a complete index on event_value anyway, and your common queries are retrieving single rows like:

SELECT * FROM tbl WHERE event_value = 123;

... then an additional partial index won't buy much. It would still be used as it's still a bit faster, but not much faster than a complete index. And the costs for an additional index may outweigh the benefits.

While the rare values are "32-bit identifiers", it may be incorrect to assume those are all > 1. Postgres uses signed integer, and 32-bit entities would also cover negative numbers. (Can we even rule out 0 or 1 as one of those identifiers?) If there can be negative values, too:

CREATE INDEX tbl_event_value_part_idx ON tbl (event_value)
WHERE event_value > 1 OR event_value < 0; -- or similar

event_value does not have to be an index column, regardless of its use in the WHERE clause. That entirely depends on the kinds of queries to expect. Either way, the safe bet is to add the same WHERE conditions literally to any query supposed to use the index, even if that's logically redundant. Postgres can make very basic logical conclusions to determine applicable indexes, but it is no AI and does not try to be (would get too expensive quickly). Like:

SELECT * FROM tbl WHERE event_value > 1 OR event_value < 0

Related:

Postgres partial index on IS NULL not working
PostgreSQL partial index unused when created on a table with existing data
How to index a query with `WHERE field IS NULL`?


On which machine we should install WAL-G? We are using PostgreSQL version 12.1. Currently we have high availability and automatic failover using Patroni and it's working fine.
Now we are planning for disaster recovery and we are planning to use WAL-G for this.
We read some article to know that on which machine we should install WAL-G master, slave or both? But couldn't get a clear idea.
Can you please help us to know on which machine should we set up WAL-G?

As WAL-G helps you both pushing and pulling backups and WAL to and from a storage, you have to install it on both the master and replica.
You can consider using a ready-made image, like Spilo, which would solve these issues for you.
Finally, there is official Patroni support on GitHub, you will get answers way faster there.


Using Galera for simple master-master replication I was reading the documentation and it specifically mentions that a Galera DB cluster must have at least three nodes to achieve quorum, although our current setup is working with two.

I am tasked with rebuilding the cluster.  My use case for Galera is a simple master-master cluster.  It is in actually a master-slave, with the secondary master sitting there in case the first server fails (then we can point the PHP configs at it).  Galera was chosen due to the fact that it is synchronous.

My questions are:

Is a two node configuration safe/supported?
In a 3 node setup, if the third node dies it now becomes a two node setup.  Is this now unsafe meaning I should really have a 4 node setup?
What exactly is a Galera "arbiter"?

1) Is a two node configuration safe\supported?

Galera will still run in a two node setup. However, there is always the threat of a split-brain scenario. For example, suppose you have DB1 and DB2 form a two node Galera Cluster. If DB1 goes down, you need to failover to DB2. While DB1 is having maintenance done to bring it back up, DB2 gets all your changes. By the time DB1 is back up, doing an IST is virtually impossible (or at least too late at this point). You would have to perform a full SST from DB2. Easiest way to do this is to delete the Galera cache files before starting up DB1. During the SST, DB2 is in a read-only state (as a donor) and cannot process any inserts, updates, or deletes. When there is a third node in the Cluster, at least one server can collect inserts, updates, and deletes.

You will also find
Percona XtraDB Cluster: Failure Scenarios with only 2 nodes more informative.

  2) In a 3 node setup, if the third node dies it now becomes a two node setup. Is this now unsafe meaning I should really have a 4 node setup?

A two node Cluster is still operational. When introducing the third node for the first time or bringing back the third node from being down, one of the two operating nodes enters a read-only state (as a donor) to help the third node play catchup. Only one

As I said in the first question: When there is a third node in the Cluster, at least one server can collect inserts, updates, and deletes.

  3) What exactly is a Galera "arbiter"?

I think you mean Arbitrator (See the subheading Using an arbitrator). It is a mechanism that helps decide who plays the role of donor and who play the role of the cluster when introducing node back into a Cluster. It helps with mitigating split-brain scenarios More can be found in the Galera Documentation.


What is the most efficient way to concatenate strings in SQL Server? I have this code:

DECLARE @MyTable AS TABLE
(
   [Month] INT,
   Salary INT
);

INSERT INTO @MyTable VALUES (1,2000), (1,3100);

SELECT [Month], Salary FROM @MyTable;

Output:

I want to concat the Salary (grouping by month) so that it will be NVARCHAR like this: '2000,3100'

How would I do this efficiently?

If you're on SQL Server 2017+, there's a built-in function that's a bit simpler to use than a subquery with XML and STUFF, etc.

You can use STRING_AGG.

SELECT p.OwnerUserId, STRING_AGG(p.Score, ', ') AS aggro
FROM dbo.Posts AS p
WHERE p.Score > 100
GROUP BY p.OwnerUserId

SELECT p.OwnerUserId, STRING_AGG(p.Score, ', ')
                                 WITHIN GROUP (ORDER BY p.Score DESC) AS aggro_order
FROM dbo.Posts AS p
WHERE p.Score > 100
GROUP BY p.OwnerUserId


Join reorders results Is there any particular reason why adding an additional join to an ordered table would reorder the result set?

Using a left join on the existing result set

table-valued function is similar to below (function returns @table):

conditional
    BEGIN
        INSERT @table
               (col1)
        select col1 from table1
        order by col1, x, y
    END
  ELSE
    BEGIN
        INSERT @table
               (col1)
        select col1 from table1
        order by col1, x, y, z
    END

There is no inherent order inside a table.

There is no pre-stablished order when you insert rows. If run for example the following query:

SELECT A.A, B.B
FROM   tblA A
       JOIN tblB B ON A.id = B.id

Without an ORDER BY, then no particular order will be used. If you want some particular sorted results, you must use an ORDER BY clause. You could use a primary key and sort according to its values for example. And doesn't matter if you add one or more JOIN to the statement, same answer, without ORDER BY you are not assure to get same order each time you run the query.

There are several questions/answers that could help you to get a better understanding of this particular point:

a really deep and detailed answer here on dba.stackexchange.com: Storage order vs Result order
You cannot guarantee the order unless you specifically provide an order by with the query
In the SQL world, order is not an inherent property of a set of data.
Without ORDER BY, You Can’t Depend On the Order of Results great blog entry by Michael J. Swart


ODBC SQL Server driver on Linux/nginx Writing here because really can't figure out what to do. I have a site located at ftp server. And now it moves to a new server based on Linux/nginx. And everything is fine except SQL Server ODBC driver. I need to connect to a SQL Server database on my php code through odbc_connect function. I asked administrators of nginx to add required driver to nginx settings, gave them host, port and tds version and they added, but it doesn't work. giving me an error:

  Warning: odbc_connect(): SQL error: [unixODBC][FreeTDS][SQL Server]Unable to connect to data source, SQL state S1000 in SQLConnect in

I'm using the same connection string as earlier.

$odbc = odbc_connect("DSN=dsn_name; Host=x.x.x.x,port_number;", "login", "pass");

Maybe we need to do some specific things to make it work? Thanks.

We some some more odbc drivers and them work fine (Sybase for example). I use AquaData Studio on my local machine to access to datasources and found that authentication setting exists only on a SQL Server database...

I'd say you miss a post for your instance in /etc/freetds.conf. The post should look something like this

[my_alias]
    host = some_db_host_address
    port = 1433
    tds version = 8.0

Then in your connection string you should use that alias. The host and port part shouldn't be needed as they are picked up from the configuration file. You may have to say which driver to use. This works for me

$conn = odbc_connect("Driver=FreeTDS;DSN=my_alias","user","pass");


Cannot drop distribution database in SQL Server 2012 I am trying to drop distribution database but cannot as I am getting this error:

  The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x00000000056000 in file 'E:\XXX_Replication\Data\distribution.MDF'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

The underlying drive on which the .mdf file existed was replaced, thus deleting the .mdf files. Replication is not needed on this server. But this is production server and cannot be restarted.. Please help me to delete this database.

first of all, thanks for the help and suggestions. I have now managed to delete the distribution database without rebooting the server. this is how i did it.

Created a distribution database with similar file locations on a different server.
Took the backup of this new distribution database.
Restored this backup on the existing distribution database with REPLACE, MOVE option.
Finally disabled distribution on the server.

Thanks again for the help.


Please Identify this Export Format I was sent this as a mysql dump, the file is labeled .sql, but it's clearly not a standard export.  Is this something that can be used to reproduce a database and if so how?

Here is a small sample

USE [Sunwave]
GO

/****** Object:  Table [dbo].[SunwaveZipCodes]    Script Date: 6/15/2014 8:10:57 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[SunwaveZipCodes](
    [POSTAL_CODE] [varchar](50) NULL,
    [UTILITY_NAME] [varchar](50) NULL,
    [CITY_NAME] [varchar](50) NULL,
    [COUNTY_NAME] [varchar](50) NULL,
    [STATE_CODE] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Like @RolandoMySQLDBA said, this is a Microsoft SQL Server script.  Find a SQL Server that you can play with, create a new Database on that instance called [Sunwave], then run your script above.  I don't believe you really wanted to migrate this database into MySQL.  I'm thinking this might be another case of the usual MSSQL =? MYSQL confusion that us multi-RDBMS admins encounter.


Disadvantages of unlogged tables To make updates faster, I am using:
ALTER TABLE imagingresourceplanning.opnav_fact_revenue_costs SET UNLOGGED ;

What are the drawbacks of this command?
What will happen if system crashes during the update? Is all the data present in the table deleted? Or only the updates which are being done will be lost?

Logging is in reference to Write-Ahead-Logs.
Essentially, everything gets written ahead of time to a log. That log can be played back at any point in time if there is a problem with database constancy (crash-safety). This means data is written twice, once to the log with representation in memory, and a later point in time flushed to the table's heap.
The log can also be shipped to a slave and can play a role in point-in-time (PITR) replication.
Check out the documents on the PostgreSQL implementation of Write-Ahead logging for more information.

If the system crashes (not shut down cleanly), the contents of unlogged tables will be deleted as stated in the manual:

However, they are not crash-safe: an unlogged table is automatically truncated after a crash or unclean shutdown


Select arbitrary element in postgres I have a table that has path1, path2, and sha1 value.  For any values of path2 and sha1, there can be multiple values of path1.  I just want one of those paths.  I don't really care which one.

I'm thinking I can do a group by for path2 and sha1.  Now I just need to select one of the values of path1.  I suppose I could select the minimum value of path1 but that would be doing extra work that isn't really needed.

Google tells me that Microsoft has "FIRST" but I don't see that in the postgres pages.  Plus... I'd like to stick with normal SQL if possible.

There are a bunch of ways you can do this, one of them is with DISTINCT ON as @Ypercube has suggested,

SELECT DISTINCT ON (path2, sha1) path2, sha1, path1
FROM table_name
ORDER BY path2, sha1;

You can also use an ordered-set aggregate which should generally be slower.

SELECT percentile_disc(0) WITHIN GROUP (ORDER BY path1) AS path1, path2, sha1
FROM table_name
GROUP BY path2, sha1;


Locking And Blocking With Merge Replication I am using merge replication in SQL 2012 with web sync.

When I insert records into the top tables in my filter hierarchy it causes a lot of locking on the tables, including the merge replication tables such as MSMerge_contents.

It means that no other process can insert into the same tables at the same time.

Because of my particular setup this is a problem. Is there a way to reduce the amount of locking / blocking that occurs in the merge replication insert triggers?

What is occurring here is not what it appeared to be. The stored procedure to write into my top level tables were using transactions.

It was the transactions that were causing heaps of extra locks. It worked fine once those transactions were removed.


Converting a varchar date into a date (dd/mm/yy) I have a CSV file that has dates written like this: 250909,240909 and they get stored in a SQL Server database as varchars.

How do I get it to convert them into dates so that it gets displayed like this: 25/09/09, using the dd/mm/yyyy format?

At the moment I can get it to convert, but it converts it like this: 2025/09/09, meaning it is using the yyyy/mm/dd format which is wrong, seeing that the first column is the day and second the month and third the year.

I understand your problem to be how to successfully convert the string into a DATE value. An undelimited string of integers is assumed to be Year-Month-Day order.  (And, of course, I agree with the comments that dates should be stored in DATE data types in the database.)

Reviewing the MSDN CONVERT documentation does not show a built-in conversion for your string, but it is easy to work around.

http://msdn.microsoft.com/en-us/library/ms187928.aspx -- CAST and CONVERT

I changed the month to 8 to make it easier to double check.  Using the CONVERT style option 3, you can do the following:

DECLARE @String VARCHAR(10);
DECLARE @DateValue DATE;
SET @String = '250809';

-- Convert your undelimited string DDMMYY into a DATE
-- First: Add / between the string parts.
SET @STRING = SUBSTRING(@String,1,2)+'/'+
     SUBSTRING(@String,3,2)+'/'+SUBSTRING(@String,5,2);
-- Second: Convert using STYLE 3 to get DD/MM/YY interpretation
SELECT @DateValue = CONVERT(Date, @String, 3);

-- Using the DATE
-- Select the value in default Year-Month-Day
SELECT @DateValue AS DefaultFormat;
-- Select the value formatted as dd/mm/yy
SELECT CONVERT(VARCHAR(20),@DateValue,3) AS [DD/MM/YY];

The results of the last two selects are:

DefaultFormat
-------------
2009-08-25

DD/MM/YY
--------
25/08/09

To get your DATE formatted in the way you want it, you have to insert the '/' delimiters then use the STYLE 3 in converting from the string to the DATE. (I am sure that there are other workarounds and conversion styles that would work as well.)

Likewise when displaying the DATE as you desire, you need to use STYLE 3


Equivalent of varchar(max) in ASE What is the equivalent of SQL Server's varchar(max) data type in ASE?
By ASE I mean what used to be known as Sybase ASE, which is now owned by SAP.
I'm attempting to execute a piece of dynamically constructed T-SQL that is longer than 8,000 chars.
If I declare a variable with varchar(max), ASE chokes with:
Incorrect syntax near the keyword 'max'.
My next thought was to try the text datatype.  But alas:
You specified an incorrect datatype for the variable containing the 'execute immediate' command string.
A minimal, complete, and verifiable example:
DECLARE @cmd varchar(max) --change this to varchar(4000) and it works
SET @cmd = 'SELECT 1;'
EXEC (@cmd);
GO

Changing the variable declaration to text or whatever will suit your purposes.  I need to execute a dynamic command that is probably 50,000 characters long.
Alternately, I could use a cursor, but I am unfamiliar with how cursors work on ASE.
Note that in SQL Anywhere, you can define an equivalent to the varchar(max) datatype as declare @cmd long varchar;, however that won't work in ASE.

ASE's (var)char max is 16384. (assuming ASE 15.x/16.x)
If the query can be broken into chunks, with each chunk no larger than 16K, then you could try concatenating them inside the execute() call.
From the documentation for the execute() command:

There are no restrictions to the number of characters supplied with
the literal string.

And from Example 6 (same link) we see a simple example:
execute ('select ' + @columname + ' from ' + @tablename + ' where id=3')


Not able to access Oracle manager online. Port 5500 is closed I am using Oracle 18c XE on CentOS 7. The database installed successfully but not able to bring it online to my hostname. what I did is:

Added port 5500 in firewall:

firewall-cmd --permanent --add-port=550/tcp
firewall-cmd --reload

edited sqlplus as:

SQL> exec dbms_xdb.sethttpport(5500)
PL/SQL procedure successfully completed.

SQL> EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);
PL/SQL procedure successfully completed.

Then trying to access as: https://linux.domain.com:5500/em

[root@linux ~]# lsof -i :5500
COMMAND   PID   USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
tnslsnr 18295 oracle   16u  IPv6 864455      0t0  TCP *:fcp-addr-srvr1 (LISTEN)

When I check in ping.eu the port 5500 is closed

Best Regards

The problem was this part:

SQL> exec dbms_xdb.sethttpport(5500)
PL/SQL procedure successfully completed.

I configured 5500 on HTTP port but was trying to access it on HTTPS


IBM data studio 4 - how to open a new sql script editor? I have to use IBM Data studio 4 and for the life of me I cannot figure how to open another SQL script editor window. Luckily it opens with one, but then I am constrained to that single window.

I don't want to go thru the trouble of having a project etc, just open another script and write a few statements.

PS: I know of a workaround of right-clicking an object in the connection tree and using Data/New Select Script, but that is a PITA as I have to open Table/View trees and point at one of the objects, and of course I am not always there (on the SP/Functions tree for instance) and it takes a while for the tree to open all objects.

Click the icon above....
That's the easiest way!


PostgreSQL merging identical tables from separate databases into single database I've started working on a project where the application data is split across multiple databases that all have the same schema (basically there's a database per "organisation").

After some discussion it's been decided that this isn't the most efficient way for us to store data and would like to merge all of these separate databases into one single database.

I was wondering what would be the best approach. I'm currently thinking about doing something like the following:

Dump the schema from one of the databases and restore it to the new primary database:

pg_dump [OLD_DATABASE] --schema-only > dbschema.sql

psql -d [NEW_DATABASE -f dbschema.sql
Dump the data from all of the old databases disabling triggers, and insert format:

pg_dump [OLD_DATABASES] --disable-triggers --data-only --inserts > old_db_data.sql
Restore all of the dumped data from database files:

psql [NEW_DATABASE] < old_db_data.sql

Is this the right way to do this kind of merge or am I completely off the mark? Additionally will this handle foreign key dependencies correctly?

Your method is easily improved, however you're mostly there.

When you run this

pg_dump [OLD_DATABASE] --schema-only > dbschema.sql

Instead do

pg_dump [OLD_DATABASE] --schema-only --section pre-data > pre.sql

Then load your dumped data, produced by this (from all the servers)

pg_dump [OLD_DATABASES] --disable-triggers --data-only --inserts > old_db_data.sql

But, then you need to add the add the constraints.. So go back to your original database and run

pg_dump [OLD_DATABASE] --schema-only --section post-data > post.sql

And, then load post.sql. This will add the constraints.


AlwaysOn Manual failback not working correctly I've set up a small fail over cluster using AlwaysOn in SQL Server 2014 and Windows 2012R2.

The cluster (SQL-CLUSTER-DEMO) just contains 2 SQL nodes (SQL-01 [Primary] & SQL-02 [Secondary]) and a file share quorum. Availability Group is set up in SQL Server no problems.

I am connecting to SQL-CLUSTER-DEMO and running transactions every second.

When I simulate failures in turn (pull the network cable from the machine, replace, pull from other machine, replace), they switch between primary and secondary as expected no problems.

The issue comes when I simulate failure on the primary and then use the Manual Fail-over wizard to revert back once it has recovered. The application I have running then starts to error with "Database is Read Only" and SQL-CLUSTER-DEMO seems to think it is pointing to the secondary.

I'm really stuck trying to figure out why automatic failure swaps perfectly, but manual fail-over doesn't seem to work... what am I missing?

I resolved this in the end. It was my own fault. I had connected using the name of the cluster "SQL-CLUSTER" rather than creating a listener. Originally I had tried to create a listener but was getting errors so stupidly I'd just ignored them. The errors were because I'd not set up the network properly (I'm a DBA not a network technician :) )

Anyway, after setting up the listener properly it works like a charm. Long story short... don't connect to the cluster, connect to the listener.


SQL Server 2012 autoupdate statistics default behaviour I found, that since SQL Server 2008 R2 there is a trace flag 2317 available. It turns on dynamical autoupdate statistics threshold, instead of default threshold of 20%. So, it greatly improves performance on systems with high tables (from 1 billion rows), because it could take a long time to reach 20% of rows modified. More of this here: http://support.microsoft.com/kb/2754171

Secondly, I found few unofficial topics, which says, that this trace flag is turned on by default since SQL Server 2012. But I can’t find any proof of this from vendor, Microsoft.

So, the question is: does anybody know anything about any changes on autoupdate stats default behavior since SQL Server 2012? Any link to BOL or MSDN would be appreciated.

I was just about to create a table, insert a bunch of data, create some statistics and do a test. But then I remembered this excellent blog post Erin Stellato wrote over on her blog. She's done that already.

Her tests show 20% in action. You can take what she's done her and the scripts provided, test it on your environment and witness the behavior yourself.

I've not experienced, seen or read anything that indicates this traceflag, or its behavior, is on by default in SQL Server 2012. I can't point you to official articles or documentations to prove a negative, though.

You can see for yourself which traceflags are enabled, however, by running

DBCC TRACESTATUS(-1)

Biggest advice here is - Empirical evidence rules the day. Not only will you know for sure, but you'll learn a lot in the process of figuring it out.


SQL Server AlwaysOn Availability Group - Creating an Index Online in SSMS I am running a 2-node SQL Server 2016 AlwaysOn Availability Group.  I would like to create a non-clustered index online in SSMS for one of the availability databases in the AlwaysOn Availability Group.  For this operation, it is very important that the index create be Online; in other words, the table should not become locked and both reads and writes should continue without interruption or error during this time.  Also, the cluster should not go into an unhealthy state where replication stops.

Here was my plan for doing this:

Go into SQL Server Management Studio (SSMS) on the master node, go to the database, table and then Indexes section in Object Explorer and right-click on Indexes and choose New Index - Non-Clustered
Add the appropriate field to the Index Key columns screen
Go to Options - and set "Allow online DML processing" to "True"
Click OK

Is the above approach the best way to do this?  Will this actually create the index online?  While the index is being created, will the cluster remain in a healthy and replicated state, will the index get created on the secondary automatically, and will the incoming reads and writes continue to happen without errors?

Any help with this is greatly appreciated!

Is the above approach the best way to do this?

Essentially this is the only way to approach this - indexes can only be created by one command. You could execute T-SQL for the CREATE INDEX manually, but the same command is going to be executed on the server regardless (barring any different options you choose, such as online).

As with any production rollout, you should have ideally tested this in a lower environment to get an idea for how long it will take and if it will cause any slow downs in the application - things we can't tell you based on the provided information.

  Will this actually create the index online?

Yes, provided you are using Enterprise edition. Otherwise the index will not be created online. Again, this is something you should have already tested in a lower environment or copy of production.

  While the index is being created, will the cluster remain in a healthy and replicated state

Index creation in general should not impact cluster health, but we also don't know your server specs, how big of an index this is, or a lot of other details that could make this a riskier operation. My advice is  to monitor the index creation if you are concerned at all.

  will the index get created on the secondary automatically

See above Enterprise Edition comment.

  will the incoming reads and writes continue to happen without errors?

If the index is being created ONLINE then the table will not be locked during the process, but still necessitates a very brief schema lock at the first / final stage of creation. Creating an index does have a resource cost though, so you may see slowness in your queries that utilize the targeted table. Which is why you should test this before releasing to production.

There are other index options like MAXDOP which can be used to limit how many resources are utilized by the index creation process.


Statement specific auditing - Oracle I have enabled default auditing in one of my database. Oracle 11g. Audit_trail is set to DB, other than that for a particular schema 'test' I have set:

audit all on test by access

With the above setting, I know I can get to know what action has been performed (alter,create,update,etc) but will I be able to fetch the SQL statments (all DML operations) with the exact SQL statement fired from that schema from the audit records? Let me know how?

If the above is not possible, what is required to fetch the exact SQL statement that is run by the schema.

AUDIT_TRAIL

If you want the SQL text to be captured, you need to use the extended option for audit_trail (db, extended or xml, extended).

alter system set AUDIT_TRAIL=db, extended scope=spfile;

or

alter system set AUDIT_TRAIL=xml, extended scope=spfile;

Changing this setting requires a database restart to take effect.

For FGA you can specify a different audit trail, for example: audit_trail => DBMS_FGA.DB_EXTENDED, that way you can leave audit_trail on DB. If you use FGA, you do not even need to use AUDIT, just create the policy you need.


Anyone Know of Some Desktop Software that Searches an SQL server for a String I have a database on a server that's running Microsoft SQL. I would like to find a tool that logs into that server, allows me to search to a string, and returns the tables and fields that contain that string. I"m not a programmer, nor do I know SQL. I saw someone using free desktop software that does this, but I've come to a Google dead-end. Does anyone know a software product that does this?

I don't think monitoring software is what I'm looking for. I might be wrong, but when I look at those products (RedGate, etc), they seem like overkill.

Step 1: Download SSMS LINK

Step 2: Download Red Gate SQL Search LINK

Step 3: Install both

Step 4: Connect to your database server

Step 5: Search!


MySQL 5.5.21 ON UPDATE CURRENT_TIMESTAMP incorrect As the title says, I'm having a few issues with 'ON UPDATE CURRENT_TIMESTAMP'. Here is the SQL definition for the table:

CREATE TABLE `judgements` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `parent_id` bigint(20) NOT NULL DEFAULT '0',
  `entry_id` bigint(20) NOT NULL,
  `group_id` bigint(20) NOT NULL,
  `comments` longtext,
  `rating` int(11) DEFAULT '0',
  `status` int(11) NOT NULL DEFAULT '1',
  `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`entry_id`,`group_id`),
  KEY `group_id` (`group_id`),
  KEY `entry_id` (`entry_id`),
  KEY `status_id` (`status`),
  CONSTRAINT `entry_id` FOREIGN KEY (`entry_id`) REFERENCES `entries` (`id`),
  CONSTRAINT `group_id` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`),
  CONSTRAINT `status_id` FOREIGN KEY (`status`) REFERENCES `status` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9082 DEFAULT CHARSET=utf8;

Records are inserted using the Wordpress database class ( $wpdb->insert ). All fields except id and modified are passed in an associative array to the function. This works as expected, the record is inserted and a timestamp is applied. The problem is that this timestamp is not always correct.
In seemingly random instances (around 15% of inserts) the timestamp will be anywhere up to 30 minutes in the future... I couldn't find any sort of pattern to this behaviour which makes the problem difficult to track down.
If it is relevant, this table experiences quite a high volume of inserts for several hours a day.
After searching Google and this site I am no closer to tracking down the problem. I did manage to find one forum post, from several years ago, where someone had a similar problem but the only reply was to file a bug report.
Has anyone else experienced this or have a theory as to why it is happening?

Thanks.

I don't think you are supposed to use DEFAULT (constant) with ON UPDATE.

According to the MySQL Docuementation

  With an ON UPDATE CURRENT_TIMESTAMP clause and a constant DEFAULT
  clause, the column is automatically updated to the current timestamp
  and has the given constant default value.

CREATE TABLE t1
(
    ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
);

This might just be stomping over with the current datetime.

On the same page, it says

  With an ON UPDATE CURRENT_TIMESTAMP clause but no DEFAULT clause, the column is automatically updated to the current timestamp. The default is 0 unless the column is defined with the NULL attribute, in which case the default is NULL.

CREATE TABLE t1
 (
     ts TIMESTAMP ON UPDATE CURRENT_TIMESTAMP      -- default 0
 );
 CREATE TABLE t2
 (
     ts TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL
 );

I suggest just removing the DEFAULT '0000-00-00 00:00:00'

Give it a Try !!!


Tons and tons of relay logs on a master I have a master that has 298 relay bin files as recent as today, going back well 298 days.

There is no relay-log definitions in the .cnf

and

mysql> show variables like '%relay%';
+---------------------------------+----------------+
| Variable_name                   | Value          |
+---------------------------------+----------------+
| innodb_overwrite_relay_log_info | OFF            |
| max_relay_log_size              | 0              |
| relay_log                       |                |
| relay_log_index                 |                |
| relay_log_info_file             | relay-log.info |
| relay_log_purge                 | ON             |
| relay_log_space_limit           | 0              |
+---------------------------------+----------------+

Reset slave clears them out, but then they just start getting regenerated.

Any idea what's causing this?  How to stop it?

EDITS TO REQUESTS

General critiques of the cnf are welcome but let's keep the OP topic in mind.

---- cnf request

[mysqld]
character_set_server = utf8

max_connections=200
max_user_connections=160
max_connect_errors=10000

userstat_running = 1

log_warnings
slow_query_log=1
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=2

innodb_file_per_table

innodb_open_files=2048

innodb_additional_mem_pool_size=1M

innodb_buffer_pool_size=512M

innodb_log_buffer_size=1M

innodb_log_file_size=128M

innodb_autoextend_increment=16

innodb_flush_method=O_DIRECT

datadir=/var/lib/mysql/

tmpdir=/var/lib/mysql_ramdisk

server-id=2

log-bin = /var/log/mysql/mysql-bin
log-bin-index = /var/log/mysql/mysql.index

key_buffer_size = 800M

preload_buffer_size = 256K

max_allowed_packet = 8M
table_cache = 512
sort_buffer_size = 8M
join_buffer_size = 8M

read_buffer_size = 2M
read_rnd_buffer_size = 2M
thread_cache_size = 32
query_cache_size = 32M
query_cache_limit = 16M

myisam_sort_buffer_size = 2000M

tmp_table_size = 64M
max_heap_table_size = 64M

---- now for the cli requests

mysql> show slave status\G
Empty set (0.00 sec)

mysql> show master status;
+---------------------+----------+--------------+------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| awesome-bin.xxxxxxx | yyyyyyyy |              |                  |
+---------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

---- version

mysql> select version();
+--------------------+
| version()          |
+--------------------+
| 5.1.47-rel11.1-log |
+--------------------+
1 row in set (0.00 sec)

If a Master has relay logs, then the Master must also be a Slave in the midst of some Replication topology (i.e., Master/Master, Daisy-Chained Replication)

What could cause relay logs to grow like this?

BROKEN REPLICATION

MySQL Replication is broken when the IO Thread or SQL thread dies under these SCENARIOS:

SCENARIO #1 : When the IO Thread and SQL thread are off, one of two things happended

MySQL was started with skip-slave-start enabled
Someone/something ran STOP SLAVE;

SCENARIO #2 : When the IO thread dies

nothing can pile up the relay logs
The SQL thread processes all SQL commands in the relay logs or until a SQL error occurs

SCENARIO #3 : When the SQL thread dies

SQL error occurred processing a SQL command
Running SHOW SLAVE STATUS\G shows you the Last_Errno and Last Error
IO Thread continued collecting SQL commands from the Master, making relay logs grow

It is SITUATION #3 that's the problem. When the SQL thread dies due to a SQL error, there is no built-in mechanism in MySQL Replication that triggers the IO thread to disconnect.

RECOMMENDATION

The only decent way to control the growth of relay logs is to set the limit on it

[mysqld]
relay_log_space_limit=4G

Setting relay_log_space_limit places a cap of 4G.

When a relay log is completely processed

it is rotated out
the SQL thread starts working on the next relay log
the I/O thread starts loading SQL from the Master from the last place it left of from, as long as there is enough freespace on the disk

EPILOGUE

If the Master used to be a Slave and it does not need to be anymore, simple disable it.

mysql -e"STOP SLAVE; CHANGE MASTER TO MASTER_HOST='';"
rm -f /var/lib/mysql/master.info

If the Master is a Slave, go correct the SQL error.

I would suggest this if the SQL error is in the way:

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE SQL_THREAD;

then run SHOW SLAVE STATUS\G every minute to see if the relay logs get processed and rotated.


Create Replica Sets on an existing sharded MongoDB How do I convert an existing mongodb instance that has sharding enabled for some of its collections to a replica set? I am able to do it the other way around, but I am not able to figure out how to add replication to existing sharded mongo instances.

You don't convert collections to a replica set. You convert (stand alone shard) node (where all databases and collections are) to the replica set.

Convert (stand alone) node to the replica set, follow these instructions.


SQL Server 2012 Express SP1 install still not 11.1 I run the SQL Server 2012 Express SP1 install and it succeeds. After I have rebooted my computer, I do a

select @@version

But still I only have version 11.0.21600.64. I have tried this on two computers now, still the same result.

The file I am running is: SQLEXPR_x64_ENU.exe downloaded from http://www.microsoft.com/en-us/download/details.aspx?id=35579

The full Express version (with SP1 included) is for installing a new instance, not for applying a service pack to existing instances. Sounds like perhaps you now have a second instance of SQL Express (no idea without seeing your configuration manager or services applet).

At this point you should download Service Pack 2 (not 1) to apply to existing instances, regardless of edition:

Download SQL Server 2012 Service Pack 2

(And perhaps clean up the extra instance, if in fact you did install a second instance inadvertently.)


Composite or Single-Field Clustering Key My DBA and I are having a disagreement on index structure.

Consider data for a medical claim...

We have the Header table with fields like:

ClaimId (varchar(50)), PaidAmount, MemberID...

and for each header we have one or more records in Detail like:

ClaimId (varchar(50)), LineNumber (smallint), MemberId...

The efficiency of the structure or duplication of the data is outside the parameters of this question.

There are additional tables that tie back to individual Detail lines by ClaimId, LineNumber, and we frequently JOIN Detail and Header on ClaimId as well.

For the Detail table, which would be preferable for the clustered index key:

ClaimId

or

ClaimId, LineNumber

ClaimId alone is NOT unique, but the combination of ClaimId, LineNumber is unique for a Detail record.

One of us believes that ClaimId alone is a better clustered key because it is narrower, and that the lookup will be just as efficient since we need to know the ClaimId before we lookup the LineNumber.

The other believes that the combination of the two is better because it removes the need for an additional RowID, and can be used in JOINing to the support tables that need the LineNumber as a JOIN condition.

This is bollocks:

  ... ClaimId alone is a better clustered key because it is narrower

because of this

  ClaimId alone is NOT uniquej

A non-unique clustered index will add a 4 byte uniquifier to remove ambiguity of ClaimId because it is the clustered index. Why? One reason is all NC indexes refer to it: so how to know ClaimId is which?

It was demonstrated (some time ago, maybe not valid now and can't find it) that non-unique clustered indexes break when you exhaust 2^32 values of the 4 byte uniquifier

Edit :

Question states ClaimId is not unique so assumed that uniqifier exists. No need to comment that it may not exist in the context of the question


Update coordinates after edit I have a roads table. The road lines are stored in a user-defined type column called SHAPE.

Normally, spatial entities just have x and y dimensions. In my case, my roads have a measure(m) dimension; which means that I have x, y, and m dimensions. (M values are used for linear referencing purposes.)

I have a problem where my GIS software does not automatically reset the m values of the lines when the x and y values are edited. This means that after lines are edited, the m values become out-of-date/incorrect.

I've successfully figured out how to generate new m values. Admittedly, the query is long and convoluted, but that's not the problem at hand. I'm working on simplifying it here.

select SDE.ST_GEOMFROMTEXT('LINESTRING M ( ' || listagg(X||' '||Y||' '||m,',') within group (order by VERTEX_INDEX) || ')', 300049)  as WKT
 from (select ROAD_ID, VERTEX_INDEX, X, Y, ROUND(sum(m) over (partition by ROAD_ID order by VERTEX_INDEX),3) as m
         from (select ROAD_ID, VERTEX_INDEX, X, Y, case when VERTEX_INDEX = 1 then 0 else SQRT(power(abs((lag(X,1) over (partition by ROAD_ID order by VERTEX_INDEX))-X),2)+power(abs((lag(Y,1) over (partition by ROAD_ID order by VERTEX_INDEX))-Y),2)) end as m
            from (
                select
                    ROAD_ID,
                    t.VERTEX_INDEX,
                    SDE.ST_X(SDE.ST_POINTN(SHAPE, t.VERTEX_INDEX)) as X,
                    SDE.ST_Y(SDE.ST_POINTN(SHAPE, t.VERTEX_INDEX)) as Y
                from
                    USER1.ROADS
                cross join
                    (
                    select level as VERTEX_INDEX
                    from DUAL connect by level <=
                        (
                        select max(SDE.ST_NUMPOINTS(SHAPE))
                        from USER1.ROADS
                        )
                    ) t
                where
                    t.VERTEX_INDEX <= SDE.ST_NUMPOINTS(SHAPE)
                    and SDE.ST_GEOMETRYTYPE(SHAPE) <> 'ST_MULTILINESTRING'
                )
            )
      )
group by ROAD_ID

Result:
LINESTRING M ( 670495.21962780 4861662.29203628 0.00000000, 670644.70911652 4862034.07146977 400.70800781, 670579.99977367 4862214.15467259 592.06494141)

Question:

Once the SHAPE column is edited (user updates the x and y), I want to take that new shape, reset the m values with my query, then update the SHAPE column with the reset m values.

I think the solution would be a fairly simple trigger. Unfortunately, I'm quite new at triggers, and am failing dramatically at making this one. How can this be done with a trigger?

Update:

I've drummed up a basic trigger to start from:

create or replace trigger "RESET_M"
before update of SHAPE
on USER1.ROADS

for each row
declare
begin
    :new.SHAPE := :new.SHAPE;
end;

Of course, this isn't helpful. It just sets the new SHAPE to the same new SHAPE, which is useless.

I don't know how I can do what I want by using my query. I want to get the edited/updated x and y, calculate new m values, and of course, commit it to the SHAPE column. I imagine this would be quite easy if I were using functions. But the underlying reason why my giant query exists is that there isn't a function to do what I need to do.

And yes, Oracle Spatial does have a function called SDO_LRS.REDEFINE_GEOM_SEGMENT that does what I want. But I unfortunately, I don't have Oracle Spatial.

Looking at your query we can see that there are lots of details that cannot be discussed here.  In order to get a trigger to work, here a small test setup that may help you to do the next steps ...

Not quite sure if the "LINESTRING M" values are stored in the same table as the original x/y values, that's why the example is written with 2 tables in mind: (1) "shape_table", containing objects of type shape, and (2) "m_values" with columns for road_id and "linestring m" (name shortened to m).  DDL as below.

create or replace type shape_t as object (
  road_id number
, vertex_index number
, x number
, prev_x number
, y number
, prev_y number
, dist_to_prev number
, member function getdistance return number
);
/

create table shape_table ( shp shape_t ) ;

create table m_values ( road_id number,  m varchar2(4000) );

After inserting some test data:

SQL> select * from shape_table;
SHP(ROAD_ID, VERTEX_INDEX, X, PREV_X, Y, PREV_Y, DIST_TO_PREV)
-----------------------------------------------------------------------------
SHAPE_T(100, 1, 0, 0, 5, 0,   0)
SHAPE_T(100, 2, 10, 0, 10, 5, 11.18)
SHAPE_T(100, 3, 30, 10, 0, 10, 22.36)
SHAPE_T(100, 4, 50, 30, 10, 0, 22.36)
SHAPE_T(100, 5, 60, 50, 10, 10, 10)

SQL> select * from m_values;
ROAD_ID  M
100      LINESTRING M ( 0 5 0, 10 10 11.18, 30 0 33.54, 50 10 55.9, 60 10 65.9 )

Now, if we have procedure that re-calculates the "LINESTRING M" and uses it for updating (the SELECT used here looks different from the one in your question, due to various reasons*)

create or replace procedure refresh_m_value
is
  m_roadid number := 0 ;
  m_linestring varchar2(4000) := '' ;
begin
-- generate the new M LINESTRING
  select
    road_id
  , 'LINESTRING M ( '
    || listagg( x || ' ' || y || ' ' || dist_ , ', ' )
       within group ( order by vi )
    || ' )' linestring
  into m_roadid, m_linestring
  from (
    select
      s.shp.road_id road_id
    , s.shp.vertex_index vi
    , round( s.shp.x, 2 ) x
    , round( s.shp.y, 2 ) y
    , sum ( s.shp.dist_to_prev )
        over ( partition by s.shp.road_id order by s.shp.vertex_index ) dist_
    from shape_table s
  ) roads_
  group by road_id ;
-- write the new M LINESTRING
  update m_values
  set m = m_linestring
  where road_id = m_roadid ;

end refresh_m_value;
/

Use a trigger for calling the procedure after the original values have been UPDATEd.  Add a log table for debugging/testing etc.

-- table for log messages generated by the trigger (see INSERTs in trigger code)

create table trigger_log ( date_ date, message_ varchar2(128) );

create or replace trigger reset_m
  after update on shape_table
declare
  error_code NUMBER;
  error_message VARCHAR2(128);
begin
  refresh_m_value;
  insert into trigger_log (date_, message_)
    values (sysdate, '[trigger: reset_m] refresh_m_value executed ') ;
exception
  when others then
    error_code := SQLCODE;
    error_message := SUBSTR(SQLERRM, 1 , 128);
    insert into trigger_log (date_, message_)
      values (sysdate, '[exception] ' || error_code || ' - ' || error_message);
end;
/

Test the trigger, check the tables (and log).

update shape_table s
set s.shp.x = 0, s.shp.y = 5
where s.shp.road_id = 100
  and s.shp.vertex_index = 1 ;

select * from shape_table;

select * from trigger_log;

select * from m_values;

More details about the (imaginary) TYPE shape_t, and populating the "shapes_table" can be found here.  However, using the dbfiddle, the TYPE was not created successfully, due to ORA-01031: insufficient privileges.


Get three last movements of the per ID with LAG and GROUP BY in MySQL I hope someone can help with this query, I have a requirement for a query that groups the id of the last date, but I want to have three columns with the 3 last movements.
So I tried grouping by the id and used MAX(date), and used LAG to get the three last movements, but I'm getting an error from (PARTITION BY id ORDER BY id..).
If I delete the two LAG functions, then the query runs. I don't know if I'm missing something there. I'm using MySQL Workbench 8.0 C.E (edit. I'm probably using another version of MySQL 5.7)
SELECT
    id,
    MAX(lastdate),
    LAG(Move, 2, NULL) OVER (PARTITION BY id ORDER BY id ASC) AS Move1,
    LAG(Move, 1, NULL) OVER (PARTITION BY id ORDER BY id ASC) AS Move2,
    Move AS Move3 ,
    action_ticket
FROM table
GROUP BY id

This is the table that I have:

id
lastdate
move
action ticket

12
25/02/20
up
scale

12
26/02/20
down
scale

12
27/02/20
left
solved

15
23/02/20
left
scale

15
22/02/20
up
scale

15
25/02/20
right
solved

And the table that I want to get is:

id
lastdate
move1
move2
move3
action ticket

12
27/02/20
up
down
left
solved

15
25/02/20
up
left
right
solved

Any help is really appreciated. Thanks a lot
Maria J.
Edit. Thank you very much for all the help !

WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY `date` DESC) rn
              FROM test )
SELECT id,
       MAX(`date`) `date`,
       MAX(CASE WHEN rn = 3 THEN move END) move1,
       MAX(CASE WHEN rn = 2 THEN move END) move2,
       MAX(CASE WHEN rn = 1 THEN move END) move3,
       MAX(CASE WHEN rn = 1 THEN action_ticket END) action_ticket
FROM cte
GROUP BY id;

fiddle


Is it a good way to use different schemas for storing the data and provide data access? For a new product we came along with a pattern, where all the data tables are stored in one schema (lets call it storage), while the clients connect to another one (here called api). The api schema defines a set of views and stored functions in order to retrieve and modify the data.

The thought about this is that the client does not see the real table structure, but only an abstraction of it, so that the storage schema can be changed without breaking any client.

Is this a good approach to achieve a stable client interface, or are there some hidden problems with such a practice.

With properly set privileges this can be a good solution.  You can define your stored procedures WITH SECURITY DEFINER, where the role with which you create them has the necessary access privileges to the tables.  Then you have an api_caller role which has only function execution rights for the procedures in the API schema.