How do I get Brent Ozar's sp_BlitzIndex to run on Azure?

Kendra here (the author of sp_BlitzIndex)

First, thanks for being interested in the procedure and trying it out.

Azure doesn't expose all the dynamic management views that we get in the boxed product. I do actually reference sys.dm_db_partition_stats, but there's other info I need to get from sys.partitions for other users. (Is it using compression? What type?)

I haven't had time to write an test an Azure specific version, just because I haven't had a lot of demand for it. But your question does tell me there's more interest than I knew of -- so there's that!

I will at minimum look at implementing some error handling so it lets you know in a graceful way though. (This is a bit of a moving target, because Azure has been expanding the features that are available to users as well.)


Get three last movements of the per ID with LAG and GROUP BY in MySQL

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

fiddle


newbie question - how to list people who has not email address related to him/her?

Aliases should be short and meaningful
select pe.c_id as "ID",
    pe.c_family_name as "Family Name",
    pe.c_last_name as "Last Name",
    em.c_email as "Email"

from
t_people pe
LEFT JOIN t_ref_email re ON re.c_party = pe.c_id
LEFT JOIN t_email em ON em.c_id = re.c_email

With the result of
ID      Family Name     Last Name   Email
1000    Test            Joe         test_email@email.com
1001    Test             Bill        (null)


sql server , set N prefix as unicode for all queries by default

No, there's no way to do this.

Any option that did what you want would be extremely confusing because there would need to be a way to turn it off within the string literal syntax. There are other considerations (like storage size) to be aware of when choosing data types, so a setting like this could be very dangerous to put in play.

With regards to .NET applications that send parameterized T-SQL statements to SQL Server, the framework takes care of this for you because a .NET String inherently supports Unicode and things were built using that assumption. This applies to ORM-type libraries as well if they're programmed well.

Consider the following code snippet:

IDbCommand cmd = connection.CreateCommand();
cmd.CommandText = @"SELECT * FROM sys.server_principals WHERE name = @name";

IDbDataParameter param = cmd.CreateParameter();
param.DbType = DbType.String;
param.ParameterName = "@name";
param.Value = "dbo";
cmd.Parameters.Add(param);

IDataReader dr = cmd.ExecuteReader();

What actually gets sent to SQL Server is this (line break added for clarity):

exec sp_executesql N'SELECT * FROM sys.server_principals WHERE name = @name',
    N'@name nvarchar(3)',@name=N'dbo'

You can see that the framework automatically used a Unicode type for the parameter (the length of 3 is a separate discussion entirely...).

The places where you need to modify code are in situations where SQL queries are not parameterized (or are unparameterizable, i.e., DDL) -- in other words, queries that have parameters concatenated directly into the string, or contain string constants. See my answer here for an example of how to avoid SQL injection issues when using dynamic SQL.


pg_upgrade does not copy WAL, nor pg_log, nor any random cruft you put in the old directory which is not under PostgreSQL's control.  Use something like du -sh /var/lib/pgsql/data/* to see where the space is actually being used in old and new servers.

Why is still showing the old data directory and not the new one?

It is showing you the files which are being copied from old to new.  Why should it show you the name of the destination, rather the name of the source?  The latter seems more natural to me.


First of all when you run mongod as a normal user you need to make sure that the particular user has the following permissions:

Access to network interface ( open socket ) in order to be able to listen to connections
Access to the database folder. The default is accessible only by root or mongod user (/data/db)
Access to logging folder /var/log/mongod

All of the above are automatically set and granted for the user mongodb (mongod in earlier versions). If you take a look at /etc/init.d/mongodb you will see that the service will be stared under the user mongodb DAEMONUSER=${DAEMONUSER:-mongodb} and not as root.


22K QPS is nice; I rarely see that high a number on a production system.  You must be doing rather trivial queries.  (In a quick survey, 7K is the 90th percentile.)

Do you use a lot of Stored Routines?  What is your Questions/Uptime?

It is unlikely that a high query rate will "crash" MySQL.  It can slow things down to the point where it feels "crashed".

How many active connections do you have?  When SHOW PROCESSLIST show more than a few dozen queries running (not "Sleep") at a time, you are threatening to get in this overburdened situation.

There have been benchmarks (carefully designed) that registered more than a million queries per second.  I could devise a situation that could saturate all the system resources, yet achieve only 1 query per minute.  Those extremes indicate that there are a lot of factors going on.


As for "all the recorded are stored chronologically", the only control you have over this is the PRIMARY KEY in InnoDB.

A simple rearrangement will provide the optimal access:

PRIMARY KEY(S_Date, id),  -- to order the data in the order desired
INDEX(id)   -- to keep AUTO_INCREMENT happy

(TOP 1 is not a MySQL construct; see LIMIT.  But it is not needed with the above change.)

(The technique above is even better than INDEX(S_date).)

Explanation

In MySQL, the data rows are ordered by the PRIMARY KEY.  Said another way: The PRIMARY KEY is "clustered" with the data.  This implies that you you fetch the rows in PK order, you will be fetching the data in the most optimal order.

With the change I proposed, the data will be ordered by S_Date (with dups ordered by id).  This is exactly the order you want.

To change the current table definition, do

ALTER TABLE SalesInvoice
    DROP PRIMARY KEY,
    ADD PRIMARY KEY(S_date, id),
    ADD index(id);

The auto_increment column must occur first in some index.  This allows the 'next' value can be efficiently found after a restart.

Stop after

  start the searching of rows from Nth position.

This performs such, but not efficiently:

ORDER BY ...
LIMIT 1000 OFFSET 20000

will skip (that is, read and discard) 20K rows, then deliver 1000.

It is more efficient to "remember where you left off".  With PRIMARY KEY(id), it is straight-forward and efficient:

WHERE id > $leftoff
ORDER BY id

With a composite index it gets messier, but still possible.  More discussion: http://mysql.rjweb.org/doc.php/pagination


Very selective partial indexes tend to get bloated quickly, because the table autovacuuming is triggered off the percent turn over in the table.  But 20% turn over in a table can mean many thousands of percent turn over in the partial index.  Nightly reindexing can also solve the bloat, but it won't stay solved for long if turn over is high.  You can crank up autovacuum so it happens multiple times throughout the day.
The first time you run this query in a while, it is probably marking a lot of obsolete index entries as killed.  That is probably one reason there are so many dirtied buffers, and also one reason it will be faster the next time, as it will no longer have to visit all the obsolete entries just to discover they are obsolete.  Of course the buffers it does still need to read are likely to be cached as well, so that would be another reason for it to be faster.
Note that this index-entry killing doesn't work in bitmap index scans, only on regular index scans.  Also, it doesn't work on hot standbys. Both of which can be a real problem for some setups.


There is no such way to access a table created by you using additional username/password for a user once he logged in. One can control access by a normal MySQL login. The username used for that can be configured to have only access to the objects you want by setting up privileges. So the username decides what is allowed and what is not.


You just need to use NOT IN

SELECT * FROM table1 WHERE col1 NOT IN (SELECT colA FROM table_2)


Query to check where database growth was?

I profiled the query and this is the portion that returns the 'Disk Usage by Top Tables' results:

exec sp_executesql @stmt=N'begin try

            SELECT TOP 1000
            (row_number() over(order by (a1.reserved + ISNULL(a4.reserved,0)) desc))%2 as l1,
            a3.name AS [schemaname],
            a2.name AS [tablename],
            a1.rows as row_count,
            (a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,
            a1.data * 8 AS data,
            (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,
            (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused
            FROM
            (SELECT
            ps.object_id,
            SUM (
            CASE
            WHEN (ps.index_id < 2) THEN row_count
            ELSE 0
            END
            ) AS [rows],
            SUM (ps.reserved_page_count) AS reserved,
            SUM (
            CASE
            WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
            ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
            END
            ) AS data,
            SUM (ps.used_page_count) AS used
            FROM sys.dm_db_partition_stats ps
            GROUP BY ps.object_id) AS a1
            LEFT OUTER JOIN
            (SELECT
            it.parent_id,
            SUM(ps.reserved_page_count) AS reserved,
            SUM(ps.used_page_count) AS used
            FROM sys.dm_db_partition_stats ps
            INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
            WHERE it.internal_type IN (202,204)
            GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
            INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id )
            INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
            WHERE a2.type <> N''S'' and a2.type <> N''IT''
            end try
            begin catch
            select
            -100 as l1
            ,   1 as schemaname
            ,       ERROR_NUMBER() as tablename
            ,       ERROR_SEVERITY() as row_count
            ,       ERROR_STATE() as reserved
            ,       ERROR_MESSAGE() as data
            ,       1 as index_size
            ,       1 as unused
            end catch',@params=N''


Check mysql domain that is using resources

You could check your slow query log. If it's CPU intensive, it may be some heavy hitting queries that take a while to process. Check to make sure your slow query threshold is set to a reasonable amount of time and then look at your log.

When your server is in the middle of a CPU spike, log in and do show full processlist to look at the queries that are running at that moment. Is there a higher number of queries than normal? Is there a high number of slow queries than normal?

You could use show open tables which will show you what's in the table cache, but it doesn't necessarily tell you what is using up all the resources, but rather which databases/tables are the most active.


SQL Server. Do reads on Read-Only Replica in Availability Group fire Auto Update Statistics on Primary?

As far as I know, auto update statistics starts when reads (SELECT and
  maybe something else) performed. But our reads execute only on
  Secondary. Does it fire Auto Update Stats on Primary?

No it does not fire any auto update statistics on the primary.

However

In Read only databases, regular permanent statistics can be leveraged to satisfy query plans. If sql server needs new statistics for the read only db or these permanent statistics in the database have become stale, then temporary statistics can be created / updated. These statistics reside in TempDB and are managed by SQL Server (you can only drop them).

There are two kinds of temporary statistics, these that get created due to missing statistics and those that get 'updated'.

Reducing the creation of temporary statistics

The creation of temporary statistics can be removed by adding the statistics manually on the primary database, generating the estimated execution plans of the reporting queries (also on the primary instance/db) or running the queries.

Reducing the updates of permanent to temporary statistics

Permanent statistics can be updated and 'transformed' to temporary statistics on a read only database.

The temporary stat updates can be resolved by updating your statistics on the primary db more often.

As a side note, on Standby / Read Only (log shipping) databases you would have the option to run ALTER DATABASE [ReadOnly] SET AUTO_UPDATE_STATISTICS OFF; to disable the temp stat updates.
This is not possible for database part of an Always On Availability Group.

These stat creates / updates can become expensive depending on the amount of data and sample rates.

Parts of this answer have been taken from a previous post of mine


how to reduce the size of the datafiles of a oracle DB?

-- create a new UNDO tablespace named UNDOTBS2

CREATE UNDO TABLESPACE undotbs2
DATAFILE '/01/oradata/dbname/undotbs201.dbf'
SIZE 50M AUTOEXTEND ON NEXT 5M;

Tablespace created.

-- Switch the database to the new UNDO tablespace.

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=BOTH;

-- Drop the old one

DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

-- If Undo is still in use

*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

I would try this out first on a test database:

(Following from dead link) from original answer:

SELECT a.name,b.status
FROM   v$rollname a,v$rollstat b
WHERE  a.usn = b.usn
AND    a.name IN (
          SELECT segment_name
          FROM dba_segments
          WHERE tablespace_name = 'UNDOTBS1'
         );

The above query shows the name of the UNDO segment in the UNDOTBS1 tablespace and its status. See which users/sessions are running this pending transaction:

SELECT a.name,b.status , d.username , d.sid , d.serial#
FROM   v$rollname a,v$rollstat b, v$transaction c , v$session d
WHERE  a.usn = b.usn
AND    a.usn = c.xidusn
AND    c.ses_addr = d.saddr
AND    a.name IN (
          SELECT segment_name
          FROM dba_segments
          WHERE tablespace_name = 'UNDOTBS1'
         );

You can then kill the session; the following assumes a SID of 147 and a SERIAL# of 4:

alter system kill session '147,4' immediate;


Performance recommendations stuck on 'Validating'

I found in the documentation that sometimes Azure will take 72 hours to validate a performance recommendation. And indeed after 72 hours it finished evaluating and reverted the recommendations.

(It turned out that identifying the indexes that would really make a big difference was quite easy and I didn't need Azure Performance Recommendations. I copied one of the long-running queries into SSMS, chose a representative set of inputs, and then ran Query > Display Estimated Execution Plan. The result showed me one step in the query was taking 99.9% of the time and it even suggested an index to add. Result was the query time came down from 20 seconds to 0.1 second. I realise this will not be news to DB admins but I hope it will help someone.)


WARN max_connections from pt-variable-advisor - what to configure for DBaaS?

There is a difference between Threads_running and Threads_connected.  The former implies that many queries are simultaneously doing something.  The other are sleeping, waiting for the user (or app) to present another SQL statement to the server.

max_connections is a limit on Threads_connected.  (Max_used_connections is a "high-water-mark".)

A typical busy system will have only 1 in 7 'connected' threads actually running.  And Threads_connected is typically much less than max_connections.  (Like 20x)

But, in bad times, too many connections each doing some slow query, leads to MySQL stumbling over itself.  Throughput (queries/second) hits some maximum and may even go down; latency goes through the roof, and a panicky DBA will reboot the machine because he thinks it has crashed, not just gotten terribly tangled up.

The solution is to find the slow queries and figure out how to speed them up.  Or re-architect the app to be more efficient.  Or change the schema and indexes.  It is rarely to get bigger hardware or change the tunables.

In a well-managed MySQL server, I rarely see more than 2-3 cores in use.  Yet, thousands of queries/second can be performed even with very few cores active.

In DBaaS, you are at the mercy of the customers.  Any one of them can write a sloppy app that hogs connections, cores, I/O, network bandwidth, anything.

Consider limiting each user to 10 connections unless he explains what he needs to do and/or pays you more money.  With the extra money, you can isolate him in a separate VM.  I once estimated, in a shop with Apache and MySQL, that 10 threads would swamp Apache, to MaxChild needed to be much lower than the default.  (We had rather heavy pages.)  If you have control over web servers, limit how many threads they have.  That is stop the problem before it gets to MySQL.

By putting every user in his own VM, you consume extra resources, but you can prevent the excesses of one from harming the others.

I'll bet many of your clients use WordPress?  Here's one performance patch to the schema:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta


If you have the information for all of the backups in the backupset table, you can build all of the RESTORE FILELISTONLY commands this way, then insert the results into a #temp table, then use a cursor to build the eventual CREATE DATABASE ... FOR ATTACH commands. I still think it would be faster to suck it up and just restore the backups already, but to each his own I suppose...

First, create a #temp table to hold the RESTORE FILELISTONLY output:

SET NOCOUNT ON;

CREATE TABLE #x
(
  ln SYSNAME, [path] NVARCHAR(512), Type CHAR(1), fgn NVARCHAR(128),
  size BIGINT, msize BIGINT, FileId INT, lsn1 BIGINT, lsn2 BIGINT,
  id UNIQUEIDENTIFIER, lsn3 BIGINT, lsn4 BIGINT, bsize BIGINT, bs INT,
  fg INT, lgid UNIQUEIDENTIFIER, lsn5 BIGINT, bid UNIQUEIDENTIFIER,
  ro BIT, p BIT, TDEThumbprint VARBINARY(32)
);

Now, based on the backups stored in msdb..backupset, generate the commands themselves and run them:

DECLARE @sql NVARCHAR(MAX) = N'';

;WITH x AS
(
  SELECT *,
    rn = ROW_NUMBER() OVER (PARTITION BY database_name
     ORDER BY backup_start_date DESC)
  FROM msdb..backupset
)
SELECT @sql += '
  INSERT #x(ln) SELECT ''' + x.database_name + ''';
  INSERT #x EXEC sp_executesql N''RESTORE FILELISTONLY '
  + ' FROM DISK = ''''' + mf.physical_device_name + ''''''
  + ' WITH FILE = ' + RTRIM(x.position) + ';'';'
FROM x
INNER JOIN msdb..backupmediaset AS ms
  ON x.media_set_id = ms.media_set_id
INNER JOIN msdb..backupmediafamily AS mf
  ON ms.media_set_id = mf.media_set_id
WHERE x.rn = 1;

EXEC sp_executesql @sql;

Now, some of those might fail, if the .bak files have been moved. Also note that the position value is important, in case you have backed up multiple times to the same file. You want to be sure you have the most recent version that reflects the current file structure of the database (but there is still no guarantee that the last backup reflects reality).

Now, add a column to serve as a row number. Warning: this relies on "natural" sort order of a heap. If this table is super large this may not work.

ALTER TABLE #x ADD rn INT;
GO

;WITH x AS (SELECT *, r = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM #x)
UPDATE x SET rn = r
OPTION (MAXDOP 1);
GO

Once you have the paths in there, you will see that they reflect the old locations and drive letters of the database files. You may want to run a query that replaces the known old locations with the new ones. For example, if you know that databases previously located at C:\MSSQL\Data\ are not located at D:\MyInstance\SQLData\, you can run:

UPDATE #x SET [path] = REPLACE([path], 'C:\MSSQLData\', 'D:\MyInstance\SQLData\')
  WHERE [path] LIKE 'C:\MSSQLData\%';

Wash, rinse, repeat until all of the [path] values accurately reflect the new location of the files.

Now, we're ready to roll up our sleeves. This could very well serve as a bad practices, do as I say but not as I do post. We need a cursor to build the CREATE DATABASE ... FOR ATTACH commands. The following cursor relies on the rn column we added and builds commands that accurately handle multiple data files, multiple log files, or both.

DECLARE @ln SYSNAME, @rn INT, @ubound INT, @cmd NVARCHAR(MAX);

DECLARE c CURSOR LOCAL FAST_FORWARD
 FOR SELECT ln, rn FROM #x WHERE [path] IS NULL;

OPEN c;

FETCH NEXT FROM c INTO @ln, @rn;

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @ubound = MIN(rn) FROM #x WHERE [path] IS NULL AND rn > @rn;

  SET @ubound = COALESCE(@ubound, 2000000000);

  SELECT @cmd = N'CREATE DATABASE ' + QUOTENAME(@ln) + '
    ON '
    + STUFF((SELECT ',' + '(NAME = ''' + ln + ''',FILENAME='''
      + [path] + ''')' FROM #x WHERE rn > @rn AND rn < @ubound
      AND [Type] = 'D' ORDER BY FileId
      FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'),1,1,'')
    + '
    LOG ON
    ' + STUFF((SELECT ',' + '(NAME = ''' + ln + ''',FILENAME='''
      + [path] + ''')' FROM #x WHERE rn > @rn AND rn < @ubound
      AND [Type] = 'L' ORDER BY FileId
      FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'),1,1,'')
    + '
      FOR ATTACH;';

  PRINT @cmd;

  FETCH NEXT FROM c INTO @ln, @rn;
END

CLOSE c;
DEALLOCATE c;

This will print a bunch of individual CREATE DATABASE ... FOR ATTACH commands. However they will only be as reliable as (a) the data in msdb..backupset, (b) the presence of the actual backup files, (c) how accurate the backup files are in terms of current database file structures, and (d) your re-mapping of old paths -> new paths.


Passing in the table into a stored procedure

Instead of creating views, why not make an inline table-valued function - effectively a parameterized view.  You basically get all the benefits of views AND parameters.

Another thing you can do in your TVF is something like this:

SELECT yourtable.*
FROM yourtable
INNER JOIN ranges
    ON yourtable.date BETWEEN ranges.start AND ranges.end
WHERE ranges.name = @range_name

Now your ranges have names (in a table where they can be easily managed without changing the schema) - this also fairly seamlessly handles multiple ranges with the same name, although if ranges overlap you can have duplicates due to the join matching both.

Also, please consider avoiding BETWEEN:

https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common

I strongly suggest that you reconsider your requirements and when asking questions here be sure to include more about your motivations, because when you bring in a question with a preconceived notion of what the solution is, you are likely to get a suboptimal solution.  (like the dynamic SQL solutions given aren't going to easily work for your OR case, but that information about the problem space is buried in a comment from you)

From: http://sqlfiddle.com/#!6/a628a/1

CREATE TABLE LogData (
  Id INT IDENTITY NOT NULL
  ,Dt DATETIME NOT NULL
  ,Data VARCHAR(max) NOT NULL
);

CREATE TABLE Ranges (
  Id INT IDENTITY NOT NULL
  ,Name VARCHAR(50) NOT NULL
  ,DtStart DATETIME NOT NULL
  ,DtEnd DATETIME NOT NULL
);

CREATE TABLE Groups (
  Id INT IDENTITY NOT NULL
  ,Name VARCHAR(50) NOT NULL
);

CREATE TABLE RangeGroups (
  RangeId INT NOT NULL
  ,GroupId INT NOT NULL
);

INSERT INTO LogData (Dt, Data)
VALUES ('2011-11-23 11:00', 'before thanksgiving day')
       ,('2011-11-24 11:00', 'on thanksgiving day')
       ,('2011-12-24 11:00', 'on christmas eve')
       ,('2011-12-25 11:00', 'on christmas day')
       ,('2012-11-21 11:00', 'before thanksgiving day')
       ,('2012-11-22 11:00', 'on thanksgiving day')
       ,('2012-12-24 11:00', 'on christmas eve')
       ,('2012-12-25 11:00', 'on christmas day');

INSERT INTO Ranges (Name, DtStart, DtEnd)
VALUES ('THANKSGIVING2011', '2011-11-24', '2011-11-25')
       ,('CHRISTMASEVE2011', '2011-12-24', '2011-12-25')
       ,('CHRISTMASDAY2011', '2011-12-25', '2011-12-26')
       ,('BOXINGDAY2011', '2011-12-26', '2011-12-27')
       ,('NEWYEARSEVE2011', '2011-12-31', '2012-01-01')
       ,('NEWYEARSDAY2012', '2012-01-01', '2012-01-02')
       ,('THANKSGIVING2012', '2012-11-22', '2012-11-23')
       ,('CHRISTMASEVE2012', '2012-12-24', '2012-12-25')
       ,('CHRISTMASDAY2012', '2012-12-25', '2012-12-26')
       ,('BOXINGDAY2012', '2012-12-26', '2012-12-27')
       ,('NEWYEARSEVE2012', '2012-12-31', '2013-01-01')
       ,('NEWYEARSDAY2013', '2013-01-01', '2013-01-02');

INSERT INTO Groups (Name)
VALUES ('HOLIDAYS2011')
       ,('HOLIDAYS2012')
       ,('ANYCHRISTMAS');

INSERT INTO RangeGroups (RangeId, GroupId)
SELECT Id, (SELECT Id FROM Groups WHERE Name = 'HOLIDAYS2011')
FROM Ranges WHERE Name LIKE '%2011';

INSERT INTO RangeGroups (RangeId, GroupId)
SELECT Id, (SELECT Id FROM Groups WHERE Name = 'HOLIDAYS2012')
FROM Ranges WHERE Name LIKE '%2012';

INSERT INTO RangeGroups (RangeId, GroupId)
SELECT Id, (SELECT Id FROM Groups WHERE Name = 'ANYCHRISTMAS')
FROM Ranges WHERE Name LIKE 'CHRISTMAS%';

CREATE FUNCTION dbo.JustDoIt(@GroupName VARCHAR(50))
RETURNS TABLE
RETURN (
SELECT LogData.*
FROM LogData
INNER JOIN Ranges ON LogData.Dt >= Ranges.DtStart
AND LogData.Dt < Ranges.DtEnd
INNER JOIN RangeGroups
ON RangeGroups.RangeId = Ranges.Id
INNER JOIN Groups
ON Groups.Id = RangeGroups.GroupId
AND Groups.Name = @GroupName
);

SELECT *
FROM dbo.JustDoIt('HOLIDAYS2011');

SELECT *
FROM dbo.JustDoIt('HOLIDAYS2012');

SELECT *
FROM dbo.JustDoIt('ANYCHRISTMAS');

This part:

SELECT LogData.*
FROM LogData
INNER JOIN Ranges ON LogData.Dt >= Ranges.DtStart
AND LogData.Dt < Ranges.DtEnd
INNER JOIN RangeGroups
ON RangeGroups.RangeId = Ranges.Id
INNER JOIN Groups
ON Groups.Id = RangeGroups.GroupId
AND Groups.Name = @GroupName

Means that each log item will be joined to the ranges it fits in (by date), each range will join to the groups they are in, but only that group selected will be used.


Your database was created using double quotes so its name is now case-sensitive. Therefore, you now need to always use double quotes when referring to it:

drop database "Blog_test";

More details about quoted identifiers (a database name is an identifier just like a column or table name) can be found in the manual: http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS


Designing a user table, FName LName v.s Username

First of all full text searches are often slower than searching on indexed columns.  But full text searches offer some semantics that column searches do not.  However, full text search is primarily valuable in larger sets of text, such as indexing documents, or when the likely order of the text is fluid. (e.g. John Johnson; Johnson, John; John, Johnson; etc)

In this constrained task of matching names, I would choose the two columns, both indexed, as likely the better performing option.  (But testing is the only way to know for sure.)

If you are using standard indexed columns, then you can quickly search the fname column and/or the lname column and when needed join them on a matching identifier. This is more accurate than a full text search, because the search is very bounded.

This does require that the search query be made knowing which name is the fname and the lname, of course, or adding additional conditions to check both ways.  (Not always so simple in a multi-language world.)

The full text search syntax will support "phrases" (words in a certain order) or the softer version of words in any order.  In the latter case 'John Johnson' would be satisified by either or both of those names in any order.

(Of course, for the exact search syntax options see the mySql documentation.)


You want to make an outer join. In Oracle 8i one has to use the (+) notation.

SELECT H.STUDENT_CODE,
   H.FIRSTNAME,
   H.LASTNAME,
   H.PROGRAM,
   H.TEACHER_CODE,
   H.COUNTRY_CODE,
   AC.COUNTRY_NAME,
   P.PROGRAM_NAME,
   T.TEACHER_NAME
FROM HTUD H,
     PROGRAM P,
     TEACHER T,
     AP_COUNTRY AC
WHERE H.FIRSTNAME LIKE '%ark%'
    AND H.PROGRAM = P.PROGRAM
    AND H.TEACHER_CODE = T.TEACHER_CODE(+)
    AND H.COUNTRY_CODE = AC.COUNTRY_CODE(+)
ORDER BY H.STUDENT_CODE ASC

Starting with Oracle 10g xou can use the ANSI join syntax,too

SELECT H.STUDENT_CODE,
   H.FIRSTNAME,
   H.LASTNAME,
   H.PROGRAM,
   H.TEACHER_CODE,
   H.COUNTRY_CODE,
   AC.COUNTRY_NAME,
   P.PROGRAM_NAME,
   T.TEACHER_NAME
FROM HTUD H JOIN PROGRAM P ON (H.PROGRAM = P.PROGRAM)
    LEFT OUTER JOIN TEACHER T ON (H.TEACHER_CODE = T.TEACHER_CODE)
    LEFT OUTER JOIN AP_COUNTRY AC ON (H.COUNTRY_CODE = AC.COUNTRY_CODE)
WHERE H.FIRSTNAME LIKE '%ark%'
ORDER BY H.STUDENT_CODE ASC


Overriding default datetime output format

There is no way to do that in PostgreSQL.
The only configuration parameter available is DateStyle, which allows you to choose between a handful of pre-defined output formats.


Is it normal for disk activity to disturb MySQL performance?

From my experience, it is better to separate the MySQL data files and log files from other application files. When you don't have dedicated MySQL server, separate the data files and log files to different disks. I have faced the similar issue and we resolved by changing our data directory and log files to a each dedicated separate directories. I suggest you to go through the MOCA (MySQL optimal configuration architecture) for better understanding. I hope this will help you.

Thanks.


MYSQL Return lines whose sum of one field equals another tables field

I think I've grasped your problem - which I solved like this (see the fiddle here):

Your data isn't quite correct in your sample above. I modified it slightly thus:

jobs table:

insert into jobs values (1000, '2019-10-01', 100, 4);
insert into jobs values (1001, '2019-10-01', 50, 4);
insert into jobs values (1002, '2019-10-01', 25, 4);
insert into jobs values (1003, '2019-10-01', 220, 6);
insert into jobs values (1004, '2019-10-01', 200, 7);

The datein and paymentjobdate are the same - same day payments from your question: - need to match up payments on the same day for the same customer that equal a single transaction for that day.

payments table:

insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (1, 25, 1000, '2019-10-01', 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (1, 25, 1000, '2019-10-01', 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (1, 25, 1000, '2019-10-01', 1);
insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (1, 25, 1000, '2019-10-01', 1);

Job 1000 - note that there are 4 separate payments of 25 = jobs.total is 100, so the SUM matches.

insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (2, 50, 1001, '2019-10-01', 1);

Job 1001, one single payment which matches the total.

insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (3, 10, 1002, '2019-10-01', 1);

Job 1002 - total is 25, but payment is only 10 - not a match.

insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (4, 220, 1003, '2019-10-01', 1);

Single matching payment for jobid 1003.

insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (5, 100, 1004, '2019-10-01', 1);

Finally, job 1004 - total is 200, payment is only 100 - no match for SUM.

Then, I performed the following SQL (1st step):

SELECT p.paymentjobno AS p_jobid, SUM(p.paymentamount) AS the_sum
FROM payments p
-- WHERE paymenttype IN (1, 2, 4) -- vary the WHERE clause as necessary
-- AND p.paymentdate = '2019-10-01' -- add as many predicates as you like!
GROUP BY p.paymentjobno;

Result:

p_jobid the_sum
1000    100.00
1001    50.00
1002    10.00
1003    220.00
1004    100.00

Now I have both the jobid and the SUM, so now can join to payments on
both the jobid and that SUM using this as a subquery. The SQL:

SELECT j.*, p.*  -- choose your fields as required!
FROM jobs j
JOIN
(
  SELECT p.paymentjobno AS p_jobid, SUM(p.paymentamount) AS the_sum
  FROM payments p
  -- WHERE paymenttype IN (1, 2, 4) -- vary the WHERE clause as necessary
  -- AND p.paymentdate = '2019-10-01' -- add as many predicates as you like!
  GROUP BY p.paymentjobno;
) AS t1
  ON j.jobid = t1.p_jobid
AND j.total = t1.the_sum
JOIN payments p
ON j.jobid = p.paymentjobno;

Result:

jobid   datein  total   chargeto    payid   paymentamount   paymentjobno    paymentdate paymenttype
1000    2019-10-01  100.00  4   1   25.00   1000    2019-10-01  1
1000    2019-10-01  100.00  4   1   25.00   1000    2019-10-01  1
1000    2019-10-01  100.00  4   1   25.00   1000    2019-10-01  1
1000    2019-10-01  100.00  4   1   25.00   1000    2019-10-01  1
1001    2019-10-01  50.00   4   2   50.00   1001    2019-10-01  1
1003    2019-10-01  220.00  6   4   220.00  1003    2019-10-01  1

Which is, if I've understood you correctly, what you were looking for. You have a match on the total for jobid 1000's 4 payments, and the single payments of the total for jobids 1001 and 1003 but not on 1002 and 1004 which haven't been fully paid on that day!

If this doesn't correspond to your request, please let me know and I will try and fix it. May I recommend that you use underscores, i.e. python_case_for_variables to separate your variables (table and column names) into legible parts and use UPPER CASE for SQL - it makes things much easier to read - if you don't like that, search for "SQL coding standards" and find one that appeals and then stick to it! May I also suggest that you use singular names for tables - a table is a set of things!

Finally, always include a version number with your question - with MySQL especially, it can make a huge difference! Also, what could greatly help would be a working fiddle - it's things like this that get you quality answers quickly! Help us to help you. My profile has a few articles on how to ask questions here - you might want to take a look? Finally, as I said in comments - welcome to the forum! :-)

EDIT in the light of OP's comment and fiddle:

What you want is GROUP_CONCAT! Fiddle here.

SQL:

SELECT
      j.jobid,
      GROUP_CONCAT(p.paymentamount SEPARATOR ', ') AS payment_hist
FROM jobs j
JOIN
(
  SELECT p.paymentjobno AS p_jobid, SUM(p.paymentamount) AS the_sum
  FROM payments p GROUP BY p.paymentjobno
) AS t1 ON
  j.jobid = t1.p_jobid AND j.total = t1.the_sum
JOIN payments p ON
  j.jobid = p.paymentjobno
GROUP BY j.jobid;

Result:

jobid.  payment_hist
1000    25.00, 25.00, 25.00, 25.00
1001    50.00
1003    220.00

I would advise strongly that you avoid like the plague anything to do with comma separated lists when working in SQL. They are contrary to the relational model and breach first normal form. They are also tricky to deal with despite the existence of constructs like GROUP_CONCAT, STRING_AGG and LIST_AGG. If you are going to be doing a lot of this kind of work and you have the possibility, I would change to PostgreSQL - it's much more powerful for this sort of work.

One "fly in the ointment" though!

I hope the above anwers your query but I'm not sure that it corresponds to your own fiddle. For example, you have

insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (1, 100, 1000, '2019-10-01', 1);

and for jobid 1000, you have

insert into jobs values (1000, '2019-10-01', 100, 4);

However, the payment above is the one and only payment corresponding to the full amount for jobid 1000, but in your result set, you have

jobid     payment_hist
1000,     100, 175

The first payment is the 100, but I'm at a loss to understand where the 175 comes from - it's not from other payments and all of your jobids appear to have been paid in full in a single payment, so no partial payments which is what I thought your question was about?


Triggers will not run in a couple of scenarios.

1) Obviously if the trigger is disabled

2) If the trigger is marked as NOT FOR REPLICATION and the DML is issued by the replication Distribution Agent.

3) If the user has ALTER TABLE permissions and is performing a BULK INSERT, BCP or SqlBulkCopy, etc with the FIRE_TRIGGERS option disabled.


That should be simple with array_agg and additional FILTER clauses:

SELECT
    posts.id, posts.content, users.name as author
    array_agg(likes.user_id) FILTER (WHERE likes.is_liked) as likes,
    array_agg(likes.user_id) FILTER (WHERE likes.is_disliked) as dislikes
FROM posts
   INNER JOIN users ON posts.user_id = users.id
   INNER JOIN likes ON likes.post_id = posts.id
GROUP BY posts.id, posts.content, users.name;


Attempt to fetch logical page (5:65424) in database 2 failed

If the database ID indicated by the error message is 2, the database affected is tempdb.  One method for fixing this type of corruption in tempdb is to simply restart the SQL Server instance.  For database ID's other than 2, follow the recommendations below.

You may need to restore from backup, however try this:

As a member of the sysadmin role, execute DBCC PAGE (2, 5, 65424, 3); and look for the Metadata: IndexId value.

If it is 0 (heap) or 1 (clustered index) you need to restore from backup.
If it is not 0 or 1, you can just rebuild the non-clustered index.

Run the DBCC command, and let us know what you find.  Check https://www.mssqltips.com/sqlservertip/1578/using-dbcc-page-to-examine-sql-server-table-and-index-data/ for details about the DBCC PAGE command


Referencing a stored procedure by a name description across all databases in a SQL Server 2000 Instance

If anyone is curious how the cursor code would look, from this MSDN post:

DECLARE @SearchString VARCHAR(255)
DECLARE @name VARCHAR(50)
DECLARE @db_name1 VARCHAR(50)
DECLARE @db_name2 VARCHAR(50)
DECLARE @sql VARCHAR(100)
SET @SearchString = 'a_ras_Arlenes_ship_sched_sp'

DECLARE db_cursor CURSOR FOR

SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor

FETCH NEXT FROM db_cursor WITH (NOLOCK) INTO @name

While @@FETCH_STATUS = 0
BEGIN
  SET @db_name1 =@name+'.dbo.syscomments'
  SET @db_name2 =@name+'.dbo.sysobjects'

  SELECT @sql = 'SELECT DISTINCT b.name FROM ' + @db_name1
              + ' a JOIN ' + @db_name2 + ' b ON a.id = b.id'
              +' WHERE a.text like %' + @SearchString + '% ORDER BY b.name'

  EXEC(@sql)

  FETCH NEXT FROM db_cursor WITH (NOLOCK) INTO @name

END
Close db_cursor
DEALLOCATE db_cursor


How to get N rows after the first row matching a condition?

You could simply add the column used for the ordering (description) to the SELECT list and the WHERE clause of the following queries:

-- 1st query:
SELECT
  id, description
FROM probe
ORDER BY description, id
LIMIT 5 ;

Lets say that you get @id = 3 and @description = 'Beta' as the last (5th) row. You can then use these values to get the next (5) rows:

-- next query:
SELECT
  id, description
FROM probe
WHERE (description, id) > (@description, @id)
ORDER BY description, id
LIMIT 5 ;


Advantage of using a database snapshot for reporting purposes

Using a database snapshot located on your production OLTP server will, in all likelihood, make performance worse. There are two main reasons:

Extra write overhead. Snapshots are copy-on-write, so this will obviously impact performance.
A snapshot will share pages from the old database on disk, but it uses its own memory in the buffer pool. If you have a large table, it will end up consuming memory for both the OLTP database, and the snapshot, even for the unmodified pages. You can end up with a lot of additional memory usage because of this, which can lead to more disk I/O if your server doesn't have loads of free RAM.

So if you're trying to make reporting less intrusive on only a single server, you're probably better off just querying the OLTP database and using WITH (NOLOCK) hints, and then reigning things in with the resource governor if needed. Beyond that, look into some kind of scale-out system (log shipping, mirroring, etc.)


Mysql get records more then 3 in interval of 1 minute

To answer your question, I did the following (works for MySQL 8 - window functions used - see fiddle here):
Created the table:
CREATE TABLE test
(
  created_date TIMESTAMP(1) NOT NULL,
  origin_url   VARCHAR (200) NOT NULL,
  client_session_id VARCHAR (50) NOT NULL
);

Populate it (sample):
INSERT INTO test VALUES
('2021-01-18 11:02:24.0', 'https://ttttttt.com/', 'znkjoc3gfth2c3m0t1klii'),
('2021-01-18 11:02:35.0', 'https://zendes.com/', 'znkjoc3gfth2c3m0t1klii'),
('2021-01-18 11:11:03.0', 'https://testett.com/', 'znkjoc3gfth2c3m0t1klii'),
('2021-01-18 11:49:28.0', 'https://zendes.com/', 'znkjoc3gfth2c3m0t1klii'),
('2021-01-18 11:50:36.0', 'https://zendes.com/', 'znkjoc3gfth2c3m0t1klii'),
('2021-01-18 11:51:05.0', 'https://zendes.com/', 'znkjoc3gfth2c3m0t1klii');

Then I used the LEAD() window function - the syntax is as follows:
LEAD(<expression>[,offset[, default_value]]) OVER (
    PARTITION BY (expr)
    ORDER BY (expr)
)

The initial SQL is:
SELECT
  created_date,
  LEAD(created_date, 2) OVER (PARTITION BY origin_url, client_session_id
                            ORDER BY created_date DESC, origin_url, client_session_id) AS l_3,
  TIMESTAMPDIFF
  (
    MINUTE,
    created_date,
    LEAD(created_date, 2) OVER (PARTITION BY origin_url, client_session_id
                            ORDER BY created_date DESC, origin_url, client_session_id)
  ) AS min_diff,
  origin_url,
  client_session_id
FROM test;

Result:
created_date    l_3 min_diff    ut_d    origin_url  client_session_id
2021-01-18 11:11:03.0                                       https://testett.com/    znkjoc3gfth2c3m0t1klii
2021-01-18 11:02:24.0                                       https://ttttttt.com/    znkjoc3gfth2c3m0t1klii
2021-01-18 11:51:05.0   2021-01-18 11:49:28.0   1   97.0    https://zendes.com/ znkjoc3gfth2c3m0t1klii
2021-01-18 11:50:36.0   2021-01-18 11:02:35.0   48  2881.0  https://zendes.com/ znkjoc3gfth2c3m0t1klii
2021-01-18 11:49:28.0                                       https://zendes.com/ znkjoc3gfth2c3m0t1klii
2021-01-18 11:02:35.0                                       https://zendes.com/ znkjoc3gfth2c3m0t1klii

So, it was noticed that UNIXTIMESTAMP (gives the difference in seconds) might be better than TIMESTAMPDIFF - (see here) - so, in the end, I used (see the fiddle for the result):
SELECT
  created_date,
  LEAD(created_date, 2) OVER (PARTITION BY origin_url, client_session_id
                            ORDER BY created_date DESC, origin_url, client_session_id) AS l_3,
  ABS(TIMESTAMPDIFF
  (
    MINUTE,
    created_date,
    LEAD(created_date, 2) OVER (PARTITION BY origin_url, client_session_id
                            ORDER BY created_date DESC, origin_url, client_session_id)
  )) AS min_diff,
  UNIX_TIMESTAMP(created_date) -
  UNIX_TIMESTAMP
  (
    LEAD(created_date, 2) OVER (PARTITION BY origin_url, client_session_id
                            ORDER BY created_date DESC, origin_url, client_session_id)
  ) AS ut_d,
  origin_url,
  client_session_id
FROM test;

The final SQL and result are:
SELECT * FROM
(
  SELECT
    created_date,
    LEAD(created_date, 2)
           OVER (PARTITION BY origin_url, client_session_id
                   ORDER BY created_date DESC, origin_url, client_session_id) AS l_3,
    created_date -
    LEAD(created_date, 2)
           OVER (PARTITION BY origin_url, client_session_id
                   ORDER BY created_date DESC, origin_url, client_session_id) AS l_diff,
    UNIX_TIMESTAMP(created_date) -
    UNIX_TIMESTAMP
    (
      LEAD(created_date, 2) OVER (PARTITION BY origin_url, client_session_id
                            ORDER BY created_date DESC, origin_url, client_session_id)
    ) AS ut_d,
    origin_url,
    client_session_id
  FROM test
) AS t
WHERE t.ut_d  < 180;

Result:
         created_date                     l_3   l_diff  ut_d    origin_url  client_session_id
2021-01-18 11:51:05.0   2021-01-18 11:49:28.0    177.0  97.0    https://zendes.com/
znkjoc3gfth2c3m0t1klii

You mention that you have MySQL 5.7 - which doesn't support window functions. You can either upgrade or use various simulation methods - my advice is to upgrade.


DBForge Studio has exactly what you are looking for.

Once installed, go to Tools -> Skin -> Visual Studio 2013 Dark. Everything will be dark (The database tree view, the query windows, the table results, etc).


create a backup table with million records

In case you won't need the data immediately, you can try extracting table structure, then rename it to backup, and create a new table with the same structure and name.

ALTER TABLE table_name RENAME TO new_table_name;

This will show the table structure. you can use the result to create new table.

>describe table_name

many suggestions are out there to copy by the select statement onstackoverflow. one is:

create table table_name as select * from table_name_oldd where 1=0;

It creates structure but won't copy any value because the condition is false.


No.

TRUNCATE and DROP are almost identical in behavior and speed, so doing a TRUNCATE right before a DROP is simply unnecessary.

Note: I wrote this answer from a SQL Server perspective and assumed it would apply equally to Sybase. It appears that this is not entirely the case.

Note: When I first posted this answer, there were several other highly rated answers -- including the then-accepted answer -- that made several false claims like: TRUNCATE is not logged; TRUNCATE cannot be rolled back; TRUNCATE is faster than DROP; etc.

Now that this thread has been cleaned up, the rebuttals that follow may seem tangential to the original question. I leave them here as a reference for others looking to debunk these myths.

There are a couple of popular falsehoods -- pervasive even among experienced DBAs -- that may have motivated this TRUNCATE-then-DROP pattern. They are:

Myth: TRUNCATE is not logged, therefore it cannot be rolled back.
Myth: TRUNCATE is faster than DROP.

Let me rebut these falsehoods. I am writing this rebuttal from a SQL Server perspective, but everything I say here should be equally applicable to Sybase.

TRUNCATE is logged, and it can be rolled back.

TRUNCATE is a logged operation, so it can be rolled back. Just wrap it in a transaction.

USE [tempdb];
SET NOCOUNT ON;

CREATE TABLE truncate_demo (
    whatever    VARCHAR(10)
);

INSERT INTO truncate_demo (whatever)
VALUES ('log this');

BEGIN TRANSACTION;
    TRUNCATE TABLE truncate_demo;
ROLLBACK TRANSACTION;

SELECT *
FROM truncate_demo;

DROP TABLE truncate_demo;

Note, however, that this is not true for Oracle. Though logged and protected by Oracle's undo and redo functionality, TRUNCATE and other DDL statements can't be rolled back by the user because Oracle issues implicit commits immediately before and after all DDL statements.
TRUNCATE is minimally logged, as opposed to fully logged. What does that mean? Say you TRUNCATE a table. Instead of putting each deleted row in the transaction log, TRUNCATE just marks the data pages they live on as unallocated.  That's why it's so fast.  That's also why you cannot recover the rows of a TRUNCATE-ed table from the transaction log using a log reader.  All you'll find there are references to the deallocated data pages.

Compare this to DELETE.  If you DELETE all the rows in a table and commit the transaction you can still, in theory, find the deleted rows in the transaction log and recover them from there. That's because DELETE writes every deleted row to the transaction log. For large tables, this will make it much slower than TRUNCATE.

DROP is just as fast as TRUNCATE.

Like TRUNCATE, DROP is a minimally logged operation.  That means DROP can be rolled back too. That also means it works exactly the same way as TRUNCATE.  Instead of deleting individual rows, DROP marks the appropriate data pages as unallocated and additionally marks the table's metadata as deleted.
Because TRUNCATE and DROP work exactly the same way, they run just as fast as one another.  There is no point to TRUNCATE-ing a table before DROP-ing it. Run this demo script on your development instance if you don't believe me.

On my local machine with a warm cache, the results I get are as follows:

table row count: 134,217,728

run#        transaction duration (ms)
      TRUNCATE   TRUNCATE then DROP   DROP
==========================================
01       0               1             4
02       0              39             1
03       0               1             1
04       0               2             1
05       0               1             1
06       0              25             1
07       0               1             1
08       0               1             1
09       0               1             1
10       0              12             1
------------------------------------------
avg      0              8.4           1.3

So, for a 134 million row table both DROP and TRUNCATE take effectively no time at all. (On a cold cache they take about 2-3 seconds for the first run or two.) I also believe that the higher average duration for the TRUNCATE then DROP operation is attributable to load variations on my local machine and not because the combination is somehow magically an order of magnitude worse than the individual operations. They are, after all, almost exactly the same thing.

If you're interested in more detail about the logging overhead of these operations, Martin has a straightforward explanation of that.


Normalizing/combing mutliple tables with similar data

Functional dependencies and normalization
In order to carry out a normalization exercise involving second and further normal forms —as per the relational model of data by Dr. E. F Codd—, one first has to know what are the relevant functional dependencies (FDs for brevity) between the attributes (usually depicted as columns) of an adapted mathematical relation (usually portrayed as a table). This kind of exercise belongs to the logical-level of abstraction of a database. That is why, in order to address your stated intention of obtaining a normalized set, I requested information about the applicable FDs via comments.
For instance, a FD involving the hypothetical attributes Foo and Bar can be depicted as Foo → Bar, which may in turn be read as “attribute Foo determines attribute Bar” or “attribute Bar is determined by attribute Foo”. In this way one can distinguish (a) one or more attributes, or one or more combinations of attributes, that are the key, or the keys, of a relation and (b) distinguish the attributes that are not, or are not part of, the key or keys.
Regarding the scenario you describe, let us suppose that the grid entitled Transmissions is a concrete representation of a mathematical relation. With respect to the values of the columns labeled as submodel, body_code, engine_code, engine, desplacement, cylinders, trans_type, trans_code and qualifier, one can say that:

Some (engine_code and trans_code) appear to be determined by the values of what would be the key of a relation called, let us say, Vehicle — i.e., the combination of (model, make, year).

Some (transmission_type and qualifier) appear to be determined by the values of what would be the key of a relation called, let us say, TransmissionAdaptor — i.e., trans_code.

Some (engine_type, displacement and cylinders) appear to be determined by the values of what would be the key of a relation called, let us say, Engine — i.e., engine_code.

But those are simple assumptions, based on my personal interpretation of the information contained in the aforementioned grid, and evidently I am not familiar at all with the business context in question. Therefore, in order to get rid of those kinds of needless and problematic assumptions you have to interview the business experts, and they will help you to identify the FDs, which would in turn guide you in performing proper normalization and laying out the database structure with the precision that the database administration profession requires. If there are no business experts who you can resort to, then you will have to dive into the data sets, observe data usage and meaning, and analyze carefully the interconnections between the pieces of information of interest to determine the significant FDs on your own.
A normalized set
Having a normalized database helps to avoid update/modification anomalies (affecting INSERT, UPDATE and DELETE operations) that  arise eventually when there exist undesirable dependencies among the attributes (columns) of the relations (tables) under consideration. In cases in which, e.g., there are attributes of a relation that (i) depend on non-key attributes, or that (ii) depend on parts of a composite —i.e., multi-attribute— key, the designer has to decompose the concerning relation into two or more.
Thus, most of the time, a normalized set is made up of various relations, each of which is meant to contain exactly one particular type of fact in its tuples (rows). Contrarily, unnormalized and non-fully-normalized sets consist of one or more relations that contain more than one type of fact in their tuples.
The design of a database starting from the conceptual level of abstraction
On the other hand, you can design the relevant database starting from a different perspective, analyzing first the structure and associations —or relationshipsa or connections— of the types of things of interest from a purely conceptual point of view, without thinking about relations (tables), attributes (columns), constraints and normalization yet. Of course, this approach also entails close communication between the database designer(s) and the business experts or, in absence of business experts, database designer(s) with in-depth knowledge of the informational needs and characteristics of the business environment.
Sample business rules
So I will put together some hypothetical business rules that will help in the creation of an expository conceptual schema based on mere assumptions about the information contained in your question.
A Vehicle is:

primarily identified by exactly one combination of Model, Make and Year
moved by exactly one Engine
equipped with exactly one TransmissionAdaptor
…

An Engine:

is primarily identified by exactly one EngineCode
is catalogued by exactly one EngineType
has exactly one Displacement
holds exactly one type of Cylinders
is provided with exactly one FuelSystem
is installed on zero, one or more Vehicles
…

A TransmissionAdaptor is:

primarily identified by exactly one TransmissionCode
categorized by exactly one TransmissionType
installed on zero, one or more Vehicles
…

A FuelSystem:

is primarily identified by exactly one Name
is classified by exactly one FuelSystemType
has exactly one Pressure
is installed on zero, one or more Vehicles
…

As demonstrated, (a) the hypothetical association types between the properties of the possible entity types of relevance, and (b) the hypothetical association types between the possible entity types themselves, have been stated in a relatively clear manner.
Clearly, these rules are exclusively a medium to expound a method you could follow to design your database. Being devised as personal interpretations, they of course ought to be confirmed, refuted or adapted in accordance with real business environment features.
Sometimes, a database designer draws a diagram portraying the conceptual schema definitions so as to supply a graphical tool that assists in the communication among all the interested —technical and non-technical— parties.
When the results of this stage are stable to a certain degree, one can start thinking about representing these conceptual aspects by means of logical level constructs more confidently.
a By the way, it is opportune to point out that a conceptual relationship is very different from a logical relation.
Illustrative SQL-DDL logical-level design
Successively, I have created the following four tables that represent the conceptual-level formulations elaborated above:
CREATE TABLE TransmissionAdaptor (
    TransmissionAdaptorCode CHAR(10) NOT NULL,
    TransmissionType        CHAR(30) NOT NULL,
    --
    CONSTRAINT TransmissionAdaptor_PK PRIMARY KEY (TransmissionAdaptorCode)
);

CREATE TABLE FuelSystem (
    Name           CHAR(6)  NOT NULL,
    FuelSystemType CHAR(10) NOT NULL,
    Pressure       CHAR(10) NOT NULL,
    FuelSystemName CHAR(30) NOT NULL,
    --
    CONSTRAINT FuelSystem_PK PRIMARY KEY (Name)
);

CREATE TABLE MyEngine (
    EngineCode     CHAR(3) NOT NULL,
    EngineType     CHAR(8) NOT NULL,
    Displacement   CHAR(3) NOT NULL,
    Cylinders      CHAR(3) NOT NULL,
    FuelSystemName CHAR(6) NOT NULL,
    --
    CONSTRAINT Engine_PK               PRIMARY KEY (EngineCode),
    CONSTRAINT Engine_to_FuelSystem_FK FOREIGN KEY (FuelSystemName)
        REFERENCES FuelSystem (Name)
);

CREATE TABLE Vehicle (
    Model                   CHAR(20) NOT NULL,
    Make                    CHAR(10) NOT NULL,
    MyYear                  SMALLINT NOT NULL,
    EngineCode              CHAR(3)  NOT NULL,
    TransmissionAdaptorCode CHAR(10) NOT NULL,
    --
    CONSTRAINT Vehicle_PK                 PRIMARY KEY (Model, Make, MyYear),
    CONSTRAINT Vehicle_to_Engine_FK       FOREIGN KEY (EngineCode)
        REFERENCES MyEngine (EngineCode),
    CONSTRAINT Vehicle_to_Transmission_FK FOREIGN KEY (TransmissionAdaptorCode)
        REFERENCES TransmissionAdaptor (TransmissionAdaptorCode)
);

Since now one would be dealing with logical-level elements, with tables that have clearly declared keys that are based on a clear conceptual schema, a proper normal forms assessment exercise would be called for in order to test the soundness of the design.
Personally, I find that the design sequence described above, i.e.,

(1) conceptual formulations ⟷ (2) logical representation ⟷ (3) testing via normal forms

is more natural than directly working with “isolated” functional dependencies, since it aids the designer(s) in understanding the scenario from different levels of abstraction in a thorough way.
Further considerations
System-assigned surrogates
As you can notice, I have not attached an extra Id column (commonly added to contain system-generated surrogate key values) to any of these base tables, since doing so only hinders the (conceptual) modelling and (logical) normalization tasks. Once you have a stable structure with the corresponding constraints, it would be suitable to evaluate the specific cases where the addition of that non-data artifact is beneficial.
Column datatypes
An important factor that was not discussed in detail is the particular domain associated with each column. So, when you continue working on the design of the database, you have to determine what are the most fitting datatypes and sizes of each column.
Data derivation
Having a logical layout like the one above, you have to make use of derived tables (e.g., tables declared by means of SELECT operations that gather columns FROM one or more base or —other— derived tables) to obtain the information as shown, e.g., in your Transmission grid.
Of course, a derived table can be defined as a view that can be queried on further, so as to, e.g., ease writing the code of future data manipulation operations.
It might be useful to study this point to see if you can accomplish your stated objective of, ideally, ending up with just one table of Vehicle data.
Physical-level aspects
The physical level of abstraction is another point that decidedly requires attention, since the configuration of the most convenient indexes is directly associated with the optimal functioning of the database (e.g., reading and writing speed, scalability). Needless to say, you have to take into account the tendencies of the data manipulation operations (or queries) in this respect. Usually, you fix indexes that support columns that are involved, e.g., in WHERE and JOIN clauses.
One of the numerous shortcomings and disadvantages of MySQL as a database management system is that it does not offer built-in support for “indexed” or “materialized” views, so this factor is decidedly worth taking into account if you want to construct a database that works efficiently.
Other physical-level aspects of significance are, e.g., setting up the involved hardware (hard drive, memory, processor, etc.), the database management system, the operating system, the network bandwidth, etc., in an optimal way.

Addendum
Evidence of the importance of possessing full knowledge of the business domain of concern when designing a database arose in a recent comment interaction, in which @Rick James brought up a pertinent consideration about some entity types, their corresponding association types and cardinality ratios in your scenario:

It looks like transmissions:adaptors is many:many, not 1:many. Please clarify.

…to which you responded as follows:

Yes, this is correct. Transmissions have their own adaptors and FuelSystems have their own adaptors. A transmission belongs to zero, one or more transmission adaptor(s) and a transmission adaptor can belong to zero, one or more transmissions.

Consequently, as you know, it is necessary to model those conceptual entity types, i.e.:

Transmission, TransmissionAdaptor, FuelSystem and FuelSystemAdaptor,

and the connecting association types, i.e.:

Transmission-TransmissionAdaptor and FuelSystem-FuelSystemAdaptor,

so that they reflect your business domain characteristics with the demanded precision.
Once all the related aspects are represented in a logical-level DDL design with the respective tables (puttin up one for each entity/association type), columns, datatypes and constraints, you may like to assess the involved functional dependencies to deliver a strong, normalized, system.


SQLAnywhere: Switch to choose between WATCOM and T-SQL?

No, but if you use a specific WSQL syntax (such as the SET statement), your procedure will be compiled as WSQL.  Similarly, if you use TSQL specific syntax (such as GOTO), your procedure will be compiled as TSQL.  Mixing the two syntaxes generally results in an error.


There is no WHERE in that part of the MERGE statement. See MERGE (Transact-SQL) in the documentation for syntax help.

There is an optional AND part in WHEN MATCHED clause so the straightforward answer is to move the condition there:

MERGE @Emp emp
USING @EmpUpdates eup
ON emp.empid = eup.empid
WHEN MATCHED
     AND emp.empaddress <> eup.empaddress
  THEN
    UPDATE
    SET emp.empaddress = eup.empaddress
WHEN NOT MATCHED BY TARGET
  THEN
    INSERT (empid, empaddress)
    VALUES (eup.empid, eup.empaddress) ;

Be careful to handle nulls correctly there (if empaddress is nullable).

It is sometimes more explicit (and efficient) to perform the operations in two separate statements (UPDATE and INSERT in your case) in a transaction.

Also, be aware there have been (and still are) some issues with MERGE.


Why if I add one more condition on WHERE clause, then using an index fails?

Perhaps you already know, but Using index confuses a lot of people -- they assume it means the query is using an index, and that's not what it means.

That means the query is using the index as a covering index -- reading row data from the index, ignoring the main table because the index contains all the necessary data, which is generally assumed to be faster since the amount of data to be read should be smaller.

Adding the extra condition -- which is not sargable, but that's a second problem -- defeats the covering index, which is expected, but it also triggers a full table scan (type is ALL and key is NULL), which seems counter-intuitive since this can still be resolved with an equality ref against the index and evaluating the condition Using where... and this is probably because you're testing against a small data set.  With more rows, the optimizer might choose a different path.

An index on (author_id, date_time) would be useful -- and might even switch the query back to Using index, though it wouldn't be optimal, because -- as I mentioned -- it's not sargable.

Except in very limited circumstances, you don't want to use a column as the argument to a function in the WHERE clause, because this must be evaluated by scanning or at least filtering.

Assuming answer.date_time is an INT UNSIGNED column containing the unix epoch time of the event (otherwise using FROM_UNIXTIME() here is incorrect, anyway) the more desirable expression is this:

AND answer.date_time
    BETWEEN UNIX_TIMESTAMP(now() - INTERVAL 1 year)
        AND UNIX_TIMESTAMP(now() - INTERVAL 1 hour);

The value of NOW() is frozen when a query begins executing, no matter how long the query takes to run.  Because of this fact, UNIX_TIMESTAMP(NOW() - INTERVAL 1 YEAR) is resolvable to a constant, as is UNIX_TIMESTAMP(NOW() - INTERVAL 1 HOUR).  The optimizer will resolve both of these to integer constants, and then can use an index including the date_time column to identify the specific rows using a range scan.


How to whitelist all connections in MySQL

This should be easily achieved by issuing the command:

GRANT ALL ON *.* to user@'%'; -- you can also use dbname.* to restrict their
                              -- access to a given database.

as explained here and here.

You can do some restricting (normally a good idea) by making use of the wildcard (% is the MySQL wildcard) - you can also do '%.domain.com' or '%.123.123.123' or similar if necessary.

You don't have to GRANT ALL PRIVILEGES - you could just give them SELECT and/or UPDATE for example and not INSERT... This is all explained here.


Is there an easy way to update a mysqldump file from version 9.11 to 10.11?

Well,

I figured out that if you use the backticks ("`") around the table name (like mysqldump uses in the comment-separator sections, e.g. `call`), it works as expected.

So, the above should look like:

--
-- Table structure for table `call`
--

DROP TABLE IF EXISTS `call`;
CREATE TABLE `call` (
  path varchar(255) NOT NULL default '',
  call_num int(12) unsigned NOT NULL auto_increment,
  date datetime NOT NULL default '0000-00-00 00:00:00',
  idf varchar(4) NOT NULL default '404',
  location varchar(255) default NULL,
  who_req varchar(8) default NULL,
  who_res varchar(8) default NULL,
  PRIMARY KEY  (path,call_num)
) ENGINE=MyISAM;

--
-- Dumping data for table `call`
--

Hope that helps.


Poor performance on MySQL 5.7 on joined tables ( scanning wrong table ?)

Damned if you do, damned if you don't.  The Optimizer has two choices -- filter on language in one table first, or scan on date in the other table first.

If the recent articles are in English, then using the date is better.  If you ask for some language with few articles, the other index is better.

Meanwhile, the Optimizer does not have the smarts to look in two different directions at once to decide which is better.

I would argue that the schema is not 'correct'.  Can't a publisher provide news in multiple languages?  If so, language should be in news, not in publishers.  If you make than change, then INDEX(language, date_added) would become an better index.

I have addressed the general "news" list problem with the addition and maintenance of a separate table.


How to find value for specific dates for a process stored in table

Details will depend on the database engine, but I think Pivot is the key search term you want.  An approach that can probably be adapted for use with many database engines is described on SO here: https://stackoverflow.com/questions/5846007/sql-query-to-pivot-a-column-using-case-when.  SQL Server has had a PIVOT operator since version 2008: https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot.

Martin


When I run a query for the first time, it takes 3-10 seconds to show me the results. On running same query again, it is in less than 1 second (very fast)

The second time you run the query, it is likely that data and index pages for the relational tables are coming from SQL Server's cache rather than being read in from disk. Likewise, the full-text portion of the processing will also benefit from caching.

As the comments on the question indicate, 4GB does not seem like very much memory for a SQL Server managing a 65GB database. More physical memory will allow SQL Server to cache a greater proportion of your relational tables and full-text indexed data.

The exact amount you need depends on the nature of your workload, and the response times your users expect. I would imagine that expanding the memory even modestly (say to 8 or 16GB) would result in a very noticeable performance improvement, and more predictable response times overall.

You would also want to configure the maximum memory for SQL Server to ensure there is enough for Windows as well. See the following links for more information on memory (and other important details):

How much memory does my SQL Server actually need?

Troubleshooting SQL Server: A Guide for the Accidental DBA (free eBook)


How to get time_zone from other sessions

While the V$SES_OPTIMIZER_ENV view lets you see the values of parameters that affect the Cost Based Optimizer across sessions, there does not appear to be any view that lets you see non-performance related settings. Besides the security implications of seeing that information, generally those settings are of interest only to an individual session.


SQL Server Maintenance Plan - Multiple vs Single Plan / Cleanup History

First Answer
It depends, i.e. number of databases, database size (maybe all database might not need daily backup and log backup). However, for flexible and reliable approach I would refer this solution.
Second Answer
The retention period must be decided based on the requirement of business and nature of the data, most cases 30-60 days retention period followed, but again it depends on your environment and business, to get an idea with the backup storage requirement quickly, take the following query result into excel, hope you can easily analyze to get average backup size per day, per month etc..
select    backup_finish_date
        , FORMAT (backup_finish_date, 'yyyyMM') as backup_month
        , database_name
        , cast( (backup_size / 1048576) as numeric (12,2)) backup_size_MB
        , cast( (compressed_backup_size / 1048576) as numeric (12,2)) compressed_backup_size_MB
from msdb.dbo.backupset

Note: BACKUP Compression not possible in SQL Express edition


What is the F301: the CORRESPONDING clause in query expression?

In a nutshell, CORRESPONDING was introduced into the SQL standard to make the syntax more in the spirit of the relational model (RM).

A full answer would inevitable involve a discussion about how the SQL language has strayed from the relational model specifically (e.g. nulls) and good language design generally (e.g. data types).

In essence, before 1992 some SQL syntax such as UNION relied on left-to-right ordering of columns. For example, without nitpicking about data types and their compatibility etc, the following would not work:

SELECT col1, col2 FROM Table1 WHERE col3 = 0
UNION
SELECT col2, col1 FROM Table1 WHERE col3 = 1

because the columns do not follow the same left-to-right ordering.

Of course, in the RM a relation has a set of attributes and a set by implication has no ordering. However, the original SQL UNION syntax implies that SELECT col1, col2 FROM Table1 is somehow not the same result as SELECT col2, col1 FROM Table1.

Therefore, UNION  CORRESPONDING corrects the earlier design flaw i.e.

SELECT col1, col2 FROM Table1 WHERE col3 = 0
UNION CORRESPONDING
SELECT col2, col1 FROM Table1 WHERE col3 = 1

will give the correct result.


What's the fastest way to migrate an Oracle database from Unix to Linux?

Is hard to say which method is faster, but I would say from my experience using source backup is faster than import.

It seems the issue you see with the file header when you use the source files comes from the file format of AIX which is big endian, while on Linux is small endian.

The files need to be converted. This can be done using RMAN:

RMAN> convert datafile 'bla'
    TO PLATFORM="Linux 64-bit for AMD (or your platform)"
    FROM PLATFORM="AIX-Based Systems (64-bit) - or your platform"

    (note: line breaks added for readability)


How to pass an array of arguments to User Defined Aggregate in SQL Server

The SQLCLR API does not allow for either overloading of methods nor the params keyword.

To pass in a variable number of parameters, you have a few options:

Concatenate them into a delimited string that can be split within the .NET code (this is your current solution)
Create a User-Defined Type (UDT) that is effectively just a wrapper to a simple array / generic list / dictionary (whichever is appropriate for your use- case). The UDT can have an "internal" method / property that is not exposed to the SQLCLR API but returns the native .NET collection such that you can get the values out of it directly. You would specify the name of the .NET struct / class of the UDT in the UDA signature instead of a Sql* type. This option allows for a variable number of elements (i.e. "parameters) to be passed in, but they will not be strongly typed (well, unless they are naturally all of the same type on the T-SQL side to begin with).
If you have a relatively small and finite number of parameters to pass in, and they are of different types, you can create a UDT that has SQLCLR-exposed properties which are each one of the desired "parameters". Like suggestion #2 directly above, you would use this UDT as the parameter type of the UDA and then access its properties directly in the UDA code to get the various "parameter" values. For example (partial example code!, not full solution):

internal string _Param1;
internal int _Param2;

public SqlString Param1
{
    get
    {
        if (IsNull)
        {
            return SqlString.Null;
        }
        else
        {
            return new SqlString(_Param1);
        }
    }

    set
    {
        if (!value.IsNull)
        {
            _Param1 = value.Value;
        }
    }
}

public SqlInt32 Param2
{
    get
    {
        if (IsNull)
        {
            return SqlInt32.Null;
        }
        else
        {
            return new SqlInt32(_Param2);
        }
    }

    set
    {
        if (!value.IsNull)
        {
            _Param2 = value.Value;
        }
    }
}

Usage:

DECLARE @Params dbo.TypeParams;
SET @Params.Param1 = N'some string';
SET @Params.Param2 = 35;

SELECT dbo.MyAggregation(
           t.type,
           N'My aggregation property',
           @Params
       )
FROM  someTable t
GROUP BY t.type;

In your .NET method for MyAggregation:

[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined)]
public class MyAggregation : IBinarySerialize
{
    private someType _SomeVariable;

    public void Init()
    {
        _SomeVariable = someValue;
    }

    public void Accumulate(SqlInt32 TheType, SqlString TheProperty,
                           typeNameOfUDT Params)
    {
        if (TheType.IsNull)
        {
            return;
        }

        SomeMethod(TheType.Value, Params._Param1, Params._Param2);
    }

    ...
}


I thought about Master / Slave setup, but I'm not sure how can I prevent deletes from replicating to slave.

You can't do that. Replication is intended to replicate as much as possible to keep the slaves as accurate copies of the master(s).

You might want to replicate to another machine so that you can have a copy to run the copy-to-archive archive process on, so that this does not impact performance of the application when being run, but that is optional (and not necessary if the process doesn't impact live performance much or you have a time window in which you don't care if it does (i.e. over-night if your application is predominantly used in one timezone so you have quiet periods when most users are sleeping)).

The most efficient way to update the archive/reporting database from the production one (either the main DB or a read-only replica), and clean up production once this is done, depends a lot on the structure of your data.


Bob,

I am NOT a big fan of maintenance plans and I try to avoid using them. While they work ok for small needs it becomes hard to customize them but some ppl disagree with that.

You need to look at the Maintenance Plan cleanup task. Make sure you enter the extension as 'bak' without quotes instead of '.bak' without quotes. The following two articles will help for you. Let us know if you need further help.

http://msdn.microsoft.com/en-us/library/ms177182.aspx

http://www.bidn.com/blogs/BradSchacht/ssis/249/sql-maintenance-plan-doesn-t-delete-backups


Choose to save the package instead of running it right away.
Then edit the dtsx file and on your copy task check "keep nulls".
Then run the dtsx.
I didn't test it but think it will solve your case.

Testing the suggestion on the comment on express with advanced services... it worked well:)
You create the destination table, set a null default on the column and after importing data with the dtsx you remove the default or set it to whatever you want. dtsx import will replace nulls with the default value of the column when you can't check the "keep nulls".

create table dbo.test (
 a int null
 , other columns ...
 )

alter table dbo.test add constraint x default null for a

--Run Import task here

alter table dbo.test drop constraint x


Nvarchar column getting truncated to 256 characters

My guess (which turned out to be correct) was that a trigger was modifying / truncating the value somehow.

I usually first check to make sure I am really observing the data correctly, since SSMS truncates display output by default (and it's different in results to text/grid). So checking the value, LEN, MAX(LEN()), and ensuring you are looking at the right column, the right table, and that your definition has been updated correctly, are useful things to check.

Once you've ruled that out, if you were updating from application, I would check to make sure the app doesn't have the wrong data type / size for its local variables or command parameters.

If you were calling a stored procedure, I would check the parameter, since silent truncation can occur here:

CREATE PROCEDURE dbo.foo @x nvarchar(255)
AS PRINT @x;
GO

DECLARE @string nvarchar(512) = REPLICATE(N'x', 254)
  + REPLICATE(N'y', 258);

PRINT @string;
EXEC dbo.foo @x = @string;

Results:

xxx...xxxyyy...
xxx...xxxy

The second value is obviously truncated to the parameter definition (but no warning or error is ever logged).

But if you are getting truncation from a direct update in SSMS, and your parameters / variables / constants look right, dig for a trigger. It's likely the 255 definition is still hard-coded there somewhere.


restrict data from select with Row-Level Security when updating

By default, row level security does not apply to the owner of the table.

If you want that, you got to

ALTER TABLE tbl2 FORCE ROW LEVEL SECURITY;

There are some other settings that may cause RLS not to be used:

If the configuration parameter row_security is off.
If the user is a superuser.
If the user was created with BYPASSRLS.


Only explanation I can think of that result, if you are sorting with Order By Items ASC as you said, is that Items column is of character datatype (either VARCHAR or CHAR) instead of numeric.

13, 533, 7, 9100 are in lexicographical order, not numerical order.

Check the data type of that column.


Restoring Oracle prod backup to test server with new database name

rule #1) use rman for backup, recovery and cloning of databases.

since this was a user managed backup, and you need your datafiles on a different location, there are a few things to do.

1) copy the file to where you want
2) create a pfile.ora that references your control_files
3) startup mount your database

at this moment you have control_files that still refer to the original datafiles locations. They need to be redirected.

4) for all your files in v$datafile, issue "alter database rename
file '/old/name' to '/new/name';
5) for all your members in v$logfile, issue "alter database rename
file '/old/name' to '/new/name';

now you should be able to open your database.

6) alter database open;

If you get problems before or during mount, it is probably because of wrong dbname, db_unique_name. Set them as they were/are in prod.


It's a little bit more complicated, you must assign each variable @type_number and @file_type again.

SELECT file_type, file_name, id
FROM (
      SELECT  @type_number := IF(@file_type = file_type, @type_number + 1, 1) AS type_number,
              @file_type := file_type as file_type,
              file_name, id
      FROM
          (SELECT @type_number := 1) x,
          (SELECT id, file_name, @file_type := file_type as file_type FROM ttest ORDER BY file_type) y
    ) z
WHERE type_number <= 2;

Check it here: http://rextester.com/IUPWT52140


how to store multiple value in column in sql server

I don't think you want to store multiple values in a single column in the jobs table; this calls for what is known as a junction or many-to-many table.

CREATE TABLE dbo.Jobs
(
  JobID             INT PRIMARY KEY,
  JobTitle          VARCHAR(255),
  JobDescription    NVARCHAR(4000),
  MinimumExperience TINYINT,
  MaximumExperience TINYINT,
  LastDate          DATETIME, -- what is this? Needs a better name I think
  InterviewDate     DATETIME, -- is this really a property of the job?
  ProjectID         INT, -- foreign key references dbo.Projects?
  HiringManagerID   INT, -- foreign key references Employees table?
  InterviewerID     INT  -- foreign key references something?
);

(Assuming there is only one interviewer. If there are multiple, consider a many-to-many table.)

CREATE TABLE dbo.Skills
(
  SkillID SMALLINT PRIMARY KEY,
  SkillName VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE dbo.JobSkills
(
  JobID INT NOT NULL FOREIGN KEY REFERENCES dbo.Jobs(JobID),
  SkillID SMALLINT NOT NULL FOREIGN KEY REFERENCES dbo.Skills(SkillID),
  IsPrimary BIT NOT NULL DEFAULT 1
);


mysqldump: replace drop table in output with delete?

Specify to not use any creation SQL with --no-create-info

mysqldump mydatabase mytable --no-create-info --where="field=value"

If you want to overwrite the records on reload of the mysqldump, just add --replace

mysqldump mydatabase mytable --no-create-info --replace --where="field=value"

I suggested --replace because REPLACE is a mechanical DELETE and INSERT.

Give it a Try !!!


you can do it by following

CREATE TABLE #Temp(id int)

insert into #Temp values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)

CREATE TABLE #Temp2(val varchar(2))

insert into #Temp2 values('a'),('b'),('c')

--Get temp table using split funtion

DECLARE @var int
SELECT @var = COUNT(0)FROM #Temp2

;WITH
    CTE AS (SELECT ROW_NUMBER()OVER(ORDER BY ID)AS SNO,* FROM #Temp),
    CTE2 AS (SELECT ROW_NUMBER()OVER(ORDER BY val)AS SNO,* FROM #Temp2)
SELECT * FROM Cte c JOIN CTE2 d ON d.SNO= CASE WHEN c.Sno%@var = 0 THEN @var ELSE c.SNO%@var END
ORDER BY c.SNO
;

DROP TABLE #Temp
DROP TABLE #Temp2


How to convert a Top 1 subquery using outer table alias to Oracle?

you would perform the join first in Oracle:

SELECT a1, a2, b2
  FROM (SELECT a1, a2, b2,
               row_number() over(PARTITION BY a.a1 ORDER BY b.b2) lfd
           FROM AA a
           LEFT JOIN BB b ON b.b1 = a.a1
          WHERE a2 = 2)
 WHERE lfd = 1

The problem with your query is that currently a subquery in Oracle can't access a value from a parent query more than two level deeper.

You could also use a PL/SQL function that would contain the inner SELECT.


Connection is Using Properly or not in Net Use via SQL Procedure

Your NET USE command tells Windows - not just SQL Server - to map a network drive to a letter. When it runs, it makes that drive letter available to the SQL Server process, where it can be used for all connections.

A lot of things in SQL Server are per-connection - like creating a temp table - but what you're doing, mapping a network drive to a letter, is for the entire SQL Server process.

Your first thought might be, "Oh, so I need to check to see if the folder is mapped first - like use xp_dirtree to get the directory contents. If the folder is already mapped, then I can skip that NET USE step."

Unfortunately, that won't work either.

Because you mentioned at the end of your procedures, you're un-mapping those drives. That means you can run into this situation:

Instance A of the stored proc starts, checks to see if the drive is mapped. It isn't, so it maps the drive, and starts working.
Instance B of the stored proc starts, checks to see if the drive is mapped. It IS already mapped, so it just starts working without creating the drive mapping.
Instance A finishes up, and unmaps the drive.
Instance B was still running - but it now can no longer write to the drive. It fails.

So how do you work around it? Don't map drive letters. Access folders by their UNC path instead, like \\10.44.11.44\dpsshare. Grant the necessary permissions to the SQL Server's account rather than hard-coding usernames and passwords inside your application code. (Security is a much bigger discussion too - I get nervous when I see you exposing passwords in your code, and I also get nervous about SQL Server being able to write to a remote file share. You're in an area where application servers are a much better fit.)


Database staging environment for non-IT personnel

Usually a decent environment would be made of at least DEV -> TEST -> PRE-PROD -> PROD. Development would normally have access to develop in DEV, acceptance testing in TEST and IT to release in PRE and PROD. If you use source control you will avoid problems around editing the same piece of code by different devs at the same time.

Technically you only need schema to be the same as in prod and not need production data below pre-prod but if you are ok with having prod data outside prod environment t you could have an automated restores into dev/test. I have done similar work with Visual Cron and it works wonders.

Likely in order to stay compliant IT personnel  will have to release changes into pre and prod so to make this easier and more resilient you could go down the route of automated deployments.


How do I configure SQL Server Management Studio Intelisense to ignore case

In case your using Sql Server 2008, Intellisense in SSMS 2008 R2 is case sensitive but T-SQL itself may not.

It was marked as fixed for later versions.

https://connect.microsoft.com/SQLServer/feedback/details/672196/t-sql-is-not-case-sensitive-but-t-sql-intellisense-is

It have been fixed on SQL 2012. I know because I am using it. You don’t need to upgrade in order to use a new version of the SQMS but you need to have the license for the new version.  SSMS is compatible with older SS Database Engine Instances but the license requires purchasing the new version.
If you have a MSDN subscription or a SA agreement maybe you already have it.

https://social.msdn.microsoft.com/Forums/en-US/871cd19f-227a-44af-a702-4dabc00cb74c/sql-server-management-studio-licensing

http://www.microsoft.com/licensing/products/products.aspx

Can I use SQL Server Management Studio 2012 on a 2008 server?

P.D.

Workaround suggested by OP : In SSMS, click on Tools->options, then navigate to Text Editor->Transact-SQL->General and uncheck Auto list members and OK. This will prevent SSMS from replacing something that you typed perfectly with something else that is very wrong. You can still manually list members, when you want to by typing Ctrl-Space, choosing what you want from the list and pressing enter to complete what you typed.


Tracing is difficult in Oracle Database for several reasons:

you need to identify the involved database sessions which is more or
less easy
you need to use DBMS_MONITOR package (only granted to SYSDBA
by default)
you need to have access to the raw trace files (they are
generated on database server side - see V$DIAG_INFO view)
you need to use TKPROF on the raw trace file to have something
readable (TKPROF is part of Oracle Client but not Oracle Instant Client).

If you need to have only SQL statements without execution plan and detailed timings you could modify your code to enable SQL trace with "ALTER SESSION SET SQL_TRACE=TRUE" (raw trace file generated on db server side).

If you have your dedicated database instance (like Oracle XE) you could just enable SQL trace on database level (ALTER SYSTEM SET SQL_TRACE=TRUE) and get all trace files.

Here is one of the best summary of SQL tracing options in Oracle:
https://oracle-base.com/articles/misc/sql-trace-10046-trcsess-and-tkprof

You don't need OEM to enable trace but you need DBA privileges or to work with your DBA to use SQL trace.


galera cluster how to run an ALTER without using RSU/TOI

RSU:  You must bypass the load balancer and go straight to each node in turn.

TOI:  Simply run the ALTER on any node.  The downside is that if the ALTER takes a long time, it will, as you say, lock/stall the cluster.

gh-ost:  This depends on the binlog, which (I think) is not used in Galera.

pt-online-schema-change:  This requires adding TRIGGERs to the table -- perhaps a problem?  And it requires a script to continually talk to the server in question -- will your load balancer allow such?  And it probably wants to treat the system as Master-Slave(s), not Multi-Master -- apparently you have set up Galera set up Multi-Master?


How to calculate shift hours in postgres?

Assuming your status is consistent, i.e. always comes in pairs you could do something like:

select dt, employee_id, min_tm, max_tm, sum(delta_tm)
from (
    select dt
         , employee_id
         , min(tm) over (partition by employee_id,dt) as min_tm
         , max(tm) over (partition by employee_id,dt) as max_tm
         , tm - lag(tm) over (partition by employee_id,dt order by tm) delta_tm
         , status
    from t
) as u
where status = 'O'
group by dt, employee_id, min_tm, max_tm;


If you are totally sure about the separation status of your data, you can set the global isolation level to READ COMMITTED or even to READ UNCOMMITTED. The first blocks less from the index, which should be enough in your case. The second one arranges for all SELECT statements to be non blocking. This is as low as you can go.

Whatever you choose, you should set the global isolation level back to REPEATABLE READ as soon as you finished inserting your data.

global means that this applies to all connections, which are established. So this change also affects connections beside your jarfiles. If you have the possibility to change the jarfiles, you should set the connection based isolation level with the SESSION keyword instead in both jarfiles.


Find the total number of rentals for each genre

I've reduced a bit your table schema:

create table movie
(
    id int primary key,
    title varchar(20)
);

create table genre
(
    id int primary key,
    name varchar(20)
);

create table movie_genre
(
    movie_id int,
    genre_id int,
    primary key (movie_id, genre_id),
    foreign key (movie_id) references movie (id),
    foreign key (genre_id) references genre (id)
);

create table rental
(
    id int primary key,
    movie_id int
);

And then insert some values:

insert into movie values
(1, 'movie 1'), (2, 'movie 2'), (3, 'movie 3'), (4, 'movie 4');

insert into genre values
(1, 'genre 1'), (2, 'genre 2'), (3, 'genre 3'), (4, 'genre 4');

insert into movie_genre values
(1, 2), (1, 3),
(2, 1), (2, 2), (2, 4),
(3, 1), (3, 2), (3, 3), (3, 4),
(4, 2);

insert into rental values
(1, 1), (2, 2), (3, 1), (4, 3), (5, 1), (6, 3), (7, 4);

Now joining all tables and grouping by genre.name you can get the count of each genre:

select   g.name, count(*) rentals
from     rental r
join     movie  m
on       r.movie_id = m.id
join     movie_genre mg
on       mg.movie_id = m.id
join     genre g
on       mg.genre_id = g.id
group by g.name;

name    | rentals
:------ | ------:
genre 1 |       3
genre 2 |       7
genre 3 |       5
genre 4 |       3

dbfiddle here


two-table-over  functional relationship: maintaining referential integrity

You add a (redundant) unique constraint on (study_id, id) on both the treatment and visit tables. Then let the foreign keys from session refer to these unique constraints by including study_id into the respective foreign keys.

Then your condition will automatically be satisfied.

The unique constraints are not quite as superfluous as they seem, because the foreign key constraint on study_id should be indexed anyway.

To put it into SQL:

ALTER TABLE treatment
   ADD UNIQUE (study_id, id);

ALTER TABLE visit
   ADD UNIQUE (study_id, id);

ALTER TABLE session
   DROP CONSTRAINT session_treatment_id_fkey,
   DROP CONSTRAINT session_visit_id_fkey,
   ADD FOREIGN KEY (study_id, treatment_id) REFERENCES treatment (study_id, id),
   ADD FOREIGN KEY (study_id, visit_id) REFERENCES visit (study_id, id);


SQL Server Express Instance Memory vs Memory Limit

Community wiki answer:

The 1GB limit is only for the buffer pool, SQL Server uses other memory pools as well so it is possible to see memory usage over 1GB.

Quoting from Memory configuration and sizing considerations in SQL Server 2012 and later versions

  Starting with SQL Server 2012, these memory limits are enforced only for the database cache (buffer pool). The rest of the caches in the SQL Server memory manager can use much more memory than is specified by these edition limits. For example, a SQL Server 2012 Express edition can use only a maximum size of 1.4 GB for its database cache. Other caches (such as the procedure cache, the metadata cache, and so on) can consume memory up to the size specified by the "max server memory" configuration.

For SQL Server 2016 SP1 onward, Express can also use an additional 352MB for columnstore segments per instance and another 352MB for in-memory OLTP (Hekaton) objects per database.

The maximum buffer pool size was also changed in the documentation from 1GB to 1410MB for SQL Server 2016, though this probably applies from SQL Server 2012 (where the big memory management changes were made).

Only the buffer pool, columnstore cache, and in-memory OLTP cache sizes are restricted by quota. Other cache stores and memory consumers in Express Edition can push overall memory usage up to the configured limit in the max server memory configuration setting.

See Also:

Why does SQL Server 2012 Express use 9.5GB of RAM on my server?
Does SQL Server 2008R2 Express actually enforce the memory and CPU limit?


You could look into slip streaming the installation.

In order to slipstream the updates:

Download the necessary updates and store them in a location that you have access to (local or UNC path is fine).
Recent updates are just the .exe file; older CUs were compressed, so if necessary extract the files
open cmd
change directory to the root of the installation media
start setup.exe from cmd spcifying the necessary switches

Setup.exe /Action=Install /UpdateEnabled=TRUE /UpdateSource="updates path"

If this is something that you are likely to do regularly, it is possible to re-package the iso with these updates in.

Extract ISO image using a utility like 7zip or mount and copy to a directory
Create an updates folder on in the root of the extracted media
Copy in the required SP/CU
Recreate the ISO image using any iso image creator such as MagicISO

To use the slipstreamed files, open cmd and run similar to before:

Setup.exe /Action=Install /UpdateEnabled=TRUE /UpdateSource=".\updates"

For a detailed walk through with screenshots see here


SQLAnywhere: Savepoint for ROLLBACK not found

This behaviour seems to be caused by the option to auto commit after each command in dbisql configurable via Tools->Options->SQLAnywhere->Execution->"Commit after every statement". If this option is set, I can reproduce the error described at the question. Unchecked it's working as expected.


I want to delete connections in tnsnames.ora. I need to figure out which ones are currently in use, so that I don't delete them mistakenly

I don't think you can get that from the database server side. The tnsnames.ora file is local to the client, so you could have a net service name (connection name) of 'ORANGE' that connects to a database service called 'BANANA'.

I had a look at some of our listener log files, and they don't have this info. The SYS_CONTEXT function exposes a lot of info but not the client's local net service name.  https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/SYS_CONTEXT.html#GUID-B9934A5D-D97B-4E51-B01B-80C76A5BD086

If you want to differentiate connections, you could have multiple service names for the database, e.g. 'DB1_OLTP', 'DB1_REPORTS', 'DB1_NORMAL'. The service name can be used to assign the session to a resource consumer group to limit CPU etc.


Transaction Log Shipping Removal, Backup and Drop Database

You should first remove logshipping using below procedure. You can use both TSQL and SSMS for this
To Remove Log Shipping
On the log shipping primary server, execute sp_delete_log_shipping_primary_secondary to delete the information about the secondary database from the primary server.
On the log shipping secondary server, execute sp_delete_log_shipping_secondary_database to delete the secondary database.

Note
If there are no other secondary databases with the same secondary ID, sp_delete_log_shipping_secondary_primary is invoked from sp_delete_log_shipping_secondary_database and deletes the entry for the secondary ID and the copy and restore jobs.

On the log shipping primary server, execute sp_delete_log_shipping_primary_database to delete information about the log shipping configuration from the primary server. This also deletes the backup job.
On the log shipping primary server, disable the backup job.
On the log shipping secondary server, disable the copy and restore jobs.
Optionally, if you are no longer using the log shipping secondary database, you can delete it from the secondary server.
To remove Log Shipping using SSMS
Right-click the database you want to use as your primary database in the log shipping configuration, and then click Properties.
Under Select a page, click Transaction Log Shipping.
Clear the Enable this as a primary database in a log shipping configuration check box.
Click OK to remove log shipping from this primary database.
After logshipping is removed drop databases.