What is the behavior of null/empty value in compound shard key? I have a shard key based on {Field1:1,Field2:1} where Field2 can
  be null. So if two documents have same value for Field1 and
  null/empty value of Field2 then are they considered to belong to
  the same shard key/non split-able??

In MongoDB 'null' is just another value so two documents that have the same values for field1 (some non-null) and same value for field2 (null) have the same value for the shard key and will always be in the same "chunk".

As per MongoDB BOL Here The shard key determines the distribution of the collection’s documents among the cluster’s shards. The shard key is either an indexed field or indexed compound fields that exists in every document in the collection.

MongoDB partitions data in the collection using ranges of shard key values. Each range defines a non-overlapping range of shard key values and is associated with a chunk.

MongoDB attempts to distribute chunks evenly among the shards in the cluster. The shard key has a direct relationship to the effectiveness of chunk distribution.

  Note: Once you shard a collection, the shard key and the shard key values are immutable; i.e. You cannot select a different shard key for
  that collection. You cannot update the values of the shard key fields.


intel p3600 SSD no increase in read speed - postgres rdbms vs hdd We run a custom application that produces analyzable data-sets for medical research.

the amount of data we have is about 600GB in form 3Billion rows distributed in 600+ tables it is hosted in postgres RDBMS and data is indexed and clustered.

Our application is read intensive(read only).

Usually it took about 7-8 hours on a normal computer running on a HDD to go through all this data and produce a dataset.

Recently we brought a Highend machine (intel i5 5960x, 32GB ram with an intel p3600 2TB SSD) and set up the database and our application.

I was amazed to find out it took almost the same amount of time that it used to take on the old machine to extract a dataset.

EDIT 1 : How the program works

The program is a Java Application

There are about 13Million patient's records in the database.
and each patient has 10000+ entries about their health/diagnosis/prescriptions etc. Hence the massive size.

What we do is look for patients with particular medical conditions(defined in a particular table) and pull out their medical history and other events(from different tables) from the database and do research on it.

The program is not CPU bound as we have very simple logic behind the program

I create a lot of java objects(at least 1 object per patient, which in turn is made up of lot of strings which hold values I pull out of database), hence we can say it is a bit memory intensive

Yes it is harddrive intensive as we read Billions of records, and that is why I though if i have a really fast harddrive I can get this running very fast. And hence brought a Intel P3600 2TB SSD

SAMPLE QUERY

These kind of queries are run on tables with 100,000+ of records for each patient 4-5 times per program execution.

SELECT Field1,Date
FROM schema.table1
WHERE Field1 IN ('aaaaaaa','bbbbbbb','ccccccc',...,'nnnnnnn' )
patientid = 'abcdefg'
AND Date <= 'xxxx-xx-xx'
order by Field1,Date;

Note: Field1,Date,patientID is indexed

EDIT 2:

**Explain Analyze Output:**

"GroupAggregate  (cost=51.75..51.78 rows=1 width=12) (actual time=1.112..1.112 rows=1 loops=1)"
"  Output: Field1, min(Date), Date"
"  Group Key: medical.Date, medical.Field1"
"  Buffers: shared hit=102 read=4 written=4"
"  ->  Sort  (cost=51.75..51.76 rows=2 width=12) (actual time=1.107..1.108 rows=1 loops=1)"
"        Output: Field1, Date"
"        Sort Key: medical.Date, medical.Field1"
"        Sort Method: quicksort  Memory: 25kB"
"        Buffers: shared hit=102 read=4 written=4"
"        ->  Index Scan using patmedmed_index on a6658.medical  (cost=0.42..51.74 rows=2 width=12) (actual time=1.016..1.089 rows=1 loops=1)"
"              Output: Field1, Date"
"              Index Cond: ((medical.patid = '00Nb'::bpchar) AND (medical.Field1 = ANY ('{C100111,C100112,C100z00,C101.00,C101000,C101100,C101y00,C101z00,C102.00,C102000,C102100,C102z00,C103.00,C103000,C103100,C103y00,C103z00,C104.00,C104.11,C104000,C10410 (...)"
"              Filter: (medical.Date <= '2015-01-01'::date)"
"              Buffers: shared hit=102 read=4 written=4"
"Planning time: 0.844 ms"
"Execution time: 1.248 ms"

I really need help

You should store your value for FIELD1 in a TABLE, instead of in a bunch of variables.  Make a three level index, a COVERED index specifically.

your IN clause is preposterous. I mean.. Learn how to Normalize.

THIS:

WHERE Field1 IN ('aaaaaaa','bbbbbbb','ccccccc',...,'nnnnnnn' )

is actually SLOWER than this:

SELECT Field1,Date
FROM schema.table1
WHERE Field1 = 'aaaaaaa'
patientid = 'abcdefg'
AND Date <= 'xxxx-xx-xx'
UNION
SELECT Field1,Date
FROM schema.table1
WHERE Field1 = 'bbbbbbb'
patientid = 'abcdefg'
AND Date <= 'xxxx-xx-xx'
UNION
SELECT Field1,Date
FROM schema.table1
WHERE Field1 = 'ccccccc'
patientid = 'abcdefg'
AND Date <= 'xxxx-xx-xx'
UNION
SELECT Field1,Date
FROM schema.table1
WHERE Field1 = 'nnnnnnn'
patientid = 'abcdefg'
AND Date <= 'xxxx-xx-xx'
order by Field1,Date


Does it make sense to order condition in where query? We have a clustered index in table Tbl with order of A,B,C.
Does it make senses to write a query to have

WHERE A = @a, B = @b, C = @c

or it'll be as same as

WHERE C = @c, B = @b, A = @a

Does the order of query make sense for clustered index?

It makes no difference: the optimiser evaluates the query and will work out predicate order for itself, matching a suitable index and all the other good stuff it does.

This is because SQL is "declarative" not "procedural": you say what you want, not how to do it.

It's nicer to read though...

Actually when we use the where clase then sql creates seperate pages for each where clause. then its make join based on AND , OR and then produce the output. So, here switching the where clause is not a matter.


SSIS Package - Error Exporting Data to Flat File - Invalid Character I have an SSIS package where I pull data from a 3rd party source into a CSV file.

I get this error:

"Data conversion failed. The data conversion for column "Description" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

Cannot copy or convert flat file data for column "Description".

SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Flat File Destination" (20300) failed with error code 0xC02020A0 while processing input "Flat File Destination Input" (20304). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure."

There seems to be an invalid character causing this issue...

I tried to remove it using this code but still an issue...

You have to deal with the unicode replacement character by calling it what it is:

Row.Description = Row.Description.Replace(NCHAR(65533), "a")

EDIT:

I apologize as I first gave you a pseudo-SQL Server solution, but with some additional research learned that SSIS behaves a little differently. I'm linking my source that gives multiple solutions, and for an answer will include the first. It seems that you could add a derived column component that would allow you to add a new column (e.g. strDescription from your table using the Type Cast operator DT_STR which will convert the unicode to string. You should then be able to use this derived column in place of the original column in you CSV destination. source Hope that helps!

In Csv Connection manager, check Unicode. This should work for you.


Size, audit and rowcount of a table in Vertica I need to find a compressed size, an audit and a row count for each table in some schemes.
For a compressed size and a row count I tried to use projection_storage:

SELECT anchor_table_schema,
   anchor_table_name,
   SUM(used_bytes),
   SUM(row_count)
FROM v_monitor.projection_storage
GROUP BY anchor_table_schema, anchor_table_name

But for empty table (0 rows) I got non-zero SUM(used_bytes) and SUM(row_count). So this way doesn't work properly.
And I calculate an audit by select audit('table_name') for each table, but it's very slowly.
So my questions:

Is there other way to find compressed size (and maybe a row count) of a table? Because I got 80000000 bytes instead of zero.
Is there more faster way to calculate an audit? I know vertica calculates audits every day for each table, but I can't find where results is stored.

Using below query, you will get the size of Vertica tables.

SELECT anchor_table_schema,
anchor_table_name,
SUM(used_bytes) / (1024 * 1024 * 1024) AS TABLE_SIZE_GB
FROM   v_monitor.projection_storage
GROUP  BY anchor_table_schema,
anchor_table_name
order  by sum(used_bytes) desc;

Check out the most important queries to check table size

https://www.orahow.com/2018/06/how-to-check-size-of-tables-in-vertica.html

Well, I suppose I can answer one of my questions.
In vertica, delete operations do not remove rows from physical storage. So if I had a table MyTable with 10 000 rows and I did delete MyTable;, then MyTable will have the same size, because its rows were just marked as deleted but were not really purged.

As a result, if I wanna get actual row count I can't use projection_storage table for it.


How to SELECT a column based on a condition on the column's name? I have the following table named MyTable:

+------+--------+--------+--------+
| Code | U_Col1 | U_Col2 | U_Col3 |
+------+--------+--------+--------+
|  123 |      1 |      2 |      3 |
|  456 |      4 |      5 |      6 |
+------+--------+--------+--------+

I want to write a query to give me the output based on the condition on the column's name. The names of the column are not fixed. They can be anything and come from another table.

CREATE FUNCTION [dbo].[udf_Value] (@Code VARCHAR(3), @Name VARCHAR(10))
RETURNS TABLE AS RETURN (

    SELECT *
    FROM MyTable T0
    WHERE T0.Code = @Code AND <columnname> = CONCAT('U_',@Name)
)

The expected output of

SELECT * FROM [dbo].[udf_Value]('123','Col3')

is 3.

How do I get this kind of a WHERE condition to work? I realize I would normally do it with dynamic SQL, but I want to do this in an inline table-valued function, which won't allow me doing it dynamically.

I tried an UNPIVOT:

SELECT X.Value
FROM (SELECT * FROM MyTable WHERE Code = @Code) T
UNPIVOT (
Value FOR Name IN (@Name)
) AS X

But there is something wrong with the syntax.

You can do this without dynamic SQL with some XML trickery.

Create XML of all values for each row and and use local-name() function to extract the value of the column you want.

Here is an example where you perhaps need to change the data types. I assumed integers.

create table dbo.T(Code int, U_Col1 int, U_Col2 int, U_Col3 int)

go

create function dbo.GetT
(
  @Name nvarchar(128),
  @Code int
)
returns table
as
return (
       select T.Code,
              C.X.value('(*[local-name() = sql:variable("@Name")]/text())[1]', 'int') as U_Col
       from dbo.T
         cross apply(select T.* for xml path(''), type) as C(X)
       where T.Code = @Code
       );
go

insert into dbo.T (Code, U_Col1, U_Col2, U_Col3) values
(123, 1, 2, 3),
(456, 4, 5, 6)

go

select * from dbo.GetT('U_Col1', 123);
select * from dbo.GetT('U_Col2', 456);

Note that the names of all columns in the table must be valid as element names in XML.

dbfiddle.uk

Well you could use something like this (I used sys.tables as example table), but this would break every index usage (except you add some more filters) and could result in a very bad performance on big and broad tables:

SELECT sub.*
  FROM sys.tables AS t
 CROSS APPLY (VALUES ('name',                            CAST(t.name COLLATE Latin1_General_CI_AS AS VARCHAR(255)))
                   , ('object_id',                       CAST(t.object_id                      AS VARCHAR(255)))
                   , ('principal_id',                    CAST(t.principal_id                   AS VARCHAR(255)))
                   , ('schema_id',                       CAST(t.schema_id                      AS VARCHAR(255)))
                   , ('parent_object_id',                CAST(t.parent_object_id               AS VARCHAR(255)))
                   , ('type',                            CAST(t.type                           AS VARCHAR(255)))
                   , ('type_desc',                       CAST(t.type_desc COLLATE Latin1_General_CI_AS                      AS VARCHAR(255)))
                   , ('create_date',                     CAST(t.create_date                    AS VARCHAR(255)))
                   , ('modify_date',                     CAST(t.modify_date                    AS VARCHAR(255)))
                   , ('is_ms_shipped',                   CAST(t.is_ms_shipped                  AS VARCHAR(255)))
                   , ('is_published',                    CAST(t.is_published                   AS VARCHAR(255)))
                   , ('is_schema_published',             CAST(t.is_schema_published            AS VARCHAR(255)))
                   , ('lob_data_space_id',               CAST(t.lob_data_space_id              AS VARCHAR(255)))
                   , ('filestream_data_space_id',        CAST(t.filestream_data_space_id       AS VARCHAR(255)))
                   , ('max_column_id_used',              CAST(t.max_column_id_used             AS VARCHAR(255)))
                   , ('lock_on_bulk_load',               CAST(t.lock_on_bulk_load              AS VARCHAR(255)))
                   , ('uses_ansi_nulls',                 CAST(t.uses_ansi_nulls                AS VARCHAR(255)))
                   , ('is_replicated',                   CAST(t.is_replicated                  AS VARCHAR(255)))
                   , ('has_replication_filter',          CAST(t.has_replication_filter         AS VARCHAR(255)))
                   , ('is_merge_published',              CAST(t.is_merge_published             AS VARCHAR(255)))
                   , ('is_sync_tran_subscribed',         CAST(t.is_sync_tran_subscribed        AS VARCHAR(255)))
                   , ('has_unchecked_assembly_data',     CAST(t.has_unchecked_assembly_data    AS VARCHAR(255)))
                   , ('text_in_row_limit',               CAST(t.text_in_row_limit              AS VARCHAR(255)))
                   , ('large_value_types_out_of_row',    CAST(t.large_value_types_out_of_row   AS VARCHAR(255)))
                   , ('is_tracked_by_cdc',               CAST(t.is_tracked_by_cdc              AS VARCHAR(255)))
                   , ('lock_escalation',                 CAST(t.lock_escalation                AS VARCHAR(255)))
                   , ('lock_escalation_desc',            CAST(t.lock_escalation_desc           AS VARCHAR(255)))
                   , ('is_filetable',                    CAST(t.is_filetable                   AS VARCHAR(255)))
                   , ('is_memory_optimized',             CAST(t.is_memory_optimized            AS VARCHAR(255)))
                   , ('durability',                      CAST(t.durability                     AS VARCHAR(255)))
                   , ('durability_desc',                 CAST(t.durability_desc COLLATE Latin1_General_CI_AS                AS VARCHAR(255)))
                   , ('temporal_type',                   CAST(t.temporal_type                  AS VARCHAR(255)))
                   , ('temporal_type_desc',              CAST(t.temporal_type_desc COLLATE Latin1_General_CI_AS             AS VARCHAR(255)))
                   , ('history_table_id',                CAST(t.history_table_id               AS VARCHAR(255)))
                   , ('is_remote_data_archive_enabled',  CAST(t.is_remote_data_archive_enabled AS VARCHAR(255)))
                   , ('is_external',                      CAST(t.is_external                    AS VARCHAR(255)))
                   ) AS sub(column_name, value)
 WHERE sub.column_name = 'name' AND sub.value = 'my_tbl'

PS: instead of CROSS APPLY you could also use UNPIVOT, but it's syntax is more compliciated. In both cases you should need a creator procedure for your function.


Unable to retrieve query plan of running query because of blocking I am trying to troubleshoot a long running query in a stored procedure that around once every month randomly runs for ever and never finishes.  Probably some plan caching issue.  But the problem is that I am unable to retrieve the query plan because of blocking in tempdb while the query is running.  I am using the below dmv's to try to retrieve the query plan

FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
cross apply sys.dm_exec_text_query_plan (plan_handle, statement_start_offset, statement_end_offset) s3
where object_name ( s2.objectid, s2.dbid) = 'sp_name'
ORDER BY statement_start_offset

The above query to retrieve the plan gets blocked by the long running  problem query because the above query needs a schema S lock on an object on tempdb.  They long running query queries and updates a temp table and has option recompile on it.

Has anyone come across this issue?

Try flipping this predicate from

where object_name ( s2.objectid, s2.dbid) = 'sp_name'

to something like

where object_id = object_id('sp_name')

You might get a simpler plan that aviods whatever resource is blocked in TempDb.

could you try this query:

SELECT
    blocker.[spid],
    blocker.[Database],
    blocker.[Login],
    blocker.[Hostname],
--  blocker.[HostPID],
--  blocker.[ProgramName],
    blocker.[LastWaitType],
    blocker.[Blocker],
    blocker.[Command] AS [BlockerCommand],
    blocker.[SQL_Text] AS [Blocker_SQL_Text],
    blocker.[QueryPlan] AS [Blocker_Query_Plan],
    spr.[spid],
    RTRIM(spr.[loginame]) AS [Login],
--  DB_NAME(spr.[dbid]) AS [Database],
    RTRIM(spr.[hostname]) AS [Hostname],
--  spr.[hostprocess] AS [HostPID],
--  RTRIM(spr.[program_name]) AS [ProgramName],
    RTRIM(spr.[lastwaittype]) AS [LastWaitType],
    spr.[blocked] AS [Blocker],
    spr.[cmd] AS [BlockedCommand],
    EST.[text] AS [Blocked_SQL_Text],
    blocker.[KillCommand] AS [BlockerKillCommand]
FROM [master].[dbo].[sysprocesses] spr
    JOIN [master].[sys].[dm_exec_connections] dc ON spr.[spid] = dc.[session_id]
    CROSS APPLY sys.dm_exec_sql_text(dc.[most_recent_sql_handle]) AS EST
    RIGHT JOIN (
        SELECT
            spr.[spid],
            DB_NAME(spr.[dbid]) AS [Database],
            RTRIM(spr.[loginame]) AS [Login],
            RTRIM(spr.[hostname]) AS [Hostname],
--          spr.[hostprocess] AS [HostPID],
--          RTRIM(spr.[program_name]) AS [ProgramName],
            RTRIM(spr.[lastwaittype]) AS [LastWaitType],
            spr.[blocked] AS [Blocker],
            spr.[cmd] AS [Command],
            EST.[text] AS [SQL_Text],
            EQP.[query_plan] AS [QueryPlan],
            'kill ' + CAST(spr.[spid] AS VARCHAR(10)) AS [KillCommand]
        FROM [master].[dbo].[sysprocesses] spr
            JOIN [master].[sys].[dm_exec_connections] dc ON spr.[spid] = dc.[session_id]
            LEFT JOIN [master].[sys].[dm_exec_query_stats] AS EQS ON dc.[most_recent_sql_handle] = EQS.[sql_handle]
            CROSS APPLY sys.dm_exec_sql_text(dc.[most_recent_sql_handle]) AS EST
            CROSS APPLY sys.dm_exec_query_plan(EQS.[plan_handle]) AS EQP ) AS blocker ON spr.[blocked] = blocker.[spid]
WHERE spr.[blocked] <> 0
--AND blocker.Blocker = 0       -- Only Running Blockers


DB2: Error when using NOT EXISTS with SYSIBM.DUAL in a batch query I have data in a java object as data1, data2.

data1 and data2 together forms a composite key in myTable where I want to insert the object.

The writing is happening as a batch. Like 10 insert statements are prepared using 10 objects and are executed as a batch.

I want to insert the above data with the constraint: data1 + data2 should not already be present in myTable i.e. data1 + data2 should be unique --- if unique then write else just ignore.

The query I am using is:

Insert into mySchema.myTable(column1, column2)
  select 'abc', '123'
  from SYSIBM.DUAL
  where not exists
        ( select 1
          from mySchema.myTable A
          where 'abc' = A.column1
          and '123' = A.column2
        )

Running above query independently for single set of data runs successfully.

However, while running in batch scenario I am getting "com.ibm.db2.jcc.b.ie: Non-atomic batch failure." error.

I think it has something to do with using SYSIBM.DUAL in batch scenario.

Code which is failing:

Insert Query:

Insert into mySchema.myTable(column1, column2)
  select ?, ?
  from SYSIBM.DUAL
  where not exists
        ( select 1
          from mySchema.myTable A
          where ? = A.column1
          and ? = A.column2
        )

Statement Setters:

ps.setString(1, item.getColumn1());
ps.setString(2, item.getColumn2());
ps.setString(3, item.getColumn1());
ps.setString(4, item.getColumn2());

where item is the java object holding the two columns to write.

Error is:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback;
bad SQL grammar

[Insert into mySchema.myTable(column1, column2) select ?,?
from SYSIBM.DUAL
where not exists
(select 1 from mySchema.myTable A where ?=A.column1 and ?=A.column2)];

nested exception is com.ibm.db2.jcc.b.ie: Non-atomic batch failure.

The batch was submitted, but at least one exception occurred on an
individual member of the batch.

Use getNextException() to retrieve the exceptions for specific batched elements.

Thanks,
Nik

This question is somewhat old, so you may have already figured out your problem, but if I may offer an alternative...

It may be better to use a MERGE statement in this situation (click the link for the publib entry):

MERGE INTO mySchema.myTable tab USING (
        VALUES ('abc', '123')
    ) AS merge (C1, C2)
    ON  tab.column1 = merge.C1
    AND tab.column2 = merge.C2
    WHEN MATCHED THEN
        IGNORE
    WHEN NOT MATCHED THEN
        INSERT (column1, column2)
        VALUES (merge.C1, merge.C2)

This will take the columns in the "fake" table merge, and compare them using the keys in the ON clause, and if there is not a match, then it will use the INSERT statement.  If there is a match, that row will just be ignored.

However, the availability of MERGE depends on your platform.  I'm fairly certain MERGE has been in Linux/Unix/Windows DB2 since v8 (although, you can only use PREPARE'd merges since 9.7), and it was added in z/OS DB2 in v9.1.  I don't know about the other platforms (AS/400, etc.).

select ?, ? from SYSIBM.DUAL is not valid syntax.  To my knowledge the question marks can only go in the where clause.


Repeating the same function in a query In the below query, there are repeated calculations such as the three calls to SUM(p.amount). Does MySQL re-calculate for each function call or is there some kind of memoization optimization under the hood? If not, how can this kind of query be optimized for maximum performance?

It seems like it would be faster after the first calculation to get the next one by the alias name, total_payemnts, but that just throws an error.

SELECT LEFT(c.last_name, 1) AS 'last_names',
    SUM(p.amount) AS 'total_payments',
    COUNT(p.rental_id) AS 'num_rentals',
    SUM(p.amount) / COUNT(p.rental_id) AS 'avg_pay'
FROM customer c
JOIN payment p ON p.customer_id = c.customer_id
GROUP BY LEFT(c.last_name, 1)
ORDER BY SUM(p.amount) DESC;

This query runs on the MySQL Sakila sample database.

A definite answer is really difficult. However, some trials might give us some hints.

We start with a few data:

CREATE TABLE customers
(
    customer_id integer PRIMARY KEY,
    last_name varchar(50) NOT NULL
) ;

CREATE TABLE payments
(
    rental_id integer not null,
    customer_id integer not null, --  references customer(customer_id),
    amount numeric(10,2) not null
) ;

INSERT INTO customers
VALUES
  (1, 'A - Customer 1'),
  (2, 'B - Customer 2'),
  (3, 'C - Customer 3'),
  (4, 'D - Customer 4') ;

