Should I disable "auto update statistics" in a data warehousing scenario? I have 200 GB data warehouse in SQL Server.

I have been experiencing really slow execution times for some queries; for example 12 hours for a simple delete query with an inner join.

After doing some research with the execution plans, I've updated the statistics of the 2 tables involved in the query, using the WITH FULLSCAN option.

The query now executes in less than a second, so it appears the statistics were not up to date.

I'm considering disabling auto update statistics on the database and running UPDATE STATISTICS manually after the data warehouse is loaded. The data warehouse is loaded incrementally from a source ERP system daily, at night.

Am I correct in assuming auto update statistics in a data warehousing scenarios is not really useful?  Instead, does it make more sense to update the statistics manually after the data is loaded?

Here is a whitepaper on when auto_update of stats occurs. Here are the salient points vis-a-vis automatic updates to statistics:

  The table size has gone from 0 to >0 rows (test 1).
  The number of rows in the table when the statistics were gathered was 500 or less, and the colmodctr of the leading column of the statistics object has changed by more than 500 since then (test 2).
  The table had more than 500 rows when the statistics were gathered, and the colmodctr of the leading column of the statistics object has changed by more than 500 + 20% of the number of rows in the table when the statistics were gathered (test 3).

So @JNK made the point in a comment that if you have 1 billion rows in a table, you'd need to have 20,000,5000 writes to the first column in the statistic to trigger an update.

Let's take the following structure:

CREATE TABLE dbo.test_table (
    test_table_id INTEGER IDENTITY(1,1) NOT NULL,
    test_table_value VARCHAR(50),
    test_table_value2 BIGINT,
    test_table_value3 NUMERIC(10,2)
);

CREATE CLUSTERED INDEX cix_test_table ON dbo.test_table (test_table_id, test_table_value);

Now we can check to see what happened in statistics land.

select *
    from sys.stats
        where object_id = OBJECT_ID('dbo.test_table')

However, to see if this is a meaningful statistic object we need to:

dbcc show_statistics('dbo.test_table',cix_test_table)

So this statistic hasn't been updated. That's because it looks like the statistic isn't updated until a SELECT occurs and even then the SELECT has to fall outside of what SQL Server has within its histogram. Here's a test script that I ran to test this:

    CREATE TABLE test_table (
        test_table_id INTEGER IDENTITY(1,1) NOT NULL,
        test_table_value VARCHAR(50),
        test_table_value2 BIGINT,
        test_table_value3 NUMERIC(10,2)
    );

    CREATE CLUSTERED INDEX cix_test_table ON test_table (test_table_id, test_table_value);

    ALTER TABLE test_table ADD CONSTRAINT pk_test_table PRIMARY KEY  (test_table_id)

    SELECT *
        FROM sys.stats
            WHERE object_id = OBJECT_ID('dbo.test_table')

    --DBCC SHOW_STATISTICS('dbo.test_table',pk_test_table)
    DBCC SHOW_STATISTICS('dbo.test_table',cix_test_table) WITH STAT_HEADER;

declare @test int = 0

WHILE @test < 1
    BEGIN
        INSERT INTO test_table (test_table_value,test_table_value2,test_table_value3) VALUES
            ('stats test' + CAST(@test AS VARCHAR(10)),@test, @test)
        SET @test = @test + 1;
    END

SELECT 'one row|select < 1', * FROM test_table WHERE test_table_id < 1;
--DBCC SHOW_STATISTICS('dbo.test_table',pk_test_table);
DBCC SHOW_STATISTICS('dbo.test_table',cix_test_table) WITH STAT_HEADER;

SET @test = 1

WHILE @test < 500
    BEGIN
        INSERT INTO test_table (test_table_value,test_table_value2,test_table_value3) VALUES
            ('stats test' + CAST(@test AS VARCHAR(10)),@test, @test)
        SET @test = @test + 1;
    END

SELECT '100 rows(add 99)|select < 100',* FROM test_table WHERE test_table_id < 100;
--DBCC SHOW_STATISTICS('dbo.test_table',pk_test_table);
DBCC SHOW_STATISTICS('dbo.test_table',cix_test_table) WITH STAT_HEADER;
--get the table up to 500 rows/changes
WHILE @test < 500
    BEGIN
        INSERT INTO test_table (test_table_value,test_table_value2,test_table_value3) VALUES
            ('stats test' + CAST(@test AS VARCHAR(10)),@test, @test)
        SET @test = @test + 1;
    END
SELECT '500 rows(add 400)|select < 100',* FROM test_table WHERE test_table_id < 100;
DBCC SHOW_STATISTICS('dbo.test_table',cix_test_table) WITH STAT_HEADER;
SELECT '500 rows(add 400)|select < 500',* FROM test_table WHERE test_table_id < 500;
--DBCC SHOW_STATISTICS('dbo.test_table',pk_test_table);
DBCC SHOW_STATISTICS('dbo.test_table',cix_test_table) WITH STAT_HEADER;
--bump it to 501
SET @test = 500;
WHILE @test < 501
    BEGIN
        INSERT INTO test_table (test_table_value,test_table_value2,test_table_value3) VALUES
            ('stats test' + CAST(@test AS VARCHAR(10)),@test, @test)
        SET @test = @test + 1;
    END

SELECT '501 rows(add 1)|select < 501',* FROM test_table WHERE test_table_id < 501;
--DBCC SHOW_STATISTICS('dbo.test_table',pk_test_table);
DBCC SHOW_STATISTICS('dbo.test_table',cix_test_table) WITH STAT_HEADER;

--bump it to 600
SET @test = 501;
WHILE @test < 600
    BEGIN
        INSERT INTO test_table (test_table_value,test_table_value2,test_table_value3) VALUES
            ('stats test' + CAST(@test AS VARCHAR(10)),@test, @test)
        SET @test = @test + 1;
    END

SELECT '600 rows (add 100)|select < 600',* FROM test_table WHERE test_table_id < 600;
--DBCC SHOW_STATISTICS('dbo.test_table',pk_test_table);
DBCC SHOW_STATISTICS('dbo.test_table',cix_test_table) WITH STAT_HEADER;

--bump it to 700
SET @test = 600;
WHILE @test < 700
    BEGIN
        INSERT INTO test_table (test_table_value,test_table_value2,test_table_value3) VALUES
            ('stats test' + CAST(@test AS VARCHAR(10)),@test, @test)
        SET @test = @test + 1;
    END

SELECT '700 rows (add 100)|select < 700', * FROM test_table WHERE test_table_id < 700;
--DBCC SHOW_STATISTICS('dbo.test_table',pk_test_table);
DBCC SHOW_STATISTICS('dbo.test_table',cix_test_table) WITH STAT_HEADER;

--bump it to 1200
SET @test = 700;
WHILE @test < 1200
    BEGIN
        INSERT INTO test_table (test_table_value,test_table_value2,test_table_value3) VALUES
            ('stats test' + CAST(@test AS VARCHAR(10)),@test, @test)
        SET @test = @test + 1;
    END

SELECT '1200 rows (add 500)|select < 1200',* FROM test_table WHERE test_table_id < 1200;
--DBCC SHOW_STATISTICS('dbo.test_table',pk_test_table);
DBCC SHOW_STATISTICS('dbo.test_table',cix_test_table) WITH STAT_HEADER;
--DROP TABLE test_table

Instead of blindly disabling auto_update statistics, I would try to examine your data set for skew. If your data exhibits significant skew, then you need to consider creating filtered statistics and then decide if managing statistics updates manually is the correct course of action.

To analyze for skew you need to run DBCC SHOW_STATISTICS(<stat_object>, <index_name>); (in the above script without the WITH STAT_HEADER) on the particular stat/index combination that you'd like to examine. A quick way to eyeball your skew would be to look at the histogram (third result set) and check out the variance in your EQ_ROWS. If it's fairly consistent then your skew is minimal. To step it up, you look at the RANGE_ROWS column and look at the variance there since this measures how many rows exist between each step. Finally, you can take the [All density] result from the DENSITY_VECTOR (second result set) and multiply that by the [Rows Sampled] value in the STAT_HEADER (first result set) and see what the average expectation would be for a query on that column would be. You compare that average to your EQ_ROWS and if there are many places where it varies significantly, then you've got skew.

If you find that you do have skew, then you need to consider creating some filtered statistics on the ranges that have high very high RANGE_ROWS so that you can give an additional steps for better estimates on those values.

Once you have these filtered statistics in place, then you can look at the possibility of manually updating statistics.


Structuring relational database tables I'm really interested in knowing what the best way to structure relational database tables in the following scenario is.

Let's say the table is recording cryptocurrency prices. There are four important aspects:

1. An ID
2. The date and time
3. What cryptocurrency it is
4. The price

I'm thinking of two possible ways to structure the resulting table, either:

ID    Date                  Crypto        Price
1     2018-01-01 12:00:00   BTC           100000
2     2018-01-01 12:00:00   ETH           1234

Or

ID    Date                  BTC_Price    ETH_Price....
1     2018-01-01 12:00:00   1000000      1234

Is there a "best practice" to structure these kind of tables? One consideration that I have is I might want to add more types of currencies in this case. I can do this with the ALTER command, but in that context the first layout makes more logical sense to me.

What are your thoughts?

Your first example is considerably better than your second.  It is a standard rule to not use repeating groups of fields and to break fields down to their smallest meaningful value.  BTC_Price and ETH_Price are not broken down to their smallest meaningful value; price and crypto.  In the first example you separated the crypto from price.  This is the correct thing to do.  Joe is also correct in that you should have another table to hold the different kinds of cryptocurrencies with a auto incrementing primary key.  This is often referred to as a lookup table.

Have a look at database normalization and learn about what are called "normal forms".  Your first table conforms with what is called "3rd normal form" and your second table conforms with I believe "2nd normal form".  Typically the higher the normal form the better but 3rd normal form is considered the standard that you should attempt to achieve at minimum.


MySQL High CPU Usage (100% in peek times) I have a simple LAMP server on Amazon EC2. (find the specs towards the end of this message.) I used the following command in the terminal to see status of each query

watch --interval=2 "mysqladmin -u{username} -p{password} processlist"

