HierarchyID: Get all descendants for a list of parents I have a list of parent ids like this 100, 110, 120, 130 which is dynamic and can change. I want to get all descendants for specified parents in a single set. To get children for a single parent I used such query:

WITH parent AS (
    SELECT PersonHierarchyID FROM PersonHierarchy
    WHERE PersonID = 100
)
SELECT * FROM PersonHierarchy
WHERE PersonHierarchyID.IsDescendantOf((SELECT * FROM parent)) = 1

Have no idea how to do that for multiple parents. My first try was to write something like several unions, however I'm sure that there should be smarter way of doing this.

SELECT * FROM PersonHierarchy
WHERE PersonHierarchyID.IsDescendantOf(
    (SELECT PersonHierarchyID FROM PersonHierarchy WHERE PersonID = 100)
) = 1
UNION ALL
SELECT * FROM PersonHierarchy
WHERE PersonHierarchyID.IsDescendantOf(
    (SELECT PersonHierarchyID FROM PersonHierarchy WHERE PersonID = 110)
) = 1
UNION ALL ...

P.S. Also I found such query to select list of ids which might be helpful:

SELECT * FROM (VALUES (100), (110), (120), (130)) AS Parent(ParentID)

To summarize, my goal is to write query which accepts array of parent IDs as a parameter and returns all their descendants in a single set.

One way to accomplish this is:

USE tempdb;
CREATE TABLE dbo.PersonHierarchy
(
    PersonHierarchyID HierarchyID
);

INSERT INTO dbo.PersonHierarchy VALUES ('/1/');
INSERT INTO dbo.PersonHierarchy VALUES ('/1/1/');
INSERT INTO dbo.PersonHierarchy VALUES ('/1/2/');
INSERT INTO dbo.PersonHierarchy VALUES ('/2/');
INSERT INTO dbo.PersonHierarchy VALUES ('/2/1/');

SELECT *
FROM dbo.PersonHierarchy;

DECLARE @T TABLE
(
    PersonHierarchyID HierarchyID
);

INSERT INTO @T
VALUES ('/1/'), ('/2/');

WITH parent AS (
    SELECT p.PersonHierarchyID
    FROM dbo.PersonHierarchy p
        INNER JOIN @T t ON p.PersonHierarchyID = t.PersonHierarchyID
)
SELECT ph.PersonHierarchyID.ToString(), parent.PersonHierarchyID.ToString() /* select only the necessary columns, do NOT use SELECT *  */
FROM dbo.PersonHierarchy ph
    INNER JOIN parent ON ph.PersonHierarchyID.IsDescendantOf(parent.PersonHierarchyID) = 1
ORDER BY ph.PersonHierarchyID; /* always include an ORDER BY */


Batch altering profiles of users I currently have a list of databases where the profiles of users are out of date. A lot are listed under default where we want them to be either USR_PROFILE or DBA_PROFILE.

I have a list of all the usernames of users we want under dba_profile. What would be the best way to run an alter user statement to batch change all usernames to USR_PROFILE other than the list of dba users (who may or may not be registered in the database).

So to do it individually,it would be

alter user U273622 profile USR_PROFILE;

However, that would take forever as this database has over 350 users. Would this be a reasonable guess, to ignore the dba users listed?

alter user where username='U******' except where username='U238234,U3493234,U738467' profile USR_PROFILE;

Kind of scared to test it out as I don't want to break all the usernames. Any help would be great!

You could create a PL/SQL script of course, but I think that let a simple query to build the ALTER statements is a better choice:

SELECT 'ALTER USER ' || username || ' PROFILE USR_PROFILE;'
FROM dba_users WHERE username NOT IN (...);

Of course you must be sure of which users you have to include in the WHERE clause.


SSIS: How do I create tables using Foreach Loop Container? I got a bunch of .sql files each containing a script that would create a certain table.

I want to create tables using these scripts in said files (each table to be created using one file).

I got a foreach loop container that specifies the path and which files to use.

I don't know how to configure the Execute SQL Task inside the foreach loop container to execute the script in each one of these files in order to create the tables.

Set the Foreach Loop as a Foreach File Enumerator pointed at your .sql files
Create a variable and map the Fully qualified file name to it
Set the Execute SQL Task's SQLSourceType to File connection, and create a new file connection for it
Set the Connection String expression on the file connection to your file name variable


SQL Server execution plan is using PK instead of index The following query is taking 5 seconds to return 9692 rows:

SELECT
    Project.CalculateSequencialProject,
    Project.CreateDate,
    Project.OpeningDate,
    Project.ForecastClosingDate,
    Project.RealClosingDate,
    Project.ProjectStatus,
    Unit.Name AS UnitName,
    UTE.Name AS UTEName,
    Process.Name AS Process
FROM
    Project
        LEFT JOIN Unit ON Project.Unit_Id = Unit.Id AND Project.Company_Id = Unit.Company_Id
        LEFT JOIN UTE ON Project.UTE_Id = UTE.Id
        LEFT JOIN Process ON Project.Process_Id = Process.Id
WHERE
    Project.Company_Id = '????????????????'

The execution plan is like this:

And the most "expensive" part of it is detailed here:

As you may se in the tooltip image, the Clustered Index Scan with 75% of cost is using the table primary key instead of one of the following indexes.

CREATE NONCLUSTERED INDEX [IX_Company_Id] ON [dbo].[Project]
(
    [Company_Id] ASC
)

CREATE NONCLUSTERED INDEX [IX_Project_02] ON [dbo].[Project]
(
    [ProjectType_Id] ASC,
    [ProjectStatus] ASC,
    [Unit_Id] ASC,
    [Id] ASC,
    [ProjectDirector_Id] ASC,
    [UTE_Id] ASC,
    [Company_Id] ASC,
    [LostType_Id] ASC,
    [Pillar_Id] ASC,
    [ProjectPhase_Id] ASC
)
INCLUDE (   [CalculateSequencialProject],
    [DescriptionTittle],
    [OpeningDate],
    [ForecastClosingDate],
    [RealClosingDate])

Why?

None of your indexes are good for this query

IX_Company_Id works only for the WHERE
IX_Project_02 has not overlap with any part of the query because the leading columns do not match a JOIN or WHERE

This should be better because it matches the JOIN and the WHERE although iIt does rely on a matching (Company_Id, Unit_Id) index on Unit

CREATE NONCLUSTERED INDEX [IX_Project_02] ON [dbo].[Project]
(
    [Company_Id] , --can swap these 2 to see what happens if no index on Unit
    [Unit_Id] ,

    [UTE_Id] ,

    ProcessID

)
INCLUDE
    CalculateSequencialProject,
    CreateDate,
    OpeningDate,
    ForecastClosingDate,
    RealClosingDate,
    ProjectStatus


Benefits of BTREE in MySQL What are the pros and cons of using a BTREE index in MySQL, regarding query speed, disk storage and memory usage?

Does BTREE provide easier iteration in increasing order ?
What kind of queries would benefit from a BTREE ?
Are there any disadvantages of using BTREE index ?
Does it increase space or indexing time?

Regardless of the Storage Engine (MyISAM or InnoDB), when it comes to BTREEs, you must make sure you understand the following characteristics:

Keys should be as small as possible
Random Keys for PRIMARY KEYs

Insertions (Bulk or Programmatic) will perform root node and internal splitting periodically
Introduces Overhead early in an index's life
Breeds node fragmentation (especially for index pages)
Causes Index Scans for Queries to be performed Randomly

Ordered Keys for PRIMARY KEYs

Bulk Ordered Insertions delay root node and internal splitting
Reloading data via mysqldump files and LOAD DATA INFILE commands promote the use of  sorting mechanisms to address index initialization/reorganization (See my Oct 26, 2012 post: How badly does innodb fragment in the face of somewhat out-of-order insertions?)
Programmatic Ordered Insertions promote root node and internal splitting of index pages in 45% of the cases
Delays creation of Overhead
Prevents node fragmentation
Causes Index Scans for Queries to be performed Sequentially (less disk I/O)

When it comes to BTREEs in InnoDB, they tend to be more bloated than that of its counterparts MyISAM because of InnoDB's gen_clust_index, where row data live.

The PRIMARY KEY of an InnoDB table points right to its gen_clust_index. Secondary indexes always include a PRIMARY KEY entry. If you run a query that uses a Secondary Index and also has non-indexed columns in the WHERE clause, you could easily be doing two Index Lookups. With that in mind, you need to make sure all Secondary Indexes have all the needed columns for you queries' WHERE clauses (a.k.a. Covering Index).


Where to put login.sql in Windows environment to configure SQL Plus? I installed Oracle 10g XE on my Windows Server 2008 R2. Now I would like to configure SQL Plus to use login.sql for login settings. I created a login.sql file and put it into a

  C:\users\myname

folder, but it does not seem to work. Where should I put this configuration file in Windows environment? My login.sql file is as follows:

SET pagesize 2000
SET LONG 10000
SET linesize 1000
COLUMN last_name format a20
COLUMN total format 999,999,999
SET feedback ON
alter session set nls_date_format = 'yyyy-mm-dd hh:mi:ssPM'
COLUMN global_name new_value gname
SET TERMOUT OFF
SELECT LOWER(USER) || '@' || global_name||CHR(10)||'SQL> ' AS global_name
FROM   global_name;
SET SQLPROMPT '&gname'
SET TERMOUT ON;

You need to set the environment variable SQLPATH (through the Control Panel) to tell SQL*Plus where to find the file:

SQLPATH=C:\users\myname

Setting environment variables is explained here: http://support.microsoft.com/kb/310519/en-us


Is there a reason to update statistics manually? In SQL Server,  statistics are updated automatically when Auto Update Statistics in True (which is the default). Is there a reason to update statistics manually and in what circumstances?

Sure, if your data is changing more frequently than the rate of auto stats (or, say, you are updating < 20% of the rows frequently, like updating statuses or date/time stamps). Or if your table is huge and it is not changing enough to trigger auto stats updates. Or if you have filtered indexes (since the auto stats threshold is still based on the % of change on the whole table, not the % of change of the subset of rows in the filtered index).


Reasonable values for thread_cache_size in relation with Connections I want to know reasonable values for thread_cache_size in relation with the connections that you have. Which is the good proportion? And which proportions do you handle in relation with your clients connected?

Thanks!

Keep in mind that thread_cache_size controls a "cache".  It is the number of threads (or processes, depending on the OS) to keep open just in case another connection request comes soon.  There is some cost in creating a thread. (This depends on the OS, etc.)