INSERT INTO payments
VALUES
  (1, 1, 900.0),
  (2, 1, 800.0),
  (3, 1, 500.0),
  (4, 1, 900.0),
  (5, 2, 500.0),
  (6, 2, 500.0) ;

We first define a DETERMINISTIC (although it has side effects) function to log calls:

CREATE FUNCTION log_numeric(_n numeric(10,2), _counter character(2))
    RETURNS numeric(10,2)
    LANGUAGE SQL
    DETERMINISTIC
    BEGIN
      UPDATE log SET n = n + 1 WHERE counter = _counter ;
      RETURN _n ;
    END ;

We have previously defined a log table, to just store counts:

CREATE TABLE log
(
    counter character(2) primary key,
    n integer default 0
) ENGINE=MyISAM;
INSERT INTO log
VALUES
    ('x', 0),
    ('L', 0),
    ('T', 0),
    ('N', 0),
    ('L2', 0),
    ('T2', 0),
    ('N2', 0) ;

At this point, if MySQL were able to just perform the common subexpression elimination optimization (common for compilers), it would just make ONE call when we issue:

SELECT
    log_numeric(30, 'x') AS c1,
    log_numeric(30, 'x') AS c2 ;

Because log_numeric(30, 'x') would return the same value in both cases (because the function is deterministic).

In fact, it makes two calls to this function, as proven by watching counter 'x';

SELECT
    *
FROM
    log
WHERE
    counter = 'x' ;

[NOTE: Tested with MySQL 5.7.12]

It might be the case that MySQL has decided that the log_numeric function has some side effects, or that User Defined Functions do not work the same way as built-in functions. Built-in functions could be optimized, whereas UDF would not. In any case, if MySQL would have optimized the two expressions with just one call, it would be clear that this optimization is standard. As it did not, we can make an educated guess (which is not the same as a proof) and assume that the functions are called as many times as they're present.

The same can be done with the case posted:

SELECT
    log_char(LEFT(c.last_name, 1), 'L') AS last_names,
    log_numeric(SUM(p.amount), 'T') AS total_payments,
    log_numeric(COUNT(p.rental_id), 'N') AS num_rentals,
    log_numeric(SUM(p.amount), 'T') / log_numeric(COUNT(p.rental_id), 'N') AS avg_pay
FROM
    customers c
    JOIN payments p ON p.customer_id = c.customer_id
GROUP BY
    log_char(LEFT(c.last_name, 1), 'L')
ORDER BY
    log_numeric(SUM(p.amount), 'T') DESC;

And the results point to all calls being evaluated as many times as they appear.

You can check all theses tests at http://rextester.com/ZKS91815.

NOTE: For curiosity, postgreSQL (9.6.1) does a little better: it recognizes the ORDER BY and GROUP BY as "already computed" on the SELECT, but it doesn't optimize either for Common subexpression elimination. Room for improvement.

Alternative query avoiding repeating functions

If you're worried things are computed more than necessary, use:

SELECT
    last_names, total_payments, num_rentals,
    total_payments / num_rentals AS avg_pay
FROM
(
    SELECT
        log_char(LEFT(c.last_name, 1), 'L') AS last_names,
        log_numeric(SUM(p.amount), 'T') AS total_payments,
        log_numeric(COUNT(p.rental_id), 'N') AS num_rentals
    FROM
        customers c
        JOIN payments p ON p.customer_id = c.customer_id
    GROUP BY
        log_char(LEFT(c.last_name, 1), 'L')
) AS q0
ORDER BY
    total_payments DESC ;

I honestly don't think the difference will be much. The weight of the functions in the time spent for the whole query is probably (ver) small. It might be signficant if the functions were complex. Depending on how MySQL deals with Subqueries (that might be temporary tables), this could actually be worse.

This not happen like this. Query runs for a single row at one time and for functions like count,sum,group by and order by it create a temporary table

If there are repeated calculations then the in payment table contains multiple entries of customer table due to which this is happening.

you can write Sub Query for this:-

SELECT LEFT(c.last_name, 1) AS 'last_names',
    SUM(t.amount) AS 'total_payments',
    COUNT(t.rental_id) AS 'num_rentals',
    SUM(t.amount) / COUNT(t.rental_id) AS 'avg_pay'
FROM customer c
JOIN ( SELECT DISTINCT p.customer_id as customer_id,
              p.amount as amount,
              p.rental_id as rental_id
              FROM payments ) as t ON t.customer_id = c.customer_id
GROUP BY LEFT(c.last_name, 1)
ORDER BY total_payments DESC;


find all numbers that appear at least three times consecutively This is a question from leetcode online judge

I tried this

SELECT Num FROM Logs GROUP BY Num HAVING COUNT (Num) >1

but got runtime error!!

Anyhelp would be appreciated

First of all, your runtime error is based on COUNT (Num). It should be COUNT(Num)

SAMPLE DATA

mysql> DROP DATABASE IF EXISTS guru;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE DATABASE guru;
Query OK, 1 row affected (0.00 sec)

mysql> USE guru
Database changed
mysql> CREATE TABLE logs
    -> (id int not null auto_increment,
    -> num int not null,
    -> primary key (id));
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO logs (num) VALUES (1),(1),(1),(2),(1),(2),(2);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM logs;
+----+-----+
| id | num |
+----+-----+
|  1 |   1 |
|  2 |   1 |
|  3 |   1 |
|  4 |   2 |
|  5 |   1 |
|  6 |   2 |
|  7 |   2 |
+----+-----+
7 rows in set (0.00 sec)

YOUR QUERY EXECUTED

mysql> SELECT Num FROM Logs GROUP BY Num HAVING COUNT(Num) > 1;
+-----+
| Num |
+-----+
|   1 |
|   2 |
+-----+
2 rows in set (0.00 sec)

mysql>

LOOK AT THE QUESTION AGAIN

Write a SQL query to find all numbers that appear at least three times consecutively

From the output in your question, 2 does not occur three times consecutively.

I have an iterative solution using user-defined variables

MY PROPOSED QUERY

SET @curnum = -999;
SET @same = 1;
SET @group = 0;
SELECT DISTINCT num FROM
(
    SELECT grp,num,MAX(samecount) same FROM
    (
        SELECT
            (@group := @group + IF(@curnum=num,0,1)) grp,
            (@same  := IF(@curnum=num,@same+1,1)) samecount,
            (@curnum:=num),num
        FROM logs ORDER BY id
    ) AA GROUP BY grp,num HAVING same >= 3
) A;

Looks messy, right ? Here is is executed

MY PROPOSED QUERY EXECUTED

mysql> SET @curnum = -999;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @same = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @group = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT DISTINCT num FROM
    -> (
    ->     SELECT grp,num,MAX(samecount) same FROM
    ->     (
    ->         SELECT
    ->             (@group := @group + IF(@curnum=num,0,1)) grp,
    ->             (@same  := IF(@curnum=num,@same+1,1)) samecount,
    ->             (@curnum:=num),num
    ->         FROM logs ORDER BY id
    ->     ) AA GROUP BY grp,num HAVING same >= 3
    -> ) A;
+-----+
| num |
+-----+
|   1 |
+-----+
1 row in set (0.01 sec)

mysql>

WHY IT WORKS

Let's strip all the aggregation and subquery stuff and start with the innermost query

SET @curnum = -999;
SET @same = 1;
SET @group = 0;
SELECT
    (@group := @group + IF(@curnum=num,0,1)) grp,
    (@same  := IF(@curnum=num,@same+1,1)) samecount,
    (@curnum:=num),num
FROM logs ORDER BY id;

When you run this, here is the output

mysql> SET @curnum = -999;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @same = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @group = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->     (@group := @group + IF(@curnum=num,0,1)) grp,
    ->     (@same  := IF(@curnum=num,@same+1,1)) samecount,
    ->     (@curnum:=num),num
    -> FROM logs ORDER BY id
    -> ;
+------+-----------+----------------+-----+
| grp  | samecount | (@curnum:=num) | num |
+------+-----------+----------------+-----+
|    1 |         1 |              1 |   1 |
|    1 |         2 |              1 |   1 |
|    1 |         3 |              1 |   1 |
|    2 |         1 |              2 |   2 |
|    3 |         1 |              1 |   1 |
|    4 |         1 |              2 |   2 |
|    4 |         2 |              2 |   2 |
+------+-----------+----------------+-----+
7 rows in set (0.00 sec)

mysql>

Note the column samecount. When samecount reaches 3 or more, the num column has the value that has been repeated 3 or more times.

Adding the aggregation layer, you get this:

mysql> SET @curnum = -999;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @same = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @group = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT grp,num,MAX(samecount) same FROM
    -> (
    ->     SELECT
    ->         (@group := @group + IF(@curnum=num,0,1)) grp,
    ->         (@same  := IF(@curnum=num,@same+1,1)) samecount,
    ->         (@curnum:=num),num
    ->     FROM logs ORDER BY id
    -> ) AA GROUP BY grp,num HAVING same >= 3;
+------+-----+------+
| grp  | num | same |
+------+-----+------+
|    1 |   1 |    3 |
+------+-----+------+
1 row in set (0.00 sec)

mysql>

After the aggregation, it is possible that a number can be repeated multiple times later on.

Adding the DISTINCT layer on just NUM, you get the end result I said before.

QUERY AGAINST MORE DATA

Let's add more data

INSERT INTO logs (num) VALUES (1),(1),(1),(2),(1),(2),(2);
INSERT INTO logs (num) VALUES (7),(7),(7),(8),(8),(9),(5);
INSERT INTO logs (num) VALUES (5),(6),(11),(11),(11),(2),(2);
INSERT INTO logs (num) VALUES (4),(4),(4),(4),(4),(3),(3);
INSERT INTO logs (num) VALUES (1),(1),(1),(2),(1),(2),(2);

MY PROPOSED QUERY EXECUTED WITH MORE DATA

mysql> SET @curnum = -999;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @same = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @group = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT DISTINCT num FROM
    -> (
    ->     SELECT grp,num,MAX(samecount) same FROM
    ->     (
    ->         SELECT
    ->             (@group := @group + IF(@curnum=num,0,1)) grp,
    ->             (@same  := IF(@curnum=num,@same+1,1)) samecount,
    ->             (@curnum:=num),num
    ->         FROM logs ORDER BY id
    ->     ) AA GROUP BY grp,num HAVING same >= 3
    -> ) A;
+-----+
| num |
+-----+
|   1 |
|   7 |
|  11 |
|   4 |
+-----+
4 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

select distinct(a.Num) as ConsecutiveNums
from Logs a, Logs b,Logs c
where a.Id=b.Id+1 and a.Num=b.Num
and b.Id=c.Id+1 and b.Num=c.Num

From https://discuss.leetcode.com/topic/22606/simple-solution


Database Design - different objects with shared tagging My background is more in web programming rather than database administration, so please correct me if I'm using the wrong terminology here.  I'm trying to figure out the best way to design the database for an application I'll be coding.

The situation: I've got Reports in one table and Recommendations in another table.  Each Report can have many Recommendations.  I also have a separate table for Keywords (to implement tagging).  However, I want to have just one set of keywords that gets applied to both Reports and Recommendations so that searching on keywords gives you Reports and Recommendations as results.

Here's the structure I started out with:

Reports
----------
ReportID
ReportName

Recommendations
----------
RecommendationID
RecommendationName
ReportID (foreign key)

Keywords
----------
KeywordID
KeywordName

ObjectKeywords
----------
KeywordID (foreign key)
ReportID (foreign key)
RecommendationID (foreign key)

Instinctively, I feel like this isn't optimal and that I should have my taggable objects inherit from a common parent, and have that comment parent be tagged, which would give the following structure:

BaseObjects
----------
ObjectID (primary key)
ObjectType

Reports
----------
ObjectID_Report (foreign key)
ReportName

Recommendations
----------
ObjectID_Recommendation (foreign key)
RecommendationName
ObjectID_Report (foreign key)

Keywords
----------
KeywordID (primary key)
KeywordName

ObjectKeywords
----------
ObjectID (foreign key)
KeywordID (foreign key)

Should I go with this second structure?  Am I missing any important concerns here?  Also, if I do go with the second, what should I use as a non-generic name to replace "Object"?

Update:

I'm using SQL Server for this project.  It's an internal application with a small number of non-concurrent users, so I don't anticipate a high load.  In terms of usage, the keywords will likely be used sparingly.  It's pretty much just for statistical reporting purposes.  In that sense, whatever solution I go with will probably only affect any developers that will need to maintain this system down the line... but I figured it's good to implement good practices whenever I can.  Thanks for all the insight!

The problem with your first example is the tri-link table.  Is that going to require one of the foreign keys on either report or recommendations to always be NULL so that keywords link only one way or the other?

In the case of your second example, the joining from the base to the derived tables now may require use of the type selector or LEFT JOINs depending on how you do it.

Given that, why not just make it explicit and eliminate all the NULLs and LEFT JOINs?

Reports
----------
ReportID
ReportName

Recommendations
----------
RecommendationID
RecommendationName
ReportID (foreign key)

Keywords
----------
KeywordID
KeywordName

ReportKeywords
----------
KeywordID (foreign key)
ReportID (foreign key)

RecommendationKeywords
----------
KeywordID (foreign key)
RecommendationID (foreign key)

In this scenario when you add something else which needs to be tagged, you just add the entity table and the linkage table.

Then your search results look like this (see there is still type selection going on and turning them into generics at the object results level if you want a single results list):

SELECT CAST('REPORT' AS VARCHAR(15)) AS ResultType
    ,Reports.ReportID AS ObjectID
    ,Reports.ReportName AS ObjectName
FROM Keywords
INNER JOIN ReportKeywords
    ON ReportKeywords.KeywordID = Keywords.KeywordID
INNER JOIN Reports
    ON Reports.ReportID = ReportKeywords.ReportID
WHERE Keywords.KeywordName LIKE '%' + @SearchCriteria + '%'
UNION ALL
SELECT 'RECOMMENDATION' AS ResultType
    ,Recommendations.RecommendationID AS ObjectID
    ,Recommendations.RecommendationName AS ObjectName
FROM Keywords
INNER JOIN RecommendationKeywords
    ON RecommendationKeywords.KeywordID = Keywords.KeywordID
INNER JOIN Recommendations
    ON Recommendations.RecommendationID = RecommendationKeywords.ReportID
WHERE Keywords.KeywordName LIKE '%' + @SearchCriteria + '%'

No matter what, somewhere there is going to be type selection and some kind of branching going on.

If you look at how you would do this in your option 1, it's similar but with either a CASE statement or LEFT JOINs and a COALESCE.  As you expand your option 2 with more things being linked, you have to keep adding more LEFT JOINs where things are typically NOT being found (an object that is linked can only have one derived table which is valid).

I don't think there is anything fundamentally wrong with your option 2, and you could actually make it look like this proposal with a use of views.

In your option 1, I have some difficulty seeing why you opted for the tri-link table.

In my experience this is what you can do.

Reports
----------
Report_id (primary_key)
Report_name

Recommendations
----------------
Recommendation_id (primary key)
Recommendation_name
Report_id (foreign key)

Keywords
----------
Keyword_id (primary key)
Keyword

And for the relation between keywords, reports and recommendations you can do one of two options:
Option A:

Recommendation_keywords
------------------------
Recommendation_id(foreign_key)
keyword_id (foreign_key)

This allows for a direct relation from Reports to Recommendations, to Keywords and finally to Keywords.
Option B:

object_keywords
---------------
Object_id
Object_type
Keyword_id(foreign_key)

The option A is the easier to apply and manage since it will have the constratints of the database to handle the data integrity and won't allow the insertion of invalid data.

The option B although requires a little more work since you will need to code the identification of the relationship. Is more flexible in the long run, if by any chance at some point in the future you need to add keywords to another item other than the report or the recommendation you just need to add the identification and use directly the table.


Stored Procedure Resultset Row Count and Resultset Size in Bytes/KBs Is there any way to get total row count and size of result-set generated by a stored procedure execution?

@@ROWCOUNT is a good option if its already added in each stored procedure but I just want something like all RPC calls with in specific time period, with Row count and Size (in Bytes or KBs) for each call without modifying any stored procedure.

The question is unanswerable as asked since stored procedures do not produce result sets. Only (certain) statements produce result sets. Executing procedures may produce none, one or more results sets depending on the statements contained and the branches executed.

For statements the row count will be tracked in sys.dm_exec_query_stats.

Result sets have no size (in bytes or KB), size is an attribute pertinent only to encoding and representation. A sensible question would be to ask how many bytes are transferred between the client and the server, and this can be tracked by transport performance counters.


what does this query do? Can you explain me the bold part?

    select   company_code,
             founder,
             (select count(distinct lead_manager_code)
              from Lead_Manager
              where  company_code = c.company_code
             )
    from     Company c
    order by company_code;

The answer is in the bold portion of my answer.

Starters

You have a table of Lead Managers (Lead_Managers) and a table for Companies (Company). A Lead Manager can be in multiple companies and can have multiple Lead Manager roles in one company.

Inner Query

The inner query retrieves the DISTINCT number of (COUNT) Lead Manager's  Codes (Lead_Manager.company_code) where the Lead_Manager's company code matches ...

Outer Query

... the company code (c.company_code) in the Company (Company c) table.

Result

The result should be a list of Company Codes, Founders and distinct count of Lead Managers for each given company. The Distinct will only select unique Lead Manager', or put another way: Even if a lead manager has multiple Lead Manager Roles in a company only 1 count will be made for that company.

As an aside, this is a short-hand. You never have to write this like this, and it's always a bad idea, imho, because it's correlated with the parent query (excessively complex for SELECT), bloats the SELECT-clause and violates the basic structure of the query, and is often likely to be slower.

select   company_code,
         founder,
         (select count(distinct lead_manager_code)
          from Lead_Manager
          where  company_code = c.company_code
         )
from     Company c
order by company_code;

Is better written with an LEFT OUTER JOIN

SELECT   company_code,
         founder,
         t.count
FROM     Company c
LEFT OUTER JOIN (
  select company_code, count(distinct lead_manager_code)
  FROM lead_manager
  GROUP BY company_code
) AS t
  USING (company_code)
ORDER BY company_code;

Speed

Analysis with PostgreSQL should be applicable on most databases.

Multirow

The VIRTUAL COLUMN method is likely to result in extra seq scans as the planner won't know to bundle them up.  Worth comparing speeds.. 145 ms for the VIRTUAL COLUMN method

EXPLAIN ANALYZE
  SELECT *, (
    SELECT count(distinct x)
    FROM foobar AS f2
    WHERE f1.x = f2.x
  )
  FROM foobar AS f1;
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Seq Scan on foobar f1  (cost=0.00..17527.50 rows=1000 width=4) (actual time=0.252..145.119 rows=1000 loops=1)
   SubPlan 1
     ->  Aggregate  (cost=17.50..17.51 rows=1 width=4) (actual time=0.144..0.144 rows=1 loops=1000)
           ->  Seq Scan on foobar f2  (cost=0.00..17.50 rows=1 width=4) (actual time=0.065..0.140 rows=1 loops=1000)
                 Filter: (f1.x = x)
                 Rows Removed by Filter: 999
 Planning time: 0.108 ms
 Execution time: 145.268 ms

Yet 2 ms for the LEFT OUTER JOIN method

EXPLAIN ANALYZE
  SELECT x, f2.count
  FROM foobar
  LEFT OUTER JOIN (
    SELECT x, count(*)
    FROM foobar
    GROUP BY x
  ) AS f2 USING (x);
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Hash Right Join  (cost=47.50..81.25 rows=1000 width=12) (actual time=1.246..2.154 rows=1000 loops=1)
   Hash Cond: (foobar_1.x = foobar.x)
   ->  HashAggregate  (cost=20.00..30.00 rows=1000 width=4) (actual time=0.750..1.100 rows=1000 loops=1)
         Group Key: foobar_1.x
         ->  Seq Scan on foobar foobar_1  (cost=0.00..15.00 rows=1000 width=4) (actual time=0.009..0.155 rows=1000 loops=1)
   ->  Hash  (cost=15.00..15.00 rows=1000 width=4) (actual time=0.484..0.484 rows=1000 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 44kB
         ->  Seq Scan on foobar  (cost=0.00..15.00 rows=1000 width=4) (actual time=0.015..0.185 rows=1000 loops=1)
 Planning time: 0.262 ms
 Execution time: 2.294 ms

table foobar created with

CREATE TABLE foobar AS SELECT x FROM generate_series(1,1000) AS x;

Single-row

Even with the single-row case, with PostgreSQL 9.5 the left outer join is faster. I just mean to say it doesn't have to be that way.

EXPLAIN ANALYZE
  SELECT *, (
    SELECT count(distinct x)
    FROM foobar AS f2
    WHERE f1.x = f2.x
  )
  FROM foobar AS f1;
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Seq Scan on foobar f1  (cost=0.00..2.04 rows=1 width=4) (actual time=0.043..0.044 rows=1 loops=1)
   SubPlan 1
     ->  Aggregate  (cost=1.01..1.02 rows=1 width=4) (actual time=0.025..0.026 rows=1 loops=1)
           ->  Seq Scan on foobar f2  (cost=0.00..1.01 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=1)
                 Filter: (f1.x = x)
 Planning time: 0.114 ms
 Execution time: 0.097 ms
(7 rows)

EXPLAIN ANALYZE
  SELECT x, f2.count
  FROM foobar
  LEFT OUTER JOIN (
    SELECT x, count(*)
    FROM foobar
    GROUP BY x
  ) AS f2 USING (x);
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=1.01..2.06 rows=1 width=12) (actual time=0.026..0.027 rows=1 loops=1)
   Join Filter: (foobar.x = foobar_1.x)
   ->  Seq Scan on foobar  (cost=0.00..1.01 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=1)
   ->  HashAggregate  (cost=1.01..1.02 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=1)
         Group Key: foobar_1.x
         ->  Seq Scan on foobar foobar_1  (cost=0.00..1.01 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)
 Planning time: 0.168 ms
 Execution time: 0.088 ms
(8 rows)

table foobar created with

CREATE TABLE foobar AS SELECT x FROM generate_series(1,1) AS x;

Conclusion

It's just too easy to mess this up. It's always slower in my experience with PostgreSQL, but that doesn't always have to be the case with other databases.


Oracle Equivalent of SQL Server's Bitwise Operators I am trying to figure out all of the common bit-wise operations in Oracle.

In SQL Server we have some very simple bit-wise operators to use against a bit-wise value:

& - Evaluates if bit exists select 10 & 2   /* result=2 */
| - Add Bit (if doesn't exist) select 10 | 2   /* result=10 */
&~ - Remove Bit (if exists) select 10 &~ 2  /* result=8 */
^ - Toggle Bit (remove if exists, adds if doesn't) select 10 ^ 2  /* result = 8 */

In Oracle, I know we have a built-in bitand() function which is more or less equivalent to SQL Server &, and I have built an Oracle bit-wise OR function to mimic SQL server's bit-wise OR operation (|) as follows:

CREATE OR REPLACE FUNCTION BITOR (x IN NUMBER, y IN NUMBER) RETURN NUMBER AS
    BEGIN
       RETURN x + y - BITAND(x,y);
    END;

My question is: how can I achieve the SQL Server &~ and the ^ operations in Oracle?

Have you checked the manual?, it should help with your question to a large extent.


MySQL - Unix timestamp from columns with hour, day, month I have MySQL table with columns (day, month, hour). Is it possible to build unix timestamp from this within the query itself?

I am looking for something like this:

SELECT unix(2016, a.month, a.day, a.hour, 00, 00) as d
FROM a
WHERE d > 456456 AND d < 789789

Values 456456 and 789789 is provided from an application. I know, I can create unix timestamp from STR_TO_DATE, but then I have to create it for every comparison in WHERE clause as well, which I am afraid will be slow.

Create table:

CREATE TABLE `a` (
  `data_a` smallint(6) NOT NULL DEFAULT '0',
  `hour` tinyint(4) NOT NULL,
  `day` tinyint(4) NOT NULL,
  `month` tinyint(4) NOT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

MySQL version 5.0

Instead of transforming the columns in the WHERE clause, do so with the constants given by the application. Those calculations only have to be done once.

We can write the condition in a short, compact form:

SELECT STR_TO_DATE(...)
FROM a
WHERE  (2016, a.month, a.day, a.hour, 0, 0)
      >
      (  YEAR(FROM_UNIXTIME(456456)),
        MONTH(FROM_UNIXTIME(456456)),
          DAY(FROM_UNIXTIME(456456)),
         HOUR(FROM_UNIXTIME(456456)),
       MINUTE(FROM_UNIXTIME(456456)),
       SECOND(FROM_UNIXTIME(456456))
      )
  AND  (2016, a.month, a.day, a.hour, 0, 0)
      <
      (  YEAR(FROM_UNIXTIME(789789)),
        MONTH(FROM_UNIXTIME(789789)),
          DAY(FROM_UNIXTIME(789789)),
         HOUR(FROM_UNIXTIME(789789)),
       MINUTE(FROM_UNIXTIME(789789)),
       SECOND(FROM_UNIXTIME(789789))
      )

or in a long form, which has much more chances of using indexes effectively, in versions before 5.7:

SELECT STR_TO_DATE(...)
FROM a
WHERE ((2016 > YEAR(FROM_UNIXTIME(456456)))
    OR (2016 = YEAR(FROM_UNIXTIME(456456))
        AND a.month > MONTH(FROM_UNIXTIME(456456)))
    OR (2016 = YEAR(FROM_UNIXTIME(456456))
        AND a.month = MONTH(FROM_UNIXTIME(456456))
        AND a.day > DAY(FROM_UNIXTIME(456456)))
    OR (2016 = YEAR(FROM_UNIXTIME(456456))
        AND a.month = MONTH(FROM_UNIXTIME(456456))
        AND a.day = DAY(FROM_UNIXTIME(456456))
        AND a.hour > HOUR(FROM_UNIXTIME(456456))))
AND   ((2016 < YEAR(FROM_UNIXTIME(789789)))
    OR (2016 = YEAR(FROM_UNIXTIME(789789))
        AND a.month < MONTH(FROM_UNIXTIME(789789)))
    OR (2016 = YEAR(FROM_UNIXTIME(789789))
        AND a.month = MONTH(FROM_UNIXTIME(789789))
        AND a.day < DAY(FROM_UNIXTIME(789789)))
    OR (2016 = YEAR(FROM_UNIXTIME(789789))
        AND a.month = MONTH(FROM_UNIXTIME(789789))
        AND a.day = DAY(FROM_UNIXTIME(789789))
        AND a.hour < HOUR(FROM_UNIXTIME(789789)))
    OR (2016 = YEAR(FROM_UNIXTIME(789789))
        AND a.month = MONTH(FROM_UNIXTIME(789789))
        AND a.day = DAY(FROM_UNIXTIME(789789))
        AND a.hour = HOUR(FROM_UNIXTIME(789789))
        AND 0 < MINUTE(FROM_UNIXTIME(789789))+SECOND(FROM_UNIXTIME(789789))))

This will also allow MySQL to use indexes on this query, if available. If not already done, you should create an index as (month, day, hour) (in this order!).


Before and After update trigger on same table I have a table with 4 columns qty, qtydiff, price and value.

I want a after update trigger that fires when qty row values change (more than one rows).

The Before trigger should read and store the old values from qty and After trigger should subtract old value from new value on qty column, get the difference value in qtydiff and multiply that with price column and update the result in the value column.

The code below is just a concept code that I have not tested yet, I am not sure it would work or not but is there a way to get before and after update to work in the same trigger?

 CREATE TRIGGER [dbo].[Price_Modified]
       ON [dbo].[STOCK]
       BEFORE UPDATE ON STOCK
    AS BEGIN
        SET NOCOUNT ON;
        IF UPDATE (Qty)
        BEGIN
            SELECT qty
            FROM STOCK
        END
AFTER UPDATE
    AS BEGIN
        SET NOCOUNT ON;
        IF UPDATE (Qty)
        BEGIN
            UPDATE Stock
            SET value = price * qtydiff
            FROM STOCK
            WHERE qtydiff = oldvalue - newvalue
        END
    END

There is no BEFORE trigger in SQL Server. An INSTEAD OF trigger can be used to provide similar functionality but the trigger code would need to perform the UPDATE.

However, an AFTER trigger can be used here by using the INSERTED (new) and DELETED (old) virtual tables to get the values needed for the calculation. The example below assumes a primary key column named StockID with a value that cannot be changed.

CREATE TRIGGER [dbo].[Price_Modified]
    ON [dbo].[STOCK]
    AFTER UPDATE
AS
SET NOCOUNT ON;
IF UPDATE (Qty)
BEGIN
    UPDATE s
    SET value = new.price * (new.Qty - old.Qty)
    FROM STOCK AS s
    JOIN inserted AS new ON new.StockID = s.StockID
    JOIN deleted AS old ON old.StockID = s.StockID
    WHERE new.Qty <> old.Qty;
END;

GO

I want to create trigger how i can?
create trigger before updating [dbo].[MGT_FORECAST_file]

if new.PROCESSING_STATUS_ID == 12 (meaning it's complete) || new.IS_DELETED == true(meaning it's complete)
then set DEPLOYMENT_STATUS_ID = 3


Enhance performance of update query I have a query that is running against a table with 50 million rows.  The phone number is stored as a CHAR(10) and an ETL process is changing the phone number if it has certain values.  We are seeing poor performance of this query and was wondering if anyone has any suggestions on improving performance.  No index currently exists with the phone number.

UPDATE Table SET phone='' WHERE ( phone IS NULL
              OR phone = ''
              OR phone = '0000000000'
              OR phone = '0'
              OR phone like '111%'
              OR phone like '222%'
              OR phone like '333%'
              OR phone like '444%'
              OR phone like '555%'
              OR phone like '666%'
              OR phone like '777%'
              OR phone like '888%'
              OR phone like '999%'
              OR phone like '800%'
              OR phone like '900%'
            )

It depends on what you mean by improve performance.  You can't make that query faster, it needs to scan the table.  But you can keep it from using too many resources and blocking other processes by chunking it, which is essentially iterating through batches of  rows to perform multiple smaller updates.

There must be some option to just write it to the target table instead of updating in place.

You could also forego the scrub altogether and use a non-persisted computed column in the target database to reformat the phone number if you don't need to index it.

Usually when performing DML operations, especially ones that affect a high percentage of rows, it is better to disable the indexes, and rebuild them afterwards. This is because most of them time, the engine is trying to reorganize the index after each DML operation. For INSERT and DELETE, it is obvious that a clustered index will be rebuilt after each row. With UPDATEs you might cause page splits, which again, will force an index rebuild.

Back to your issue, I don't think an index will solve the problem, as long as your condition is set on the updated column, so in the end, all your rows must be tested. In fact, it is better to work directly on the table rather than making roundtrips to the index.

Is there a way to do updating as a nightly job? I'm afraid to you might be limited by the hardware resources. If you are sure that that's not the case, please give me some more details.


Compare each column in a row and return an error if any of them is different I'm attempting to find any identifiers with any values that are different in the same row.

+------------+--------+--------+--------+--------+
| Identifier | Value1 | Value2 | Value3 | Value4 |
| f001       | a1     | a2     | a2     | a2     |
| f002       | a4     | a4     | a4     | a5     |
| f003       | a2     | a2     | a2     |        |
| f004       | a1     | a1     | a1     | a1     |
| f002       | a9     |        |        |        |
+------------+--------+--------+--------+--------+

For example,

The first identifier should return "MisMatch",
The second identifier should return "MisMatch",
The third identifier should return "NoIssue",
The fourth identifier should return "Mismatch",
The fifth identifier should return "NoIssue",

Any help would be great, I'm stuck at this point.

Some lines have hundreds of columns, while others only have one. I'm hoping to be able to locate any line that contains any mismatch.

I'm using SQL Server 2008 R2.

I see this question is tagged both Excel and SQL Server.

In Excel you could use the approach from here

The formula in cell F2 above is

=IF(SUM(IF(FREQUENCY(IF(LEN(B2:E2)>0,MATCH(B2:E2,B2:E2,0),""), IF(LEN(B2:E2)>0,MATCH(B2:E2,B2:E2,0),""))>0,1))>1,"MisMatch","Match")

Pay attention to the note in the linked article

  The formulas in this example must be entered as array formulas. Select
  each cell that contains a formula, press F2, and then press
  CTRL+SHIFT+ENTER.

Curly braces will then appear around the formula in the formula bar

Another (SQL Server) way (credit to Geoff Patterson for improvements) would be

SELECT T.Identifier,
       CASE
         WHEN min_val <> max_val
           THEN 'MisMatch'
         ELSE 'NoIssue'
       END
FROM   @T T
       CROSS APPLY (SELECT MIN(Val),
                      MAX(Val)
                    FROM   (VALUES (Value1),
                                   (Value2),
                                   (Value3),
                                   (Value4)) V(Val)) V(min_val, max_val)

It should work well for the "hundreds of columns" mentioned.

Though it introduces some extra complexity into the plan compared to just a simple compute scalar.

Would this help?

    select test.*,
           case when min(v1,v2,v3,v4) = '' then 'NoIssue'
           else 'MisMatch'
       end
    from   test
    where  max(v1,v2,v3,v4) <> min(v1,v2,v3,v4)


How to avoid deadlock while updating 2 rows in MySQL This is a interview problem:

There is a perl program that updates the database, and it could run in different processes.
One process may execute a transaction like:
update row A -> update row B -> commit

The other process may execute a transaction like:
update row B -> update row A -> commit

The rows need to be updated is selected in the program before the transaction.

I was asked how to avoid deadlocks without changing the transaction logic (I cannot commit after updating A and commit again after updating B).
They want me to propose at least 3 different methods. What I know is to use select .. for update when selecting row A and B. Can anyone help to suggest some other methods?

I don't like the interview question because in addition to the (presumably) correct SELECT ... FOR UPDATE on all rows, I suspect the other answers they're looking for may be something theoretically accurate but not necessarily the best idea (especially in a high-concurrency environment), such as:

use table level locking to force serialization of the transactions
use a semaphore mechanism such as GET_LOCK() and RELEASE_LOCK() to serialize the transactions with named locks... admittedly, this is a feature in MySQL that I really do find very useful (such as for preventing recurring scheduled events that run longer than expected from firing up parallel instances, and serializing the execution of unrelated low-priority background/maintenance jobs to keep resource utilization by the maintenance jobs at a minimum) but that I do not normally use for serializing live transactions.

@Chris Travers has already given what may be more useful real-world answers than these, but from my reading of the question, standardizing the order (#2) and using stored procedures (#3) aren't permitted modifications, perhaps, by definition... so I offer the speculation, above.

You should choose the correct transaction isolation level.
Check the

READ UNCOMMITTED,
READ COMMITTED,
REPEATABLE READ,
SERIALIZABLE

isolation levels of MySQL.
http://dev.mysql.com/doc/refman/5.6/en/dynindex-isolevel.html


Can I delegate SQL Agent rights without granting sysadmin rights? I'd like to give a user the ability to administer SQL Agent jobs.  According to the doc page, I can do this by granting the "appropriate" roles on msdb.  I've granted all three SQLAgent% roles, but the user gets this error when (as a test) trying to rename a job:

Only members of sysadmin role are allowed to update or delete jobs owned by a different login.
(Microsoft SQL Server, Error: 14525)

This question is similar, but no answer there addresses SQL Agent.  Is the sysadmin role the only solution, in MS SQL 2K8?

Unfortunately, administration of Agent jobs can currently only be done by members of the sysadmin role.  The relevant documentation is found on MSDN.  You'll note that the three Agent roles (User, Reader, and Operator) have different levels, but ultimately a user without sysadmin can only edit/manage jobs that user owns and not jobs created by other users.

Page https://docs.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed-database-roles says "A user must be a member of one of these fixed database roles or a member of the sysadmin fixed server role to use SQL Server Agent." (emphasis mine)

So you SHOULD not need to be a member of the sysadmin role to see SQL Server Agent, which contradicts what Mike Fal said above. Maybe it's a SQL Server or SSMS bug...


Restore SQL Server database using Windows Powershell 3.0 I'm trying to restore a SQL Server database with a PowerShell script, but I'm having problems.

Here is the error I'm getting:

  Exception calling "SqlRestore" with "1" argument(s): "Restore failed
  for Server 'WUSFK250042-OLU\SQLSERVER2008R2'. " At line:48 char:1
  + $smoRestore.SqlRestore($server)

Here is my code:

#clear screen
cls

#load assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
#Need SmoExtended for backup
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

$backupFile = "C:\SafewayRT\SafewayRTFUll.bak"

#we will query the database name from the backup header later
$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "WUSFK250042-OLU\SQLSERVER2008R2"
$backupDevice = New-Object("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") ($backupFile, "File")
$smoRestore = new-object("Microsoft.SqlServer.Management.Smo.Restore")

#restore settings
$smoRestore.NoRecovery = $false;
$smoRestore.ReplaceDatabase = $true;
$smoRestore.Action = "Database"
$smoRestorePercentCompleteNotification = 10;
$smoRestore.Devices.Add($backupDevice)

#get database name from backup file
$smoRestoreDetails = $smoRestore.ReadFileList($server)

#display database name
"Database Name from Backup Header : " +$smoRestoreDetails.Rows[0]["Safeway_LogixRT"]

#give a new database name
$smoRestore.Database =$smoRestoreDetails.Rows[0]["Safeway_LogixRT"]

#specify new data and log files (mdf and ldf)
$smoRestoreFile = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
$smoRestoreLog = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")

#the logical file names should be the logical filename stored in the backup media
$smoRestoreFile.LogicalFileName = $smoRestoreDetails.Rows[0]["Safeway_LogixRT"]
$smoRestoreFile.PhysicalFileName = $server.Information.MasterDBPath + "\" + $smoRestore.Database + "_Data.mdf"
$smoRestoreLog.LogicalFileName = $smoRestoreDetails.Rows[0]["Safeway_LogixRT"] + "_Log"
$smoRestoreLog.PhysicalFileName = $server.Information.MasterDBLogPath + "\" + $smoRestore.Database + "_Log.ldf"
$smoRestore.RelocateFiles.Add($smoRestoreFile)
$smoRestore.RelocateFiles.Add($smoRestoreLog)

#restore database
$smoRestore.SqlRestore($server)

You're going to have to get into that exception to see what the problem is. Powershell keeps track of errors for you in a system variable called $Error, which is a heap (i.e. index 0 is the most recent error). I usually do something like this:

$e = $error[0]
$e.Exception
$e.Exception.InnerException
$e.Exception.InnerException.InnerException
...

Until I find the real error. It could be a problem with the actual restore (i.e. database with that name already exists, bad paths for the physical files, etc). But until you get to the root of that exception you won't know!

From http://www.morgantechspace.com/2014/11/Powershell-script-to-Backup-and-Restore-SQL-Database.html

# Set SQL Server instance name
$sqlName= "localhost\SQLExpress"

# Set new or existing databse name to restote backup
$dbname= "MorganDB"

# Set the existing backup file path
$backupPath= "D:\SQLBackup\MorganDB.bak"

#Load the required assemlies SMO and SmoExtended.
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null

# Connect SQL Server.
$sqlServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlName

# Create SMo Restore object instance
$dbRestore = new-object ("Microsoft.SqlServer.Management.Smo.Restore")

# Set database and backup file path
$dbRestore.Database = $dbname
$dbRestore.Devices.AddDevice($backupPath, "File")

# Set the databse file location
$dbRestoreFile = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile")
$dbRestoreLog = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile")
$dbRestoreFile.LogicalFileName = $dbname
$dbRestoreFile.PhysicalFileName = $sqlServer.Information.MasterDBPath + "\" + $dbRestore.Database + "_Data.mdf"
$dbRestoreLog.LogicalFileName = $dbname + "_Log"
$dbRestoreLog.PhysicalFileName = $sqlServer.Information.MasterDBLogPath + "\" + $dbRestore.Database + "_Log.ldf"
$dbRestore.RelocateFiles.Add($dbRestoreFile)
$dbRestore.RelocateFiles.Add($dbRestoreLog)

# Call the SqlRestore mathod to complete restore database
$dbRestore.SqlRestore($sqlServer)

Write-Host "...SQL Database"$dbname" Restored Successfullyy..."


Sort a varchar type column (Alphanumeric Values) In my table VARCHAR data type column called CartonNo, that contain either numbers or number with letter it depends on. I want it to be sorted numerically, this would be an acceptable ascending sort order.

Issue No. 1

For example, contains like following data

1-2
1
3-5
6
1-2A
6-12
7-8
9-12D
9-12
17
9-12B
19
20-30
15-18

Now I want to order the data as below

1
1-2
1-2A
3-5
6
6-12
7-8
9-12
9-12B
9-12D
15-18
17
19
20-30

Issue No. 2

the bellow value How to sort alphanumeric?

PT15-8-2

PT15-12-10

PT15-12-3

PT15-12-572-575

PT15-12-31

PT11-96

PT11-133-36

PT11-133

PT16-136

PT8-644

PT09-655

PT9-655

PT12-752

PT14-752

PT13-752

PT14-753

PT10-778

PT13-977-978

PT13-979-988

Can anyone please tell me how should I write the query?

i am assuming before - you would have integer value always

declare @temp table
(val nvarchar(99))
insert into @temp values ('1-2'  )
insert into @temp values ('3-5'  )
insert into @temp values ('1-2A')
insert into @temp values ('6-12')
insert into @temp values ('6-7')
insert into @temp values ('6-12D')
insert into @temp values ('20-30')
insert into @temp values ('15-18')
--insert into @temp values ('3')
--insert into @temp values ('53')

QUERY

select * from @temp
order by CAST(LEFT(val,CHARINDEX('-',val)-1)  AS INT)

Exactly Ordered

select * from @temp
order by CAST(LEFT(val,CHARINDEX('-',val)-1)  AS INT)
,CAST(LEFT(RIGHT(val,LEN(val)-CHARINDEX('-',val)), patindex('%[^0-9]%', RIGHT(val,LEN(val)-CHARINDEX('-',val))+'.') - 1)  AS INT),
RIGHT(val,LEN(val)-CHARINDEX('-',val))

after run second query the output would be

UPDATE AFTER COMMENT: sometime varchar val won't come (3-5) or (1-12A) instead of whole number 3 or 5... at the time the above answer getting an error... how to correct way to sort.

-- DECLARE TABLE
DECLARE @temp TABLE
(val nvarchar(99))

--INSERT INTO TABLE
INSERT INTO @temp VALUES
('1-2'), ('1'), ('3-5'), ('6'), ('1-2A'), ('6-12'), ('7-8'), ('9-12D'), ('9-12'), ('17'), ('9-12B'), ('19'), ('20-30'), ('15-18')

-- QUERY
SELECT * FROM @temp
ORDER BY
CASE
    WHEN ISNUMERIC(val) = 0 THEN CAST(LEFT(val,CHARINDEX('-',val)-1)  AS INT)
    WHEN ISNUMERIC(val) = 0 THEN CAST(LEFT(RIGHT(val,LEN(val)-CHARINDEX('-',val)), patindex('%[^0-9]%', RIGHT(val,LEN(val)-CHARINDEX('-',val))+'.') - 1)  AS INT)
    ELSE val
    END ,val

RESULT:

Sorry for that

I think below query is fine for that

SELECT * FROM table_name ORDER BY cast(CartonNo as decimal(38,10)) asc;


How to delete a column named grave accent in MySQL As title.

Every time when I try to execute the query:

ALTER TABLE tablename DROP COLUMN ```

I got syntax error, which is understandable.

So, what should I do to delete this column whose name is the symbol of grave accent?

OK, I found out the answer.

The correct query should be

alter table tablename drop column ````

alter table `tablename` Drop column `accent`

This will work you need to wrap with ``.
What is the error you are getting, what is your table name?


How to show all system privileges for all users in Oracle database I need a query how to list all system privileges only (not the object ones) which are granted to each user in database. I need listed system privileges whatever they granted thru role or direct.
Thank you

"Retrieving all user privileges within Oracle can range from a simple task using a basic SQL query to an advanced script, depending primarily on how involved the roles and privileges are configured within the server."
https://chartio.com/resources/tutorials/oracle-user-privileges--how-to-show-all-privileges-for-a-user/


Firebird-Time Interval- between 2 Dates I try to select the period of employment for the members in a Company.

Example:
Date of join: 01.01.2001
Today: 27.02.2014
Now, I want this:
13 years, 1 month and 27 days

I have a Statement which works on MSSQL, but I cant translate it to firebird-SQL.

Could anybody help me?

Thx

Erik

Relatively green Firebird n00b here w/a fair PostgreSQL background, although specialized GIS work focused mostly to DB design and the sourcing/cleaning/making spatial multi-source data sets (usually of dubious quality) and then querying for map-able results in answer to specific location riddles. Toolbox is light, therefore, on Admin/Optimization/3rd Party Application Setup etc. You may (err, will) see some Firebird specific questions from me in the coming weeks. Thanks in advance!
Took a stab at this slightly older Firebird question to get a feel for the new-to-me flavor of date handling and concatenation. Took more tweaking & testing than expected & my dabbling may not be the most efficient/elegant solution but, assuming the existence of column join_date in table employees, this will work:

SELECT Y ||' Years, '|| M ||' Months, and '|| D ||' Days'
FROM
(
SELECT
CASE WHEN datediff(year, join_date, current_date) <> datediff(day, join_date, current_date)/365
THEN datediff(year, join_date, current_date)-1
ELSE datediff(year, join_date, current_date)
END as Y,
CASE WHEN datediff(year, join_date, current_date) <> datediff(day, join_date, current_date)/365
AND datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(month, dateadd(year, datediff(year, join_date, current_date)-1, join_date), current_date)-1
WHEN datediff(year, join_date, current_date) <> datediff(day, join_date, current_date)/365
AND datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)>=0
THEN datediff(month, dateadd(year, datediff(year, join_date, current_date)-1, join_date), current_date)
WHEN datediff(year, join_date, current_date) = datediff(day, join_date, current_date)/365
AND datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(month, dateadd(year, datediff(year, join_date, current_date), join_date), current_date)-1
ELSE datediff(month, dateadd(year, datediff(year, join_date, current_date), join_date), current_date)
END as M,
CASE WHEN datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(day, dateadd(month, datediff(month, join_date, current_date)-1, join_date), current_date)
ELSE datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)
END as D
FROM employees
)

The subquery turned conditional on me when it became clear that: a) calculations on year & month don't consider the date as a whole; and b) date subtraction will happily return negative values regardless of the order of events.

Year/Month/Day Example: datediff() returns a value of 1 when retrieving the number of days, months, or years between 31.12.2015 and 1.1.2016.

Negative Value Example: about 69% of my test-date values are less than zero when I calculate the number of days less the number of days contained by whole months using:

SELECT datediff(day, dateadd(month, datediff(month, date_column, current_date), date_column), current_date) as D FROM date_table

We can streamline the subquery if substituting 365 days for one year is acceptable. The trick works with my test data but may trigger pitfalls with a broader range of dates. The simplified query looks like:

SELECT Y ||' Years, '|| M ||' Months, and '|| D ||' Days'
FROM
(
SELECT
datediff(day, join_date, current_date)/365 as Y,
CASE WHEN datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(month, dateadd(year, datediff(day, join_date, current_date)/365, join_date), current_date)-1
ELSE datediff(month, dateadd(year, datediff(day, join_date, current_date)/365, join_date), current_date)
END as M,
CASE WHEN datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)<0
THEN datediff(day, dateadd(month, datediff(month, join_date, current_date)-1, join_date), current_date)
ELSE datediff(day, dateadd(month, datediff(month, join_date, current_date), join_date), current_date)
END as D
FROM employees
)

Holler if I goofed it or if I employed bad/overwrought logic. Hope it's helpful to someone at some point! Keep a lookout for new Firebird questions (might already have locked in struggle with a JDBC connection string that
just
will
not
lose).

Thanks and hello again!
--Rob

I never had a plan-- Anonyman

YEARS=DATEDIFF(YEAR FROM :BEGIN_DATE TO :END_DATE);
MONTHS=DATEDIFF(MONTH FROM :BEGIN_DATE TO :END_DATE);
DAYS=DATEDIFF(DAY FROM :BEGIN_DATE TO :END_DATE);


Single Row Table for Storing an Additional Unique Identifier I just came across a somewhat old database (and frontend) and it has funny way of dealing with the aspect of unique ids.

I've got one table with a single column and row storing an Integer (currently 31448). This number is used on an Invoices table as 'InvoiceNo', the Invoices table also has a unique auto-inc id (currently 2847)

It looks a bit like this:

CREATE TABLE InvoiceNumber(
    LatestId [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE Invoices(
    InvoiceId [int] IDENTITY(1,1) NOT NULL,
    InvoiceNo [int] NOT NULL,
    Amount [decimal](18, 2) NULL
) ON [PRIMARY]
GO

No actual relationship between the two tables other than the InvoiceNo which is not actually an enforced relationship.

The frontend then, when the user adds a new invoice performs the following:

public static void CreateInvoice(Invoice newInvoice)
{
    try
    {
        using (var ctx = new DataContext)
        {
            var lastInvoice = (from lastNumber in ctx.SingleRowTable
                                    select lastNumber).Single;

            var newInvoiceNo = lastInvoice.InvoiceNumber +1; // Int

            lastInvoice.InvoiceNumber = newInvoiceNo;

            newInvoice.InvoiceNo = newInvoiceNo;

            ctx.Invoices.InsertOnSubmit(newInvoice);

            ctx.SubmitChanges;
        }
    }
    catch  (Exception x)
    {
        // Sleep now in the fire
    }
}

So basically when a new invoice is created the frontend requests the number on the InvoiceNo (single row) table and adds one (+1) that number then becomes the new invoice number and also the latest used invoice number.

Other tables in the database (InvoiceItems) reference the InvoiceId not the InvoiceNo.

I supposed the question is: Why would someone use this approach? is it an anti-pattern or design error or are there any real implementation towards it?

My thought was that for some reason the "Real World" invoice number needed to be preserved so they decided that running an additional 'Unique Id' would be the best way to deal with it. I thought maybe the developers couldn't afford a one thousand jump on the invoice number. (?) Maybe.

I used to work with a program that used a similar system to generate unique IDs for invoices. It did originate in a different database system, one without auto-incrementing IDs.

There's one difference with your system, and it's a difference I like a lot. The invoice table has a unique, auto-incrementing ID - and it has a separate, customer-facing (I'll assume) invoice number.

I went through a process of merging two structurally identical databases. The auto-incrementing ID values overlapped, so we had to reset one set of them as we went through the merge process. Our invoice table used the unique, auto-incrementing ID as the customer-facing invoice number. So, we functionally lost those invoice numbers in the system.

While it would have been potentially problematic to have two invoices with the same (customer-facing) invoice number, at least we would have had that option if the customer-facing value had been distinct from the primary key.

The solution is a 'normal' solution to generate a unique key. It is used when the database has no sequence or auto-increment option. You can also use it to become database independent.


Is it okay to put dots in SQL Server database names? If I use a database name with a dot in it in SQL Server (2005 or 2008), something like "MyApp.Sales", will this cause any problems?

You can, but I wouldn't. You would always have to wrap the DB name with square brackets such as [MyApp.Sales].

So to recap: if you value your sanity, don't do it.

One of our suppliers has chosen to introduce the schema name: [System.Activities.DurableInstancing]. It's broken our dataload tool, so we now have further development to cope with their weirdness. Just don't do it. Use a '-'.


Can I use OpenLDAP to authenticate users in Oracle? I like to use OpenLDAP to authenticate my Oracle users. It should be possible to fetch at least the password from the directory.

There appearently is something for the Oracle LDAP servers, but would it work with OpenLDAP (plain, from i.e. Debian)?

Yes!

According to this post, you absolutely can, with significant speed improvements over OID.

Looking at this absolutely ancient post, it was apparently possible back in 2003.

Here's the Archive.Org transcript of the page they reference.

This is a Oracle documentation link about hooking OID up to an "external directory". Good luck.

There's a page describing step by step how to set up OpenLDAP to resolve TNS aliases.


Hierarchical data in MongoDB? I have a question regarding Hierarchical data.

Our app shows posts, that are structured inside parents. Here's how it goes:

prof->sprof->subject->post

(Each prof can contain multiple sprofs, and each sprof can contain multiple subjects, and each subject contains multiple posts)

Right now, we've set up a temporary MySQL Database that looks like this:

There are 4 tables: profs, sprofs, subjects and posts. Inside the table profs you have a row for each prof - and each prof has it's unique ID.

Inside the table sprofs, each sprof has it's own unique ID, but It also has a field for the ID of its parent. For example, if there's a prof with the ID 1, a sprof might have a parent_id of 1 (to indicate that its prof (its parent) is prof number 1).

And so on - each subject has a field for its sprof's ID, and each post has a field for its subject's ID.

So for example, if we want to get all the posts under the subject number 2, we do:

SELECT * FROM 'posts' WHERE 'subject_id'=2

A friend told me, that MongoDB can have structured data, so for example I could put a "table" of "posts" under a "row" inside the "table" of "subjects".

Is it true? Will it help? Will it be faster than using MySQL?

I'm not familliar with MongoDB at all, but I'd like to learn that anyway.

The question to answer first is, are any of the possible children bounded or unbounded sets of data? The next question is, how are you going to be requesting the data? Another question would be, how often will the data be written?

One of the things you'll learn quickly with MongoDB is the answer to any schema questions is almost always "It depends". It depends on the answers to the questions I asked above and a few more. To get you on your way though, MongoDB has some great documentation on the subject of hierarchies. This gets you part of the way to your solution.

Then you have to understand your data access patterns and when to normalize or denormalize data. Something to help you make that decision can be read in this great blog.

Sorry to leave you with a not so cut and dry answer. Yes, MongoDB can take care of hierarchical data. How should it be done? It depends. Will it be faster than MySQL, it depends. Can it be faster than MySQL? Most definitely.

Scott

That's true. You can put hierarchical data in MongoDB. But I think you could perfectly fit what you got in one MySQL table?

Let's say structure would look like

ID  ParentID    TypeID  Value
1   NULL        1       'Dr. Mittens'
2   1           2       'Doc. Woofer'
3   2           3       'Name of Subject'
4   3           4       'Post 1'
5   3           4       'Post 2'
6   2           3       'Name of another Subject'

I might be wrong - correct me if you can.


Copy maintenance plans to a different server Is there a way to copy maintenance plans from one SQL Server 2012 to another? Both servers are not accessible via the same copy of Management Studio at the same time (on different networks).

Go to the server where the jobs exist, in Management Studio's Object Explorer go to Management > SQL Server Agent and highlight the Jobs folder. Hit F7 (or View > Object Explorer Details). You should see multiple jobs in the right pane. Highlight the ones you want to copy, right-click, Script Job As > Create To > Clipboard. Now connect to the other server, open a new query window, paste, and hit F5. Much easier than mucking with SSIS.

You may have to take additional steps, depending on what the plans do. Connect to the Integration Services instance, rather than the database instance, expand Stored Packages > MSDB > Maintenance Plans, and right-click any plan and choose Export package. On the other server, follow the same steps, but right-click Maintenance Plans and choose Import package. You will have to do this for each plan.

log as "sa" otherwise will not be displayed.
to see quickly:


How to execute multiple statement in PgAdmin 4 like we do in Sql Server/Oracle? How to execute multiple statement in PgAdmin 4 like we do in Sql Server/Oracle (press F5)?

select * from employees;

select * from department;

As much I worked with PgAdmin, it allows us to execute only one selected statement or last statement per time, is there any way or any setting to execute multiple dml statement together?

pgAdmin (both III and 4) execute both statements. For the first one, it just discards the output, unless there is an error.

You can actually chain as many statements as you want:

-- We create one table
CREATE TABLE digit_names
(
    digit integer PRIMARY KEY,
    digit_name text,
    UNIQUE (digit_name)
) ;

-- Fill it with values
INSERT INTO digit_names
VALUES
    (0, 'zero'),
    (1, 'one'),
    (2, 'two'),
    (3, 'three'),
    (4, 'four'),
    (5, 'five'),
    (6, 'six'),
    (7, 'seven'),
    (8, 'eight'),
    (9, 'nine') ;

-- And perform a SELECT query
SELECT
    digit_name
FROM
    digit_names
    JOIN (VALUES (1), (2), (3) ) AS three_digits(digit) USING (digit) ;

-- All in just one statement.

But you'll get only the output from the last one.

If you're using pgAdmin 4, you just type everything on the Query Tool pane, and then press the button with a [lightning bolt] (or press F5).

PG Admin does not support multiple executions of a select statement


How can I take backup of particular tables in SQL Server 2008 using T-SQL Script I want to take a backup of particular tables available in my database in a .bak file, and all these should be done using a T-SQL script.

Backup Types are dependent on SQL Server Recovery Model. Every recovery model lets you back up whole or partial SQL Server database or individual files or filegroups of the database. Table-level backup cannot be created, there is no such option. But there is a workaround  for this

Taking backup of SQL Server table possible in SQL Server. There are various alternative ways to backup a table in sql SQL Server

BCP (BULK COPY PROGRAM)
Generate Table Script with data
Make a copy of table using SELECT INTO
SAVE Table Data Directly in a Flat file
Export Data using SSIS to any destination

Here I am explaining only the first one rest you might be knowing

Method 1 – Backup sql table using BCP (BULK COPY PROGRAM)

To backup a SQL table named "Person.Contact", which resides in SQL Server AdventureWorks, we need to execute following script, which

-- SQL Table Backup
-- Developed by DBATAG, www.DBATAG.com
DECLARE @table VARCHAR(128),
@file VARCHAR(255),
@cmd VARCHAR(512)
SET @table = 'AdventureWorks.Person.Contact' --  Table Name which you want    to backup
SET @file = 'C:\MSSQL\Backup\' + @table + '_' + CONVERT(CHAR(8), GETDATE(), 112) --  Replace C:\MSSQL\Backup\ to destination dir where you want to place table data backup
+ '.dat'
SET @cmd = 'bcp ' + @table + ' out ' + @file + ' -n -T '
EXEC master..xp_cmdshell @cmd

OUTPUT

Note -

You must have bulk import / export privileges
In above Script -n denotes native SQL data types, which is key during restore
-T denotes that you are connecting to SQL Server using Windows Authentication, in case you want to connect using SQL Server Authentication use -U -P
This will also tell, you speed to data transfer, in my case this was 212468.08 rows per sec.
Once this commands completes, this will create a file named "AdventureWorks.Person.Contact_20120222" is a specified destination folder

Alternatively, you can run the BCP via command prompt and type the following command in command prompt, both operation performs the same activity, but I like the above mentioned method as that’s save type in opening a command prompt and type.

bcp AdventureWorks.Person.Contact out C:\MSSQL\Backup\AdventureWorks.Person.Contact_20120222.dat -n -T

Backup a single table with its data from a database in SQL Server 2008:

SELECT * INTO  [dbo].[tbl_NewTable]
FROM [dbo].[tbl_OldTable]


mysql UNIQUE KEY multiple columns with one column as NULL I understand the way UNIQUE KEY works in Mysql 5.5+, and understand that NULL allows duplicate entries to be created.

In following schema and I would finally want that family_relation should have only a unique (father, mother, kid) relationship with optionally kid (as kid_id can be null). In the INSERT INTO ON DUPLICATE KEY at the end, you will see that two family_relation entries are created for same father and mother due to kid_id=null. How can I prevent this and enforce mysql to have only one relationship?

DROP TABLE IF EXISTS family_relation;
DROP TABLE IF EXISTS person;

CREATE TABLE person (
    person_id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE
) DEFAULT CHARSET=utf8 ENGINE=InnoDB;

CREATE TABLE family_relation (
    pk INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    father_id INTEGER UNSIGNED NOT NULL,
    FOREIGN KEY (father_id) REFERENCES person(person_id) ON DELETE CASCADE,
    mother_id INTEGER UNSIGNED NOT NULL,
    FOREIGN KEY (mother_id) REFERENCES person(person_id) ON DELETE CASCADE,
    kid_id INTEGER UNSIGNED COMMENT 'family may or may not have kids',
    FOREIGN KEY (kid_id) REFERENCES person(person_id) ON DELETE CASCADE,
    UNIQUE KEY one_father_mother_with_possible_kids (father_id, mother_id, kid_id)
) DEFAULT CHARSET=utf8 ENGINE=InnoDB;

INSERT INTO person (name) VALUES ('john the father');
INSERT INTO person (name) VALUES ('ashley the mother');
INSERT INTO person (name) VALUES ('dennis the kid');

INSERT INTO family_relation (father_id, mother_id) VALUES((SELECT person_id from person where name like '%john%'), (SELECT person_id from person where name like '%ashley%'))
ON DUPLICATE KEY UPDATE father_id=VALUES(father_id), mother_id=VALUES(mother_id);

-- This creates a duplicate entry of family_relation due to kid_id = NULL. HOW DO I PREVENT THIS?
INSERT INTO family_relation (father_id, mother_id) VALUES((SELECT person_id from person where name like '%john%'), (SELECT person_id from person where name like '%ashley%'))
ON DUPLICATE KEY UPDATE father_id=VALUES(father_id), mother_id=VALUES(mother_id);
SELECT * from family_relation;

/* Result is as
"pk" "father_id" "mother_id" "kid_id"
"1" "1" "2" \N
"2" "1" "2" \N
*/

here is http://sqlfiddle.com/#!2/834d6/1

Possibilities, you can:

use the BDB-Storage engine in MySQL prior 5.1. which treats nulls as non-distinct to each other. Later versions don't support BDB
introduce a special value for "no kid_id", so you won't have nulls
add a not-null pseudo column, which is filled by triggers and has to be in the constraint instead of the original kid_id
use a before-insert trigger instead of the constraint, which checks if there is a row containing all the 'unqiue' fields (use <=> for also compare nulls) and changes the PK of the to-be-inserted-row to the one of the existing one so it results in a "Duplicate key" error.

NULL should also be seen as a value, so I also say it's a bug. I will make my NULL column a NOT NULL column with default value 0 to get around this bug.


How to force order of WHERE clauses in PostgreSQL? I have 6 columns in Postgres table:

A1 character varying(5)[]
A2 character varying(5)[]
A3 int REFERENCES ... -- FK
B1 character varying(5)[]
B2 character varying(5)[]
B3 int REFERENCES ... -- FK

I need a SELECT where the first row matched is the winner (limit 1)
with matching to A group and B group.

I know Postgres doesn't care about the order of WHERE clauses and I must prepare an ORDER clause or find a different approach.

I want to prepare lookup with priority of matching, my importance of WHERE clauses is as follow:

highest priority: (A1 and B1) OR
.                 (A1 and B2 OR A2 and B1) OR
.                 (A1 and B3 OR A3 and B1) OR
.                 (A2 and B3 OR A3 and B2) OR
lowest priority:  (A3 and B3)

The query is matching with 6 values, like:
a1 to A1, a2 to A2, a3 to A3, b1 to B1, b2 to B2, b3 to B3

So A1 and B1 means a1 matched with A1 and b1 matched with B1,

in SQL this part is written as:

  ("A1" @> ARRAY['19956']::varchar(5)[] AND "B1" @>
  ARRAY['27407']::varchar(5)[])

with a1='19956', b1='27407'.

Is it possible to prepare it as single query, despite declarative aspect of SQL? I am considering 5 joins on the same table, but maybe there is an easier way.

This information is crucial:

  the first row matched is the winner (limit 1)

You do not actually need any sort order for a single result. ORDER BY is just one idea how to solve the task. I suggest a completely different approach, probably (much) faster:

SELECT * FROM tbl WHERE A1 AND B1
UNION ALL
SELECT * FROM tbl WHERE A1 AND B2 OR A2 and B1
UNION ALL
SELECT * FROM tbl WHERE A1 AND B3 OR A3 and B1
UNION ALL
SELECT * FROM tbl WHERE A2 AND B3 OR A3 and B2
UNION ALL
SELECT * FROM tbl WHERE A3 AND B3
LIMIT 1;

This is assuming each SELECT can only return a single row. Else you get an arbitrary pick or you need to add ORDER BY to individual SELECTs enclosed in parentheses for a deterministic pick. (See linked answers below.)

The beauty of it: Postgres stops evaluating as soon as the first row is found. Test with EXPLAIN ANALYZE to see "never executed" for trailing SELECTs. So you can use indexes, which might mean orders of magnitude in performance.

And you can conveniently return a default row if nothing matches. Just append one more SELECT before LIMIT 1.

BTW, this is a single query.

Related:

Optimize a query on two big tables
Select first record if none match

ORDER BY
  A1 and B1 DESC,
  A1 and B2 OR A2 and B1 DESC,
  A1 and B3 OR A3 and B1 DESC,
  A2 and B3 OR A3 and B2 DESC,
  A3 and B3 DESC;


What is difference between tempdb log and database log? In which circumstances these log files uses? As we all know there are two types of log file (*.ldf) comes into the picture whenever any large query getting executed 1. database log (transaction log) file 2. tempdb log.

Please describe me how SQL server uses thes files while any query executed?

2nd thing i want to know, when it is safe to shrink log files (database log, tempdb log)? In which case we shouldn't log files?

MS SQL Server is an ACID compliant RDBMS.  One of the critical tools involved in providing ACID compliance is a concept of a transaction log.  The transaction log is where the pages modified is saved, so it can revert them and put them back in the previous state if you need to rollback a transaction.  There is much to consider here but you want to ensure you size your VLFs and manage it in advance.

TempDB is a temporary area used by MS SQL Server to store objects like temp tables, join results, some caching, etc.  Unless you have performance issues, you generally don't want to worry about this, don't even back it up.  It get's rebuilt on startup.  It is good practice to create at least 2 or maybe up to 4 physical files for TempDB so you don't get PAGELATCH contention.

Paul Randall (An authority on MS SQL Server) recommends letting your transaction log be at whatever size it grows to after a full weeks activity after reindexing.  Really, unless it grows a lot, don't worry too much about it.  Just make sure to avoid growths.

tempdb is used for temporary objects such as temp tables, table variables etc. it is also used for transfering large amounts of data between tables when the amount of memory is not enough to contain the transfer. tempdb will shrink and grow as the objects use it.

The Log file (ldf) is used to store transactions. Usually in the case a transaction needs to be reverted the .ldf is used to revert to the previous state.The .ldf will usually stay enlarged until a full backup of the .mdf is made. You will see the .ldf file grow as you do inserts, deletes, updates etc. all of those commands use logging and are therefore stored in the .ldf file.

Some commands, like TRUNCATE, use minimal logging so you may not see an increase in the .ldf file size.

The only safe way to shrink the log file is to create a full backup first (therefore stating that all the committed transactions are final) and that the transactions can be discarded.

As for the individual file growth you should note that some T-SQL commands affect .mdf and .ldf files. For more information you can lookup MSDN for T-SQL commands that use logging
(as a typical example the DELETE statement won't shrink the .mdf file and may increase the .ldf file, while TRUNCATE may shrink the .mdf file but will not increase the .ldf file due to the fact that the statement doesn't use logging as much)

The rule of thumb is never to shrink the .mdf file as this will mess up the index pages and will cripple the query performance. if you must shrink the .mdf file rebuilding all the indexes is recommended, and depending on the database size this may take anywhere from several minutes to a couple of hours.


Why are my domain and username being prepended to stored procedures I create? In Microsoft SQL Server Management Studio, every time I write a stored procedure such as ThisIsAStoredProcedure, it ends up showing in the list of stored procedures as COMPANYDOMAIN\MYUSERNAME.ThisIsAStoredProcedure.

I'd like it to be dbo.ThisIsAStoredProcedure instead.

I've tried right clicking and renaming, but this doesn't work.

I have three concerns:

Why is this happening?
How can I change the existing stored procedures with my domain\username prefix to dbo.procedurename?
How can I change the configuration to name procedures dbo.procedurename by default?

If you didn't explicitly set it, then objects created by you will be created under the default schema for your user. In SSMS, look in:
Database -> Security -> Users -> <you> -> Properties -> General -> Default schema
ALTER SCHEMA dbo TRANSFER [COMPANYDOMAIN\MYUSERNAME].ThisIsAStoredProcedure;
Change the setting you looked at in 1.

Of course, you may not have permission to do 2 or 3, in which case it's a question for the person that does have permission.

On the right hand upper side of Sql Server Management Studio, there is an option when creating the table, to select the schema. I recreated the database and selected the proper schema