I see roughly 4-5 queries running every now and then that result in 5-100 rows on an average (they're not very costly queries). In another console, I bring up the task manager using the "top" command. In it, I see mysqld using 150%+ CPU on an average time where the overall CPU usage is 80-90%. In the peak times, the overall CPU goes up to 100%. I've also realized the update takes a long timer sometimes. This server only uses MySQL.

The select queries that are running frequently are pulling on indexes. It's very strange to see the MySQL hogging up to more than 3/4th of the CPU to run just a few queries. I think I just don't have the correct configuration or need to fine tune MySQL configuration for a better performance. Anyone have any idea why this may be happening?

Server Specs:

Ubuntu 12.04
Intel Xeon E5-2670 v2 (Ivy Bridge) Processors
7.5 GB RAM
SSD

MySQL Specs: (Ver 14.14 Distrib 5.5.37, for debian-linux-gnu (x86_64) using readline 6.2)

max_allowed_packet 64M
innodb_read_io_threads 4
innodb_write_io_threads 4

After a few modification:
I modified the queries to ensure the where clause is using all index and it's using the correct type. In the execution plan or EXPLAIN statement, I see the following

Select_type: simple
type: index_merge
possible_keys: idx_tickets_city_code,index3,index6
key: index5,idx_tickets_city_code
key_len: 4,4
rows:1612
extra: Using intersect(index3,idx_tickets_city_code);

Total result count is 142 rows and the CPU usage is still ~60% (a little high) for few instances of this query running every few seconds.

Consider using the Percona configuration tool for MySQL here https://tools.percona.com/

They also have a query advisor tool.

Both require registration in order to use them.


count duplicate records and select first 2 I have troubling issue where I want to only select the first 2 chunks of every dbspace created.
query used:
SELECT
a.dbsnum,
b.chknum,
a.name,
b.fname
FROM
sysdbspaces a, syschunks b where a.dbsnum = b.dbsnum
GROUP BY
1,2,3,4
ORDER BY 1,2

Output:
dbsnum  1
chknum  1
name    rootdbs
fname   /dev/informix/rootdbs01

dbsnum  1
chknum  344
name    rootdbs
fname   /dev/informix/chunk1081

dbsnum  1
chknum  965
name    rootdbs
fname   /dev/informix/chunk1280

dbsnum  1
chknum  1099
name    rootdbs
fname   /dev/informix/chunk1281

What I want is:
dbsnum  1
chknum  1
name    rootdbs
fname   /dev/informix/rootdbs01

dbsnum  1
chknum  344
name    rootdbs
fname   /dev/informix/chunk1081

I am trying to put logic together to select the first 2 chunks created for example from each dbspace that exists. rootdbs has a total of 4 chunks I only want the first 2.
I can achieve this using a korn shell script:
for i in `echo "output to pipe cat without headings select unique(dbsnum) from sysdbspaces" | dbaccess sysmaster 2> /dev/null | sed '/^$/d'`
do
fname=`echo "output to pipe cat without headings select a.fname from (select
first 2
a.dbsnum,
b.chknum,
a.name,
b.fname
FROM
sysdbspaces a, syschunks b where a.dbsnum = b.dbsnum
AND a.dbsnum = '$i'
--AND a.is_temp = 1
GROUP BY
1,2,3,4
ORDER BY 1,2) a" | dbaccess sysmaster 2> /dev/null | sed '/^$/d'`
echo "$fname"
done

select * from (SELECT
    row_number() over (partition by 1,2,3,4 order by 1,2) as RowNo,
    a.dbsnum,
    b.chknum,
    a.name,
    b.fname
    FROM
    sysdbspaces a, syschunks b where a.dbsnum = b.dbsnum
    GROUP BY
    1,2,3,4) as innerSelect
where RowNo <=2


Would SQL Server in a Cluster failover if it is running out of RAM I have a SQL Server 2008 R2 in a Cluster in an active-passive mode.
The server is running out of RAM (both physical and pagefile).
In the instance level settings there is not Max RAM set (set to the maximum number).

Would the instance failover?

Starting with SQL Server 2012 the cluster will have a more comprehensive health check which allows for a possible resource based health condition using the SP_Server_diagnostics procedure. https://msdn.microsoft.com/en-us/library/ff878233.aspx

More on that failover policy can be read about here: https://msdn.microsoft.com/en-us/library/ff878664.aspx#monitor

Unfortunately though SQL Server 2008R2 and earlier doesn't have much of a sophisticated cluster health check for the SQL Service. Simply the health check will just issue the following command "SELECT @@Servername" and if it returns in a timely fashion the health check passes.

I would look instead though at why is the server under memory pressure and setting the max memory setting in the SQL Server instance putting a cap on that memory foot print.


Unique identifiers for the same type of object in the same request This question is about your opinion and common approach for relational databases. So please, do not vote me down. I really need to solve this problem.. I remove this question once I meet the answer.

I have a response from my server sth like this:

"groups":[
              {
                 "id":"ea369ff6-cbc3-44b6-8eb6-b79e8db0145c",
                 "ingredients":[
                    {
                       "id":"f5126c1f-11a7-5ce5-a717-c6c86483058e",
                       "name":"First",
                       "position":1
                    },
                    {
                       "id":"dfcf217e-53dd-b06c-9f2b-084f8180ba1a",
                       "name":"Second",
                       "position":2
                    }
                 ]
              },
              {
                 "id":"0fa91ada-1fe4-4d6d-b772-caae44758848",
                 "position":2,
                 "ingredients":[
                    {
                       "id":"9cacade2-92fe-bd5c-c84c-7c5fe36c8a5e",
                       "name":"Third",
                       "position":1
                    },
                    {
                       "id":"f5126c1f-11a7-5ce5-a717-c6c86483058e",
                       "name":"First",
                       "position":2
                    }
                 ]
              }
           ]

Here are two groups. Each of them has unique id.
Every group has some ingredients. They are the same type
Every group has ingredient with name First. This ingredient has the same id in every group, which means, it is the same object.

but

First ingredient has different values of position depending on where it is located.

The question is:

Should they have the same id while they are not the same? What is good approach for this problem? I need simple, quick and clarified answer.

Doesnt id mean that this is unique along with the same type of object? What If I need to save it to core data or other persistent store on device?

Very thanks.

Normalizing this will result in three tables with an many:many relation, the columns in parens () indicate the logical Primary Key:

groups: (group_id)

ingredients: (ingredient_id), name

ingredients_per_group: (group_id, ingredient_id), position

This is the junction table between groups and ingredients, the position is just an attribute of the ingredient in this specific group.

If an ingredient could be used many times in the same recipe but in different positions, (say use some butter in step 2 but also in step 17), then the PK of ingredients_per_group should be (group_id, ingredient_id, position).

It's like an ingredient for a receipt, the same ingredient can/will be used in multiple receipts, but in different amounts or in a different order.


building a view w/ fields that only contain a certain word Trying to build a simple SQL query... I have a table filled with "Yes" and "No"

I want a view that only shows me records that contain "No"

SELECT PhysicalBuild.Name AS Name,
  PhysicalBuild.Labels AS Labels,
  PhysicalBuild.DNSDRAC AS DNSDRAC,
  PhysicalBuild.DNSHOST AS DNSHOST,
  PhysicalBuild.PRTGDRAC AS PRTGDRAC,
  PhysicalBuild.PRTGHOST AS PRTGHOST,
  PhysicalBuild.LouKVM AS LouKVM,
  PhysicalBuild.SwitchPorts AS SwitchPorts,
  PhysicalBuild.FiberSwitch AS FiberSwitch
FROM PhysicalBuild

But I want it to be WHERE * = "No" or something to that effect.... my syntax is incorrect. anyone?

EDIT: I came up w/ this. Is there a cleaner/easier way to handle this? How about a way to change the records that contain "Yes" into NULL so they aren't displayed? So only the No's are shown?

SELECT PhysicalBuild.Name AS Name,
  PhysicalBuild.Labels AS Labels,
  PhysicalBuild.DNSDRAC AS DNSDRAC,
  PhysicalBuild.DNSHOST AS DNSHOST,
  PhysicalBuild.PRTGDRAC AS PRTGDRAC,
  PhysicalBuild.PRTGHOST AS PRTGHOST,
  PhysicalBuild.LouKVM AS LouKVM,
  PhysicalBuild.SwitchPorts AS SwitchPorts,
  PhysicalBuild.FiberSwitch AS FiberSwitch
FROM PhysicalBuild
WHERE (PhysicalBuild.Labels = 'No') OR
  (PhysicalBuild.DNSDRAC = 'No') OR
  (PhysicalBuild.DNSHOST = 'No') OR
  (PhysicalBuild.PRTGDRAC = 'No') OR
  (PhysicalBuild.PRTGHOST = 'No') OR
  (PhysicalBuild.LouKVM = 'No') OR
  (PhysicalBuild.SwitchPorts = 'No') OR
  (PhysicalBuild.FiberSwitch = 'No')

What you have is the simplest and most straightforward.

your WHERE will require every column mentioned somewhere
changing YES into NULL requires a function on each column

For some RDBMS you can create computed column at the table level.

ALTER TABLE PhysicalBuild
   ADD SummaryState AS
     CASE WHEN Labels = 'No' AND
        DNSDRAC = 'No' AND
        DNSHOST = 'No' AND
        PRTGDRAC = 'No' AND
        PRTGHOST = 'No' AND
        LouKVM = 'No' AND
        SwitchPorts = 'No' AND
        FiberSwitch = 'No'
     THEN 'No' ELSE 'Yes' END

Then your query is

...
FROM PhysicalBuild
WHERE SummaryState = 'No'

Note: you don't need aliases the same as the column name...


MySQL Monitoring Tools I want to deploy tools to monitor my Production Percona servers (5.6 on Linux). I never used Nagios or any tool before. I did go through this great link
but I wanted to clarify few things before deploying them:

Those mysql exporter and node exporters of Prometheus which I need to deploy in my database host - what would be overhead of them? Resource intensive?
I did go through Nagios as well - Is Nagios better than Grafana and is it possible to use Grafana with Nagios?

Any better suggestions would really help me as well.

The 'mysql' & 'node' exporters are lightweight processes and shouldn't impact your servers if you're running with default settings. Be careful when enabling the additional stats which use information_schema - these can impact your DB servers (especially when you have a lot of databases / tables)
Nagios is chiefly used for 'Alerting' while Grafana on the other hand is chiefly used for 'Trending'. The alerts that Nagios provides are based on specific thresholds (warning / critical). It is possible to use Prometheus' AlertManager (which collects data for Grafana) in order to receive alerts on specific metrics. With Prometheus you can leverage the time series database to generate more intelligent alerts which look at the rate of change rather than a specific threshold - see https://prometheus.io/docs/alerting/alertmanager/ for more details on how to set this up.


remove old SSRS Schedule jobs We have a application that creates SSRS Schedules 2 times a year and it does create one for each of our customers.

Each schedulation results in a SQL Agent job with a GUID as name and the description This job is owned by a report server process. Modifying this job could result in database incompatibilities. Use Report Manager or Management Studio to update this job..

The schedulations just run one time and then just collects dust.

Is there any way to remove all old jobs and information about the schedulations in the SSRS database?

I found a solution to this problem.
Create the following files:

RemoveSubscriptions.bat:

@echo OFF
SET Server=http://localhost/ReportServer_MSSQL_2008
SET ReportUrl=/test/reports/testreport
"C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\rs.exe" -i RemoveSubscriptions.rss -s %Server% -e Mgmt2010 -v reportUrl=%ReportUrl% > %date%.log

RemoveSubscriptions.rss:

Public Sub Main()
    rs.Credentials = System.Net.CredentialCache.DefaultCredentials
    Dim subscriptions as Subscription() = rs.ListSubscriptions(reportUrl)
    For Each item As Subscription in subscriptions
        'Delete all subscriptions that are older then one month
        If (item.LastExecutedSpecified AndAlso item.LastExecuted.AddMonths(1) < Date.Now) Then
            Console.WriteLine("Removing subscription with id {0} from {1}", item.SubscriptionID, reportUrl)
            rs.DeleteSubscription(item.SubscriptionID)
            Console.WriteLine("{0}, {1}, {2}, {3}", item.SubscriptionID, item.Active, item.LastExecuted, item.LastExecutedSpecified)
        End If
    Next
End Sub

Now if you change the variables in the bat file and run the bat file it should remove all subscriptions that are older then 1 month this includes the actual sql job.

Warning: This will remove jobs that run more then once so if you have a report scheduled to run once a year it will be lost!


How to check the syntax of pg_hba.conf and other postgresql conf files on ubuntu/debian/linux? Many times, the postgresql service can't restart after some configuration changes.

Is there any command line tool allowing to check the syntax of pg_hba.conf and other pg *.conf files before reloading/restarting the service, or even better, after any config change ?

You can run:

postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=#

.. from within psql. or kill -HUP the postmaster process.

Any config validation errors will then get put into the Postgres log file, and it won't reload the config. Example log:

2016-10-05 10:31:57 BST LOG:  received SIGHUP, reloading configuration files
2016-10-05 10:31:57 BST LOG:  invalid connection type "awdawdawdawd"
2016-10-05 10:31:57 BST CONTEXT:  line 1 of configuration file "/etc/postgresql/9.3/main/pg_hba.conf"
2016-10-05 10:31:57 BST WARNING:  pg_hba.conf not reloaded

I'm not aware of a tool that just checks config for validity, but there was a thread on the pgsql-hackers mailing list here that discussed a proposal for a validator.


generating all substrings (n-grams) for bit-strings I followed SELECT all substrings (n-grams) of length n? to get a function for generating all n-grams for text types.  Works great.  I figured out how to cast my bit-strings to text, and the function from that link works.
However I need it to be as fast as possible, thus I wanted a bit-string native implementation, and I came up with this:

CREATE OR REPLACE FUNCTION public.ngrams(in input bit varying, in len integer)
 RETURNS SETOF bit
 LANGUAGE sql
AS $function$
SELECT cast((input << alpha) as bit(4))
FROM GENERATE_SERIES(0, LENGTH($1)-($2+1), 1) alpha;
$function$

This works, for N=4.  However when I tried to change it to $2 or len I would get:

ERROR:  22P02: invalid input syntax for type integer: "len"
LINE 5: SELECT cast((input << alpha) as bit(len))
                                        ^

How come I can use the named parameter from the function declaration for input but not for len? Or better yet, how can I feed a variable into that spot?

You can solve the error by using the bit varying datatype. That being said, I don't actually know if this is the behavior you're looking for - it looks weird to me and doesn't match what the text version does.

testdb=# CREATE OR REPLACE FUNCTION public.ngrams(in input bit varying, in len integer)
 RETURNS SETOF bit
 LANGUAGE sql
AS $function$
SELECT cast((input << alpha) as bit varying)
FROM GENERATE_SERIES(0, LENGTH($1)-($2+1), 1) alpha;
$function$
;
CREATE FUNCTION
testdb=# CREATE OR REPLACE FUNCTION ngrams(varchar, integer) RETURNS SETOF TEXT AS $$
SELECT SUBSTRING($1 FROM n FOR $2)::TEXT
FROM GENERATE_SERIES(1, LENGTH($1)-($2-1), 1) n;
$$ LANGUAGE SQL;
CREATE FUNCTION
testdb=# with inputs as (select '10111' as x, c as y from generate_series(2, 3) c)
select x, y, ngrams(cast(x as text), y) ngrams_txt from inputs;
   x   | y | ngrams_txt
-------+---+------------
 10111 | 2 | 10
 10111 | 2 | 01
 10111 | 2 | 11
 10111 | 2 | 11
 10111 | 3 | 101
 10111 | 3 | 011
 10111 | 3 | 111
(7 rows)

testdb=# with inputs as (select '10111' as x, c as y from generate_series(2, 3) c)
select x, y, ngrams(cast(x as bit varying), y) ngrams_bit from inputs;
   x   | y | ngrams_bit
-------+---+------------
 10111 | 2 | 10111
 10111 | 2 | 01110
 10111 | 2 | 11100
 10111 | 3 | 10111
 10111 | 3 | 01110
(5 rows)


suddedenly increased in size of Log files form 32GB to 70 GB I am not DBA ,but I need to check on the size of database files .
my database log files earlier was around 32 GB but from last few days it starts increasing and finally reached to 70 GB . first I thought it was natural , but soon I realized  that I was wrong  .

For shrinking the size of log file ,firstly I used to determine the % of space occupied in log file  by using .

        DBCC SQLPERF(logspace)

Result was :- log size(MB) > 70 and Log Space Used > 94%
 with

          DBCC LOGINFO

I found more than 700 transactions having Status = '2'

After that I took "log as well as full backup"
and run above query again but still got >700  transactions having Status = '2'
detached and reattached , but still no difference in size .

Then I went for Shrinking log files and for testing purpose I gave the size 60GB .
 Then run following query again

   DBCC SQLPERF(logspace)

and result was :- log size(MB) > 66 and Log Space Used =  99.1258%.

Question is : How can I bring the size of log files near to earlier size i.e. around 32 GB or near by and how to keep check on it so that its size does not change dramatically .

** Recovery Mode :- FULL

Thanks in advance ....

A full database backup won't help your log size.

Full Recovery Model means "Can restore to any point in time." - including before that new database backup was taken.

Simple Recovery Model means "Can restore to the last database backup".

So... have a think about whether you need to restore to this morning or not (before your database backup). If you don't, then put your database into Simple Recovery Model, then back into Full. Then take a differential backup. Once you've done those three steps you will be able to recover to your last full database backup, the differential backup you just took, and any point in the future. Take regular log backups - try every ten-to-fifteen minutes.

And then look at your log. You should find it's mostly empty.

If not, look in sys.databases to see why it's not truncating. Other possible reasons include Replication that's broken, for example.


Optimizing mysql queries running on 50 million rows Is there any technique/advice in order to optimize the performance of the queries below?

According to my needs, my average db size will be approximately 30mill. rows per day, so every second less, will make huge difference.

My DB engine is innoDB and I'm using 1 core CPU, with 2GB RAM.

Exec Time : 7,5 sec (5,5 mil. rows)

SELECT vpn_group, username, from_interface_addr_ip
FROM SystemEventsR
WHERE (timestamp > ( NOW( ) - INTERVAL 10 MINUTE ) AND
SysLogTagflag=1 AND
username !='')
GROUP BY username

Exec Time : 88,4 sec (5,7 mil. rows)

SELECT syslogtagid, DeviceReportedTime, class, definition, SystemEventsR.SysLogTag,
COUNT(id) AS Records,
explanation, action
FROM SystemEventsR,oa_syslogtags
WHERE  (SystemEventsR.SysLogTag = oa_syslogtags.syslogtag AND
flagid = 1 AND DATE(timestamp) = DATE(NOW()))
GROUP BY SystemEventsR.SysLogTag
ORDER BY Records DESC

Any help would be appreciated.

There are useful tips in the mysql online documentation:

http://dev.mysql.com/doc/refman/5.7/en/optimization.html

It's worth reading about indexing, like James Green has suggested.

Also you may wish to use the keyword "EXPLAIN" to look at the query plan to find potential bottlenecks. This will highlight which indexes are being used in your query. To make sure you're query plan is correct you will need to keep updating index statistics on your database.

Hope this helps and let me know if you find anything!

Many Thanks,

Simon.


Variable value is not incrementing Why is the country_rank value 1 for each row, which I assume is due to STRCMP returing false everytime. I think @current_country is not set correctly but dont know why.

Table:

sn country city
1  AAA     aaa
2  BBB     bbb
3  AAA     ccc

Output Expected:

country city country_rank
AAA     ccc  1
AAA     aaa  2
BBB     bbb  1

Query:

SELECT country,city,
@country_rank := IF(STRCMP(country,@current_country) = 0,  @country_rank + 1, 1) AS country_rank,
@current_country := country
FROM table
ORDER BY country,sn  DESC

The query is supposed to rank the cities in each country as per sn.
MYSQL 5.6

SELECT country,city,
       @country_rank :=
          IF(STRCMP(country,@current_country) = 0,  @country_rank + 1, 1)
          AS country_rank,
       @current_country := country  setctry
  FROM  cities,
        (select @current_country="", @country_rank=0) init
  ORDER BY country,sn  DESC

cfr. SQL Fiddle http://sqlfiddle.com/#!9/c388ef7/2


count(id) on Char(0) column does not work! Possible Duplicate:
  What is the difference between select count(*) and select count(any_non_null_column)?

I have a column with the type of char(0).
some of the rows are NULL and some of them are empty('').
The question is that when I run the following query, the result will be 0.
SELECT count(id)
FROM test
WHERE id IS NULL

but when I run the following query, the result is correct:
SELECT count(*)
FROM test
WHERE id IS NULL

I have one column,why is that the case?

COUNT(somecolumn) ignores NULLs
COUNT(*) won't

You can see this with

SELECT count(id),count(*) FROM test WHERE id IS NULL

Coincidently, all aggregate functions ignore NULL except COUNT(*)


Azure SQL Database "Login failed for user" contained users. I'm having a similar issue to this question (https://dba.stackexchange.com/posts/123094/edit)! I have followed the following article: https://azure.microsoft.com/en-gb/documentation/articles/sql-database-manage-logins/ and just cannot get it to fully work.

-- first, connect to the master database

CREATE LOGIN login1 WITH password='password';

-- Establish a new connection to the database

CREATE USER login1user FROM LOGIN login1;

GRANT CONNECT TO login1user

EXEC sp_addrolemember 'db_datareader', 'login1user ';

EXEC sp_addrolemember 'db_datawriter', 'login1user ';

EXEC sp_addrolemember 'db_ddladmin', 'login1user ';

The issue is, I cannot connect to my database with login1user. I have to use the login1 details, which isn't really what I was expecting to happen. I would be happy having the names the same, except for bullet point three in the remarks section of this article (https://msdn.microsoft.com/library/ff929188.aspx), suggests that this isn't best practise.

Any help appreciated!

It looks like you mixed a few different steps from the Azure article. Many of these Azure articles contain lots of great information but the layout is borderline horrible. Topics just blend into each other making it confusing for most readers (DB firewall configuration is another commonly confused doc).

If you want to use contained DBs with contained users, your create user statement should be something like

CREATE USER MyContainedUser01 WITH password='MyL@m3P@$$0rd';

as described in https://azure.microsoft.com/en-gb/documentation/articles/sql-database-manage-logins/#managing-contained-database-users

When you create a user mapped to a login as per your sample script, that's a regular, non-contained user. With that, you need to connect using the login which then maps to the user.


How to sort strings with special characters in Hibernate? For example, if the Order in criteria is ascending and if we have two names like follows:

_zname_9
1_aname_

I get an output (from hibernate result) like:

_zname_9
1_aname_

But the expected output (java sorting) is:

1_aname_
_zname_9

Am I missing something? Please note that I dont want to escape special characters while setting the criteria.

Can we mention regarding special characters while sorting?

I'm not an expert when it comes to collations but the following does provide the sorting your require. It may cause other characters to be sorted unexpectedly so please test.

WITH cte AS
(
    SELECT  '_zname_9' AS SortVal
    UNION ALL
    SELECT  '1_aname_' AS SortVal
)

SELECT  *

FROM    cte

--ORDER BY  SortVal
ORDER BY SortVal COLLATE SQL_Latin1_General_Cp437_BIN2

I've used a BIN2 collation to cover that fact that your name column may be a  unicode data type.


Is there a postgres function documentation pattern that is similar in concept to javadoc? I am mostly a Java developer. Today I am writing some postgres functions. Out of habit I keep trying to document these functions using a Javadoc-like comment style describing the function parameters and return type. Is there an official or widely supported pattern for documenting postgres functions?

Not really, however in Postgres and many other DBMS vendors you can comment on various database objects like:

COMMENT ON FUNCTION my_function (timestamp) IS 'Returns Roman Numeral';

As far as I can tell there is no way you can comment on individual arguments for a function.

For the presentation of the documentation, you may want to have a look at:

http://schemaspy.org/


Do we have logging feature for SQL AUDIT like we have it for transaction and error logs in SQL server 2008? Do we have logging feature for SQL AUDIT like we have it for transaction and error logs in SQL server 2008 ????

There's two forms to audit a SQL Server

The SQL Profile:

https://msdn.microsoft.com/en-us/library/ff650699.aspx

And DDL Trigger's

https://msdn.microsoft.com/en-us/library/ms189799.aspx


How to enhance the SQL query performance for thousands and millions of huge data Now Our team are involved in a forum data analysis project. Its target data is so huge(I think). We should find the potential informations from it. Such as the popular products,the customers activity,also the complains of customers.

one case:(we try to analysis the customers' complain post)
we have the following tables:

Threads(ThreadID,TechnologyID,CreateTime,IsAnswer)20 millions of data

Posts(PostID,ThreadID,CreateTime,IsAnswer) 100 millions of data
--(multiply posts can set as Answer)

CustomerConcerns(ThreadID,PostID,NegativeWords,Verbatim)
--(if complained, there will be a record in it.)

 We also contain many mapping tables(millions).

(When thread was set as Answered but it still have customers involved in it and it recored in the CustomerConcerns table. It looked as one complain.)

So from the three tables, we need to find the complained threads， also how many compains after answered.

select t1.ThreadID,t1.CreateTime,MaxAnswerDate,MinAnswerDate,p1.MaxReplyDate,p1.Replies,p1.Users
from
(
    select  th.ThreadID,th.CreateTime,max(p.CreateTime) as MaxAnswerDate,min(p.CreateTime) as MinAnswerDate
    from Thread th,TopConcernTest tt,Post p
    where  tt.ThreadID=th.ThreadID and tt.[Type]=1 and th.LanguageID=58 and th.TechnologyID=''
        and th.IsAnswer=1
        and th.BigintTime>=20130101 and th.BigintTime<=20130501
        and p.ThreadID=th.ThreadID and p.IsAnswer=1
        group by th.ThreadID,th.CreateTime
    -- First I try to get the MaxAnswerDate and MinAnswerDate of the threads.
    -- Because The TopConcerTest is small.(compare with thread, post) So I joined with
    -- it.
)t1
cross apply
(
    select max(tp.CreateTime) as MaxReplyDate,Count(tp.PostID) as Replies,count(distinct tp.AuthorID) Users
        from
    (
        select p.CreateTime,p.IsAnswer,p.AuthorID,p.PostID from Post p
        where t1.threadID=p.ThreadID
    )tp
    where tp.IsAnswer<>1 and tp.CreateTime>MinAnswerDate
                )p1
    where p1.MaxReplyDate is not null
    -- I use a cross apply here. Because I think I have minimize the data before.
    -- And I try to get the MaxReplyDate and How many post and unique users
    -- after the first answered reply.
)t2
inner join
(
    select tt.ID as TopConcerID,th.ThreadID,th.ThreadTitle,th.ThreadUrl,p.PostID,p.AuthorID,p.CreateTime as PostCreateTime,tt.Entities,tt.NegativeWords,tt.Verbatim
    from Thread th,TopConcernTest tt,Post p
    where  tt.ThreadID=th.ThreadID and tt.[Type]=1 and th.LanguageID=58 and th.TechnologyID=''
        and th.BigintTime>=20130101 and th.BigintTime<=20130501
        and th.IsAnswer=1
        and p.ThreadID=th.ThreadID and tt.PostID=p.PostID
        )p2
    on t2.ThreadID=p2.ThreadID and p2.PostCreateTime>t2.MinAnswerDate
    -- I get the threadTitle,ThreadUrl and the Complain data here.
    -- You see I may get these data before instead to query them(ThreadTable,TopConcernTable) again. But I found I will spend much time to group them
    -- and order.
        )t3