For Windows, thread_cache_size should be zero.  (Sorry, I don't have a reference stating that.)

If thread_cache_size / max_connections < 2% or > 15%, it may be poorly tuned.

If Threads_running / thread_cache_size > 2, then the cache may not be big enough.  (This depends on whether the number of connections is stable or fluctuating wildly.)

I suggest that Threads_created / Connections is not a good metric -- What if you have only one Connection/sec or 1000 Connections/sec?  Hence, I prefer this:  If Threads_created / Uptime > 3/second, I suggest that the cache needs increasing.

In a survey of systems, only 10% had Threads_created / Uptime > 0.26 / sec.

MySQL 5.6.8 automatically computes thread_cache_size based on max_connections.

MariaDB 10.2 takes a different approach; it "increases value of thread_cache_size to 32; adds 5 minute timeout before automatically removing threads from thread cache."  This feels like a good algorithm.

Bottom line:  One should rarely have to worry about thread_cache_size.


MariaDB allowing select only user to create databases I'm baffled. I have a select only user, but somehow, the user can create a new database, and create table inside the new database. It can't create tables inside other db. It's really odd.

This is MariaDB 5.5.28.

$ mysql -upermtest -pxxx -h mysqlhost
...
Your MySQL connection id is 4355 to server version: 5.5.28-MariaDB-log
...
mysql> show grants;
+----------------------------------------------------------------------------------------------------------+
| Grants for permtest@%                                                                                |
+----------------------------------------------------------------------------------------------------------+
| GRANT SELECT ON *.* TO 'permtest'@'%' IDENTIFIED BY PASSWORD '*XXX' |
+----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create database test_ddb_again;
Query OK, 1 row affected (0.00 sec)

mysql> use test_ddb_again;
Database changed

mysql> create table hello ( a int );
Query OK, 0 rows affected (0.01 sec)

mysql> use previously_existing_db;
Database changed

mysql> create table hello ( a int );
ERROR 1142 (42000): CREATE command denied to user 'permtest'@'10.30.25.51' for table 'hello'
mysql>

I'm sure there is a good explanation for this.

After reading this (thanks @ypercube)

MySQL : Why are there "test" entries in mysql.db?

I removed the test entries from mysql.db and it fixed it. I then realized that all the test I'd ran were using database named like test_xxx. Creating database with a name not beginning in 'test_' was not working as expected.


Is there a way to stop MySQL Replication on the master? I want the master to stop replicating data to the slave. I know I can do that on the slave with STOP SLAVE;, but I wonder if there is a way to do it in the master.

One possible solution might be to change the server_id to 0, but in this case I'll have to restart mysql in the master for changes to take effect.

What I'm looking for is a statement like STOP MASTER;.

There does not exist a STOP MASTER; command nor is there a manual mechanism from the Master to stop Replication. You would have to go to each Slave and run on of the following:

STOP SLAVE; (Kills the IO Thread and SQL Thread)
STOP SLAVE IO_THREAD; (Kills the IO Thread only)

Running either of these will get you the following:

Clean Recording of the Replication Coordinates in master.info

Master_Log_File
Read_Master_Log_Pos
Relay_Master_Log_file
Exec_Master_Log_Pos

The IO Thread is what communicates with the Master. Killing the IO Thread on the Master Side using the KILL command would abort the IO Thread on each Slave. That could corrupt the Clean Recording of Replication Coordinates.


how to check whether or not queries are doing partition elimination when accessing my partitioned table? I am considering partitioning a table.

I am trying to think in terms of how we query that table so that I achieve partition elimination.

Suppose I have a table that has a partition by month, what would I do to cause a partition elimination in my queries?

how could I test whether or not the query is causing partition elimination?

I heard I could even use a calculate field, as long as it is persisted.

For partition elimination to come into play, you would need to use the partitioned column in your join or predicate clauses which would allow SQL Server to narrow down the search using the partition metadata available. To check if partition elimination is occurring or not, look for PtnId or RangePartitionNew in the query plan.

See this post for more information. If you want to look at the XML plan, then look for RunTimePartitionSummary which provides the partition access summary details. However, this would need to be retrieved from the actual query plan.

Another option would be to partition align your indexes so that they can help with searches on additional columns. An example of that is available in this post.


Database design with multiple tables I have a data structure as follows:

Two main components: Projects and Results

So I have a table called Project which has multiple fields as Id, Name, Title, StarDate, EndDate and many more which correspond to a project.

And each project can have many Results. So first I thought to make a results table but there is a problem. I have many types of results (around 15) each of them not having too many common fields. So then I thought to make a separate table for each type of result but then it will be very hard to get all the results for a project because they can be in any of the result tables.

So to be more clear I have the following data:

For Project: ProjectID, UserID, Type, Title, Summary, StartDate, EndDate, Value, Website

For results I have the following types:

Book: Title, Authors, Year, publisher, city, number_pages
Book Chapter: [All the fields from book] + chapter title, number of pages for chapter, pagination
Article: type, title, authors, magazine, ISSN, year, pagination
Thesis: title, author, coordinator, year
Citation: category, number of citations
Events: type, name, period, members
Brevets: author, name, holder, institution, number, type
And five who have the exact same structure (Tehnology, methods, products...): name, authors
And a distinct category: Others

EDIT:

The result is something that a user attaches to the application as a deliverable for a projects...what was resulted in that X project. And in the end the application must be able to select all results (regardless of their type) for a Project X or for a User X and display them, edit them...manage them.

So far I am thinking of:

making one large table with all the fields (can come up to 30 columns)
making a table for each type of result and another one for a master control which depending on the type says in which table to look
make one small table for all the results and keep only project_id and a description column in which i save all the fields as XML or JSON

But none of these seems good to me and was wondering if there is something else acceptable an ok.

How can I structure the database for it to work in the best way.

Making one large table would be a mistake: you'll have a table with lots of NULL values and it would soon evolve into a maintenance nightmare. And about saving the data as XML or JSON -- (slaps your wrist) that's for even considering it.

Every Resulttype should get its own table, each of these tables has a primary key over an ID column and (this is the trick!) all of these IDs are taken from the same sequence!

Then you only need a table with the columns projectid, resultid (and optionally resulttype). Since you will look for the primary key, even searching over all tables can be done fast.


Net Start OracleXETNSListner I am trying to run Oracle Express Edition on Windows 7 and getting the following error.

C:\oraclexe\app\oracle\product\1O.2.O\server\BIN>net start OracleXETNSListener
    System error 5 has occurred.
    Access is denied.
    C:\oraclexe\app\oracle\product\1O.2.O\server\BIN>net start OracleServiceXE
    System error 5 has occurred.
    Access is denied.
    C :\oraclexe\app\oracle\product\1O. 2 .O\server\BIN>_

How I can fix this?

Thanks.

Access service management console via Run->services.msc.
Locate  OracleXETNSListener service from the list, Right click and open 'properties' window.
See Log Ontab, update Log On as account, normally Network Service will do the trick if Local System account is not working for you.


fetching Remote table data and storing into local dump file in pgSQL? I want to copy one table's content from a remote machine to local. I have tried with psql Command \copy. It's working fine as "copy" is not working due to copying from remote.

But is there any way I can do the same without the command console, and execute a simple query through c# and do it programatically? I am using  Postgres 9.4

Using C#, you could have a relatively simple program. For instance, if you want to write to a CSV file, you could use the following code:

using System.IO;  /* We want to Write to the FileSystem */
using Npgsql;     /* Connection to PostgreSQL database */
using CsvHelper;  /* CSV writer - https://joshclose.github.io/CsvHelper/ */

namespace ConnectToDB
{
    class Program
    {
        const string connectionString =
            "Server=localhost;" +
            "Database=joanolo;" +
            "User Id=user;" +
            "Password=password;";
        const string fileName = "Output.csv";
        const string SQLCommand = "SELECT * FROM t";

        static void Main(string[] args)
        {
            /* Connect to the database */
            NpgsqlConnection DB = new NpgsqlConnection(connectionString);
            DB.Open();

            /* Prepare to read data */
            NpgsqlCommand Cmd = new NpgsqlCommand(SQLCommand, DB);
            NpgsqlDataReader dataReader = Cmd.ExecuteReader();

            /* Create CSV writer */
            StreamWriter outputFile = new StreamWriter("Output.csv") ;
            var csv = new CsvWriter(outputFile);
            csv.Configuration.Delimiter = ",";
            csv.Configuration.Quote = '"';
            csv.Configuration.QuoteAllFields = true;

            /* Write Data Header */
            for (var i = 0; i < dataReader.FieldCount; i++)
            {
                csv.WriteField(dataReader.GetName(i));
            }
            csv.NextRecord();

            /* Read data from DB and write it to CSV */
            while (dataReader.Read())
            {
                for (var i = 0; i < dataReader.FieldCount; i++)
                {
                    var value = dataReader[i].ToString();
                    csv.WriteField(value);
                }
                csv.NextRecord();
            }

            /* Close everything */
            outputFile.Close();
            DB.Close();
        }
    }
}


How to convert an integer that was the end result of a bitwise OR to a table of the individual integers that the bitwise OR was applied against? How can I convert the final integer of a bitwise OR back to it's original set of integers that the bitwise OR operation was applied to?

For example if I have the following set of bit values: {0, 1, 2, 4, 8, 16} and if I have the bitwise OR generated value of 11 then I would like to convert that value of 11 back to 1, 2, and 8 (the only possible combinations of values).

The answer provided by J.D. works, except in the example the value 1 is not included in the results, even though for 11 the correct bits are 1, 2, 8.

The below example extends the logic from that answer, but uses a recursive CTE instead of a loop to generate the bit values and wraps it in a function that can be called easily to return a table of bits for a given mask. It also doesn't use a table to store the bit values and generates them on the fly.

Code:

CREATE FUNCTION fnGetBitsForMask (@Mask INT)
RETURNS TABLE
AS
RETURN
    WITH BitwiseValues AS
    (
        SELECT CAST(1 AS INT) AS RowNumber, CAST(0 AS INT)  AS BitValue
        UNION ALL
        SELECT b1.RowNumber+1 AS RowNumber, POWER(2, b1.RowNumber-1) AS BitValue
        FROM BitwiseValues b1
        WHERE b1.RowNumber < 30
    )
    SELECT b.BitValue
    FROM BitwiseValues b
    CROSS JOIN (SELECT @Mask AS Mask) m
    WHERE b.BitValue & m.Mask > 0

In use:

SELECT * FROM dbo.fnGetBitsForMask(11)

Result:

BitValue
1
2
8

As a Table-Valued Function, you can use this in SELECT statements and other queries without having to maintain tables or use loops.


mysql transaction vs lock I have a question regarding MySQL locking and transaction management.
 My Question is ..does mysql takes lock on the the tuple/table on which I am doing select/update in a transaction ?

With MyISAM, you can use the concurrent_insert=2 optimization, where an INSERT will not block existing or new SELECT statements (but will block anything else).

With InnoDB, the rules are:

SELECTs never block
An UPDATE or DELETE on a row will place
a lock on said row. But this can work even while the same row is
being SELECTed.
A second update (on concurrent transaction) on
same row will block, until the first is committed or rolled back.
INSERTs are more complicated: if the table has an AUTO_INCREMENT, then that is locked (though other concurrent operations are allowed if not competing for AUTO_INCREMENT)
Some concurrent INSERTs may block one another because locking is done over the clustered index.
DELETEs, UPDATEs on ranges are also more complex and may involve firther blocks due to locks on clustered index.

This is probably not a thorough list, but should get you the general picture.


SQL for Inserting the system date and a fixed time (not the system time) in a column I have a query like this:

Insert into UNITRIP (START_TIME, END_TIME)
   (TO_DATE('10/20/2015 06:05:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/20/2015 07:34:06', 'MM/DD/YYYY HH24:MI:SS')

I require a sql where the start_time is of this type: system-date 06:05:00 (fixed date is to be replaced by sysdate).

How to write a query for this?

If you're asking for an expression that returns 6:05am on the current day

trunc(sysdate) + interval '6:05' hour to minute


Two Inner join on the same field to get same data I'm developing a Report in SSRS for MS CRM 2011.

There is a table named StringMapBase which contains Option Set we have declared in different entities.
This table has Value and AttributeValue column.
Every option Set I have created in any entity, has the Value(which is the name of that) and AttributeValue(which is the name I have choosed for that option set) in the StringMapBase table.
If I named an Option Set, I should read the the name from StringMapBase.
Like this :

    select
SalesOrder.new_Order_Code,
orderreceiptstep.new_NextFollow,
orderreceiptstep.new_realization,
StringMapBase.Value,
orderreceiptstep.AccountIdName,
orderreceiptstep.TotalReceivableAmount,
orderreceiptstep.name,

orderreceiptstep.DueDate,
orderreceiptstep.ReceiptStepTypeIdName,
StringMapBase.Value,

from orderreceiptstep
inner join StringMapBase on (orderreceiptstep.OrderReceiptStepStatus=StringMapBase.AttributeValue and StringMapBase.LangId=1065)

It gives me the name of one Options Set.
Now, The problem is that I have another Option set on the same entity that should get the name of that.
It means I need another Inner Join Like above,  Like this :

    inner join StringMapBase on (orderreceiptstep.OrderReceiptStepPrioriy=StringMapBase.AttributeValue and StringMapBase.LangId=1065)

In the first  I Used orderreceiptstep.OrderReceiptStepStatus of orderreceiptstep table and in the second I used orderreceiptstep.OrderReceiptStepPrioriy in the Inner Join clause.

I want to Have These two joins on the same field, Like this :

        select
SalesOrder.new_Order_Code,
orderreceiptstep.new_NextFollow,
orderreceiptstep.new_realization,
StringMapBase.Value,
orderreceiptstep.AccountIdName,
orderreceiptstep.TotalReceivableAmount,
orderreceiptstep.name,

orderreceiptstep.DueDate,
orderreceiptstep.ReceiptStepTypeIdName,
StringMapBase.Value,

from orderreceiptstep
inner join StringMapBase on (orderreceiptstep.OrderReceiptStepStatus=StringMapBase.AttributeValue and StringMapBase.LangId=1065)
    inner join StringMapBase on (orderreceiptstep.OrderReceiptStepPrioriy=StringMapBase.AttributeValue and StringMapBase.LangId=1065)

This means StringMapValue in the first Should return something, and in the second Inner Join must return another thing.
How Can I do that?

Ah, you're right there.

It's easier if you use aliases. SQL Server needs some way to distinguish between the copies of the tables.

    select o.*, s1.Value, s2.Value
    from orderreceiptstep o
    inner join StringMapBase s1 on o.OrderReceiptStepStatus=s1.AttributeValue
    inner join StringMapBase s2 on o.OrderReceiptStepPrioriy=s2.AttributeValue
    where s1.LangID = 1065
    and s2.LangID = 1065

You might get better performance with a cte like this:

    with StringMaps as (select AttributeValue, Value from StringMapBase where LangID = 1065)

    select o.*, s1.Value, s2.Value
    from orderreceiptstep o
    inner join StringMaps s1 on inner join StringMapBase on o.OrderReceiptStepStatus=s1.AttributeValue
    inner join StringMaps s2 on o.OrderReceiptStepPrioriy=s2.AttributeValue


Query Efficiency - Filter people when name and surname are different columns, and string may appear in any of them I am working with a table representing real people data, with a schema like this (MySQL Server):

CREATE TABLE Users (
   id INTEGER PRIMARY KEY,
   name NVARCHAR(32) NOT NULL,
   surname1 NVARCHAR(32) NOT NULL,
   surname2 NVARCHAR(32),
   (more columns irrelevant to this problem)
);

From this table I need to be able to search people by name. As you can see, I have the name divided on three columns (the second surname is for accounting for Spaniards and other countries where people can have two surnames).

In lots of searches I found around the web, they usually let you put a keyword, and people with that keyword as name OR as a surname would both be retrieved. That's what I did:

SELECT * FROM Users WHERE
   (name LIKE '%keyword1%' OR surname1 LIKE '%keyword1%' OR surname2 LIKE '%keyword1%')
   AND (...same with the rest of the keywords)

It works as intended. If I send, for example, 'Mark Paul' I will get any people named 'Mark Paul' or 'Paul Mark', or 'Paul Markusson'.

However, someone pointed out to me that this query may start to take its sweet time once we start to have a respectable number of rows on the User table.

Honestly, I don't know what kind of optimization I should use for this type of query. I think MySQL can't use indexes when using LIKE comparisons starting by wildcards.

Do any of you more experienced people have an idea of what I can do or should I give up and make the app user have to search by each column separately?

If it is an innodb table you could just use 'match':
(see https://dev.mysql.com/doc/refman/8.0/en/fulltext-natural-language.html)

SELECT * FROM users WHERE MATCH (name, surname1, surname2) AGAINST ('Mark Paul' IN NATURAL LANGUAGE MODE);

But honestly i have no idea how it performs with millions of entries...

Cheers
Olaf


What is the difference between a "record" and a "row" in SQL Server? There was a rather innocuous question about adding dates and times in SQL Server that set off a rather fascinating taxonomic debate.

So how do we differentiate between these related terms and how we use them properly?

Row

Record

To quote Joe Celko (not only can you find this reference all over the web and in his Wikipedia entry, but you will even see it on T-shirts at some conferences):

  Rows are not records.

A lot of people point him out as a pedantic jerk who just likes to humble and verbally abuse newbies, and I will admit that is how he comes across. But I have also met him in person - even shared a meal with him - and I can't tell you how different his real-life persona is from his online front. I even once caught him calling rows records, and he was very embarrassed (full backstory here).

In any case, say what you will about the guy's online character, but he wrote the standard, and the fact that such an authority dictates that there is a distinction should tell you something. And as much as he cringes when someone calls a row a record, so do many of my colleagues - who are also experts in the SQL Server world. And those of us in that camp believe he is right.

For example, Itzik Ben-Gan, an obvious SQL Server guru. Here is a quote from the very first lesson in his Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012:

  As an example of incorrect terms in T-SQL, people often use the terms “field” and “record” to refer to what T-SQL calls “column” and “row,” respectively. Fields and records are physical. Fields are what you have in user interfaces in client applications, and records are what you have in files and cursors. Tables are logical, and they have logical rows and columns.

And, knowing Itzik, if you send him an e-mail or corner him at a conference he will happily tell you the same. If you call a row a record, in his opinion, you're not using the terminology correctly.

Now, being an industry full of folks of all kinds, you are likely to find material (such as the tech target articles posted in another answer) that seem to make very subtle distinctions between the two, and you will find many people in the industry consider them the same (I know several folks at Microsoft, and other folks like Brent Ozar, who will just always call it a record). That doesn't make them right, that's just their way of looking at it - they view logical and physical as the same (at least in this context) and many of them probably think the rest of us are just anal retentives spending too much time on semantics.

Since no vendor gets to say "thou shalt call them {records|rows}", we will forever be dealing with this argument, because there will always be someone who doesn't get the logical vs. physical, or was taught differently, or came from Access or programming backgrounds, etc. Just like some people say tomay-to and other people say tomah-to, there will always be a variety of people who range from "they're the same" to "they're completely different" - and many shades in between. Again, that doesn't make any of them right, because nobody can be the ultimate authority on this. But in the SQL Server space, there is definitely a majority.

That said, IMHO, when you are talking about data that is in a table, you call it a row. When you are performing an insert, you are inserting a row into a table. When you run an update, you are updating a row that is in a table. And when you perform a SELECT, you are retrieving rows from a table.

Feel free to call it a record once your application has a hold of it. But don't get angry if you say, "I inserted a record," and someone corrects you.


How to get response from stored procedure before it finishes? I need to return partial result (as simple select) from a Stored procedure before it is finished.

Is it possible to do that?

If yes, how to do that?

If not, any workaround?

EDIT: I have several parts of the procedure. In the first part I calculate several string. I use them later in the procedure to make addtional operations. The problem is that string is needed by the caller as soon as possible. So I need to calculate that string and pass it back (somehow, from a select for example) and then continue to work. The caller gets its valuable string much more quickly.

Caller is a Web Service.

You probably are looking for the RAISERROR command with the NOWAIT option.

Per the remarks:

  RAISERROR can be used as an alternative to PRINT to return messages to calling applications.

This doesn't return the results from a SELECT statement, but it will let you pass messages/strings back to the client. If you want to return a quick subset of the data you're selecting then you might want to consider the FAST query hint.

  Specifies that the query is optimized for fast retrieval of the first number_rows. This is a nonnegative integer. After the first number_rows are returned, the query continues execution and produces its full result set.

Added by Shannon Severance in a comment:

From Error and Transaction Handling in SQL Server by Erland Sommarskog:

  Beware, though, that some APIs and tools may buffer on their side, thereby nullifying the effect of WITH NOWAIT.

See the source article for the full context.


OUTER JOIN Works in one direction but not the other This started as a Rails app question but has evolved as I need to do a raw SQL query.  My title may be off but it's the best I could come up with.

Here is my data:

SELECT
     "model_locations".*
FROM
     "model_locations"

    +----+---------------------+-------------+
    | id |            model_id | location_id |
    +----+---------------------+-------------+
    |    | 17                  | 50          |
    |    | 17                  | 51          |
    |    | 10                  | 24          |
    |    | 19                  | 11          |
    |    | 19                  | 5           |
    |    | 19                  | 51          |
    +----+---------------------+-------------+
    6 rows in set

SELECT
    "models"."id", "models"."location_from_id", "models"."location_to_id"
FROM
    "models"'

+----+------------------+----------------+
| id | location_from_id | location_to_id |
+----+------------------+----------------+
| 17 | 1                | 5              |
| 18 | 50               | 24             |
| 10 | 3                | 8              |
| 1  | 50               | 11             |
| 19 | 1                | 5              |
| 20 | 1                | 11             |
| 21 | 11               | 5              |
+----+------------------+----------------+
7 rows in set

So for example:

Model 17 has Locations 50, 51 AND 1 ,5
Location 11 has Models 19, 1 AND 20, 21

So I can find the Model Locations:

SELECT
    "locations".*
FROM
    "locations"
    LEFT JOIN "model_locations" ON "locations"."id" = "model_locations"."location_id"
WHERE
    "model_locations"."model_id" = 17 OR "locations"."id" IN (1,5)

This works great - I get my 4 locations however I can't get the reverse to work:

SELECT
     "models".*
FROM
     "models"
WHERE
     "models"."location_from_id" = 11 OR "models"."location_to_id" = 11
INNER JOIN "model_locations" ON "model_locations"."model_id" = "models"."id"
WHERE
     "models_locations"."location_id" = 11'

This fails at the INNER JOIN:

PG::SyntaxError: ERROR:  syntax error at or near "INNER"

I am pretty weak on my SQL but I don't get while this doesn't work.  I have tried several variations etc. and am sure I don't have a typo.

I need the SQL that will get me Models 19, 1 AND 20, 21 related to Location 11

UPDATE

I should really not stay up late working on this stuff.  This seems to work and a swear I tried this:

SELECT
     "models".*
FROM
     "models"
LEFT JOIN "model_locations" ON "model_locations"."model_id" = "models"."id"
WHERE
     "models_locations"."location_id" = 11'
     OR
     "models"."location_from_id" = 11
     OR
     "models"."location_to_id" = 11

There are two (plus 1) - seemingly independent - relationships between models and locations. One relationship is in model_locations and the other two in models.

I find both your queries to be incorrect because they don't examine the relationships independently. If there is a relationship (between a model and a location) only in one of these two tables, the query will not return any. Using LEFT joins might be possible but seems inappropriate, too.

I'd use simple OR conditions with EXISTS subqueries or a UNION:

Final all locations of a certain model (17):

SELECT
    loc.*
FROM
    locations AS loc
WHERE EXISTS
      ( SELECT 1
        FROM model_locations AS ml
        WHERE ml.model_id = 17
          AND ml.location_id = loc.id
      )
   OR EXISTS
      ( SELECT 1
        FROM models AS mod
        WHERE mod.id = 17
          AND loc.id IN (mod.location_from_id, mod.location_to_id)
      ) ;

Final all models of a certain location (11):

SELECT
    mod.*
FROM
    models AS mod
WHERE EXISTS
      ( SELECT 1
        FROM model_locations AS ml
        WHERE ml.location_id = 11
          AND ml.model_id = mod.id
      )
   OR 11 IN (mod.location_from_id, mod.location_to_id)
 ;


SELECT row based on range from columns in second table I want to SELECT all rows from  first_table INNER JOIN second_table for values where the column in first_table fall within range of values from two columns in second_table.
As example, I have created these two tables
First table:
CREATE TABLE location (loc_id int, gpstime int, lat_start float, lat_end float);

Second table:
CREATE TABLE segments( segment_id int, lat float,foot float,bike float,bus float,car float,metro float);

Then insert these values:
INSERT INTO location
SELECT 1, 12345, 41.10, 41.17 UNION ALL
SELECT 2, 99999, 42.00, 42.12 UNION ALL
SELECT 3,12346, 41.35, 41.30

INSERT INTO segments
SELECT 1, 41.15, 0, 0, 0.95, 0.025, 0 UNION ALL
SELECT 2, 42.19, 0.95, 0, 0, 0, 0.025 UNION ALL
SELECT 3, 41.33,  0,0, 0, 0, 1

The result set I want should return all rows for which value of column lat in segments table is a value that exists between lat_start and lat_end columns of location table.
So I write the following query:
SELECT gpstime AS timestamp, lat_start,  lat_end,
CASE
    WHEN GREATEST(car, bike, bus, metro, foot) = car THEN 'car'
    WHEN GREATEST(car, bike, bus, metro, foot) =  bike THEN 'bike'
    WHEN GREATEST(car, bike, bus, metro, foot) =  bus THEN 'bus'
    WHEN GREATEST(car, bike, bus, metro, foot) =  metro THEN 'metro'
    WHEN GREATEST(car, bike, bus, metro, foot) =  foot THEN 'walking'
ELSE 'na' END AS mode
FROM location
INNER JOIN segments
ON  lat BETWEEN lat_start AND lat_end;

Problem: The BETWEEN key word returns TRUE only when check-value fall in range (min, max) and does  not evaluate to (max, min). Clearly, the value of lat in the "last" row of segments (41.33) is a value that fall within the range of (lat_start, lat_end) of location (41.30-41.35). However, the BETWEEN clause excludes this row, as per returned the table below:
+-----------+-----------+---------+------+
| timestamp | lat_start | lat_end | mode |
+-----------+-----------+---------+------+
|     12345 | 41.1      | 41.17   | bus  |
+-----------+-----------+---------+------+

How do I ensure all rows satisfying the criteria are returned?
Additional details
I posted this question yesterday, and following David answer, I was glad everything seems to be fixed. However, working on my database looks like there is still an error in the query as yet some rows are still missing. I reproduce minimal scenario of my work  once again:
Table 1:
CREATE TABLE location (id int, lat float, lon float, gpstime int);
Table 2:
CREATE TABLE trips( trip_id int, lat_start float, lat_end float,
lon_start float, lon_end float,
travel_mode text);

Add rows:
INSERT INTO location
SELECT 1, 41.2, 8.3, 11111 UNION ALL   --both lat and lon in the interval
SELECT 2, 43.3, 8.5, 22222 UNION ALL   --only lat meets criteria
SELECT 3, 44.0, 8.5,  33333            --only lon meets criteria

INSERT INTO trips
SELECT 1, 41.1, 41.9, 8.0, 8.8, 'foot' UNION ALL
SELECT 2, 43.1, 43.5, 9.5, 8.9, 'bus' UNION ALL
SELECT 3, 42.1, 42.8, 8.7, 8.0, 'metro'

And then the query, using BETWEEN SYMMETRIC:
SELECT trip_id, gpstime AS timestamp, lat, lon,  travel_mode
FROM location
INNER JOIN trips
    ON (lat BETWEEN SYMMETRIC lat_start AND lat_end)
    AND (lon BETWEEN SYMMETRIC lon_start AND lon_end)

Results:
+---------+-----------+------+-----+-------------+
| trip_id | timestamp | lat  | lon | travel_mode |
+---------+-----------+------+-----+-------------+
|       1 |     11111 | 41.2 | 8.3 | foot        |
+---------+-----------+------+-----+-------------+

Yet, row 3 of location table is not included in the result set. What I am missing here?
Note: Using BETWEEN SYMMETRIC doesn't solve the problem yet. It appears value-checking is only applied to lat column and NONE to lon.
Db<>fiddle
I share with you, this  db_fiddle  I created to test the query. Cleary, all rows of location table should be returned as rows 2 and 3 meet one criteria either.

When writing the answer below I wasn't aware of postgres' BETWEEN SYMETRIC, since pointed out by a_horse_with_no_name. That would be the simplest solution and I assume would be as easy for the query planner & runner to use indexes to serve as just BETWEEN.

In my defense, I'm mainly a SQL Server person... The longer answer is hopefully still useful for others in a similar position but not using prosgres, or where cross-DB compatibility is a requirement.

The ends of a BETWEEN comparison are always assumed to be low and higher, in that order. In most cases X BETWEEN Y AND Z it is simple syntactic sugar for X>=Y AND X<=Z and 41.33>=41.35 AND 41.33<=41.30 obviously isn't true.

You could use CASE to swap the values:

ON  lat BETWEEN
        (CASE WHEN lat_start<lat_end THEN lat_start ELSE lat_end END)
        AND
        (CASE WHEN lat_start>lat_end THEN lat_start ELSE lat_end END)

or the less verbose version suggested by ypercube™ in the comments:

ON (lat BETWEEN lat_start AND lat_end  )
OR (lat BETWEEN lat_end   AND lat_start)

but both of those would be inefficient because your join predicate is no longer saragble so will not result in an indexed search. If the number of rows being considered in each case is small because of other filters then this will not be an issue.

Another option is to UNION two comparisons:

SELECT <stuff>
FROM   location
INNER JOIN
       segments
       ON lat BETWEEN lat_start AND lat_end
WHERE  lat_start <= lat_end
UNION ALL
SELECT <stuff>
FROM   location
INNER JOIN
       segments
       ON lat BETWEEN lat_end AND lat_start
WHERE  lat_start > lat_end
;

(here the extra WHERE in each half stops you getting two rows for instances when lat_start = lat_end, so we can use the more efficient UNION ALL instead of plain UNION without worrying about duplicates in those cases)

This means you get indexed lookups for both queries instead of a scan of segments.
(Technically it would be possible for a query planner to decompose ypercube™'s example into the two lookups like the UNION example, but I'm not aware of a query planner that does this - there is a limit to how much thinking a QP can do before it starts taking too much time planning in the general case, compared to the time spent running the result.)

You could change how you are storing your data and insist that the line/area is always defined bottom-left to top-right (so you have lat_left and lat_right instead of lat_start and lat_end) then your original query will word. If direction of travel is important then you can add that as a separate boolean value: 0 for left-to-right,  1 for right-to-left, or similar.

You might want to look into extensions that exist for postgres specifically designed for geometric (including geological, lat+long based) data types and related queries: https://postgis.net
They may do a chunk of your work for you, after the initial learning curve, especially if you start needing to consider more complex shapes.


no pg_hba.conf entry for replication connection from host (Also posted to pgsql-general)

I have been configuring a slave server that needs to connect to the host. Both the master and the standby servers have a pg_hba.conf that looks like this:

# Allow anyone to connect remotely so long as they have a valid username and
# password.
host    replication     ${REP_USER}     0.0.0.0/0               md5
host    ${DB_NAME}      ${DB_USER}      0.0.0.0/0               md5

This should allow access from every IP address, right? Evidently, though, the standby server cannot connect using the REP_USER credentials via primary_conninfo

primary_conninfo = 'host=${MASTER_PORT_5432_TCP_ADDR} port=5432 user=${REP_USER} password=${REP_PASS}'

I know this doesn't work because I never see in my logs:

LOG:  streaming replication successfully connected to primary

..and I cannot connect to the master server via psql either:

psql -h $MASTER_PORT_5432_TCP_ADDR -U replication
psql: FATAL:  no pg_hba.conf entry for host "$SLAVE_PORT_5432_TCP_ADDR", user "replication", database "replication", SSL off

Why? I don't get it...

When doing this:

$ psql -h $MASTER_PORT_5432_TCP_ADDR -U replication
psql: FATAL:  no pg_hba.conf entry for host "$SLAVE_PORT_5432_TCP_ADDR", user "replication", database "replication", SSL off

Since there's no explicit database indicated with -d (for example -d postgres ), it takes replication as the database, the same as the user name. However that's probably not what you want. replication is not supposed to be a real database, as mentioned in the doc for pg_hba.conf:

http://www.postgresql.org/docs/static/auth-pg-hba-conf.html

  The value replication specifies that the record matches if a
  replication connection is requested (note that replication connections
  do not specify any particular database). Otherwise, this is the name
  of a specific PostgreSQL database.

So if you expected that this line:

host    replication     ${REP_USER}     0.0.0.0/0               md5

would allow psql to connect with the command above, it's normal that it doesn't work, even assuming that ${REP_USER} is actually replication.

As for the other line:

host    ${DB_NAME}      ${DB_USER}      0.0.0.0/0               md5

not knowing what are the real values for ${DB_NAME} and ${DB_USER} it's not really possible to comment on it.

If the real problem is the replication not happening, I don't think psql is very useful to test this since psql must connect to a specific database before doing anything useful, whereas the replication process must not and does not.


Designing a database tables for the following scenario I just got following entities Employee, Client and Company. Where Employee and Client have one to many relationship with Company  ie A single employee may be mapped to attend to many companies and same for clients too. How would i design a optimized table for this situation.
I had thought of below

Employee:

Id

Name

CompanyId

but since it is one to many companyid would have to be saved as comma seperated company id's. What do i do in this situation.

Assuming a company can be "attended to" by more than one employee, and likewise for client, you would model like this:

So both relations are 'many:many'. As gbn also said, adding "comma separated ids" would be something of a disaster in database design: you would regret it many times over.


How can I expose an Amazon RDS database as read only to other partners who access through home broadband? In one of our projects we have hosted our web application on an Amazon Linux AMI instance and it relies on a Amazon RDS MySQL instance as DB server.

One of our client's developer wants to have access to the DB to query the tables and its contents.  We do not want to provide an access with which he should be able to change any table contents (inadvertently).  We are looking something like PHPMyadmin or SQLWorkbench access, but only in read-only mode.

Adding complexity he works from home and have a broadband with dynamic IP.  Hence it seems we cannot grant IP based access as well.

As an alternate idea, we thought of replicating the database on to another server and grant full access to that replicated one so that we can be safe and satisfy the need. But this involves additional cost and the changes will not be replicated immediately.

Any ideas to expose the existing RDS as read-only will be a great advantage and is highly appreciated.

Presumably your connection is secured so only your office IP can access the database.

With that in mind a secure VPN tunnel that only directs out to the database area would provide security from them accessing any other systems

And from the database perspective his access rights should be secured down to read-only which will prevent him from modifying anything.

See http://dev.mysql.com/doc/mysql-security-excerpt/5.6/en/adding-users.html for more details on creating users, note the GRANT command, GRANT SELECT will allow them select access, so they cannot update, delete, insert or perform any other such actions.

See http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAM.html for more details on RDS authentication if that is required

As for VPN that depends on the infrastructure you have setup, speak to your IT Admins about this


out of shared memory error when doing pg_dump I have a postgres database with 3 schemas and around 1200 tables. When I try to take a schema only pg_dump of it using the command, pg_dump -h localhost -p 5432 -U postgres -d my_test_db -s, I sometimes get error

  ERROR:  out of shared memory
  HINT:  You might need to increase max_locks_per_transaction

This case also occurs in a random manner, when I re-run the same query, it works well. Is there any ways to overcome this

pg_dump takes share locks on each table it dumps. So you need to configure Postgres to support that many locks It's unclear to me if you have 3 schemas with 1200 tables each - which would mean a total of 3600 tables, or if there are 1200 table distributed across 3 schemas.

Quote from the manual

  The shared lock table tracks locks on max_locks_per_transaction * (max_connections + max_prepared_transactions) objects (e.g., tables); hence, no more than this many distinct objects can be locked at any one time. This parameter controls the average number of object locks allocated for each transaction; individual transactions can lock more objects as long as the locks of all transactions fit in the lock table

Assuming everything is configured as the defaults, you wind up with 64 * (100 + 0) = 6400 objects that can be locked. If pg_dump is the only thing running on your database the default configuration should be fine.

The fact that you only occasionally get that error, means that you have concurrent transactions that also use many object locks so that pg_dump can't allocate enough locks.

You should increase that value nonetheless to avoid the "occasional" errors. I would start with increasing it to 128.


Oracle Enterprise Manager 11g: How to see statistics of Interfaces usage? I need to give our client the following statistics:

How many users are using the Interfaces or each interface?
What interfaces are mostly used?
What are the usual usage time?
What kind of actions are mostly done?

All this info is needed to show to the client that our product is working.
My question is how to find all this data, numbers and statistics?

If you are talking about Listener statistics on Oracle Enterprise Manager then-

1) Click on Listener name on Home screen of OEM.

2) Then Click on 'Established Connections per minute' under 'State' section.

3) Now you have got the page with some statistics of listeners.