where t3.ID between 1 and 20
-- Actually I have a page split at here. For I still have to query more infos.

This is my query. Sorry it's so complex. But My questions most of the time I can hardly to tun my code.

I want to minimize the range of my query data. So I use the TopConcernTest table at two place. But I also doubt whether I should scan the table twice?
You see I scan the Post table 3 times(It a very big table.), because I have to get the MaxAnsweredTime,MinAnsweredTime,MaxReplyTime. The three time are incompatible.
Whether I should scan 3 times?
I had try to get the data from TopConcernTest table one time. But it cost much time to group,or order the data.

Because our customers' query are mostly base on 3 variables:technology,language,time. I double whether I should set these three variables into every table, so I can minimize the range of data. base the results of subquery? (It will make too many duplicate data?

Possible this be helpful for you -

;WITH thread AS
(
    SELECT
          th.ThreadID
        , th.CreateTime
        , th.ThreadTitle
        , th.ThreadUrl
    FROM dbo.Thread th
    WHERE th.LanguageID = 58
        AND th.TechnologyID = ''
        AND th.BigintTime BETWEEN 20130101 AND 20130501
        AND th.IsAnswer = 1
)
SELECT
      t1.ThreadID
    , t1.CreateTime
    , t1.MaxAnswerDate
    , t1.MinAnswerDate
    , p1.MaxReplyDate
    , p1.Replies
    , p1.Users
FROM
(
    SELECT
          th.ThreadID
        , th.CreateTime
        , MaxAnswerDate = MAX(p.CreateTime)
        , MinAnswerDate = MIN(p.CreateTime)
    FROM thread th
    JOIN dbo.TopConcernTest tt ON tt.ThreadID = th.ThreadID
    JOIN dbo.Post p ON p.ThreadID = th.ThreadID
    WHERE tt.[Type] = 1
        AND p.IsAnswer = 1
    GROUP BY
          th.ThreadID
        , th.CreateTime
)t1
CROSS APPLY (
    SELECT
          MaxReplyDate = MAX(p.CreateTime)
        , Replies = COUNT(p.PostID)
        , Users = COUNT(DISTINCT p.AuthorID)
    FROM dbo.Post p
    WHERE p.IsAnswer != 1
        AND t1.ThreadID = p.ThreadID
        AND p.CreateTime > t1.MinAnswerDate
)p1
JOIN (
    SELECT
          TopConcerID = tt.ID
        , th.ThreadID
        , th.ThreadTitle
        , th.ThreadUrl
        , p.PostID
        , p.AuthorID
        , PostCreateTime = p.CreateTime
        , tt.Entities
        , tt.NegativeWords
        , tt.Verbatim
    FROM thread th
    JOIN TopConcernTest tt ON tt.ThreadID = th.ThreadID
    JOIN Post p ON tt.PostID = p.PostID AND p.ThreadID = th.ThreadID
    WHERE tt.[Type] = 1
)t3 ON t2.ThreadID = p2.ThreadID AND p2.PostCreateTime > t2.MinAnswerDate
WHERE p1.MaxReplyDate IS NOT NULL
    AND t3.TopConcerID BETWEEN 1 AND 20


SUM of column by GROUP BY which is WHithin a GROUP BY i want to display the rows GROUP BY main Reference Id and then show the amount GROUP BY type.

this my query

SELECT gl.`drcr`,gl.`amount`,gl.`name`,gl.`type`,
       gl.`narration`,gl.`main_reference`,gl.`date` "
. "FROM `general_ledger` AS gl "
. "INNER JOIN ( "
. " SELECT SUM(amount) AS total
    FROM `general_ledger` GROUP BY `type` ) AS tot "
. "WHERE gl.`date` BETWEEN '$start_date' AND '$end_date' "
. "GROUP BY gl.`main_reference`,gl.`date`,gl.`name`,
            gl.`type`,gl.`amount`,gl.`drcr`,gl.`narration`

if main reference id and type same want to add both row amount and need to display as one row like

please help me..
thanks in advance

SELECT `drcr`,SUM(`amount`)
`amount`,`name`,`type`,`narration`,`main_reference`,`date`
FROM (
    SELECT gl.`drcr`,gl.`amount`,gl.`name`,gl.`type`,gl.`narration`,gl.`main_reference`,gl
  .`date` "
                              . "FROM `general_ledger` AS gl "
                              . "INNER JOIN ( "
                              . " SELECT SUM(amount) AS total FROM `general_ledger` GROUP BY `type` ) AS tot "
                              . "WHERE gl.`date` BETWEEN '$start_date' AND '$end_date' "
                              . "GROUP BY gl.`main_reference`,gl.`date`,gl.`name`,gl.`type`,gl.`amount`,gl.`drcr`,gl.`narration`
) Interimresults
GROUP BY `drcr`, `name`,`type`,`narration`,`main_reference`,`date`


Can I recover CDC tables from transaction logs? Is there anyway to restore CDC tables from transaction logs? My basic understanding is that CDC reads these logs, and entries that are specially marked, are grabbed by CDC. Once this has happened though, this data is no longer flagged for harvesting by CDC.

I need this to happen because a DB was restored without any KEEP_CDC options.

MS SMS, 2008 R2, sql server 10.5.16

Transaction logs are used as a source to get the data CDC uses but it would be very difficult to extract data yourself and insert it into CDC tables.

Problem is that transaction log is not well documented and there are basically two functions you can try DBCC LOG and fn_dblog.
Another option is to use a third party log reader such as ApexSQL Log or Quest Toad but even with those tools you’ll be only able to read what’s written in transaction log. It would be up to you to take this data and somehow add it to CDC tables.


How to change location of SQL Server 2012 database which is already configured with a merge replication I have SQL Server 2012 configured with merge replication. currently the location of the replication database and the .mdf and .ldf files reside in C:\ drive.

The C:\ drive is now full due to that fact that these files reach over 80GB. Therefore, I want to relocate the files to another drive where I have more space with out affecting the replication and the data.

How do I do this safely?

Oh Boy! System Databases on C Drive are recipe for disaster !

Change the default database location away from C:\ drive.

You can do it using ALTER DATABASE .. MODIFY FILE

Change the db file location

ALTER DATABASE db_name
MODIFY FILE (NAME = logical_data_file,
     FILENAME = 'D:\data\physical_file.mdf');

ALTER DATABASE db_name
MODIFY FILE (NAME = logical_file,
     FILENAME = 'L:\log\physical_file_log.ldf');

Stop log reader agent, distribution and merge agent (if they are running).
Offline the database using alter database db_name set offline
Once offline, you have to PHYSICALLY move the data and log files to the new location that you put in in step 1.
Bring the database ONLINE alter database db_name set ONLINE
Start the jobs that were disabled in step 2.


Simple and Full recovery models Not an SQL admin and new to ola-hallengren great scripts.

Just wondering if people are mostly doing a blanket user database backup with his full/diff script regardless on if a DB is in Simple or Full recovery mode, or is it preferred that Simple mode DBs and backup up separately etc.

Or, does it not even matter??

It depends on your RTO (Recovery Time Objective) and RPO (Recovery Point Objective).

Recovery Time Objective: Time duration to restore the database in case of any disaster.

Recovery Time Objective: The time interval that may pass until the database recovered. (Acceptable time duration for data loss).

Before you decide your recovery model and backup strategy, I would suggest you go through the Database Recovery Model and Backup Types in SQL Server before you decide your recovery model and backup strategy.

Edit:1

The ola-hallengren backup SP doesn't automatically execute all supported backups according to the recovery model of respective databases.

So, if you execute the SP with @BackupType = 'LOG' for the database with simple recovery model, I hope it'll fail and raise & log an error.

You need to execute separate script for all types of backup with @BackupType = 'FULL',@BackupType = 'DIFF' & @BackupType = 'LOG'.
You can execute separate script for LOG backup of the databases with FULL/BULKED_LOG recovery model with @Databases='Database_1, Database_2,...'.

Note: All backup logs (success/failure) are maintained into CommandLog table.

Edit:2

At line 2136, it's validating, " if @BackupType with recovery model of the database, then only it's proceeding to prepare backup script. to if condition is like this. Prior to that line I didn't see any validation to raise/log an error message into the commandlong table.

So I would suggest you to execute separate script for transaction log backup that includes the databases with full recovery model.

Fourth line of the below script validating the condition.

IF @CurrentDatabaseState = 'ONLINE'
AND NOT (@CurrentUserAccess = 'SINGLE_USER' AND @CurrentIsDatabaseAccessible = 0)
AND NOT (@CurrentInStandby = 1)
AND NOT (@CurrentBackupType = 'LOG' AND @CurrentRecoveryModel = 'SIMPLE')
AND NOT (@CurrentBackupType = 'LOG' AND @CurrentRecoveryModel IN('FULL','BULK_LOGGED') AND @CurrentLogLSN IS NULL)
AND NOT (@CurrentBackupType = 'DIFF' AND @CurrentDifferentialBaseLSN IS NULL)
AND NOT (@CurrentBackupType IN('DIFF','LOG') AND @CurrentDatabaseName = 'master')
AND NOT (@CurrentAvailabilityGroup IS NOT NULL AND @CurrentBackupType = 'FULL' AND @CopyOnly = 'N' AND (@CurrentAvailabilityGroupRole <> 'PRIMARY' OR @CurrentAvailabilityGroupRole IS NULL))
AND NOT (@CurrentAvailabilityGroup IS NOT NULL AND @CurrentBackupType = 'FULL' AND @CopyOnly = 'Y' AND (@CurrentIsPreferredBackupReplica <> 1 OR @CurrentIsPreferredBackupReplica IS NULL) AND @OverrideBackupPreference = 'N')
AND NOT (@CurrentAvailabilityGroup IS NOT NULL AND @CurrentBackupType = 'DIFF' AND (@CurrentAvailabilityGroupRole <> 'PRIMARY' OR @CurrentAvailabilityGroupRole IS NULL))
AND NOT (@CurrentAvailabilityGroup IS NOT NULL AND @CurrentBackupType = 'LOG' AND @CopyOnly = 'N' AND (@CurrentIsPreferredBackupReplica <> 1 OR @CurrentIsPreferredBackupReplica IS NULL) AND @OverrideBackupPreference = 'N')
AND NOT (@CurrentAvailabilityGroup IS NOT NULL AND @CurrentBackupType = 'LOG' AND @CopyOnly = 'Y' AND (@CurrentAvailabilityGroupRole <> 'PRIMARY' OR @CurrentAvailabilityGroupRole IS NULL))
AND NOT ((@CurrentLogShippingRole = 'PRIMARY' AND @CurrentLogShippingRole IS NOT NULL) AND @CurrentBackupType = 'LOG')
AND NOT (@CurrentIsReadOnly = 1 AND @Updateability = 'READ_WRITE')
AND NOT (@CurrentIsReadOnly = 0 AND @Updateability = 'READ_ONLY')
AND NOT (@CurrentBackupType = 'LOG' AND @LogSizeSinceLastLogBackup IS NOT NULL AND @TimeSinceLastLogBackup IS NOT NULL AND NOT(@CurrentLogSizeSinceLastLogBackup >= @LogSizeSinceLastLogBackup OR @CurrentLogSizeSinceLastLogBackup IS NULL OR DATEDIFF(SECOND,@CurrentLastLogBackup,GETDATE()) >= @TimeSinceLastLogBackup OR @CurrentLastLogBackup IS NULL))