For your last question "What kind of actions are mostly done?" , I think You need to use database auditing so that you can track the activities of users connecting using the listener.


How to detect level in nested MySQL table? I have simple child-parent relation by saving parent_id. Is it possible to count which level each entry?

My DB looks like this:

As You can see from the image, for example the first item relation is:

Aston Martin

Vanquish

10

V8...
DB9
Rapide

When I selecting Aston Martin I need to know that it is first record, when Vanquish is the second, and so on.

in my opinion structure not normalised, but if need resolve it as is

SELECT
t1.id, t1.name,
CASE WHEN parent_id = 0 THEN
       1
ELSE
    (SELECT count(id)+1 from Table t2 WHERE t2.parent_id = t1.parent_id and t2.id < t1.id)
END AS seq_no
    FROM Table t1

there are many modifications possible, depending from what finally You need, such as if need select seq_no for single name


mysql add a column to a number of tables I want to add a column to a number of tables:

my tables are in the format of No_tmp e.g 1_tmp .. 9999_tmp

No = a number 1 - 1000 ...
I want to be able to add a column to all No_tmp tables every single table

Then I want to insert a text value eg "abcdef" in some tables

is it posible, I don't wish to do it manually on each table

ie

tables 1 - 223 I want to insert "guppy" into the "group" field

tables 224 - 399 I want to insert "barb" into the "group" field

tables 400 - 475 I want to insert "goldfish" into the "group" field

I figured out what I needed was the ALTER command ..

I probably did not word my question very well.

Both answers provided are good.

What I have is a large number of csv files with different sized tables, different column sizes, different numbers of columns etc, so I'm making new tables for each group of items, and logging the details of each new table in a master, from that I can analyse the structure and find common fields.

My choice of using numbers as part of the table name just makes it more readable than choosing random characters, and is easily accomplished and automated. Once I have the data I'll then start to look at normalising / formatting it.

Thank you for the answers.

From this question, I understand that, you need to store same data multiple times on a database. Before starting this understand that, it is not necessary to store same data multiple times on database. IF I understood correct, a simple work around will be, have a master table group with data guppy, barb, goldfishand use its primary id on a child tables.
Example:

master table:

create table group (id int, group_name char(10), id primary key) engine = innodb;

child table:

create table child1 (id int, group_id int, ..... foreign key group_id references group(id), id primary key) engine = innodb;

But, it depends on your requirement, may be after analyzing complete requirement, this model may/may not applicable. I hope this answer will help you in some way. Thank you.


All pull subscriptions cannot connect to Distributor recently all my PULL subscriptions have this message in the synchronization status. :-