Thanks!


Oracle - Foreign Key on non Unique - is materialized view the answer? we have the following table to store texts in different languages:

CREATE TABLE TXT
(
  TXTID VARCHAR2(10 BYTE) NOT NULL
, LANGUAGE VARCHAR2(2 BYTE) DEFAULT 'DE' NOT NULL
, STEXT VARCHAR2(10 BYTE)
, LTEXT VARCHAR2(4000 BYTE)
, LABELTEXT VARCHAR2(20 BYTE)
, CONSTRAINT TXT_PK PRIMARY KEY
  (
    TXTID
  , LANGUAGE
  )
)

So you can have multiple language with one txtid.
Now I want to have the TXTID as a foreign key on another table.
First I created a view with

select distinct txtid from txtid

which of course doesn't work because you can't really use the constraints on the view.
So I heard of the materialized view:

CREATE MATERIALIZED VIEW mv_txtid_txt
REFRESH ON DEMAND
AS SELECT distinct txtid from txt

alter table mv_txtid_txt
add CONSTRAINT TXT_PK PRIMARY KEY (TXTID)
ENABLE

So far so fine, not perfect but I thought it would work... adding the foreign key to a table:

ALTER TABLE DATADICT
ADD CONSTRAINT DATADICT_FK2 FOREIGN KEY(TXTID)
REFERENCES mv_txtid_txt(TXTID)
ENABLE

But I get the:
ORA-02270: no matching unique or primary key for this column-list

How to get the not unique TXT.TXTID as a foreign key? Do I really must use triggers on the tables to check the foreign key manually?

Kind regards,

If the concept identified by TXT_ID can be referenced by some other entity independently of the text language, then it deserves to be a separate entity represented by a separate table, as mentioned by RDFozz in a comment. Text translations will then become its children, as will DATADICT.


Map new user to table owner We have a database that was accessed as user MARY on the old SQL Server. All the tables are:
MARY.<tablename>

The application assumes it can access tables by name only. E.g. select * from <tablename> rather than select * from MARY.<tablename>
We have a new userid that the software uses to access the database: APPSERVER.
When APPSERVER logs in, it has full rights to the DB, but can only access the tables as MARY.<tablename>.
So the code will not run.
How do I either:

get new user APPSERVER to magically map to MARY. to access tables
or

update all tables so they are now: APPSERVER.<tablename>.

(This is all via SQL native auth, not integrated.)

Solved by adjusting the SQL user id to have the appropriate default schema... and tada!


Conditionally move files in SSIS I have a simple SSIS package with a ForEach Loop Container with 1 Data Flow task that triggers when a file is present. If that task succeeds, I want to move the input file to a "success" folder. If it fails, I want to move the file to an "error" folder. I am not seeing an error (red arrow) from the data flow task. How do I access the error condition for the data flow task?

I found the answer to my question.

The answer that worked for me was to add the 2nd move File System Task. Then when connecting to previous Data Flow Task, right click on the Precedence Constraint and click the "Failure" option.  I now have my error condition!


MySQL many to many relation Sorry is this seems a stupid question, but I have a table of server addresses and a second table with a list of hardware devices which use the addresses, it is possible for multiple devices to use the same server address and a device to use multiple server addresses, so there is a many to many mapping between them.
I am a little confused how I can represent the relationship between them in SQL, so far I am using a third table with the primary keys from the address table and device table and perform two joins in the query, but I'm not sure if this is the best way.

You are doing the right way,

In many-to-many relation your must have a third table mapped by the ID of the two other tables. The two Id must be foreign key, and the association of the two is the primary key of your association's table

You can also store in this table the information that are related on the relation.


How to join multiple tables (SQL) to find the difference between an order and shipped quantities? I have a collection of orders that were submitted to an internal ERP, which were processed and partially shipped.

The table hierarchy is as follows:

Order table contains OrderReference(PO), OrderID
OrderItems table contains Product/Lot/Qty requested (OrderID is parentID that joins these tables)
Shipping contains OrderReference(PO), ShippingID
ShippingArchive contains Product/Lot/Qty that was shipped (ShippingID is parent that joins these tables)

I want to create a report that looks as follows:

OrderReference#

Order Requested / Shipped Items / Missing Pieces

PC/Lot/Qty    -   PC/Lot/Qty    - PC/Lot/Qty

I have used a temp table to get some of the data but I realize I'm taking the wrong approach. I should be able to do all of this with a single query, though it will seemingly be a bit complex.

I get held up when I try to think about how to join where pieces don't exist, and where to perform the mathematics in my sorting and filtering. Please help!

Sincere thanks :)

I think this is what you want.  I had to make a few assumptions though.  First that a given item is identified by product and lot.  So I added a join between ShippingArchive and OrderItems based on these columns.   Next that in your report you only needed to see the product and lot information once and needed the requested, shipped and missing information for qty only.  I also made the assumption that missing meant requested-shipped.

Edited to handling multiple shipping for the same order.

WITH Shipped AS (SELECT Shipping.OrderReference, ShippingArchive.Product, ShippingArchive.lot,
                        SUM(ShippingArchive.qty) AS Qty
                FROM Shipping
                JOIN ShippingArchive
                    ON Shipping.ShippingId = ShippingArchive.ShippingId
                GROUP BY Shipping.OrderReference, ShippingArchive.Product, ShippingArchive.lot
                )
SELECT  [Order].OrderReference, OrderItems.Product AS Requested_Product, OrderItems.lot AS Requested_Lot, OrderItems.qty AS Requested_Qty,
    ISNULL(Shipped.qty,0) AS Shipped_Qty, OrderItems.qty - ISNULL(Shipped.qty,0) AS Missing_Qty
FROM [Order]
JOIN OrderItems
    ON [Order].OrderId = OrderItems.OrderId
LEFT OUTER JOIN Shipped
    ON [Order].OrderReference = Shipped.OrderReference
    AND OrderItems.Product = Shipped.Product
    AND OrderItems.Lot = Shipped.Lot


Slow updates of PostgreSql ByteA fields with just 1KB of data TL;DR: Inserting or updating table, with putting 1024 bytes into bytea column works quite slow.

I have a PostgreSql 9.5.12 installation on my Desktop Linux Mint 18, with SSD drive, Core-i5, and 16GB Ram.

I've created table with the folowing structure:

CREATE TABLE "cells" (
  "x" integer NOT NULL DEFAULT 0,
  "y" integer NOT NULL DEFAULT 0,
  "palette_id" integer DEFAULT 1,
  "image" bytea NULL
);

It represents a "cells" on a 2D-field with 32x32 pixels image on it. "image" column always contain 1024 bytes, each byte is a color of one of the 32x32 pixels (each color is represended by a single byte).

So, every time, when I'm changing image and write it back to table, it takes about 450 milliseconds (almost a half of a second!).

I'm writing it with query like this (using php PDO):

UPDATE cells
SET image=decode('00000000000000000000000000000000000000000000000000000000000000000000002a2a4b6b6c4b4a2a2a4b00002a222a000000002222000000002200000000002a4b2a4b95b6956c2a4b95b6954b2a4b6c956c4b2a2a6b6c4b2a222a2a00004a4a4a2a4a4b4b4b4a222a4b734a2a2a2a4a4b4b2a222a4b4b2a2a222a4b2a7151312a2a2a2a5252292a2a5229312a2a2a2a5252292a2a5231312a2a2a2a317172524b2a2a4b4b4b4a2a2a4a4b4b2a2a2a4a4b4b2a2a2a2a4b4b2a2a2a2a4b71738d6d4b236d8e8e6d23236d8e6e6d23236d6e6e4c23236d6e6d4423236d6e7193aeaf4b4b8eb0b08643438eb0b08643438eb0876643438e8f8766234366877193b6af4b4baed8d08e4b4baed0d08e434baed0b08e434baed0b08e434366877193b6af6d6cafd8d0af6c6cafd0d0af6c6cafd0b08e646caed0af8e444466877173aeb08f8fafb0b0af8787afb0b08786868faf8f8766668f8f8787666666677173aed0b0b0b0b0b0b0b0b0a8a8a8a8a88787878787878787878787878767667193b6d0d0d0d0b0b0b0b0b0b0b0b0b0b0b0b0afafafafafa7a78787878767667193b6d0d0d0d0d0d0d0d0d0b0afafafafafafafafafaf8fafafafafaf8767667193b6d0d0d0d0d0d0d0d0d0afae6d6dafaf8d8dafaf6d6d8fafafafaf8767667193b6d0d0d0d0d0d0d0d0b0af8d4b4c8eaf6d6c8f8f6c648eafafafaf8767677193b6d0d0d0d0d0d0d0d0af8e6c23438eaf6d6daf8f6c648eafafafaf8767677193b6d0d0d0afaeaeaeaf8e6d4322438eaf6d6dafaf6d648eafafafaf8767677193b6d0d0af6d4c4b4b6c6c432222438eaf6d6dafaf6d6c8eafafafaf8767677193b6d0d0af4c2222222343222222438eaf6d6dafaf6d6c8eb0afafaf8787677193b6d0d0af6c2222222343222222438eaf6d6dafaf6d6c8eb0afafaf8787677193b6d0d0af6d4c4b4b6c6c432222438eaf6d6dafaf6d6c8eb0afafaf8787677193b6d0d0d0afaeaeaeaf8e6d4322438eaf6d6dafaf6d6c8eb0afafaf8787677193b6d0d0d0d0d0d0d0d0af8e6c234b8eaf6d6dafaf6d6c8eb0b0afaf8787677193b6d0d0d0d0d0d0d0d0d0af8d4b4cafaf6d6dafaf6c6c8eb0b0afaf8787677193b6d0d0d0d0d0d0d0d0d0afae6d6dafaf8d8dafaf8d6dafb0afafafa787677193b6d0d0d0d0d0d0d0d0d0d0afafafafb0afafafafafafafb0afafafa787677193b6d0d0d0d0d0d0d0d0d0d0d0d0d0d0d0d0d0b0b0b0b0b0b0afafafa7876700b5b6d0d0d0d0d0d0d0d0d0d0d0d0d0d0d0d0d0b0b0b0b0b0b0afafafaf876700f8d8d0b0b0b0b0b0b0b0b0afaeaeaeaeaeaeaeafd0b0b0b0b0b0afaf87876700f8d8b0906868686868688f9493937373737394b7d0d0b0b0b0b0af8787876700000000686848484848486e9391717171717193b6d0d0b0b0b0b0af87676700', 'hex')
WHERE id=2941

I've also tried to use the E'\\x escaping instead of "decode" function.

If I run this query from the psql console, it takes about 0.25 second, which is faster whan php's query, but not dramatically.

I've googled for "bytea performance issues" and found, that people have problems with importing like 100MB of data into a table column, not the 1kb.