The process could not connect to Distributor 'DISTRIBUTIONDB'

This is only happening with the PULL subscriptions. I have attempted to reinitialize with a new snapshot, but the message remains.

when attempting to look at the history it suggests that the agent job doesn't exist. So I removed it and recreated the subscription and the job from a T-SQL script. monitor now suggests that the job doesn't exist but when run the T-sql sp_addpullsubscription code on the subscriber i get

Msg 21002, Level 11, State 1, Procedure sp_addpullsubscription_agent, Line 250
The Distribution Agent for this subscription already exists (YAZOO-MLT-Statics-KINKS-MLT-Replicated-94CD221C-4F8E-4732-8738-A6101E9D571B).

This is a permissions issue, the Distribution Agent process account does not have rights on the distributor.

Verify the Distribution Agent process account is a member of the PAL, has read permissions on the snapshot share, and is a member of the db_owner fixed database role in the subscription database.


Questions and answers database design for Core Data I am designing database for a question and answer projects , and I am stuck. I do not know how to do it.

Here is information:

There will be many users
There will be many questions
Each question has 3 options (answers) and user chooses one.
Each question has 3 time constraints to answer. (Time constraints will
change as questions.)
Each time constraint has different point. (Also points will change as
questions.) (For example, question 1 has 3, 10 , 40 seconds time constraints. If user answers in 3 seconds, he/she will take 30 points. If users answers in 10 seconds, he/she will take 10 points etc.)
Only one answer is correct, other 2 are wrong.
There will be score table for users.
User will answer once a question.

My current design can be seen here: https://s32.postimg.org/lolr4vqvp/Screen_Shot_2016_06_29_at_17_14_01.png

I want to manage everything with Core data after I want to collect all data in sqlite database. Could you help me to create database design?

So, this is

an MCQ (Multiple Choice Questionnaire)
a test where users will only take it once
there are time constraints

I think that you've over-engineered on your first pass - you woudn't be the first! :-)

A preliminary proposal would be be as follows:

CREATE TABLE Student (Student_ID int, Name VARCHAR(25));

CREATE TABLE Question (Question_ID int, Name VARCHAR(25),
                       Question_Text VARCHAR(64000),
                       Answer1 VARCHAR(4096),
                       Answer2 VARCHAR(4096),
                       Answer3 VARCHAR(4096),
                       Correct_Answer int,
                       Threshold_1 int,  -- time constraints per question
                       Points_T1   int,
                       Threshold_2 int,
                       Points_T2   int,
                       Threshold_3 int,
                       Points_T3   int);

The reason I put the thresholds in with the question is that you have said that they may vary with the question.

CREATE TABLE Student_Response (Student_ID int,
                               Question_ID int,
                               Student_Answer int,
                               Answer_Time int,
                               Answer_Points int);

Now, from here, SQLite doesn't support calculated/computed/generated fields, and from here nor does it support IF or CASE statements in TRIGGERs, so I think you're going to have to manage the calculation of Answer_Points in application code.

This will obviously depend on

whether the answer is correct and
the time taken to answer

You will also need the RI (Referential Integrity) CONSTRAINTs below:

ALTER TABLE Student_Response
  ADD CONSTRAINT fk_sr_question FOREIGN KEY (Question_ID)
    REFERENCES Question (Question_ID);
ALTER TABLE Student_Response
  ADD CONSTRAINT fk_sr_student FOREIGN KEY (Student_ID)
    REFERENCES Student (Student_ID);
 ALTER TABLE Student_Response
  ADD CONSTRAINT uq_sr_student_question
    UNIQUE (Student_ID, Question_ID);

You also said

  User will answer once a question

so, you will need this UNIQUE CONSTRAINT:

ALTER TABLE Student_Response
  ADD CONSTRAINT uq_sr_student_question
    UNIQUE (Student_ID, Question_ID);

.


What is limiting MySQL to write max. 10 Inserts per second? I am running an MySQL server. Build a small RAID with 3 harddisks which can Write 300MB/s and this damn MySQL just don't want to speed up the writing. 10 Inserts per second is the max I have seen till now.

What is the bottleneck?!

Here are some Screenshots:

Answers to comments:

Example table: 10 Double Columns and one Datetime, 500.000 rows (to import)
The inserts I do are just initial data inputs like 500.000 times "insert into ... value ..."
I tried RazorSQL which is sending 500 transactions at once, but no effect.
It is a RAID0 with 3x2TB no-SSD-disks.

Tuning LOG:

SET GLOBAL sync_binlog = 500; results in 20.25 inserts per second.
SET GLOBAL innodb_buffer_pool_size = 2684354560; no effect
SET innodb_buffer_pool_instances = 4 (in mysqld.conf); setup in config file makes no effect. still instances = 1.

So this is my SOLUTION. Imported my data of 500.000 rows in 3 minutes. In fact a lot of this settings is not for company grade systems. In a private LAN with 1-2 workstations it should be okay.

Here is my config file for the record. May it help somebody and my future me. =)

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /raiddrive/mysql
log-error       = /raiddrive/mysql/error.log
bind-address    = 192.168.2.104
port            = 3306
#hardcore performance https://dba.stackexchange.com/questions/29913/innodb-insertion-faster
skip-log-bin
skip-innodb-doublewrite
transaction-isolation = READ-COMMITTED
innodb_flush_log_at_trx_commit = 0
#if you lost root password
#init-file      = /raiddrive/mysql/setroot.sql
secure_file_priv= ""
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_io_capacity=2000
innodb_buffer_pool_instances=4
innodb_buffer_pool_size=8388608000


Is there a way to restrict new database creation to a particular zone? We have a sharded mongo cluster with a database per client model. Given many roughly same size clients - the default db creation has proven to work well at evenly distributing the load, while avoiding multi-shard queries.

Up till now, all shards were homogeneous SSD based deployments, however now, we are trying to add heterogeneous larger but slower HDD based shards - so each clients can have few large bulk collections located there (while their primary shard stays on SSD). This is easliy accomplished with the zoning method (2 zones, hdd and ssd) - and would work very well if for one issue:

TL;DR: Are there any ways to restrict new database creation to only happen on SSD shards?

Only way I know of is to let DB creation happen, then check where it got created and use movePrimary if it is in wrong zone. Issue with this is that process is manual and involves hunting down and restarting every mongos router - as movePrimary was not quite designed to be production ready....

Answer is yes and no. Clustered system marks DB's primary location to shard what have less DB's. Trying to distribute evently. So, automatically you cannot define that location.

IF you log into specific replica set directly and create DB there, then that's it DB's primary location then.

However, you can use that movePrimary command (without mongos reboot) if you move sharded collection. You can shard collection what don't exists, you move that non-existing collection to wanted location and then "drop" that collection. DB have new primary location and all mongos knows now that new location. ;-)


SQL recursion and cte - dates timeseries I need to build vector(s) of time series dates (say 10) for a subset of start dates.

The following statement returns 10 consecutive dates starting from '2010-01-01'.

;with cte as
(select 1 i union all
 select i+1 i from cte where i < 10)
select '2010-01-01' as start_date, dateadd(d, i-1, '2010-01-01') AS trial_date
INTO #tmp_trialdates
FROM cte

Does anyone have any suggestions as to how I could incorporate querying a table (or other dataset) of multiple start dates (not necessarily sequential),  generating time series dates for each start date, then populating the data in the one output table?

For this you need to change the anchor set of the recursive CTE, so each row starts with the dates from the other table before starting the recursive portion.

DECLARE @AmountDays INT = 10

;WITH GeneratedDays AS
(
    -- Anchor
    SELECT
        DateSeriesIdentifier = T.DateSeriesIdentifier,
        Date = T.Date, -- Assuming it's DATE data type
        RecursionLevel = 1
    FROM
        OtherTable AS T

    UNION ALL

    -- Recursion
    SELECT
        DateSeriesIdentifier = G.DateSeriesIdentifier,
        Date = DATEADD(DAY, 1, G.Date),
        RecursionLevel = G.RecursionLevel + 1
    FROM
        GeneratedDays AS G
    WHERE
        G.RecursionLevel + 1 <= @AmountDays
)
SELECT
    G.DateSeriesIdentifier,
    G.Date,
    G.RecursionLevel
FROM
    GeneratedDays AS G
ORDER BY
    G.DateSeriesIdentifier,
    G.RecursionLevel

This is a result from a set I used to test which had dates 2016-02-04, 2018-01-19 and 2018-08-30:

DateSeriesIdentifier    Date            RecursionLevel
21                      2016-02-04      1
21                      2016-02-05      2
21                      2016-02-06      3
21                      2016-02-07      4
21                      2016-02-08      5
21                      2016-02-09      6
21                      2016-02-10      7
21                      2016-02-11      8
21                      2016-02-12      9
21                      2016-02-13      10
620646                  2018-01-19      1
620646                  2018-01-20      2
620646                  2018-01-21      3
620646                  2018-01-22      4
620646                  2018-01-23      5
620646                  2018-01-24      6
620646                  2018-01-25      7
620646                  2018-01-26      8
620646                  2018-01-27      9
620646                  2018-01-28      10
639701                  2018-08-30      1
639701                  2018-08-31      2
639701                  2018-09-01      3
639701                  2018-09-02      4
639701                  2018-09-03      5
639701                  2018-09-04      6
639701                  2018-09-05      7
639701                  2018-09-06      8
639701                  2018-09-07      9
639701                  2018-09-08      10


Find "n" consecutive free numbers from table I have some table with numbers like this (status is either FREE or ASSIGNED)

id_set  number  status
-----------------------
1       000001  ASSIGNED
1       000002  FREE
1       000003  ASSIGNED
1       000004  FREE
1       000005  FREE
1       000006  ASSIGNED
1       000007  ASSIGNED
1       000008  FREE
1       000009  FREE
1       000010  FREE
1       000011  ASSIGNED
1       000012  ASSIGNED
1       000013  ASSIGNED
1       000014  FREE
1       000015  ASSIGNED

and I need to find "n" consecutive numbers, so for n = 3, query would return

1       000008  FREE
1       000009  FREE
1       000010  FREE

It should return only first possible group of each id_set (in fact, it would be executed only for id_set per query)

I was checking WINDOW functions, tried some queries like COUNT(id_number) OVER (PARTITION BY id_set ROWS UNBOUNDED PRECEDING), but that's all I got :) I couldn't think of logic, how to do that in Postgres.

I was thinking about creating virtual column using WINDOW functions counting preceding rows for every number where status = 'FREE', then select first number, where count is equal to my "n" number.

Or maybe group numbers by status, but only from one ASSIGNED to another ASSIGNED and select only groups containing at least "n" numbers

EDIT

I found this query (and changed it a little bit)

WITH q AS
(
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY id_set, status ORDER BY number) AS rnd,
         ROW_NUMBER() OVER (PARTITION BY id_set ORDER BY number) AS rn
  FROM numbers
)
SELECT id_set,
       MIN(number) AS first_number,
       MAX(number) AS last_number,
       status,
       COUNT(number) AS numbers_count
FROM q
GROUP BY id_set,
         rnd - rn,
         status
ORDER BY
     first_number

which produces groups of FREE/ASSIGNED numbers, but I would like to have all numbers from only first group which meets the condition

SQL Fiddle

This is a gaps-and-islands problem. Assuming there are no gaps or duplicates in the same id_set set:

WITH partitioned AS (
  SELECT
    *,
    number - ROW_NUMBER() OVER (PARTITION BY id_set) AS grp
  FROM atable
  WHERE status = 'FREE'
),
counted AS (
  SELECT
    *,
    COUNT(*) OVER (PARTITION BY id_set, grp) AS cnt
  FROM partitioned
)
SELECT
  id_set,
  number
FROM counted
WHERE cnt >= 3
;

Here's a SQL Fiddle demo* link for this query: http://sqlfiddle.com/#!1/a2633/1.

UPDATE

To return only one set, you could add in one more round of ranking:

WITH partitioned AS (
  SELECT
    *,
    number - ROW_NUMBER() OVER (PARTITION BY id_set) AS grp
  FROM atable
  WHERE status = 'FREE'
),
counted AS (
  SELECT
    *,
    COUNT(*) OVER (PARTITION BY id_set, grp) AS cnt
  FROM partitioned
),
ranked AS (
  SELECT
    *,
    RANK() OVER (ORDER BY id_set, grp) AS rnk
  FROM counted
  WHERE cnt >= 3
)
SELECT
  id_set,
  number
FROM ranked
WHERE rnk = 1
;

Here's a demo for this one too: http://sqlfiddle.com/#!1/a2633/2.

If you ever need to make it one set per id_set, change the RANK() call like this:

RANK() OVER (PARTITION BY id_set ORDER BY grp) AS rnk

Additionally, you could make the query return the smallest matching set (i.e. first try to return the first set of exactly three consecutive numbers if it exists, otherwise four, five etc.), like this:

RANK() OVER (ORDER BY cnt, id_set, grp) AS rnk

or like this (one per id_set):

RANK() OVER (PARTITION BY id_set ORDER BY cnt, grp) AS rnk

* The SQL Fiddle demos linked in this answer use the 9.1.8 instance as the 9.2.1 one doesn't appear to be working at the moment.


What are the two protocols used by SQL Server Windows authentication? I was asked this question in an interview, and I have no idea what the answer is.

I know we have Windows authentication mode and SQL Server authentication. But the interviewer said that we have two protocols that Windows verify when using Windows authentication.

I just can't find anything on the Internet about this.

They were probably looking for an answer that discuss the "Kerberos" and "NTLM" Windows authentication protocol/schemes.

Once you know what to search for, there are lots of articles online about how these schemes relate to SQL Server:

Questions About Kerberos and SQL Server That You Were Too Shy to Ask
Understanding Kerberos and NTLM authentication in SQL Server Connections

  In the NTLM protocol, the client sends the user name to the server; the server generates and sends a challenge to the client; the client encrypts that challenge using the user’s password; and the client sends a response to the server.

...and

  Kerberos authentication (protocol) provides a mechanism for mutual authentication between a client and a server on an open network.

Comment: emphasis/meaning added
How to make sure that you are using Kerberos authentication when you create a remote connection to an instance of SQL Server 2005


Set uncommitted read as default in Oracle SQL Developer with DB2 database I am using SQL Developer with a IBM DB2 database. I am only querying the data for analytical purposes - never making any updates, additions or deletions. I was wondering if there is a way to ensure that every query is an uncommitted read by default so that I never lock the tables. Is there a way that I can do this without typing FOR FETCH ONLY WITH UR at the end of every query? Would enabling the settings for Autocommit in Preferences -> Database -> Advanced achieve the same goal (not lock tables)?

There are a couple of ways to achieve this. Firstly, you can simply run the statement

SET CURRENT ISOLATION UR

in your session before executing other statements.

Alternatively, you can set a property in the JDBC URL you use to connect to the database, like so:

jdbc:db2://host:port/database:defaultIsolationLevel=1;

The value 1 corresponds to the constant java.sql.Connection.TRANSACTION_READ_UNCOMMITTED (note that it is implementation-dependent and may break).


how to make table names dynamic in SQL? I am trying to make the table name dynamic

declare @varString as nvarchar(10)
set @varString = '06232016'
declare @testString as nvarchar(500)
set @testString = 'SELECT  *  from ' +  'Cases_' + @varstring
execute sp_executesql @testString
Select top 10 FROM @teststring

The first 5 lines of the code are working fine. Now how do I use the dynamic table name I just created? The last line is giving the error "Incorrect syntax near the keyword 'FROM'".

The line that is failing is:

Select top 10 FROM @teststring

a) You're missing the column names (or indeed *)

b) What you're trying to do is not possible unless you use dynamic SQL

You use it in the exact way you are already using it.

-- Declare Table Name
DECLARE @tablename AS nvarchar(10);
SET @tablename = 'MyTestTable';

-- Execute a query
DECLARE @query_a AS nvarchar(500);
SET @query_a  = 'SELECT TOP 10  * FROM dbo.' +  @tablename;
EXECUTE sp_executesql @query_a;

-- Execute another query
DECLARE @query_b AS nvarchar(500);
SET @query_b = 'UPDATE dbo.' + @tablename + ' SET col_a = ''xyz''';
EXECUTE sp_executesql @query_b;