Also, for comparision, in other table I'm inserting an array of 256 char items (which are 9 bytes long each), and it takes about 0.00156 second.

So:

1024B to bytea takes 0.25 seconds

2304B to char[9][] array takes 0.00156 seconds

How could it be? What should I do? Should I use text datatype instead of bytea? Or maybe an array of bytes would be more efficient?

To my shame, the problem was not related to bytea data type at all.

It was just absence of the primary key index in my table, and as table grow to several hundreds of thousands of records, finding record by id became too slow.

Now, when I've created primary key index, inserting of the data takes about 0.0001 seconds.


Ensure that only one record has a specific value Let's say there's a table that stores jobs and their state (something like CREATE TABLE jobs (id INT, status VARCHAR(255)), how to ensure that only one job could be in running state i.e. how a query that tries to start a job should look like?

That can't be performed with single query. Therefore you have to create stored routine like that:

CREATE PROCEDURE `start_job`( IN `pid` INT, IN `state` varchar )
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
   UPDATE *
     FROM jobs AS w
      SET w.status = "idle"
    WHERE w.status = "running"
   ;
   INSERT INTO jobs (id, status)
   VALUES (pid, state)
       ON DUPLICATE KEY UPDATE
          status = state
   ;
END


MySQL copy all views from one database to another in stored procedure I've got a stored procedure that copies all tables from one database to another but steps through each table individually (because of queries).

I need to add to the stored procedure to copy all views from the original database but I'm not totally sure how to loop through the results of this query:

SHOW FULL TABLES IN stations WHERE TABLE_TYPE LIKE 'VIEW';

Stored procedure:

USE station;
DROP PROCEDURE IF EXISTS updateStation;
DELIMITER //
CREATE PROCEDURE updateStation (IN stationName VARCHAR(255))
 BEGIN
   # stationList
   DROP TABLE IF EXISTS station.stationList;
   CREATE TABLE station.stationList LIKE stations.stationList;
   INSERT INTO station.stationList SELECT s.* FROM stations.stationList s WHERE s.hostName = stationName;
   # pinTypes
   DROP TABLE IF EXISTS pinTypes;
   CREATE TABLE station.pinTypes LIKE stations.pinTypes;
   INSERT INTO station.pinTypes SELECT p.* FROM stations.pinTypes p;
   # pinModes
   DROP TABLE IF EXISTS pinModes;
   CREATE TABLE station.pinModes LIKE stations.pinModes;
   INSERT INTO station.pinModes SELECT p.* FROM stations.pinModes p;
    # Copy All Views
    FOR SELECT TABLES IN stations WHERE TABLE_TYPE LIKE 'VIEW'
    DROP TABLE IF EXISTS ?;
    CREATE TABLE station.? LIKE stations.?;
 END //
DELIMITER ;

Perhaps something like this? :

DECLARE cur CURSOR FOR SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'stations' AND ENGINE IS NULL;
DECLARE done INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
read_loop: LOOP
    FETCH cur INTO tableName;
        IF done THEN
          LEAVE read_loop;
        END IF;
    DROP TABLE IF EXISTS 'station'.tableName;
    CREATE TABLE 'station'.tableName LIKE 'stations'.tableName;
END LOOP;

CLOSE cur;

You need to call SHOW CREATE VIEW to get the view code.  However, that will have the database name in the code.  You would need to parse the output, replace that database name, then execute dynamic statements with PREPARE and EXECUTE.

Stored procedures may be the most difficult way to manage this. For example, you can mysqldump can dump/load the views from one database to another in one line : https://stackoverflow.com/questions/1658204/backing-up-views-with-mysql-dump

Other tools like Flyway and Liquibase can help with schema management as well.


MariaDB query time in profile not match From a MariaDB 10.1 database with the profile enabled.

After making some test with an example query, why the time reports from the query doesn't match the duration time that the profiles report?

I try to know why the same query is sometimes slow.

MariaDB [db1]> select id from sol_demo where id = 1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

MariaDB [db1]> select id from sol_demo where id = 1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.10 sec)

MariaDB [db1]> select id from sol_demo where id = 1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.40 sec)

MariaDB [db1]> show profiles;
+----------+------------+----------------------------------------------+
| Query_ID | Duration   | Query                                        |
+----------+------------+----------------------------------------------+
|      145 | 0.00008608 | select id from sol_demo where id = 1 |
|      146 | 0.00009588 | select id from sol_demo where id = 1 |
|      147 | 0.00011883 | select id from sol_demo where id = 1 |
|      148 | 0.00008926 | select id from sol_demo where id = 1 |
|      149 | 0.00009260 | select id from sol_demo where id = 1 |
|      150 | 0.00008944 | select id from sol_demo where id = 1 |
|      151 | 0.00008301 | select id from sol_demo where id = 1 |
|      152 | 0.00008771 | select id from sol_demo where id = 1 |
|      153 | 0.00010690 | select id from sol_demo where id = 1 |
|      154 | 0.00009748 | select id from sol_demo where id = 1 |
|      155 | 0.00008521 | select id from sol_demo where id = 1 |
|      156 | 0.00009257 | select id from sol_demo where id = 1 |
|      157 | 0.00009136 | select id from sol_demo where id = 1 |
|      158 | 0.00056927 | select id from sol_demo where id = 1 |
|      159 | 0.00008753 | select id from sol_demo where id = 1 |
+----------+------------+----------------------------------------------+
15 rows in set (0.00 sec)

Profile shows the (server-side) operating system resource usage by the server process, while the command-line interface reports the (client-side) statement execution elapsed time, which also includes in-client processing and the time spent sending the statement to the server for processing and receiving the results. In other words, elapsed time reported by the CLI is a superset of time measured by the "profiler". The shorter the statement execution time, the higher the proportion of the client overhead in the overall elapsed time.

See also this related question.


Oracle 11g: functions or procedures in C/C++ I want to create a function called by a trigger on oracle but in c or c++. Something like in PostgreSQL: Audit trigger in C.

Or, in any case, is there support for this but in other languages like Python?

Thanks!

Oracle database supports Java and external C calls. They can be used as wrappers to call programs written in other languages such as Python.

Publishing External Procedures

  Oracle Database can only use external procedures that are published
  through a call specification, which maps names, parameter types, and
  return types for your Java class method or C external procedure to
  their SQL counterparts.

Create a sample program:

$ cat /home/oracle/1.c
char* myfunc()
{
   return "Hello World!";
}

$ gcc -shared -o /home/oracle/1.so -fPIC /home/oracle/1.c

In the database, create a library and a function using it:

create or replace library lib_myfunc is '/home/oracle/1.so';
/

create or replace function myfunc
return varchar2 as external
name "myfunc"
library lib_myfunc
language c;
/

Enable the use of this library in $ORACLE_HOME/hs/admin/extproc.ora:

SET EXTPROC_DLLS=/home/oracle/1.so

Then finally use it as:

SQL> select myfunc from dual;

MYFUNC
------------
Hello World!

More about this:

Developing Applications with Multiple Programming Languages


Remove parent and child rows if child or parent fits criteria I have the following table:

OrderID | OldOrderID    | Action    | EntryDate | Source
    1   |     NULL      |   Insert  | 2016-01-12|   A
    1   |     NULL      |   Remove  | 2016-01-13|   A
    2   |     NULL      |   Insert  | 2016-01-12|   B
    3   |     NULL      |   Insert  | 2016-01-12|   C
    4   |       3       |   Insert  | 2016-01-13|   C
    4   |     NULL      |   Remove  | 2016-01-14|   C

I'm trying to query all rows that dont have the Action Remove. A row can also have a child (a child can also have a child). If a child get the Action Remove it should also remove the parent and so on until it gets to the first parent.

The query I'm currently using fails to remove the parent if a child is removed.

WITH Active AS
(
  SELECT *, rn = ROW_NUMBER()
    OVER (PARTITION BY OrderID,Source ORDER BY EntryDate DESC)
  FROM Orders
)
SELECT *
FROM Active WHERE [Action] <> 'Remove' AND rn = 1;

One other problem is that OldOrderID can be 0 or equal to OrderID insted of NULL.

Can this problem be solved with SQL only or should I programaticly filter a simple query?

EDIT:
As I might have explained it badly some extra information:

Each row can make a child of its self ( New OrderID, OldOrderID = prevoius OrderID). Or make a "clone" of its self with a diffrent Action, but the same OrderID.

A child row will be inserted with a  OrderID and an OldOrderID that refrences a OrderID from a prevoius row. If a child is updated to indicate it has been removed it will get a new row with the same OrderID, but the OldOrderID might be NULL or it might still contain a value indicating wich row is its parent.

The Source column idicates where the data came from as this data is aggreggated in a single table from 5 difrent sources. The same OrderID number can exitst on multiple sources, but it dosent mean its the same order. Each row also has a Uniqe ID, but I didnt include it in the table as it didn't seem useful.

The exception are rows that get fake children. They have OldOrderID equal to 0 or is the same as OrderID - in this case its the same as if OldOrderID was equal to NULL.

In the example table I only need the row with the OrderID 2. OrderID 1 gets a "clone" and is removed. OrderID 3 gets a child with OrderID 4. This child then gets removed. So the parent, 3, should also get removed.

I hope this clears some of the questions.

This is one way to do it:

IF OBJECT_ID('tempdb..#Orders') IS NOT NULL
    DROP TABLE #Orders;

CREATE TABLE #Orders (
    OrderID int,
    OldOrderID int,
    Action varchar(10),
    EntryDate date,
    Source char(1)
)

-- Your sample data, plus a couple more rows
--to demonstrate the recursive query
INSERT INTO #Orders
VALUES
    (    1   ,     NULL      ,   'Insert'  , '2016-01-12',   'A'),
    (    1   ,     NULL      ,   'Remove'  , '2016-01-13',   'A'),
    (    2   ,     NULL      ,   'Insert'  , '2016-01-12',   'B'),
    (    3   ,     NULL      ,   'Insert'  , '2016-01-12',   'C'),
    (    4   ,       3       ,   'Insert'  , '2016-01-13',   'C'),
    (    4   ,     NULL      ,   'Remove'  , '2016-01-14',   'C'),
    (    5   ,       4       ,   'Insert'  , '2016-01-15',   'C'),
    (    5   ,     NULL      ,   'Remove'  , '2016-01-16',   'C');

WITH SourceData AS (
    -- Crosstab to get all orders on a single row
    -- The assumption is you can get just two actions (insert and remove)
    SELECT OrderId,
        ParentOrder = MAX(OldOrderID),
        InsertDate = MAX(CASE WHEN Action = 'Insert' THEN EntryDate END),
        RemoveDate = MAX(CASE WHEN Action = 'Remove' THEN EntryDate END),
        Source = MAX(Source)
    FROM #Orders
    GROUP BY OrderId
),
OrderHierarchy AS (
    -- Recursive query to get top OrderId
    SELECT *,
        OrderID AS TopOrderId
    FROM SourceData
    WHERE ParentOrder IS NULL

    UNION ALL

    SELECT SD.*, OH.TopOrderId
    FROM SourceData AS SD
    INNER JOIN OrderHierarchy AS OH
        ON SD.ParentOrder = OH.OrderID
),
AllOrders AS (
    -- Get MAX(RemoveData) for the order chain
    SELECT *, MaxRemovedDate = MAX(RemoveDate) OVER (PARTITION BY TopOrderId)
    FROM OrderHierarchy
)
-- Return just the rows that have no remove date in the chain
SELECT *
FROM AllOrders
WHERE MaxRemovedDate IS NULL;


Views with parameters, is it possible? I am working in a PHP application and some "complex" queries are starting to appear in the code. Because of the complexity I am not able to use any ORM and the only resource I have is a plain SQL and PHP MySQL native functions which I don't like.

Without more here is one of the queries I want to convert into a view:

SELECT
  COUNT(*) AS 'rec',
  CONCAT(
      IF(agreement_list.ActiveFlag, '', 'Agreement is Inactive.'),
      IF(agreement_type.ActiveFlag, '', 'Agreement Type is Inactive.'),
      IF(distributor.ActiveFlag, '', 'License Distributor is InActive.'),
      IF(agreement_distributor.ActiveFlag, '', 'Agreement Distributor is InActive.'),
      IF(customer.ActiveFlag, '', 'Customer is Inactive.'),
      IF(cf_program_level.ActiveFlag, '', 'Program Level is Inactive.')
  ) AS errormessage,
  IF((agreement_list.ActiveFlag + agreement_type.ActiveFlag + distributor.ActiveFlag + agreement_distributor.ActiveFlag + customer.ActiveFlag + cf_program_level.ActiveFlag) < 6, 1, 0 ) AS error
FROM
  license
  JOIN agreement_list ON (agreement_list.AgreementTypeID = license.AgreementTypeID AND agreement_list.CustomerSiteID = license.CustomerSiteID AND agreement_list.Source = license.Source)
  JOIN customer ON (customer.id = license.CustomerSiteID AND license.source = customer.Source)
  JOIN distributor ON (distributor.DistributorID = license.DistributorID AND license.source = distributor.Source)
  JOIN distributor AS agreement_distributor ON (agreement_distributor.DistributorID = agreement_list.DistributorID AND agreement_list.source = agreement_distributor.Source)
  JOIN agreement_type ON (agreement_type.AgreementTypeID = license.AgreementTypeID AND license.source = agreement_type.Source)
  JOIN cf_program_level ON (cf_program_level.CFProgramLevelID = '{$CFProgramLevelID}' AND license.source = cf_program_level.Source)
WHERE
  license.AgreementTypeID = '{$AgreementTypeID}'
  AND license.CustomerSiteID = '{$CustomerSiteID}'
  AND license.Source = '{$Source}'

$CFProgramLevelID, $AgreementTypeID, $CustomerSiteID, $Source are parameters coming from PHP and that's my only problem. How do I pass a parameter to a view if it's possible?

I am using MySQL 5.6 at the moment.

Seems simple.  Build the VIEW without the last 5 lines.  Then use those 5 lines when you use the VIEW as if it were a TABLE.

If those JOINs are not "many:one", you will get an inflated COUNT(*).


How to return rows with a specific value last? My question is reverse of this question, is it possible to define the sort order.

I want to return some records from SQL Server database, sorted in ascending order based on a column's value BUT two particular records should always be on the end.

The original SQL (which needs to be changed) is below:

select code_id as CategoryID, code_desc as CategoryDescription
from codes
where code_id > '000001' and code_id < '000100'
order by CategoryDescription

The results are returned as

000003   ***First Record
000002   ***Another Record
000004   Apple
000016   Books
000014   Cables

I want the below result (first two with asteriks on the last):

000004   Apple
000016   Books
000014   Cables
000003   ***First Record
000002   ***Another Record

I tried the below UNION statement but the resultset is automatically sorted in ascending order and those two rows are in the beginning by default.

select code_id as CategoryID, code_desc as CategoryDescription
from codes
where code_id > '000003' and code_id < '000100'
--order by categoryDescription

UNION

select code_id as CategoryID, code_desc as CategoryDescription
from codes
where code_id > '000001' and code_id < '000004'

This seems like a much simpler solution than introducing a bunch of unions:

SELECT code_id as CategoryID, code_desc as CategoryDescription
FROM dbo.codes
WHERE code_id > '000001' AND code_id < '000100'
ORDER BY CASE code_id
  WHEN '000002' THEN 3
  WHEN '000003' THEN 2
  ELSE 1 END, CategoryDescription;


TEMPDB full causing login problems with server login trigger We have a server trigger used to prevent some logins from accessing SQL Server via SSMS in our DEV environment.

CREATE TRIGGER [Deny_SQLLogin_SSMS_Trigger]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @login    SYSNAME = ORIGINAL_LOGIN()
    DECLARE @app      SYSNAME = APP_NAME()
    DECLARE @hostname SYSNAME = HOST_NAME()

    -- is this login in our prohibited list.
    IF @login IN ('some_user', 'some_user2', 'some_user3') AND @app LIKE N'%Management Studio%'
    BEGIN
        -- yes so send back an error
        --ROLLBACK;

        -- .. and record the attempt.
        INSERT msdb.dbo.AuditLog([Login],App,HostName,EventTime)
        VALUES (@login, @app, @hostname, SYSUTCDATETIME());

        THROW 51000, 'Connection not allowed.', 1;
    END
END

GO

ENABLE TRIGGER [Deny_SQLLogin_SSMS_Trigger] ON ALL SERVER
GO

This works just great when tempdb isn't full but when it is, all logins get rejected.

Message
Logon failed for login 'some_user' due to trigger execution.

Message
The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'.

No one can login from anywhere and we are forced to restart the instance. This is not going to be possible for our production instances when we start using the login trigger there as well.

So how can we design the trigger so that logins don't get blocked in case tempdb fills up unexpectedly?

I would like to point out an another way to do this instead of the trigger. As @Nic pointed out in the comment, your solution does not prevent users from connecting through SSMS (as you can change the app name).

You should create SQL-logins for the application or use service accounts if you are using Active Directory and create windows logins for them. This is only solution that does not allow your denied logins to connect at all and it does not matter if tempdb is full.


One to many relation getting recent records plus one column from previous record Table 1

ProductID   Status
PD_1       OPN
PD_2       CLS

Table2

Table2_id   ProductID   Amount
1           PD_1        900
2           PD_1        800
3           PD_2        1000
4           PD_2        900
5           PD_2        700

Final expected result

ProductID   Recent_Amount   Previous_Amount   Status
PD_1        900             800               OPN
PD_2        1000            900               CLS

You can get the previous row value for a column using the LAG function and limit to the most recent row using ROW_NUMBER:

SELECT ProductId,
  RecentAmount,
  PreviousAmount,
  Status
FROM
(
SELECT
  t1.ProductId,
  t2.Amount AS RecentAmount,
  LAG(t2.Amount, 1, 0) OVER (PARTITION BY t2.ProductId ORDER BY t2.Id ASC) AS PreviousAmount,
  ROW_NUMBER() OVER (PARTITION BY t2.ProductId ORDER BY t2.Id DESC) AS Rn,
  t1.Status
FROM Table2 t2
INNER JOIN Table1 t1 ON t1.ProductId = t2.ProductId
) t
WHERE Rn = 1

Your example data is a little confusing though. For PD_2, your expected results indicate that 1000 is the most recent amount and 900 the previous amount, however, the 1000 record has a lower Id value the 900 record. Typically, one would assume the higher Id value records were entered alter than the lower ones.

In any case, you can see a working example of the LAG function, combined with ROW_NUMBER to pick the single row here. This example uses specific ordering in the LAG (DESC) and ROW_NUMBER (ASC) functions to return your expected resultset.

If your example data is incorrect and the 1000 record should actually be a higher Id value, you can change the LAG and ROW_NUMBER functions from

LAG(t2.Amount, 1, 0) OVER (PARTITION BY t2.ProductId ORDER BY t2.Id DESC) AS PreviousAmount,
  ROW_NUMBER() OVER (PARTITION BY t2.ProductId ORDER BY t2.Id ASC) AS Rn,

to

LAG(t2.Amount, 1, 0) OVER (PARTITION BY t2.ProductId ORDER BY t2.Id ASC) AS PreviousAmount,
  ROW_NUMBER() OVER (PARTITION BY t2.ProductId ORDER BY t2.Id DESC) AS Rn,

as shown in this example.


Coalesce Function Returning Duplicates I have a many-to-many relationship setup between three tables.

  CompanyNotices (M-M) CompanyNoticesLocations (M-M) Locations

I would like to retrieve all rows from the CompanyNotices table for one CompanyID and aggregate all of the LocationNames (from the Locations table).

I have tried to use the Coalesce function to do this in a stored procedure but it is returning all LocationNames for each CompanyNotice row.

DECLARE @CompanyID INT
SET @CompanyID = 9
DECLARE @Location varchar(4000)

SELECT @Location = COALESCE(@Location + ', ', '') + L.LocationName
FROM dbo.CompanyNotices AS CN
INNER JOIN dbo.CompanyNoticesLocations AS CNL ON CN.CompanyNoticeID = CNL.CompanyNoticeID
INNER JOIN dbo.Locations AS L ON CNL.LocationID = L.LocationID
WHERE CN.CompanyID = @CompanyID
GROUP BY L.LocationName

SELECT CN.CompanyNoticeHeading, CN.CompanyNoticeText, IsHR, IsApprover, IsEmployee, @Location AS LocationName
FROM dbo.CompanyNotices AS CN
INNER JOIN dbo.CompanyNoticesLocations AS CNL ON CN.CompanyNoticeID = CNL.CompanyNoticeID
INNER JOIN dbo.Locations AS L ON CNL.LocationID = L.LocationID
WHERE CN.CompanyID = @CompanyID
GROUP BY CN.CompanyNoticeID, CN.CompanyNoticeHeading, CN.CompanyNoticeText, IsHR, IsApprover, IsEmployee

I do realise that when I am setting the @Location that it is not linked to each CompanyNotice, so I am wondering how I can incorporate the first SQL query into the second to COALESCE the LocationName for each CompanyNotice?

Thanks in advance.

Thanks Martin, using your example answer I have it working using the following code:

SELECT CN.CompanyNoticeHeading, CN.CompanyNoticeText, IsHR, IsApprover, IsEmployee,
    Locations = STUFF
    (
        (
            SELECT ', ' + LocationName FROM dbo.Locations
            WHERE LocationID IN
                (SELECT LocationID FROM dbo.CompanyNoticesLocations WHERE CompanyNoticeID = CN.CompanyNoticeID)
            FOR XML PATH ('')
        ),1,1,''
    )
    FROM dbo.CompanyNotices AS CN
    INNER JOIN dbo.CompanyNoticesLocations AS CNL ON CN.CompanyNoticeID = CNL.CompanyNoticeID
    INNER JOIN dbo.Locations AS L ON CNL.LocationID = L.LocationID
    WHERE CN.CompanyID = @CompanyID
    GROUP BY CN.CompanyNoticeID, CN.CompanyNoticeHeading, CN.CompanyNoticeText, IsHR, IsApprover, IsEmployee


SQL Server to MySQL migration - how can I remove UCS-2 surrogate pairs in SQL Server? I have been tasked with migrating a SQL Server 2005 database to MySQL 5.6 (these are both database servers runnig locally) and would really appreciate some help.

-------Edit - Answered-------

This issue has now been resolved. I used Remus Rusanu's suggestion for finding the rows with these surrogate pair characters using CHARINDEX and have decided to use SUBSTRING to exclude the troublesome characters like so:

UPDATE test
SET a = SUBSTRING(a,  1,   (CHARINDEX(0x83dc, CAST(a AS VARBINARY(8000)))+1)/2 - 1) -- string before the unwanted character
+ SUBSTRING(a, (CHARINDEX(0x83dc, CAST(a AS VARBINARY(8000)))+1)/2 +1, LEN(a) ) -- string after the unwanted character
WHERE CHARINDEX(0x83dc, CAST(a AS VARBINARY(8000))) % 2 = 1 -- only odd numbered charindexes (to signify match at beginning of byte pair character)

-----------------------------

SQL Server source database has latin1 collation (so has ISO 8859-1 character set right?) but doesn't have any char/varchar fields (any string field is nvarchar/nchar) so all this data should be using the UCS-2 character set.
MySQL target database wants the character set UTF-8.

I decided to use the database migration toolkit in the latest version of the MySQL workbench. at first it worked fine and migrated everything as expected. But I have been totally tripped up upon encountering UCS-2 surrogate pair characters in the SQL Server database.

The migration toolkit copytable program did not provide a very useful error message:

  Error during charset conversion of wstring: No error.

It also did not provide any field/row information on the problem-causing data and would fail within chunks of 100 rows. So after searching through the 100 rows after the last successful insert I found that the issue seemed to be caused by two UCS-2 characters in one of the nvarchar fields. They are listed as surrogate pairs in the UCS-2 character set. They were specifically the characters DBC0 and DC83 (I got this by looking at the binary data for the field and comparing byte pairs (little endian) with data that was being migrated successfully).