Warning: If you are inputting the tablename from a GUI, you need to sanitise the data to avoid SQL Injection attacks (https://msdn.microsoft.com/en-us/library/ms188001.aspx)

N.B. Use an ORDER BY when using TOP


(re)creating empty tables in partially restored mysql database fails with “table exists” Following situation:

I have a MySQL Server (running Percona Server 5.7) set up as a replication slave. This server should hold only a partial replication. To get to this I made a partial backup of another identical slave and applied it to the server (using innobackupex). This is running fine with no problems at all.

The problem that i have now:

There are a few tables that are "slave-specific" that i have to create. Unfortunately when i try to create one of these tables, mysql only creates the tablespace but stops after this, telling me that the table already exists. But in a "show tables" the tables don't show up.

In the mysql data directory only the .ibd files are created for said tables. Deleting these files and trying again doesn't help.

Is there any place where InnoDB saves it's tables that i don't know of? The information_schema database was copied with the backup so maybe there is something left in it but I do not know where that could be.

I solved this by making a complete backup and then truncating the tables that need to be empty on the slave.
Partial backup didn't work unfortunately.


Setting Alerts for Autogrow Can I set alerts for an autogrow event in SQL Server (2005 or 2008 R2)?

I've searched around and haven't found the 'error number' for autogrowth, if there is one.

In case this isn't possible, how else do you suggest setting up alerts for autogrow?

I've thought about configuring SQL Server notifications with Service Broker as an alternative, but I'm open to suggestions.

I think Event Notifications for DATA_FILE_AUTO_GROW and LOG_FILE_AUTO_GROW are the appropriate way. Since the autogrowth events are not errors they cannot be detected by the SQL Agent eventing infrastructure so EN is pretty much the only reasonable alternative.


Why “sudo vim /etc/my.cnf” file is empty? I want to edit some of InnoDB variables ,I go to the .my.cnf which is located in home/.my.cnf I found the file just empty. And when I check SHOW VARIABLES in MySQL it gives me all the variables and their values. Why the file is empty and how can I edit the needed variables ?

.my.cnf in your home directory is only read by the mysql clients, so this is not where you should set system variables for the mysql server. .my.cnf can be used e.g. for your credentials so that you can log in without having to type the username and password every time.

The system variables for the server as well as system wide for all users can be configured in /etc/my.cnf and files included by that file. If you want to edit this or change any system variables, make sure to set the variables in the correct section of the file. See the documentation on option files for more details and examples.

SHOW VARIABLES; will show all system variables and their values - this includes variables you haven't explicitly set in your .cnf files as it will also show variables that just have default values.


I would like to get a unique based on email address (MySQL) I can work on basic queries and have been having a bit of trouble the way Wordpress stores their database information. The below query I have been using to download all form submissions for contests that we run. I usually download the results and then sort what I need to using excel or google sheets. Our last bunch of contests have had a lot of submissions, and I would like to take the following query and take it one step further :

SELECT b.id, b.date_created, a.lead_id, a.form_id,
   max(case when a.field_number = 13 then value end) as FirstName,
   max(case when a.field_number = 14 then value end) as LastName,
   max(case when a.field_number = 15 then value end) as Address,
   max(case when a.field_number = 16 then value end) as Address2 ,
   max(case when a.field_number = 17 then value end) as City,
   max(case when a.field_number = 18 then value end) as Province,
   max(case when a.field_number = 19 then value end) as PostalCode,
   max(case when a.field_number = 20 then value end) as Country,
   max(case when a.field_number = 4 then value end) as PhoneNumber,
   max(case when a.field_number = 3 then value end) as EmailAddress,
   max(case when a.field_number = 44 then value end) as Question,
   max(case when a.field_number LIKE 42.1 then value end) as ClientNewsletter,
   max(case when a.field_number LIKE 27.1 then value end) as LocalNewsletter
FROM wp_rg_lead_detail a
LEFT JOIN wp_rg_lead b
ON a.lead_id = b.id
WHERE a.form_id = 45
GROUP BY a.lead_id;

This gets me my full list of entries. I would like to be able to generate three more results.

This current result I would like to get unique entries by EmailAddress

The two new lists I would like are based on whether ClientNewsletter is selected, as well as LocalNewsletter.

Any help is appreciated.

Are you saying just all the unique email addresses?  Have you tried incorporating DISTINCT in any way?

As for the others, seems like an WHERE statement would be the way to go.


Mixed data type case expression PostgresSQL I am trying to do a case expression with mixed data types with no avail. Please could I get some help with what I'm doing wrong, please see my statement below

Case
   when c.student_age > 19
     and c.count_forms > 3
     and ss.value :: date <@ fiscal_year.f_year
       then "Y"
   else "N"
end as student_r_year

I'm using both int and date data in the first part of the case statement and that's where the issue is.

I could not find the definition of <@, but from your description, I assume that you  mean that ss.value is the same year as fiscal_year.f_year:

Case
   when c.student_age > 19
     and c.count_forms > 3
     and ss.value :: date BETWEEN (fiscal_year.f_year::varchar || '-01-01')::date
                              AND (fiscal_year.f_year::varchar || '-12-31')::date
   then 'Y'
   else 'N'
end as student_r_year

An alternative is to extract the year from ss.value:

Case
   when c.student_age > 19
     and c.count_forms > 3
     and extract(year from ss.value :: date) = fiscal_year.f_year
   then 'Y'
   else 'N'
end as student_r_year


How to use internal variables in Report Builder Textboxes I have a query in my dataset that is calculating a date range:

DECLARE @StartDate AS DATE = DATEADD(WK, DATEDIFF(WK, 0, GETDATE()) - 1, 0)
DECLARE @EndDate AS DATE = DATEADD(WK, DATEDIFF(WK, 0, GETDATE()), 0)

I've setup the parameters as an internal value with no prompt.

I'm trying to use these variables/parameters in a report title:

"Resource Activity Between Two Statuses - " & Parameters!StartDate.Value & " - " & Parameters!EndDate.Value

...and I'm getting an error.  Unsure if I'm stepping outside of the design of what SSRS can do, or if I'm going about it the wrong way, but the end result should be obvious - I'm wanting to show the date range in the title of the report.

  The report parameter ‘StartDate’ has a DefaultValue or a ValidValue that depends on the report parameter “StartDate”. Forward dependencies are not valid.

Ok, found out that if you're using SSRS, you cannot declare variables in the query and expect to use them on the layout of the report.  If you do, you need to declare and set the values in SSRS, not in the query.

Here is the page I used to set dates for start and end dates for the variables through SSRS:  https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a62672c2-4005-4dc6-935f-7c90e9e55491/ssrs-expression-retrieve-last-week?forum=sqlreportingservices


Reset all IDs in a Large Table Consider a table that contains around 9,000,000 rows. Each row has a int-based "ID" IDENT column whose value automatically increments on each insert. The table undergoes a lot of "churn," that is to say, a good number of the rows are deleted and added to this table in the course of a day. Although the total number of rows isn't growing at a particularly high rate, the value of the numeric ID assigned to individual rows could conceivably hit the maximum ID value (2^32 ?) at some point over the next couple of years.

Is there some SQL code that can be run to reset all IDs in all rows of the table ? Ideally, if the table contains 'x' number of rows, IDs between 1 and 'x' would be assigned. I'd then re-seed the identity generator to 'x+1'.

The ID column is not a foreign key for any other table.

The process would only need to be performed once every few years so I wouldn't mind running the query in a single-user environment if necessary.

I can't change the type of the ID column to long or varchar to solve the problem. Apparently there is legacy code that depends on the value being a 32bit integer.

I am using SQL Server 2008.

Alright, then.

One option is to create a staging table with a new ID as Row_number(). Consider the below sql:

begin transaction createstaging

select row_number() over (order by existingID) newIDValue, DataColA, DataColB, ...
into staging_table
from source_table

truncate table source_table

commit transaction createstaging
GO

set identity_insert source_table on
GO

insert source_table
(existingID, DataColA, DataColB, ...)
select
newIDValue, DataColA, DataColB, ...
from staging_table
order by newIDValue

GO

set identity_insert source_table off
GO

drop table staging_Table
GO

The truncate table in combination with the identity_insert should mean that you do not have to manually reset the identity seed on the source table.


SQL Server making arithmetic abort enabled I am using SQL Server 2008.

In my stored procedures, I often have to call for XML data type, functions (like split).

Is it possible to permanently enable arithmentic abort for a database? I know it can be done using properties window > options but is there any script i can use to turn it on?

The reason to look for script is obvious; i have too many DBs to do it manually.

I assume set arithabort off and set arithabort on turns on arithabort only for the context.

As well as Marian's answer which is SQL Server Instance level, you can use ALTER DATABASE to set it individually. You can also use it on the model database so that all new databases inherit the setting


How to perform multiple selects without using for loop I am querying for ids using a for-loop. So multiple selects are being used. Is there a better way to do this to have a single select statement for all ids?

for id in aoi_ids:
    sql = """SELECT id, date, col3 FROM table1 WHERE id=%s"""
    params = [id]

    return helper_func_to_execute_query(sql, params)

I know once way is to use another table. Any other faster way to do it?

If you can sanitize input, you can use an IN statement.

SELECT *
FROM t
WHERE id IN ( 1,2,3 );

Or you can chain the WHERE clauses.

WHERE ID = 1 OR ID = 2;


get row/value by primary key number I'm writing my own python wrapper on top of official mysqldb connector. I'm trying to design method that would dynamically extract row data knowing KEY ID.

 SELECT * FROM cameras;
+----------+----------------------+-----------+------------+
| cameraID | cameraName           | fullframe | sensorSize |
+----------+----------------------+-----------+------------+
|        2 | Canon EOS 5D Mark II |         1 | 36x24      |
|        3 | Nikon                |         1 | 10x10      |
+----------+----------------------+-----------+------------+

knowing table name ("cameras" in example above), and primaryID key integer value (but not primaryID column name), how can i get a row ?

if I had primary key column name, than i could construct:

mysql> SELECT * FROM cameras WHERE cameraID=2;

But How can I ask for it without column name ? Is there something like "WHERE ID=#" or "WHERE 'COLUMN KEY'=#"?

Edit:
I was trying to create multistatement:

mysql> SELECT @columnID:=`COLUMN_NAME` AS columnID FROM `information_schema`.`COLUMNS` WHERE (`TABLE_NAME` = 'cameras') AND (`COLUMN_KEY` = 'PRI');
+----------+
| columnID |
+----------+
| cameraID |
+----------+

but mysql did not allow me to use variable as column name directly:

mysql> SELECT * FROM cameras WHERE ', @columnID, '= '2';
Empty set (0.00 sec)

So I found one 'solution' for mysql:

SELECT @columnID:=`COLUMN_NAME` AS columnID FROM `information_schema`.`COLUMNS` WHERE (`TABLE_NAME` = 'cameras') AND (`COLUMN_KEY` = 'PRI');
SET @SQLCOMMAND = CONCAT('SELECT * FROM cameras WHERE ', @columnID, '= 2');

PREPARE stmt1 FROM @SQLCOMMAND;
EXECUTE stmt1;
+----------+----------------------+-----------+------------+
| cameraID | cameraName           | fullframe | sensorSize |
+----------+----------------------+-----------+------------+
|        2 | Canon EOS 5D Mark II |         1 | 36x24      |
+----------+----------------------+-----------+------------+
1 row in set (0.00 sec)
DEALLOCATE PREPARE stmt1;

As I am using python connector to execute those commands, I had to modify it to different approach. Multiquery was not working for me in this case

cursor = self.conn.cursor(buffered=True)

query1 = "SELECT `COLUMN_NAME` FROM `information_schema`.`COLUMNS` WHERE (`TABLE_NAME` = {0!r}) AND (`COLUMN_KEY` = 'PRI');".format(tablename)
cursor.execute(query1)
columnID = cursor.fetchone()[0]

query2 = "SELECT * FROM {0:s} WHERE {1:s} = {2!r};".format(tablename, columnID, str(idx))
cursor.execute(query2)
single_row = cursor.fetchone()
cursor.close()

(2, 'Canon EOS 5D Mark II', 1, '36x24')


How does PostgreSQL store timestamp internally? In case of MySQL,

TIMESTAMP values are stored as the number of seconds since the epoch ('1970-01-01 00:00:00' UTC)`

In case of PostgreSQL with the version less than or equal to 9.6

timestamp values are stored as seconds before or after midnight 2000-01-01

In case of PostgreSQL with the version greater than or equal to 10, there is no explanation about this
I have two questions about the internal logic of PostgreSQL.

Does it still use the same standard as the version 9.6?
Why "midnight 2000-01-01"? Unix epoch starts from 1970-01-01 00:00:00 UTC. J2000 epoch starts from 12 noon (midday) on January 1, 2000.

It seems like only a few systems use 2000-01-01 00:00:00.
Because PostgreSQL provides functions to convert UNIX epoch into the timestamp to_timestamp or vice versa EXTRACT(EPOCH FROM ...), using the standard that is different from UNIX epoch seems like to require additional offset calculations.

That information from the 9.6 documentation is wrong. Timestamps are stored as microseconds since 2000-01-01 00:00:00.
See include/datatype/timestamp.h:
/*
 * Timestamp represents absolute time.
 *
 * [...]
 *
 * Timestamps, as well as the h/m/s fields of intervals, are stored as
 * int64 values with units of microseconds.  (Once upon a time they were
 * double values with units of seconds.)
 *
 * [...]
 */

typedef int64 Timestamp;
typedef int64 TimestampTz;

Back in 9.6, there was also the option to store timestamps as floating point values, but that has been removed since.
That should answer your first question.
The reason why 2000 was picked probably stems from the time when you could store timestamps as floating point values. Here, the precision got better for values close to the epoch, and it made sense to pick something more future-proof than 1970 (mind you, that was before 2000).
Subtracting a constant in order to calculate the Unix epoch is no great effort.


Recover lost MySQL password I hate to ask a stupid question like forgetting the password, but this one is really hard to get back. Tried all the methods I can find online, and none is working. Even deleting the database requires my password, which means if I don't get my password back somehow, or reset, I am basically done with using it for my life.

I am using Mac OS 10.10.2 Here is what i have tried: Tried to stop it first, using a script from the oracle site:

kill `cat /mysql-data-directory/host_name.pid`

...getting no such directory error. I terminated MySQL manually from system preferences, and did the following:

Everyone is saying to use:

mysqld_safe --skip-grant-tables

...but it is not working here. No mysqld_safe command even exists when I punched it into cmd. Then I digged into the directory files, and used:

sudo ./usr/local/mysql/bin/mysqld_safe --skip-grant-tables &

After i did, it still asked for my password! Then I tried to remove sudo:

./usr/local/mysql/bin mysqld_safe --skip-grant-tables

it becomes:

fan$ ./mysqld_safe --skip-grant-tables
./mysqld_safe: line 522: /usr/local/mysql/data/mysqld_safe.pid: Permission denied
awk: i/o error occurred while closing /dev/stdout
 input record number 8, file
 source line number 1
160204 00:21:13 mysqld_safe Logging to '/usr/local/mysql/data/guiyang-Fans-MacBook-Pro.local.err'.
touch: /usr/local/mysql/data/guiyang-Fans-MacBook-Pro.local.err: Permission denied
chmod: /usr/local/mysql/data/guiyang-Fans-MacBook-Pro.local.err: Permission denied
160204 00:21:13 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
./mysqld_safe: line 130: /usr/local/mysql/data/guiyang-Fans-MacBook-Pro.local.err: Permission denied
rm: /tmp/mysql.sock: Permission denied
rm: /usr/local/mysql/data/guiyang-Fans-MacBook-Pro.local.pid: Permission denied
rm: /usr/local/mysql/data/guiyang-Fans-MacBook-Pro.local.pid.shutdown: Permission denied

Please help to save my life with this mess! I really really don't understand why Oracle wants to give me a so sophisticated password in the beginning instead of asking me to set one! I jotted down the password it gave me, which was a combination (&*,<>AaFz0).

I have updated my termination procedures, and it is not working at all. I have used http://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html to try to solve the problem. I am getting the same thing,the big chunk of nonsense above.

Here is what I got:

/usr/local/mysql/data/mysqld_safe.pid: Permission denied
awk: i/o error occurred while closing /dev/stdout
 input record number 1, file
 source line number 1
160204 01:11:50 mysqld_safe Logging to '/usr/local/mysql/data/Fans-MacBook-Pro.local.err'.
touch: /usr/local/mysql/data/Fans-MacBook-Pro.local.err: Permission denied

Try the below:

Add below 2 lines in my.cnf

skip-grant-tables
skip-networking

Restart mysql service

service mysqld restart

Note: if you don't have a service, you can do from /etc/init.d/
Connect mysql:

mysql

Update root password:

UPDATE mysql.user
SET PASSWORD=PASSWORD('MyNewPass')
WHERE USER='root';

flush privileges";
exit;

Remove newly added lines from my.cnf and restart the mysql service.

Permission errors mean you don't have admin permission where you are trying to do the task, so login with admin login first.


MySQL subquery with WHERE filters not working as expected I have a question/tagging system like StackExchange.  I want to display the tags associated with each question and also show the total number of times the tag is used, unless the question has been taken offline or is suspended, then it is not included in the total number count.
Tables are:
QUESTIONS - includes fields: suspended and offline
TAGS - includes fields: tag_id and tag_name
TAGS_X - includes fields: tag_id, question_id
The query below almost works, but the subquery seems to return the total count of times the tag is used and the filtering on the suspended and offline fields is not functioning as I intended (does not seem to filter on those conditions).
SELECT tags_x.tag_id, tags.tag_name, tags_x.question_id, questions.suspended, questions.offline,
(select count(tags_x.tag_id) from tags_x WHERE tags_x.tag_id=tags.tag_id AND questions.suspended = 0 AND questions.offline = 0) num
from tags_x
LEFT JOIN tags ON tags.tag_id = tags_x.tag_id
LEFT JOIN questions ON questions.question_id = tags_x.question_id
WHERE questions.suspended = 0 AND questions.offline = 0

Below shows a typical result.  Tag 'a' actually shows up in 21 rows.  One row is filtered where the offline value is 1.  The subquery count is returning 22, but I really want it to show the filtered result of 21.  Seems like the WHERE/(offline, suspended) filters in the subquery are incorrectly applied.
Can you help me out to determine the correct way to do this?

Seems like I had to use a larger subquery and join it with the main query. Works fine this way.  Not sure why it did not work the above way though...
SELECT tags_x.tag_id, tags.tag_name, questions.question_id,X.num
FROM tags_x
LEFT JOIN tags ON tags.tag_id = tags_x.tag_id
LEFT JOIN questions ON questions.question_id = tags_x.question_id
LEFT JOIN
(SELECT tags_x.tag_id, count(tags_x.tagsx_id) AS num
FROM tags_x
LEFT JOIN questions ON tags_x.question_id = questions.question_id
LEFT JOIN tags ON tags.tag_id = tags_x.tag_id
WHERE questions.suspended =0 AND questions.offline = 0 AND tags.tag_name IS NOT NULL
GROUP BY tags.tag_id) AS X ON X.tag_id=tags.tag_id
WHERE questions.question IS NOT NULL
ORDER BY `questions`.`question_id` ASC


Does PostgreSQL Explain Analyze Execution Time consider Fetching time I have some tests on GitHub which record how much time it takes to execute a query and fetch all results.

If I check the execution plan, I get:

Index Scan using post_comment_pkey on post_comment postcommen0_  (cost=0.29..2716.48 rows=34752 width=43) (actual time=6.767..14.352 rows=34880 loops=1)
  Filter: (post_id = 1)
  Rows Removed by Filter: 34880
Planning time: 0.659 ms
Execution time: 14.840 ms

So, the query is using an index and executing in 15 ms.

However, on the Java side, after the ResultSet is fetched and processed, the time measurement indicates that it took 90 ms.

The only explanation would be that the Explain Analyze doe snot consider the fetch time and fetching 34880 records takes 50-60 ms.

Is this correct?

Fetching performance is dependant of the client performance, available memory, cpu usage, network performance... I don't see how EXPLAIN ANALYZE could take this into account. So I would say your correct.


Transfer of large database from 1 server to another I am in the process of setting up a test environment on a server in a different location to the LIVE server.

I need to make a copy of the SQL Server database and move it across.

I want to know, if I were to Zip the Data and Log files and then split the zip file into smaller files for faster FTP. Would this damage or alter the data alignment in any way?

Before you bring ZIP into the mix, be advised that Sql Server natively supports backing up a database to multiple files typically resulting in smaller files and faster backup time.

Check out Backup to multiple files for faster and smaller SQL Server files by Greg Robidoux.

Summarizing Greg's post:

Problem

  Have you ever wished you could get your backups to run faster? Well
  there may be a way, by writing to multiple files. Creating SQL Server
  backups is pretty simple to do; you can use the SQL Server management
  tools or you can use T-SQL commands to issue the backup. But sometimes
  with large databases it takes a long time to run and it is difficult
  to copy this very large file across the network or even to your backup
  tapes.

Solution

  Write your database backup to multiple files. In addition to writing
  your database backup to one file you have the ability to write to
  multiple files at the same time and therefore split up the workload.
  The advantage to doing this is that the backup process can run using
  multiple threads and therefore finish faster as well as having much
  smaller files that can be moved across the network or copied to a CD
  or DVD. Another advantage to writing to multiple files is if you have
  multiple disk arrays you can write your backup files to different
  arrays and therefore get better I/O throughput. One thing to note is
  that to maintain the I/O throughput you should keep the writing of
  your backup files and the reading of the database file on different
  disk arrays.

Backup to multiple files using T-SQL

BACKUP DATABASE [Northwind] TO
DISK = 'C:\Northwind_file1.bak',
DISK = 'D:\Northwind_file2.bak',
DISK = 'E:\Northwind_file3.bak',
DISK = 'F:\Northwind_file4.bak'
WITH INIT , NOUNLOAD , NAME = 'Northwind backup', NOSKIP , STATS = 10, NOFORMAT,COMPRESSION

On another note:

You should SERIOUSLY consider taking this backup with COPY_ONLY to make sure you don't mess up your production differential base.