When this surrogate pair was removed from the SQL Server database the row was migrated successfully to MySQL.

Here is the problem:

I have tried to search for these characters in a test SQL Server table (this chartest table is just various test strings an nvarchar field) to prepare a replacement script and keep getting strange results... I must be doing something incorrectly.

Searching for

SELECT * FROM chartest WHERE text LIKE NCHAR(0xdc83)

Will return any surrogate pair character (whether or not it uses DC83), but obviously, only if it is the only character (or part of the pair) in that field. This isn't a big deal since I would like to remove any instance of these anyway (I dont like to remove data like this but I think we can afford it).

Searching for

SELECT * FROM chartest WHERE text LIKE '%' + NCHAR(0xdc83) + '%'

Will return every row! Regardless of whether it even has a unicode character present in the field let alone the DC83 character. Is there a better way to find and replace these characters? Or something else I should try?

I have also tried setting the target databse, table, and field character set to UCS-2 but it seems as though it does not make a difference.

I should also mention that this migration is using live data (~50GB database!) while one of the sites that feeds it is taken offline so any solutions to this need to have a quick running time...

I would appreciate any suggestions very much! Please let me know if there is any information I have left out.

You need to take the data from UTF-8 and convert it into UCS-2LE using something like iconv. For example, using the character in your example:

echo "010000: dcb3" | xxd -r -s -0x10000 | iconv -f "UTF-8" -t "UCS-2LE" | xxd
0000000: 3307

Now I'm not sure what character UTF-8 \xdcb3 is, but apparently it's correct translation to UCS-2LE is \U0733. If you have \0xDCB3 in the SQL Server it means it was not translated into UCS-2LE before import. You should not have surrogates in the NVARCHAR fields, UCS-2 is "surrogate agnostic". See UCS-2 vs. UTF-16 (not quite Kramer vs. Kramer).

I'm not an expert in the MySQL tool set so I can't say what step is missing that was supposed to do the iconv.

Update

to locate the records with surrogates you must turn to the binary representation, since any character function will threat the surrogates as 'special':. Luckily the string manipulation functions work on binary too with the expected semantics. Eg. CHARINDEX:

insert into test(a) values  (N'a');
insert into test(a) values  (NCHAR(0xdc83));
insert into test(a) values  (N'b');
go

select * from test where charindex(0x83dc, cast(a as varbinary(8000))) > 0;


Performing group by, Order by on a table starting from particular date to recent last date Am sorry if this question sounds so dumb, please help.

I currently use the below MS SQL query to get the list of volunteers on each date and it works perfectly fine,

select Date, count(id) as co from volunter Group BY Date Order by Date

and the result set looks like this

----------
Date       | co
25/2/2016    8
5/3/2016     18
25/3/2016    28

i Now want to perform the same but from a particular date, say from 5/3/2016 to the latest date in the table or say till the last record in the table, how do i do this ? Thank you so much for your time

You just need to add a WHERE clause in the statement, like: where Date >= '2016-03-05':

select v.Date, count(v.id) as co
from volunter as v
where v.Date >= '2016-03-05'
group by v.Date
order by v.Date ;

Details may differ depending on the DBMS you use. For SQL Server for example, the safe format for the date and datetime types is 'YYYYMMDD', so it would be better to write the where as where v.Date >= '20160305'. In Oracle and Postgres you could use where v.Date >= DATE '2016-03-05'.
Date is a reserved keyword in some DBMS, so it would be better to choose another name for that column. Or always prefix it with the table name/alias.


How will Oracle fill a datafile added to a tablespace? I have a tablespace with one datafile which is 50% used. If I add a new datafile to the tablespace -

will the first datafile continue to be used until it reaches full capacity and only then will the second one start to be used for data or
the first datafile will forever remain only 50% used as the data changes are written to the new datafile or
something else happens which I haven't thought of?

I need this info to plan a strategy for dealing with increasing the size of tablespaces without wasting physical space.

In general, extents are allocated in a round-robin fashion among all data files in a tablespace so long as those data files have enough free space to allocate the extent.  So if you add a new data file, you would generally expect that half of the new extents would be allocated in the existing data file and half of the new extents would be allocated in the new data file.  Assuming the two data files are the same size, you would generally expect that by the time the first data file got to 100% full, the second data file would be roughly 50% full.

I'm not sure, though, how this helps you deal with increasing tablespace size without wasting physical space.  I'm hard-pressed to imagine in what circumstances knowing how extents are going to be allocated is going to help you use less physical space.  It shouldn't matter from that perspective whether you double the size of the existing data file, add a new data file, or add multiple data files and it shouldn't matter whether one data file is filled up before the next one starts being used.  In any case, you've allocated the same amount of space at the operating system level, you've allocated the same number of extents in the tablespace, and your segments are all the same size.

You would decrease the amount of space allocated at the operating system level by letting the data files autoextend rather than allocating fixed size data files but then you have to monitor the free space at the operating system level to ensure that the data files all have enough space to grow and you may complicate your ability to move data files to different mountpoints.  You may also decrease the amount of parallelism you can get in your backups forcing the backup job to run a bit longer.


persistence entity with composite key I have class named 'User'. It has the following properties

firstname
lastname
mobile
email
pin

I want to design a table or hibernate entity that will have unique email for each row; the same for mobile. One of email or mobile will be allowed to be null. I should be able to fetch a record by using email or mobile independently.

Some RDBMS will allow you to design a table's data column as unique but not required and some will not. Supposing it was allowed then you would simply write some code to first query the table on the most commonly entered field and if not found, on the least commonly entered. Job done.

Otherwise, you may need to do something like creating a unique filtered index as mentioned in this Stack Overflow answer:
https://stackoverflow.com/questions/3570805/unique-non-required-column-in-sql-server-2008


Local SSMS connected to VPN SQL Instance - update fails when VPN disconnects I connect to a VPN.

I open SSMS from my local computer.

I connect my local computer SSMS to the SQL instance on the VPN

I execute a long running SQL Update from my local computer, and during this a VPN error occurs.

All progress now with the SQL Update is lost.

Is there a way to use local computer to connect to remote SQL instance, and get the SQL Update to finish even though I get disconnected.

As far as I know, if the connection between the computer issuing the query batch and the server executing the batch is broken, the server will terminate the batch.

As the comments have noted, your best solutions are executing the command from a computer that won't be likely to lose its connection; executing the commands from the server itself is, unsurprisingly, about as safe as you can get.

As sp_BlitzErik recommended, executing your batch from a SQL Server job is good, when the query can safely be run as a fully unattended process works, and shouldn't consume too much of the server's resources.

If a process needs to be monitored above and beyond simple success/failure on completion, then the most common solution is probably a remote desktop connection. Depending on our organization's security restrictions, you could hopefully be allowed to remote into another computer on the same LAN as the server (if not the server itself, if it wouldn't constitute too much of a resource drain).

If your VPN connection breaks, the session on the remote server keeps running - you just lose your visibility into it. When you re-establish your VPN connection, you can reconnect to the remote server, and see what progress has happened while you were disconnected.

As noted, you want to be sure that your remote connection is not consuming resources needed by your server, and that all necessary security concerns are dealt with. You do not necessarily need to be on the server itself; as I said, any computer on the same network as the server will do, as long as it has the software you need to issue your query.

Over the course of the past few years, I have worked on a few projects that required long weekends hours every other weekend, or two out of three even, for months. Remote desktop connections from home made life much easier (and was much more appropriate than basically living in my cubicle :-) ).


$out not working in mongodb Sharding I am bit stuck in MongoDB while exporting my output to some other collection.

Scenario :

Actually, i want to query out my result to another collection which is successful in normal mongoDB( without Sharding).
But, when i Shard that same DB , Mongo DB is giving me error code 13435 which is cannot read from replica set and gives me below error set :

Error: command failed: {
    "ok" : 0,
    "errmsg" : "listIndexes failed: { ok: 0.0, errmsg: \"not master and slaveOk=false\", code: 13435, codeName: \"NotMasterNoSlaveOk\" }",
    "code" : 18631,
    "codeName" : "Location18631"
} : aggregate failed :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:16:14
assert.commandWorked@src/mongo/shell/assert.js:370:5
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1319:5
@(shell):1:1

Currently working on below versions of mongoDB :
Version : 3.4.6
OS : Windows Server 2012

The error message indicates that, in your cluster, there was shard what didn't have primary available and query went to secondary and with the current configuration, a query to secondary was not allowed.

So, what was a reason that there was no primary? An answer to that is the key to solve the problem.

Can you check (by login to every RS) that every sharded replica set have primary available? Second possibility is that config servers don't have up-to-date information of your shards configuration... But that should not be possible if your cluster works other ways fine.


Empty v$asm_diskgroup with sqlplus I have an Oracle Database instance installed under an OL7. It is a 12c2.
Oracle GI also installed and my database has some files stored in ASM.

I am  using sqlplus to connect to the database instance and query v$asm_diskgroup. My ORACLE_HOME points to the home of my database instance.

When I connect without specifying  a "@CONNECTION" (e.g using: sqlplus user/name), then the view v$asm_diskgroup returns no rows.
When I connect specifying a @TNSNAME (e.g: sqlplus user/name@TNSNAME ), then the view v$asm_diskgroup returns data correctly.

Also, when I connect using sql developer, from another machine, the v$asm_diskgroup returns the rows.

Why are  no v$asm_diskgroup rows returned when I use sqlplus without @CONNECTION, that is, the default logon?

You would need to connect to the ASM instance using a user ID that has the SYSASM system privilege:

connect scott as sysasm;

Balaz Papp's comment above is correct in the explanation why connected without a connect string is working. And his comment below is correct; any user could be granted query access to the view, when connected to the regular Oracle database instance.


How can I email query results formatted in html from MSSQL 2000 server? The only system SP I have is master.dbo.xp_smtp_sendmail and I can't make it work (or I just don't know how).  I cannot upgrade the DB at this time.  I'm trying something like this:

@body_text=N'SELECT TOP 100 * FROM payhistory WHERE entered BETWEEN
DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0)
AND DATEADD(wk,DATEDIFF(wk,7,GETDATE()),4) AND desk=020 ORDER BY entered
DESC

exec master.dbo.xp_smtp_sendmail
@server = 'server',
@from = 'administrator@mydomain.com',
@to = 'me@mydomain.com',
@subject = 'HTML Testing...',
@type = 'text/html',
@message = @body_text

I can do this easily in 2005 and up using "msdb.dbo.sp_send_dbmail" but I am stuck on 2000 due to the size of the DB for now.

Any help or suggestions would be greatly appreciated.

Thanks

It looks like you're missing an apostrophe from the end of the @body_text parameter.  This fixes that problem:

@body_text=N'SELECT TOP 100 * FROM payhistory WHERE entered BETWEEN
DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0)
AND DATEADD(wk,DATEDIFF(wk,7,GETDATE()),4) AND desk=020 ORDER BY entered
DESC';

exec master.dbo.xp_smtp_sendmail
    @server = 'server',
    @from = 'administrator@mydomain.com',
    @to = 'me@mydomain.com',
    @subject = 'HTML Testing...',
    @type = 'text/html',
    @message = @body_text;

Also, You might consider using xp_sendmail from SQL Server 2000.  The syntax is:

EXEC xp_sendmail @recipients = 'recipients'
      , @message = 'message'
      , @query = 'query'
      , @attachments = 'attachments  ;...n '
      , @copy_recipients = 'copy_recipients  ;...n '
      , @blind_copy_recipients = 'blind_copy_recipients  ;...n '
      , @subject = 'subject'
      , @type = 'type'
      , @attach_results = 'attach_value'
      , @no_output = 'output_value'
      , @no_header = 'header_value'
      , @width = width
      , @separator = 'separator'
      , @echo_error = 'echo_value'
      , @set_user = 'user'
      , @dbuse = 'database'

Of course, xp_sendmail requires an Outlook profile be setup on the server, which might be prohibitive.