How to load textfiles into SQL Server Database?

Certainly you are person who knows a lot of technology tools etc. But I will just point here, that two things are probably worth of research:

Storage of the files on Linux filesystem. From my experience cames that even opening and listing directory with 100 000 or more files is a task which Microsoft cannot do in efficient way. Just using more efficient operating system here may give you at least some ergonomic gains.
Maybe you should research etl tools like Apatar. It works with many files by GUI, so in some cases you may have a visualization of your process, which probably makes it easier to automate or analyse.
It may be surprising in relation to this problem, but on the market there are very efficient data collection tools. Maybe thinking outside the box, and using clustered tools for system log acquisition, like Graylog log server, will be interesting choice. It has pipelining system, which takes data from network port, or through remote agents monitoring particular text files on remote system, and crunch it through various regex collectors, then has ability to divide that streams into output depending on given regex results. Output may be defined by you, and sent to another network device. Even on single machine acquisition of several hundreds of thousands records takes a seconds. It is very, very efficient. I believe at least part of your work may be automated here, for example it may test patterns and produce stream with wrong format entries ( or with good ones). And it gives you some basic statistical informations like dashboard with graphic representation of error types etc.


Which all system parameters to be considered for standard Vacuum process

The default settings will probably be fine.

That will limit the IO write usage to about 4MB/sec, that is, 4kB/msec = (8KB * vacuum_cost_limit / vacuum_cost_page_dirty / autovacuum_vacuum_cost_delay)

And RAM usage to about 48 MB (autovacuum_max_workers * maintenance_work_mem)


SQL Query is getting progressively slower

Try SET ARITHABORT ON in your client application...

Why would SET ARITHABORT ON dramatically speed up a query?

I have almost always seen this to be differences between the ADO(usually OFF) and SSMS(ON) for the ARITHABORT setting. This setting can dramatically impact the Query Plan that SQL Server uses for certain queries. Here is a very detailed article (slow queries but SSMS works fine) and how to perform parameter sniffing troubleshooting to get to the bottom of it: http://www.sommarskog.se/query-plan-mysteries.html

One way to test this would be to set it OFF in SSMS (it is on by default in SSMS and off in client apps generally) and run the queries to see if they slow down:

From the MSDN article:

  The default ARITHABORT setting for SQL Server Management Studio is ON. Client applications setting ARITHABORT to OFF can receive different query plans making it difficult to troubleshoot poorly performing queries. That is, the same query can execute fast in management studio but slow in the application. When troubleshooting queries with Management Studio always match the client ARITHABORT setting.


This line clearly states the reason for the block corruption:

ORA-26040: Data block was loaded using the NOLOGGING option

NOLOGGING operations are not logged in the redo as traditional operations, the amount of information logged is greatly reduced to an extent, where data recovery is not possible. Because of this, if you did not make a backup of the affected datafiles after these operations had finished, those modifications can not be recovered with archivelogs, and you will receive the above error.

NOLOGGING should be handled with care, and because of the above, you should perform a backup after performing a NOLOGGING operation. If your backup was created earlier, you will not be able to fully recover data using this backup, even if you have the archivelogs.

So, perform the backup after the NOLOGGING operations was finished.

Another way is to control the NOLOGGING attribute of your objects. You can set this at table, index, LOB segment, tablespace level. You can set it to LOGGING, so operations on the object will be logged (or this will be the default inherited value in case of tablespace).

Finally, you can enable FORCE LOGGING at the tablespace or database level, so operations will be logged regardless of the object level setting.


For the specific example:

WHERE [allotetTO] LIKE '%RA[MJ]%'

For more complex ones, you could use a join to a values table with the wanted patterns:


Why is a scan faster than seek for this predicate?

Why is there a large difference in CPU time for these two queries?

The scan plan evaluates the following pushed non-sargable (residual) predicate for each row:

[two_col_key_test].[ID1]<>N'1'
AND [two_col_key_test].[ID1]<>N'10'
AND [two_col_key_test].[ID1]<>N'11'
AND [two_col_key_test].[ID1]<>N'12'
AND [two_col_key_test].[ID1]<>N'13'
AND [two_col_key_test].[ID1]<>N'14'
AND [two_col_key_test].[ID1]<>N'15'
AND [two_col_key_test].[ID1]<>N'16'
AND [two_col_key_test].[ID1]<>N'17'
AND [two_col_key_test].[ID1]<>N'18'
AND [two_col_key_test].[ID1]<>N'19'
AND [two_col_key_test].[ID1]<>N'2'
AND [two_col_key_test].[ID1]<>N'20'
AND [two_col_key_test].[ID1]<>N'3'
AND [two_col_key_test].[ID1]<>N'4'
AND [two_col_key_test].[ID1]<>N'5'
AND [two_col_key_test].[ID1]<>N'6'
AND [two_col_key_test].[ID1]<>N'7'
AND [two_col_key_test].[ID1]<>N'8'
AND [two_col_key_test].[ID1]<>N'9'
AND
(
    [two_col_key_test].[ID1]=N'FILLER TEXT'
    AND [two_col_key_test].[ID2]>=N''
    OR [two_col_key_test].[ID1]>N'FILLER TEXT'
)

The seek plan does two seeking operations:

Seek Keys[1]:
    Prefix:
    [two_col_key_test].ID1 = Scalar Operator(N'FILLER TEXT'),
        Start: [two_col_key_test].ID2 >= Scalar Operator(N'')
Seek Keys[1]:
    Start: [two_col_key_test].ID1 > Scalar Operator(N'FILLER TEXT')

...to match this part of the predicate:

(ID1 = N'FILLER TEXT' AND ID2 >= N'' OR (ID1 > N'FILLER TEXT'))

A residual predicate is applied to rows that pass the seek conditions above (all rows in your example).

However, each inequality is replaced by two separate tests for less than OR greater than:

([two_col_key_test].[ID1]<N'1' OR [two_col_key_test].[ID1]>N'1')
AND ([two_col_key_test].[ID1]<N'10' OR [two_col_key_test].[ID1]>N'10')
AND ([two_col_key_test].[ID1]<N'11' OR [two_col_key_test].[ID1]>N'11')
AND ([two_col_key_test].[ID1]<N'12' OR [two_col_key_test].[ID1]>N'12')
AND ([two_col_key_test].[ID1]<N'13' OR [two_col_key_test].[ID1]>N'13')
AND ([two_col_key_test].[ID1]<N'14' OR [two_col_key_test].[ID1]>N'14')
AND ([two_col_key_test].[ID1]<N'15' OR [two_col_key_test].[ID1]>N'15')
AND ([two_col_key_test].[ID1]<N'16' OR [two_col_key_test].[ID1]>N'16')
AND ([two_col_key_test].[ID1]<N'17' OR [two_col_key_test].[ID1]>N'17')
AND ([two_col_key_test].[ID1]<N'18' OR [two_col_key_test].[ID1]>N'18')
AND ([two_col_key_test].[ID1]<N'19' OR [two_col_key_test].[ID1]>N'19')
AND ([two_col_key_test].[ID1]<N'2' OR [two_col_key_test].[ID1]>N'2')
AND ([two_col_key_test].[ID1]<N'20' OR [two_col_key_test].[ID1]>N'20')
AND ([two_col_key_test].[ID1]<N'3' OR [two_col_key_test].[ID1]>N'3')
AND ([two_col_key_test].[ID1]<N'4' OR [two_col_key_test].[ID1]>N'4')
AND ([two_col_key_test].[ID1]<N'5' OR [two_col_key_test].[ID1]>N'5')
AND ([two_col_key_test].[ID1]<N'6' OR [two_col_key_test].[ID1]>N'6')
AND ([two_col_key_test].[ID1]<N'7' OR [two_col_key_test].[ID1]>N'7')
AND ([two_col_key_test].[ID1]<N'8' OR [two_col_key_test].[ID1]>N'8')
AND ([two_col_key_test].[ID1]<N'9' OR [two_col_key_test].[ID1]>N'9')

Rewriting each inequality e.g.:

[ID1] <> N'1'  ->  [ID1]<N'1' OR [ID1]>N'1'

...is counterproductive here. Collation-aware string comparisons are expensive. Doubling the number of comparisons explains most of the difference in CPU time you see.

You can see this more clearly by disabling the pushing of non-sargable predicates with undocumented trace flag 9130. That will show the residual as a separate Filter, with performance information you can inspect separately:

This will also highlight the slight cardinality misestimate on the seek, which explains why the optimizer chose the seek over the scan in the first place (it expected the seeking portion to eliminate some rows).

While the inequality rewrite may make (possibly filtered) index matching possible (to make the best use of the seeking ability of b-tree indexes), it would be better to subsequently revert this expansion if both halves end up in the residual. You could suggest this as an improvement on the SQL Server feedback site.

Note also that the original ("legacy") cardinality estimation model happens to select a scan by default for this query.


In my opinion , based on version of windows you have and especially using always on configuration i would recommend having a quorum configured. In your case its File share which seems OK or else you have the option of quorum drive witness.

More on this is very neately explained by @Brentozar here

  You don't have a choice as to whether or not to have quorum: Always On
  Availability Groups are built on top of Windows Server Failover
  Clustering, which requires quorum voting to understand who the primary
  is for a given AG at a given time.

  I think what you might be asking is whether or not you need an
  old-school quorum drive (like a Q drive on a SAN). These days, Windows
  Server Failover Clustering (WSFC) can either use a quorum drive
  witness, OR a file share witness.

  Windows Server 2012 R2 includes dynamic witness capabilities: if you
  have an even number of voters (like if 1 of your 3 cluster nodes goes
  down), then Windows will automatically add in your witness in order to
  be a tiebreaker. That's why you want to configure the witness (either
  a disk or a file share) ahead of time - Windows will automatically
  manage its voting rights whenever you have node failures or network
  failures.

Also refer to another part of the answer which should help you here

  In a 3 node cluster you can setup node majority as your quorum mode
  and then you can survive the failure of a single node within the
  cluster . That is in most cases sufficient. If your cluster spans
  multiple sites you might want to fiddle with the quorum-vote weight
  depending on your failover plans


The execution order absolutely matters, and you shouldn't change it from what was generated. It is likely you have dependencies in one script on objects that need to be created first from another script. For example, if one of your Stored Procedures use a View then it will fail upon generation because it can't find the View it references. This is why generating scripts puts them in order by dependency instead of object type.
Only in the very rare case where all your scripts don't have dependencies on each other (or your re-ordering is coincendentally in order of dependency still), then you'd be ok.


how do I know whether I am currently in chained or unchained mode

Sybase ASE's default mode is unchained, as I suspect you already know.

You can use SELECT @@tranchained; to see the status for the current session's transaction chaining mode.

The result is either 0 or 1, per the following:

0 = unchained
1 = chained

Transaction chaining mode can be modified with SET CHAINED ON; or SET CHAINED OFF;


If you are interested in High Availability and Performance for MySQL, then High Performance MySQL : Optimization, Backups, Replication, and More

is definitely the book for you (Third Edition is out now) !!!

I would still have MySQL 5.0 Certification Study Guide

as well. I used this book as my reference book for 10 months before taking the Certification Exams. In honesty, it has many typos and incorrect facts, all minor in nature.

There is no substitute for Practical Experience. Implement the things leaned from these two books and you will develop the needed confidence to handle,..., no, manhandle MySQL.

If you must use MySQL 4.x, then you will also need MySQL Reference Manual.


The question does not contain test data, and some details are not quite clear eg we don't know what the "diff" in the original query stands for.  Suppose we have the following test table (assuming that diff is just a constant value):

-- Oracle 12c
create table test
as
select
  sysdate - level as my_date_col
, 1 as diff
from dual
connect by level <= 400 ;

SQL> select * from test fetch first 10 rows only ;
MY_DATE_COL  DIFF
04-JUN-18    1
03-JUN-18    1
02-JUN-18    1
01-JUN-18    1
31-MAY-18    1
30-MAY-18    1
29-MAY-18    1
28-MAY-18    1
27-MAY-18    1
26-MAY-18    1

Original query

The counts for the "sections" (SIX_AGO, FIVE_AGO etc) do not add up to the expected "SUM".

 SELECT
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 365 -  diff ) AND to_date(TRUNC(sysdate, 'DAY') + 6  -  diff) then 1 END) AS SUM,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 365 -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 36 - diff) then 1 END) AS SIX_AGO,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 35  -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 29 -  diff) then 1 END) AS FIVE_AGO,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 28  -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 22 -  diff) then 1 END) AS FOUR_AGO,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 21  -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 15 -  diff) then 1 END) AS THREE_AGO,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 14  -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 8  -  diff) then 1 END) AS TWO_AGO,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 7   -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 1  -  diff) then 1 END) AS ONE_AGO,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') -        diff ) AND to_date(TRUNC(sysdate, 'DAY') + 6  -  diff) then 1 END) AS NO_AGO
FROM TEST ;

-- result
SUM  SIX_AGO  FIVE_AGO  FOUR_AGO  THREE_AGO  TWO_AGO  ONE_AGO  NO_AGO
367  329      6         6         6          6        6        2

The problem seems to be that the nested functions used in BETWEEN ... AND ... lead to incorrect counts.  Thus, I suggest that we rewrite the query in a way that allows us to use integer values for the between conditions. Something like ...

select
  count( case when trunc( my_date_col - sysdate ) between -7   and -1  then 1 end ) section1
, count( case when trunc( my_date_col - sysdate ) between -14  and -8  then 1 end ) section2
, count( case when trunc( my_date_col - sysdate ) between -21  and -15 then 1 end ) section3
, count( case when trunc( my_date_col - sysdate ) between -28  and -22 then 1 end ) section4
, count( case when trunc( my_date_col - sysdate ) between -35  and -29 then 1 end ) section5
, count( case when trunc( my_date_col - sysdate ) between -365 and -36 then 1 end ) section6
, count( case when trunc( my_date_col - sysdate ) between -365 and -1  then 1 end ) overall
from test ;

-- result
SECTION1  SECTION2  SECTION3  SECTION4  SECTION5  SECTION6  OVERALL
7         7         7         7         7         330       365


How to edit data to 2 tables using MySQL Workbench

If you are editing data through the editor(mysqlworkbench) it needs a table on where it should execute the update command and the where column. That's why you can edit the record only when you have executed SELECT * FROM table because result of this query has primary key and table So Updating the data through workbench will be allowed

If you are using join to get the record then the editor displays the record of both the tables. And workbench(editor) won't get the table and primary key to update.


Migrate legacy application to new db/schema

As mentioned in the comments, this rather misses a question regarding a database technology. However here is my suggestion:

Since there are likely not any MySQL or Postgres specific features involved, just dump to CSV roughly in the data format that you will likely want in your target system and insert it there into any kind of table. If you afterwards need to massage data and add postgres features on top of it, create a new target table with the functionality you require (e.g. indexes, constraints, relations, views) and insert into it from the previous table.

I would not suggest anything more complicated, since you seem to migrate only small data.

PG has all the manipulation functions you need. Once you are done make it work in your application software.


Inside of your stored procedure use EXECUTE with a character string.  Something like this:

EXEC ('USE [WC48_Database1]; EXEC sp_startpublication_snapshot @publication = @PublicationName;');


How can I create an ad-hoc domain user on SQL Server

You cannot actually login to SQL Server with any domain account. You can either pass a SQL Login with its password, or you can logon using "Windows Authentication". What the latter means is that the account that is executing the windows executable is passed to SQL Server to authenticate.

That means you just need to execute the app using a local machine account and add that account as a windows login to SQL Server.

If the application on the other hand is running on a domain computer and you just pointed it to use SQL Server on your machine, you are out of luck. However, if you were able to change the connection string to point to your machine, you should be able to just add a SQL Login to that same connection string.


MySQL server with root user that can be accessed remotely with just a password. How hard is it to break in?

create a user with certain privileges (depends on what queries you run). I also advice to disallow root remotely login.


How do I allow remote connection in SQL Server 2008 Express

Go into the SQL Server Configuration Manager (SSCM) and under Service Protocols enable TCP/IP for your SQL Server Express instance (most likely called SQLEXPRESS).


Read after transaction from secondary session not visible

If you can install Mysql UDF Plugin, you can using lib_mysqludf_redis plugin to synchronous records between Mysql and Redis by Mysql trigger.

Here is a example:

CREATE TABLE `player_channel_log` (
  id       int         AUTO_INCREMENT PRIMARY KEY,
  date     datetime,
  player   int,
  channel  varchar(32)
);

set up a trigger for the table and call redis command by mysql udf

DELIMITER $$
CREATE TRIGGER `after_insert_player_channel_log`
AFTER INSERT ON `my_table` FOR EACH ROW
BEGIN
  DO `redis`('redis://@127.0.0.1/0/', 'SET', new.`player`, new.`channel`);
END $$
DELIMITER ;

then you can get value by player id

from mysql:

mysql>  SELECT `redis`('redis://@127.0.0.1/0/', 'GET', <player id>)\G
*************************** 1. row ***************************
`redis`('redis://@127.0.0.1/0/', 'GET', <player id>): {
        "out":  <channel>
}

from redis-cli utility:

$ redis-cli -h 127.0.0.1 -n 0 GET <player id>
"<channel>"


Where is the documentation for version 1.0 of PostgreSQL wire protocol

It is all in the Git repository, here is a link to the source. You can actually read the SGML source pretty well.


I've since worked out the specific issue this post focused on (the joins)... and have moved on to a new set of issues with it. So to keep the post on topic I'm going to answer/close it up. Nobody disputed the way I ended up joining the tables or suggested a "more correct" or efficient way, so I guess what I have can be considered correct for this situation.
Maybe somebody else who lands on this page will find my visual diagram and subsequent joins helpful in visualizing/joining their own tables... who knows.
Anyways, this is what worked for me:
from account
left join store on account.id = store.account_id
left join menu on store.id = menu.store_id
left join menu_item on menu.id = menu_item.menu_id
left join kitchen_item on (menu_item.kitchen_item_id = kitchen_item.id and store.id = kitchen_item.store_id)
join store_address on store.id = store_address.store_id
join address on store_address.address_id = address.id


How to enable failed login profle on MySQL

I don't think MySQL supports database/logon triggers as other DBMS do. At least I couldn't find anything about it in the docs. So that "usual way" is ruled out here.

But apparently you can do it via the general server log. Set the location of the log in your option file with

general_log_file = /where/you/want/it

and set

log_warnings = 2

. That will log warnings about unsuccessful logins.

Set

general_log = 1

when you also want to log successful logins.

However be aware, that this will mean, that a lot of other stuff is logged too. It's all just about raising or lowering log levels here, not specifically setting login related logging.


How to replicate from SQL Server 2000 to SQL Server 2008 R2?

Bad news: SQL Server 2000 isn't officially supported. You're putting yourself in a situation where the link between these two servers could cause problems, and if you call Microsoft for support, you'll be out of luck.

Don't be That Guy who breaks the production server because he wanted to connect these two servers. Tell your management that SQL Server 2000 is out of support. SQL Server 2008 R2 has lots of cool features to help - rather than working with replication in an unsupported scenario, put your time into getting that old SQL Server 2000 box into a supported scenario.


Using XQuery to filter out some children, but preserve parents

You may try following

SELECT @xml.query('
    element a {
        for $node in /a/*[local-name() != "d"]
            return $node
    }');

But it may not be suitable if your real XML is more complex and you have to exclude node at a deeper level of nesting.

Speaking honestly, I don't think that XQuery is the good tool for such sort of tasks. In essence we have XML and we need to remove a node somewhere within, i.e. we need to transform it. The more suitable tool, I think, is XSL transformations. SQL Server has no built-in XSLT capabilities unfortunately (it can be added via SQLCLR function, though).

This is how body of XSL for solving this task could look

<!-- Skip "d" under "a" -->
<xsl:template match="a/d" />

<!-- Apply identity transform to other nodes -->
<xsl:template match="@*|node()">
  <xsl:copy>
    <xsl:apply-templates select="@*|node()" />
  </xsl:copy>
</xsl:template>

It will work for a element residing anywhere within XML, not just at the root.

SQLCLR-function for performing non-parameterized XSL transformation can be as simple as few lines of C# code

[SqlFunction(Name = "XslTransform")]
public static SqlXml XslTransform(SqlXml xml, SqlXml xsl)
{
    if (xml.IsNull || xsl.IsNull)
        return SqlXml.Null;

    var xslt = new XslCompiledTransform();
    using (var xr = xsl.CreateReader())
        xslt.Load(xr);

    var xws = new XmlWriterSettings
    {
        Encoding = Encoding.Unicode,
        OmitXmlDeclaration = true
    };

    var output = new MemoryStream();
    using (var xw = XmlWriter.Create(output, xws))
    using (var xr = xml.CreateReader())
    {
        xslt.Transform(xr, null, xw);
        xw.Flush();
    }

    output.Seek(0, SeekOrigin.Begin);

    return new SqlXml(output);
}

It should be declared in database like

CREATE FUNCTION SQLCLR.XslTransform
(
    @xml xml,
    @xsl xml
)
RETURNS xml
AS EXTERNAL NAME [AssemblyName].[ClassName].[XslTransform];
GO

And it can be used then as

DECLARE
    @xml xml = N'(Your XML goes here)',
    @xsl xml = N'(Your XSL goes here)';

SELECT SQLCLR.XslTransform(@xml, @xsl);


how to update information_schema.tables in mysql

Information_Schema are views and read-only. Yo can not update meta data about tables, only by modifying actual table with ALTER TABLE. If you do an ALTER TABLE you will have current timestamp in meta data.

  INFORMATION_SCHEMA is a database within each MySQL instance, the place
  that stores information about all the other databases that the MySQL
  server maintains. The INFORMATION_SCHEMA database contains several
  read-only tables. They are actually views, not base tables, so there
  are no files associated with them, and you cannot set triggers on
  them. Also, there is no database directory with that name.

  Although you can select INFORMATION_SCHEMA as the default database
  with a USE statement, you can only read the contents of tables, not
  perform INSERT, UPDATE, or DELETE operations on them.

MySQL 5.7 Reference Manual  /  INFORMATION_SCHEMA Tables

What's the exact reason you need to change meta data?


Oracle 12c (RDS) Questions from a SQL Server DBA

Index Maintenance: Generally speaking you don't need to rebuilt b-tree indexes because they are self-managed.

When to rebuild Indexes?

Index Internals- Rebuilding The Truth

Integrity Check: Oracle has DBVERIFY(dbv) command-line utility for physical data structure integrity check. But you can't use this tool on Amazon RDS as it doesn't provide shell access to database server.

 DBVERIFY: Offline Database Verification Utility

Statistics: The preferred method for gathering statistics in Oracle is to use the supplied automatic statistics gathering
job. Or you can gather it manually using DBMS_STATS package. Details are given in the following white paper.

Best Practices for Gathering Optimizer Statistics

  In SQL Server stats are automatically taken care of when a table grows by 500 rows + 20%, is there an Oracle equivalent?

How often we should gather statistics is depend upon how much changes are being made in the tables which affect the query plan. In other words when statistics become stale we need to gather statistics. You can set stale percentage threshold for a table using STALE_PERCENT(default is 10%) parameter of SET_TABLE_PREFS procedure under DBMS_STATS package.
For details:

DBMS_STATS

Tablespaces: You can define the increment size using AUTOEXTEND ON NEXT clause either in CREATE TABLESPACE or ALTER TABLESPACE statements.

  And also is it not better practice to presize these to avoid any growth during production hours?

Auto extending doesn't affect the performance noticeably as it doesn't happen frequently unless we specify very small size on NEXT clause.

CREATE TABLESPACE

 Important DBA and Performance Views: Some the of the important dynamic performance views are given below(v$ views).

V$SESSION: Currently active sessions.

V$SQL: SQL command recently executed and its related information.

V$SGA: Statistics related to Oracle's shared memory called SGA.

Dynamic Performance View Description

 Key Metrics

  On the above point, are the key metrics in Oracle the same as SQL Server, roughly? I.e. PLE, buffer hit cache, disk latency etc.

Generally speaking, these are same just Oracle has different terminologies to address them. For example, Page is equivalent to Data Block in Oracle and life expectancy of a block is determined by touch count algorithm similar to LRU(Least Frequently Used) algorithm.


Query timeouts when duration is under a second

I have realized that what ever duration is, it is not the time that the timedout query ran for.  I ran waitfor delay '00:00:30' in a query window set to timeout in 10 seconds.  The results should have been 10 seconds for all of the time outs.  But I got these values for my 6 tests: 70707, 311, 269, 264, 3442, 272.  My guess is that duration is the amount if time it took to handle the Attention event (i.e. Stop the query).

Just to be sure that the duration wouldn't work correctly with an actual running query, I ran a long running one, again set to timeout in 10 seconds.  The durations where: 64246, 58002, 52828, 68061, 48153, 49084.  They took longer, but none of those times is close to 10 seconds.  Again, confirms that duration is not what it seems to be.

The documentation says duration is in Microseconds (not milliseconds).  That also could work if it is the time to handle the Attention event, but it would never match up to the time it took for the timed out query to run.

I ran the long running query without a time out and had to stop it after 120 seconds.  It took a lot longer to stop, and the duration for the Attention event was 6400055.  6.4 seconds is about how long it took to stop.  Again confirming that duration is the amount of time it takes to handle the "Attention" event.


How to make sqlplus output appear in one line?

It is not enough to force sqlplus not to wrap lines. It is also necessary to tell the viewer that you use to view the spool file not to wrap lines.
If your viewer is less then -S the option you have to use according to
https://superuser.com/questions/272818/how-to-turn-off-word-wrap-in-less .
On Unix/Linux you can use head -1 output.txt to get the first line of a file and so check if this is as expected or you can use od -c output.txt|head
to see where the line breaks are actually placed in your output file.

If you display LONG columns and their values contain line breaks then multiple lines will be printed for these column values and you cannot overrule this with sqlplus settings.

The following sqlplus commands maybe usefull:

SET LINESIZE linesize the length of the line. In most cases the maximum value for linesize is 32767.  You can find out your maximum value if you set LINESIZE to an invalid value and check the error message
so SET LINESIZE 0 may give SP2-0267: linesize option 0 out of range (1 through 32767)  (1)
SET TRIMSPOOL ON otherwise every line in the spoolfile is filled up with blanks until the linesize is reached.
SET TRIMOUT ON otherwise every line in the output is filled up with blanks until the linesize is reached.
SET WRAP OFF Truncates the line if its is longer then LINESIZE. This should not happen if linesize is large enough.
SET TERMOUT OFF suppresses the printing of the results to the output.
The lines are still written to the spool file. This may accelerate the exectution time of a statement a lot.
SET PAGESIZE 0 to set an infinite pagesize and avoid headings , titles and so on.
There are some other SET parameters concerning output (NUMWIDTH, NUMFORMAT, LONG, COLSEP)  and performance (ARRAYSIZE, LONGCHUNKSIZE).

You have to use the COLUMNcommand to format individual columns.

E.g. column name format a30 will format the column name in the output to a maximum length of 30 characters.

If you want the display size not be fix but it should be equal to the size of the actual value of a columns in a row then the only way I know is that you change the select clause of your statement to get the desired result and use the
string concatenation operator || , e.g.

select emp_id||' '||first_name||' '||last_name
from emp;

A full description of all variable can be found in SQL*Plus User's Guide and Reference.

If you want to reuse some settings (or COLUMN definitions)  you can store them in a file and run this file when you need them again. You even can run this file automatically if you start sqlplus.

(1)  "How to Find the LINESIZE Maximum Value (It is System Dependent) (Doc ID 1547262.1)"


What is the purpose of a buffer and a buffer manager?

A buffer is an 8 KB page in memory, the same size as a data or index page you can consider buffer as a frame which holds data and index pages when they are brought from disk to memory.

SQL Server buffer manager manages the task of reading data pages into buffer pool and also writing it to disk. It is a reserved memory store for SQL Server and by default if you do not set value for it it will take as much memory as possible.

What you are looking is documented in SQL Server Buffer Management


Remove these for a start, they are almost certainly counterproductive:

table_open_cache = 256
join_buffer_size = 1M
thread_concurrency = 16

There is also a conspicuous absence of innodb_buffer_pool_size being set.

Configuration isn't going to be the root cause of your problem, though - you need to identify what queries are eating all of your CPU and address those specifically, either by rewriting them or by making sure that appropriate indexes for them exist.


Archive a single giant table from a SQL 2008 database - filegroup backup?

I'd favour a BCP extract.

BCP export to file.
Import somewhere (different database or server) to confirm the file "is good".
Archive multiple copies, to multiple tapes.

@Marian answered a question of mine with a great list of BCP references that you might find useful. The BCP Basics by SQLFool is also a good starter for ten.


SSIS Design consideration with object variables

I would replace your output Recordset Destinations with OLE DB Destinations and deliver the required data into database tables.  As well as making the remaining work easier, this could also give you a far more useful Audit/History of previous runs.

From that point I would then write SSRS reports against those tables and use SSRS subscriptions to prepare well formatted emails and deliver them.


In general there should be no need to restore controlfile or spfile. You say '7 days of backups' but you didn't specify what type of backups, nor what your retention policy is, nor what kind of housekeeping you do. If its a vanilla inc 0 and inc 1 backups with retention policy of recovery period of 7 days or more, a simple 'set until' followed by 'restore database' followed by 'recover database' should do it


Has anyone done an research as to the overhead created by enabling AWE on the 64-bit OS/SQL combination?

This is one of the Myths that Paul Randal clearly debunks in his famous series - A SQL Server DBA myth a day

  On 64-bit systems (2005+): AWE is not required (and in fact enabling it does nothing).

Also refer to Fun with Locked Pages, AWE, Task Manager, and the Working Set… by Bob Ward :

  The AWE APIs for 32bit and 64bit SQL Server systems are used for different purposes. In 32bit it is really to extend memory access beyond 4Gb or to enable the AWE feature. For 64bit systems, it is to possibly gain performance and to “lock pages” for the buffer pool.

and

  In fact, the code for SQL Server for 64bit systems ignores awe enabled sp_configure option. It is a “no-op” for 64bit SQL Server systems.


You can get it by using CASE WHEN in ORDER BY clause:

Note: I've added COALESCE(Parent_ID_Asset, '') just to get NULL Parent_ID in first place, you could replace it by CASE WHEN Parent_ID_Asset IS NULL THEN 0 ELSE 1 END

SELECT
    ID_Asset,
    Parent_ID_Asset,
    ProductTitle
FROM
    Test
ORDER BY
    CASE WHEN Parent_ID_Asset IS NULL THEN ID_Asset ELSE Parent_ID_Asset END,
    COALESCE(Parent_ID_Asset, ''),
    ProductTitle;
GO

| ID_Asset | Parent_ID_Asset | ProductTitle                                                 |
|----------|-----------------|--------------------------------------------------------------|
| 2        | NULL            | Live Maps Unity                                              |
| 3        | NULL            | mShare                                                       |
| 4        | NULL            | Nessus Professional                                          |
| 5        | NULL            | Enterprise Server                                            |
| 6        | NULL            | PhantomPDF                                                   |
| 7        | NULL            | Sharegate                                                    |
| 8        | NULL            | ADONIS Server                                                |
| 16       | 8               | ADONIS Designer                                              |
| 20       | 8               | ADONIS Portal Module “Control & Release” Package XS          |
| 9        | NULL            | Automated Intelligence AI Compliance Extender & AI Syncpoint |
| 10       | NULL            | Agility BridgeChecker                                        |
| 11       | NULL            | Office Timeline                                              |
| 12       | NULL            | ThinkBuzan iMindMap 8 Ultimate                               |
| 13       | NULL            | Total Management Suite                                       |
| 14       | NULL            | Webex Business Messaging and Advanced Meetings               |

Rextester here

UPDATE

It seems you need the result ordered somehow by ProductTitle, you can use this query: (Keep in mind you're adding an extra job that maybe could be done at presentation layer.)

SELECT
    ID_Asset,
    Parent_ID_Asset,
    ProductTitle
FROM
    Test
ORDER BY
    MAX(CASE WHEN Parent_ID_Asset IS NULL THEN ProductTitle ELSE '' END)
        OVER (PARTITION BY COALESCE(Parent_ID_Asset, ID_Asset)),
    COALESCE(Parent_ID_Asset, ID_Asset),
    Parent_ID_Asset,
    ProductTitle;

ID_Asset | Parent_ID_Asset | ProductTitle
-------: | --------------: | :-----------------------------------------------------------
       8 |            null | ADONIS Server
      16 |               8 | ADONIS Designer
      20 |               8 | ADONIS Portal Module “Control &amp; Release” Package XS
      10 |            null | Agility BridgeChecker
       9 |            null | Automated Intelligence AI Compliance Extender &amp; AI Syncpoint
       5 |            null | Enterprise Server
       2 |            null | Live Maps Unity
       3 |            null | mShare
       4 |            null | Nessus Professional
      11 |            null | Office Timeline
       6 |            null | PhantomPDF
       7 |            null | Sharegate
      12 |            null | ThinkBuzan iMindMap 8 Ultimate
      13 |            null | Total Management Suite
      14 |            null | Webex Business Messaging and Advanced Meetings

db<>fiddle here


How can I use SQL Profiler to capture a single stored procedure

I would suggest to use Extended Events instead of Profiler as the Extended Events has less overhead and more events to capture compare to Profiler.

In your case, create a new session in Extended Events by selecting rpc_completed event. For detailed steps..


You need to change the bind-address parameter to the IP you wish to allow remote access to.  However, most people just comment out the bind-address parameter in the my.cnf file to allow access from anywhere and deal with who can access the DB server with user access control.  I would suggest the latter and not the former. Don't forget to restart mysql server after making changes to my.cnf for the changes to take effect.

Please refer Here for more information.

Additionally, The grant statement should have single quotes around the name as well as the host:

mysql> GRANT ALL ON foo.* TO 'bar'@'192.168.0.1' IDENTIFIED BY 'PASSWORD';

Also, don't forget to

Flush privileges;


Query to collect the recent TimeSampled row using inner joins in MS SQL

One way is to use the ROW_NUMBER window function to order each row grouped by DisplayName (or better, the underlying primary key, if different and DisplayName isn't indexed), then pick out the latest using the result:

SELECT DisplayName, ObjectName, CounterName, InstanceName, SampleValue, TimeSampled
FROM (
    SELECT DisplayName, ObjectName, CounterName, InstanceName, SampleValue, TimeSampled,
        OrderNum = ROW_NUMBER() OVER (
            PARTITION BY DisplayName
            ORDER BY TimeSampled DESC)
    FROM PerformanceDataAllView pdv
    INNER JOIN PerformanceCounterView pcv
    ON pdv.performancesourceinternalid = pcv.performancesourceinternalid
    INNER JOIN BaseManagedEntity bme
    ON pcv.ManagedEntityId = bme.BaseManagedEntityId
    WHERE ObjectName = 'Processor'
        AND TimeSampled >= DATEADD(MINUTE,-5,GETDATE())
        AND DisplayName LIKE 'BLRW1ETST01.jcp.com'
        AND CounterName = '% Processor Time'
) t
WHERE OrderNum = 1;

That said, there are a few oddities with your query

It's better to qualify column references with the table alias to avoid mishaps if same-named columns get added to different tables
You use LIKE for filtering on DisplayName, yet specify a simple string rather than a pattern
The filter on TimeSampled means, if the most recent entry for a DisplayName is more than five minutes ago, it won't be returned despite being the most recent entry, which goes against how you described the requirements
Be careful on thinking NOLOCK is a magic bullet (https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere)


70-761 Practice exam Question about @@Trancount final value

The expected answer provided in the practice exam is incorrect. Microsoft's own documentation spells out clearly the same thing you've found - every BEGIN TRANSACTION increments @@TRANCOUNT by 1, every COMMIT TRANSACTION decrements it by 1 and ROLLBACK TRANSACTION resets @@TRANCOUNT to 0.

Nested transactions count and increment the @@TRANCOUNT value by 1 as well, so the statement that SQL Server doesn't actually create a nested transaction is also false.

See here for more info. It also does not make a difference if the code is in a stored procedure or not.


Regarding Sql server 2014 express edition

The size limit on Express Edition is for the database size, not the data file size.

You can have ten 1GB files, or one 10GB file.

If your data is larger than 10GB, you'll need to move some data to a different database.

Can you get around this?

Sure.

Say your database had one table that was 5GB, and the sum size of every other table was 5GB, putting you at the size limit.

Create a second database, and move your 5GB table into that database.
Drop the original table in the original database.
Create a synonym in the original database, with the name of the original table, and point it at the new table in the new database.
Existing code will "just work" and you'll have expanded your capacity beyond 10GB.


How does SQL Server handle SSIS project deployments, and what is best practice executing these packages?

Could I edit the deployed packages in the future?
You can import from GIT, open the package in SSDT and deploy again. Overwriting the existing deployed package.
You can also export using SSMS save and open in SSDT. Once you make your changes deploy again and update your source control.
As far as not letting anybody else modify or deploy packages this document will give you a good start.  A SSISDB is considered another user database.
Is my deployment method correct and best practice?
As you are using datatools to create your solution I would follow this method (assuming you have the privilege).

Deploy packages by using SQL Server Data Tools (Visual Studio)
In Visual Studio, with an Integration Services project open, select
the package or packages that you want to deploy.
Right-click and
select Deploy Package. The Deployment Wizard opens with the selected
packages configured as the source packages.
Complete the wizard.
Follow the remaining steps described in Package Deployment Model.

You can also do the same using SSMS. Details here.
In case you are not aware that you can deploy package from SSMS using tsql, details here.


Can this be accomplished with Oracles Fine Grained Auditing?

Get the client to call dbms_session.set_identifier('PHILTEST');. This will then be set in the CLIENT_ID audit field. Obviously you'l need to call it first from the client whenever a new connection is pulled from the pool.

For example:

PHIL@PHILL11G2 > conn / as sysdba
Connected.
Loading glogin.sql

Session altered.

SYS@PHILL11G2 AS SYSDBA> audit all by phil by access;

Audit succeeded.

SYS@PHILL11G2 AS SYSDBA> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY ACCESS;

Audit succeeded.

SYS@PHILL11G2 AS SYSDBA> truncate table aud$;

Table truncated.

SYS@PHILL11G2 AS SYSDBA> TRUNCATE TABLE fga_log$;

Table truncated.

SYS@PHILL11G2 AS SYSDBA> conn phil/phil

PHIL@PHILL11G2 > exec dbms_session.set_identifier('PHILTEST');

PL/SQL procedure successfully completed.

PHIL@PHILL11G2 > select count(*) from fgatest;

  COUNT(*)
----------
     1

PHIL@PHILL11G2 > conn / as sysdba

SYS@PHILL11G2 AS SYSDBA> select CLIENT_ID from dba_audit_trail;

CLIENT_ID
----------------------------------------------------------------
PHILTEST
PHILTEST

2 rows selected.

SYS@PHILL11G2 AS SYSDBA>


To apply changes done to "/etc/my.cnf", do we need to restart mysqld service?

Some server parameters are dynamic and can be changed on the fly. Some are not and should be configured in the my.cnf followed by service restart. max_connections can be changed on the fly by command SET GLOBAL max_connections=XXX. This value will be in effect until service restart.

To be clear, the possible options are:

Run SET GLOBAL max_connections = XXX for immediate use until service restarted
Add the max_connections = XXX line to the [mysqld] section of the my.cnf AND restart the service for permanent use
Run SET GLOBAL max_connections = XXX AND add the max_connections = XXX line to the my.cnf but DO NOT restart the service.

The third option allows to get both immediate changes as well as permanent but with zero downtime. Value in the my.cnf will wait for some event/incident/accident causing a service's restart.


Installation and shell to move physical and logical SQL Server in azure IaaS VM

It's typically not necessary to use custom templates and installation for SQL Server on Azure Virtual Machines.  The Azure Marketplace has an extensive collection of pre-configured templates for SQL Server on Azure Virtual Machines, which you can deploy through the Azure Portal or through Powershell.  These are built and maintained by the SQL Server engineering team, and definitely the easiest way to deploy and run SQL Server on Azure VMs.


AWS RDS: How do I diagnose this failure?

I have had this happen, even to production RDSs, though it is rare.

How to go about diagnosing

Check there was no advance notice of maintenance work:
Did you get any email notifications from AWS to say that your instance needed mandatory system upgrades or maintenance?  Was this in your instance's permitted maintenance window?

Raise a ticket with AWS Support:
If you have AWS support, raise a ticket with them to ask them what happened.  On the occasions that this has happened and I have raised a ticket they were not able to give me a good reason for the DB going away, but have generally shrugged and named local networking issues on the instance's HyperV.

Did I loose data?
It's unlikely though that you lost data if you are using innoDb and it was a shutdown issued by AWS. The log lines that you cite:
"Giving 2 client threads a chance to die gracefully", then
"Shutting down slave threads"
Are lines from a command initiated shutdown, rather than a crash.  It looks like AWS issued a shutdown for you.

Other Notes
Occasionally AWS sees fit to move your instance to another Hypervisor without prior notice, sometimes because they want to clear the Hypervisor that your instance is on, it may have developed hardware problems, for example.
There might be networking issues within AWS, or you might be subject to 'noisy neighbours'.  There are many possible reasons and unfortunately contractually AWS don't have to tell you why.


Can you provide rights to a snapshot without providing rights to the database?

From Limitations and Requirements of Database Snapshots on TechNet:

  A database snapshot inherits the security constraints of its source database at the time of snapshot creation. Because snapshots are read-only, inherited permissions cannot be changed and permission changes made to the source will not be reflected in existing snapshots.

Update:

As Martin Smith suggested in a comment, one way is to grant permissions on database, take a snapshot, and then remove permissions. The login could be temporarily disabled for the period that rights were granted.


Looking at the code, I don't see a reason for the cursor. It's unclear what column is supposed to be unique, but you can handle your EXIST in a WHERE clause (i used NOT IN) and remove all the variables, and cursor as well.

insert into purchase_order (order_number, job_id, supplier_id, contact_id, user_list_id, status_id, notes)
SELECT
    a.order_id
    ,j.job_id
    ,s.supplier_id
    ,sc.contact_id
    ,ul.user_list_id
    ,2
    ,null
FROM
    _po_temp_b b
inner join
    _po_temp_a a on a.order_id = b.order_id
inner join
    job j on j.job_number = b.job_num
inner join
    supplier s on s.supplier_name = b.supp_name
inner join
    supplier_contact sc on sc.supplier_id = s.supplier_id
inner join
    user_list ul on ul.user_order_number = b.user_num
where
    a.order_number not in (SELECT order_number FROM purchase_order)

You may need LEFT joins... it all depends.


PostgreSQL 11: How to restrict privileges to stored functions?

If you want to use a function to give a user access to otherwise inaccessible data, you need to define it as SECURITY DEFINER and make the owner a user with the required privileges on the data.

Don't forget to

REVOKE EXECUTE ON FUNCTION myfunc FROM PUBLIC;

because by default, everybody can execute a function
ALTER FUNCTION myfunc SET search_path = ..., pg_temp;

so that the function is safe from privilege escalation attacks.


Implement a Parent-Level Count in Binary Search Tree

If you look at the GetFamilyTree code, there is nothing monitoring the tree height or any particular level.
To compensate, I have a revised version of this code
DELIMITER $$

DROP FUNCTION IF EXISTS `junk`.`GetFamilyTree` $$
CREATE FUNCTION `junk`.`GetFamilyTree` (GivenID INT,LevelLimit INT)
RETURNS varchar(1024) CHARSET latin1
DETERMINISTIC
BEGIN
    DECLARE rv,q,queue,queue_children,front_rc,level_part VARCHAR(1024);
    DECLARE queue_length,front_id,front_ht,pos,curr_level INT;

    SET rv = '';
    SET queue = CONCAT(GivenID,':0');
    SET queue_length = 1;

    WHILE queue_length > 0 DO
        SET front_id = FORMAT(queue,0);
        IF queue_length = 1 THEN
            SET front_rc = queue;
            SET queue = '';
            SET pos = LOCATE(':',front_rc);
        ELSE
            SET pos = LOCATE(',',queue);
            SET front_rc = LEFT(queue,pos - 1);
            SET q = SUBSTR(queue,pos + 1);
            SET queue = q;
            SET pos = LOCATE(':',front_rc);
        END IF;
        SET front_id = LEFT(front_rc,pos - 1);
        SET front_ht = SUBSTR(front_rc,pos + 1);
        SET queue_length = queue_length - 1;

        SELECT IFNULL(qc,'') INTO queue_children
        FROM (SELECT GROUP_CONCAT(CONCAT(id,':',front_ht+1)) qc
        FROM pctable WHERE parent_id = front_id) A;

        IF LENGTH(queue_children) = 0 THEN
            IF LENGTH(queue) = 0 THEN
                SET queue_length = 0;
            END IF;
        ELSE
            IF LENGTH(rv) = 0 THEN
                IF front_ht < LevelLimit THEN
                    SET rv = queue_children;
                END IF;
            ELSE
                IF front_ht < LevelLimit THEN
                    SET rv = CONCAT(rv,',',queue_children);
                END IF;
            END IF;
            IF LENGTH(queue) = 0 THEN
                SET queue = queue_children;
            ELSE
                SET queue = CONCAT(queue,',',queue_children);
            END IF;
            SET queue_length = LENGTH(queue) - LENGTH(REPLACE(queue,',','')) + 1;
        END IF;
    END WHILE;

    #
    # Strip away level parts of the output
    #
    IF LENGTH(rv) > 0 THEN
        SET curr_level = 1;
        WHILE curr_level <= LevelLimit DO
            SET level_part = CONCAT(':',curr_level);
            SET rv = REPLACE(rv,level_part,'');
            SET curr_level = curr_level + 1;
        END WHILE;
    END IF;

    RETURN rv;
END $$

DELIMITER ;

This is how I changed it: For each id in the queue, I introduce a height.
Each member of the list will look like this:
id:height,id:height,id:height, ... ,id:height

I place a limit on when to concatenate by adding IF front_ht < LevelLimit THEN
When the list is compiled, I then taken the return value rv and remove all colons (:) and the number behind each colon. What's left is a comma-separated list of IDs within the height range set by LevelLimit. If you want the height to be included in the output, remove the Strip away level parts of the output code at the bottom of the Stored Procedure.
Give it a Try !!!
UPDATE 2014-11-29 16:05 EST
It is important to note that you need to make GROUP_CONCAT have enough space to collect a long list of info. Please run this in mysql:
SET GLOBAL group_concat_max_len = 1048576;
SET group_concat_max_len = 1048576;

Then, add this to my.cnf
[mysqld]
group_concat_max_len =1048576;


Given the functional dependencies of a relation is there more then one way to normalize it?

Normalization to Boyce-Codd Normal Form, 5NF or 6NF (and therefore to any "lesser" NFs) certainly can be automated. Google for Bernstein's alogrithm for a simple example of how.

There is frequently more than one way in which a set of dependencies can be normalized to any given Normal Form. Again, there are formal methods that can help you choose. Typically we want a database that is not just normalized but also dependency-preserving.


How to troubleshoot "client driver has sent multiple requests while the session is in single-user mode"

I don't see it stated anywhere as to what version of the .NET Framework is being used, but given that this question was asked in May of 2014, and .NET Framework version 4.5 came out on 2012-08-15, the following note on SqlConnection.ConnectionString for the Asynchronous Processing / Async keyword seems relevant:

  This property is ignored beginning in .NET Framework 4.5.

So, it might help to find out the exact version of Entity Framework being used, and the target version of the .NET Framework.

However, it kinda sounds like this issue might be a result of connection pooling. I suspect connection pooling because after the error message about the connection being closed, the session was still around.

The first thing to try is to simply disable connection pooling by adding the following to the connection string:

Pooling=false;

If the problem never comes back, then this was most likely the issue. I hesitate to say that this was the issue since it isn't exactly proof. It could have merely reduced the frequency from "once every few days" to once every few weeks or even months. But, if this does improve things, yet the app hits the server frequently and you would prefer to use connection pooling if at all possible, then you can try to reintroduce connection pooling, but in limited sense: you can set a low value for the Connection Lifetime keyword:

Connection Lifetime=2;

This will have connections close upon returning to the connection pool, if they are over 2 seconds old. This allows for a series of quick query executions to go through without each of them spending time establishing the connection, while at the same time not allowing the connection to linger out there, possibly holding locks, etc.

Even if the error still occurs, by not having connection pooling keeping the session and connection alive, that should allow for proper cleanup, releasing of locks, etc.

Just to have this stated since it was questioned in comments on the question: Multiple Active Result Sets (MARS) is used by Entity Framework to do Lazy Loading. So if the app has been developed to use Lazy Loading, then turning off MARS is not an option.


Better way to restore to existing pg database

The command you showed looks fine.

pg_restore -d target_database /path/to/the/backup.dbbackup

At a guess, there were errors during the restore but you didn't see them. Most likely you were running with a user ID that did not have the correct permissions to perform the restore.

If you are certain that there is no data of any value in the target database (which will be deleted by the following command) try:

sudo -u postgres pg_restore --create --clean -d postgres /path/to/dbbackup.sql

If you have issues, post the output in an edit to your question, then comment here.

Also: You do not need to restart the server. At most, you should restart your Rails application. Usually that means restarting Apache, but it depends a bit on how you're running Rails. Restarting the whole server is completely unnecessary.


How does security context propagate between schemas within a database?

Thanks to the OP's clarification of the error, I'm going to post my comment as a suggested answer.

In SQL Server, permissions are checked only when ownership changes.

Consider the following tables:

Table SchemaA.Table1, owned by Alice.
View SchemaA.View1, owned by Alice, depends on Schema1.Table1.
View SchemaB.View1, owned by Bob, depends on Schema1.View1.

Consider these situations:

Alice attempts to select from SchemaA.Table1:

No change in ownership so permissions are not checked on the table. Alice is allowed access.
Alice attempts to select from SchemaA.View1:

No change in ownership so permissions are not checked on the view or on the table. Alice is allowed access.
Bob attempts to select from SchemaA.Table1:

There is a change in ownership on SchemaA.Table1 (Bob is attempting an operation on Alice's object) so Bob's permissions are checked on SchemaA.Table1.
Bob attempts to select from SchemaA.View1:

This is a change in ownership on SchemaA.View1 (Bob is attempting an operation on Alice's object) so Bob's permissions are checked on SchemaA.View1. There is no change in ownership from SchemaA.View1 to SchemaA.Table1 so Bob's permissions are not checked on SchemaA.Table1.
Bob attempts to select from SchemaB.View1.

There is no change in ownership on SchemaB.View1 (Bob is attempting an operation on Bob's object) so permissions are not checked on SchemaB.View1. There is a change in ownership from SchemaB.View1 to SchemaA.View1 so Bob's permissions (not Alice's) are checked on SchemaA.View1. There is no change in ownership from SchemaA.View1 to SchemaA.Table1 so Bob's permissions are not checked on SchemaA.Table1.

So, in the OP's sitation, the view [TESTCTRL].[CTRL_TEST_VIEW] depends upon the table [TESTDATA].[UserData]. The view and the table have different owners so there is a change in ownership (something some SQL Server books call a "break in the ownership chain"). To query the view, JoeUser needs to be granted the appropriate permissions to both the view and the table.


SQL Server - SQL Plan Management for baseline

You would just pull the execution plans for the most relevant queries from the plan cache.  I would query sys.dm_exec_query_stats, and join to sys.dm_exec_query_plan and sys.dm_exec_sql_text to get the plan and query text, respectively. Within sys.dm_exec_query_stats you can pull queries with the highest IO, CPU, execution count, etc. - whatever is most important to you.

Looking to the future, SQL Server 2016 introduces the Query Store:

Monitoring Performance By Using the Query Store


Is it possible to extract the multiple variable parts of data into different columns without a procedure?

You should be able to do this with some string manipulation using charindex() and substring().

It looks like you can use X1: to find the start of the value you are looking for and ) to locate the end of a value.

declare @T table(MyCol varchar(100));

insert into @T values
('MyText'),
('MyText2\nX1: [S 1: 3] (S 3)\nX2: [S 1: 2] (S 2)\nX3: [S 75: 1] (S 75)Further text2'),
('MyText3\nX2: [S 800: 3] (S 2400)Further text3');

select substring(T.MyCol, P1.X1, P2.X1 - P1.X1 + 1) as X1,
       substring(T.MyCol, P1.X2, P2.X2 - P1.X2 + 1) as X2,
       substring(T.MyCol, P1.X3, P2.X3 - P1.X3 + 1) as X3,
       substring(T.MyCol, P1.X4, P2.X4 - P1.X4 + 1) as X4
from @T as T
  cross apply (
              select charindex('X1:', T.MyCol) as X1,
                     charindex('X2:', T.MyCol) as X2,
                     charindex('X3:', T.MyCol) as X3,
                     charindex('X4:', T.MyCol) as X4
              ) as P1
  cross apply (
              select case when P1.X1 > 0 then charindex(')', T.MyCol, P1.X1) end as X1,
                     case when P1.X2 > 0 then charindex(')', T.MyCol, P1.X2) end as X2,
                     case when P1.X3 > 0 then charindex(')', T.MyCol, P1.X3) end as X3,
                     case when P1.X4 > 0 then charindex(')', T.MyCol, P1.X4) end as X4
              ) as P2;

Result:

X1                   X2                       X3                     X4
-------------------- ------------------------ ---------------------- -----
NULL                 NULL                     NULL                   NULL
X1: [S 1: 3] (S 3)   X2: [S 1: 2] (S 2)       X3: [S 75: 1] (S 75)   NULL
NULL                 X2: [S 800: 3] (S 2400)  NULL                   NULL


How to check for mutiple rows with same data?

DISTINCT will combine identical rows.  GROUP BY will combine them and allow you to know which ones had multiples using COUNT(*).  To meet your "not select data of multiple rows" requirement you will need to add a HAVING clause.  Your query will look something like this:

SELECT cola, colb, colc FROM T1 GROUP BY cola, colb, colc HAVING count(*) = 1;

SQL Fiddle Demonstration


Select rows that have multiple required filters

This is referred to as the intersection of sets.  One way to perform the query is the use the INTERSECT operator:

SELECT ag.artist_id
FROM dbo.artists_genres ag
WHERE ag.genre_id = 1
INTERSECT
SELECT ag.artist_id
FROM dbo.artists_genres ag
WHERE ag.genre_id = 2

INTERSECT returns distinct rows that are output by both the left and right inputs.  The first part of the query retrieves the artist_id where the genre_id is 1, the second part of the query retrieves the artist_id where the genre_id is 2, and the INTERSECT operator then compares both lists, returning only rows that match precisely, eliminating duplicates.

This is very similar to using an INNER JOIN against two common-table-expressions, commonly referred to as CTEs, as in:

;WITH g1 AS
(
    SELECT *
    FROM dbo.artists_genres ag1
    WHERE ag1.genre_id = 1
), g2 AS
(
    SELECT *
    FROM dbo.artists_genres ag1
    WHERE ag1.genre_id = 2
)
SELECT g1.artist_id
FROM g1
    INNER JOIN g2 ON g1.artist_id = g2.artist_id;

If you don't like common-table-expressions (CTEs), this looks like:

SELECT g1.artist_id
FROM
    (
        SELECT *
        FROM dbo.artists_genres ag1
        WHERE ag1.genre_id = 1
    ) g1
    INNER JOIN
        (
            SELECT *
            FROM dbo.artists_genres ag1
            WHERE ag1.genre_id = 2
        ) g2 ON g1.artist_id = g2.artist_id


How to add all combinations of two pairs of key-value to a table?

INSERT INTO Pairs
SELECT DISTINCT NULL, t1.KeyID, t1.ValueID, t2.KeyID, t2.ValueID
FROM ItemKeyValues t1
JOIN ItemKeyValues t2 USING (ItemID)
WHERE t1.KeyID < t2.KeyID;

INSERT INTO ItemPairs
SELECT t1.PairID, t2.ItemID
FROM Pairs t1
JOIN ItemKeyValues t2 ON t1.Key1 = t2.KeyID
                     AND t1.Value1 = t2.ValueID
JOIN ItemKeyValues t3 ON t1.Key2 = t3.KeyID
                     AND t1.Value2 = t3.ValueID
                     AND t2.ItemID = t3.ItemID;

fiddle

PS. I have simplified your structures by removing indices and options.

PPS. Your DDL scripts pack contains at least 1 error (copypaste misprint?).


Prioritizing values in select statement in ORACLE

If I have well understood your question, you should use the DECODE function in your query in the SELECT and GROUP BY sections.

NB: for performance purpose, you might create an index using that function.


Circular referencing required for class?

I assume the error you're getting is due to trying to reference the DEPARTMENT table before it exists?

If so, then the solution is simple.

Create the EMPLOYEE table without the constraint that references the DEPARTMENT table;
Create the DEPARTMENT table as above;
Alter the EMPLOYEE table to add the constraint against the DEPARTMENT table.

Review the documentation on ALTER TABLE for how to use that command.

Given that there's no major changes needed once you know what the command is, here's a SQLFiddle link that shows you can create your tables if you pull the constraint against the then non-existent DEPARTMENT table out of CREATE TABLE EMPLOYEE, and add that constraint in using ALTER TABLE once both tables exist. (Click the "Build Schema" button to see that it builds successfully).

As pointed out by @a1ex07, you will probably want to initially make DEPARTMENT.MGR_SSN and EMPLOYEE.DNO NULL instead of NOT NULL; in fact, at least one will need to be NULL.

The problem would be that, initially, you have no rows in either table. You would be unable to insert a row into EMPLOYEE because there would be no valid value you could provide for DNO, and you'd be unable to insert a row into DEPARTMENT because you could provide no valid value for MGR_SSN.

Keeping in mind our above solution, you could

make DEPARTMENT.MGR_SSN allow NULL values initially
create the departments you need (with no managers)
add your employees (EMPLOYEE.DNO can be NOT NULL, as all the initial departments exist)
update DEPARTMENT with the appropriate MGR_SSN values
use ALTER TABLE to make DEPARTMENT.MGR_SSN NOT NULL.

Note, however, that you if you ever have a new department headed by a new employee, you'll need to:

Create the new employee tied to an existing department;
Create the new department, with the new employee as MGR_SSN;
Update the employee with the correct DNO.


SQL Server Command Line Install - How to change the edition?

I found some information about your question.
There is a parameter when you set up on the command line which is /PID.
Check out the document.

Specifies the product key for the edition of SQL Server. If this
parameter is not specified, Evaluation is used.

C:\SQLMedia\SQLServer2019> setup.exe /Q
/IACCEPTSQLSERVERLICENSETERMS /ACTION="install"
/PID="AAAAA-BBBBB-CCCCC-DDDDD-EEEEE" /FEATURES=SQL,AS,IS,Tools
/INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="MyDomain\MyAccount"

You can use this parameter with the product key.
Evaluation: 00000-00000-00000-00000-00000
Express: 11111-00000-00000-00000-00000
Developer: 22222-00000-00000-00000-00000
Excerpt from the blog of Pinal Dave.


dbcc checkdb gives error "Incorrect PFS free space information for page"

Thank You for response John!

Tried to backup DB and rebuild clustered index, but didn't help. As I understand there is no patch, but it is and old bug. Or something was really damaged during hardware crash..

This helped:

--dbcc checkdb;
--dbcc checktable(DocStreams) WITH ALL_ERRORMSGS;

ALTER DATABASE WSS_Content
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

dbcc checktable(DocStreams, REPAIR_FAST) WITH ALL_ERRORMSGS;
GO
dbcc checktable(DocStreams, REPAIR_REBUILD) WITH ALL_ERRORMSGS;

-- use this only if above does not work
--DBCC CHECKTABLE(DocStreams, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;

ALTER DATABASE WSS_Content SET MULTI_USER;

But first

Do backup database
I also backuped table using this cmd (press Enter multiple times):

bcp WSS_Content.dbo.DocStreams out C:\backups\DocStreams\DocStreams.dat -t -T

pause

Use in Your own risk)

links:
  incorrect-pfs
  bcp tool

Upd.: or You can try "reorganize with (lob_compaction = on)" first (link)


How to eliminate for loop in a nested aggregate problem

For just the shown example, plain string manipulation would do the job:

WITH tbl(j) AS (SELECT json '[{"a":"1","b":"2"},{"c":"3","d":"4","e":""}]')
SELECT '{' || regexp_replace( left(right(j::text, -1), -1)
                            , '"([^"]+)" *:', '\1=', 'g')
    || '}'
FROM  tbl;

If you have more sophisticated keys and values, here is a rewrite of your function. Using a simple SQL function instead:

CREATE OR REPLACE FUNCTION f_parse(_in jsonb)
  RETURNS text LANGUAGE sql STABLE AS
$func$
SELECT '{{' || string_agg(part, '},{') || '}}'
FROM   jsonb_array_elements(_in) q1
CROSS  JOIN LATERAL (
   SELECT string_agg(q2.key || '=' || q2.value::text, ',') AS part
   FROM   jsonb_each(q1.value) q2
   ) q2
$func$;

If you don't trust that the original order of elements is guaranteed, use this safe variant:

CREATE OR REPLACE FUNCTION f_parse_safe(_in jsonb)
  RETURNS text LANGUAGE sql STABLE AS
$func$
SELECT '{{' || string_agg(part, '},{' ORDER BY q1.ordinality) || '}}'
FROM   jsonb_array_elements(_in) WITH ORDINALITY q1
CROSS  JOIN LATERAL (
   SELECT string_agg(q2.key || '=' || q2.value::text, ',' ORDER BY q2.ordinality) AS part
   FROM   jsonb_each(q1.value) WITH ORDINALITY q2
   ) q2
$func$;

See:

How to preserve the original order of elements in an unnested array?

db<>fiddle here


Thanks to @ Justin Cave I got the answer. The problem was that another table (Department) was referencing the Store table as well. so I added in the trigger body:

DELETE FROM Department
WHERE Department.storeCode = :oldRow.stCode;


How can i update table when counter reach a specific value? (thread safe)

One update is atomic operation, so when running the second update twice, the second thread has to wait until the first finishes (when using innodb and transactions, entire transaction is atomic) and it should not update anything, because the click.count will be set to 0 before by the other thread.

But I see a potential problem with your code - if other thread updates count to 1001 too fast, your update won't do anything and will never repair it. You should probably do

SET click.count = click.count - 1000 ... WHERE click.count >= 1000

so you can catch such cases. (They are maybe not very probable, but if you have lot of hits, then they can sure happen if you do not prevent them by some other means)


Drastic performance difference in View Dependencies between SQL Server 2005 and 2008 R2

The 2008r2 "View Dependencies" functionality was completely rewritten from the 2005 version. This is because the 2005 version didn't work.

So it's not surprising that the results may be different (i.e., they should be correct now).  It is a little unexpected that the 2008r2 version might be slower at it, but not entirely surprising, as it is after all, using a completely different approach.

So, my guess would be that this phenomenon has little or nothing to do with your other problems.


How to get hierarchical data from two tables in oracle?

Use grouping sets. You can try it here.

select cat1, cat2, cat3, sum(val) as val
  from t1 join t2 on t1.f_id = t2.f_id
group by grouping sets ((cat1),(cat1, cat2),(cat1, cat2, cat3))
order by cat1, cat2 nulls first, cat3 nulls first

OUTPUT

cat1 cat2 cat3 val
A           15
A   a       10
A   a   aa  4
A   a   ab  6
A   b       5
A   b   ba  4
A   b   bb  1
B           21
B   c       12
B   c   ca  6
B   c   cb  6
B   d       9
B   d   da  9

If you prefer underscore than null use NVL

select cat1, nvl(cat2,'_') as cat2, nvl(cat3, '_') as cat3, sum(val)
  from t1 join t2 on t1.f_id = t2.f_id
group by grouping sets ((cat1),(cat1, cat2),(cat1, cat2, cat3))
order by cat1, cat2 nulls first, cat3 nulls first


This query takes anywhere from 1 - 30 seconds depending on server load... How can it be optimized?

Assuming that id is the PRIMARY KEY of the watch_list table, the following should make the query very fast.

ALTER TABLE `watch_list`
ADD INDEX `AppropriateIndexName` (`item_id`,`user_id`)


PostgreSQL comes to my mind. You have pgcrypto library for encrpyting data and there are drivers for .Net


Can Status of NOEXEC be Checked with a DMV?

NOCOUNT can be determined for the current session by parsing @@OPTIONS or by running DBCC USEROPTIONS.
Unfortunately, the only way I could find to determine the value of this option for a different session is by capturing the ExistingConnection event in SQLTrace or Extended Events.

Example 1:

DBCC USEROPTIONS

Output:

+-------------------------+----------------+
|       Set Option        |     Value      |
+-------------------------+----------------+
| textsize                | 2147483647     |
| language                | us_english     |
| dateformat              | mdy            |
| datefirst               | 7              |
| lock_timeout            | -1             |
| quoted_identifier       | SET            |
| arithabort              | SET            |
| ansi_null_dflt_on       | SET            |
| ansi_warnings           | SET            |
| ansi_padding            | SET            |
| ansi_nulls              | SET            |
| concat_null_yields_null | SET            |
| isolation level         | read committed |
+-------------------------+----------------+

Example 2:

SELECT *
FROM (
    VALUES
    (1     ,'DISABLE_DEF_CNST_CHK'),
    (2     ,'IMPLICIT_TRANSACTIONS'),
    (4     ,'CURSOR_CLOSE_ON_COMMIT'),
    (8     ,'ANSI_WARNINGS'),
    (16    ,'ANSI_PADDING'),
    (32    ,'ANSI_NULLS'),
    (64    ,'ARITHABORT'),
    (128   ,'ARITHIGNORE'),
    (256   ,'QUOTED_IDENTIFIER'),
    (512   ,'NOCOUNT'),
    (1024  ,'ANSI_NULL_DFLT_ON'),
    (2048  ,'ANSI_NULL_DFLT_OFF'),
    (4096  ,'CONCAT_NULL_YIELDS_NULL'),
    (8192  ,'NUMERIC_ROUNDABORT'),
    (16384 ,'XACT_ABORT')
) AS v(num, descr)
WHERE @@OPTIONS & num = num;

Output:

+------+-------------------------+
| num  |          descr          |
+------+-------------------------+
|    8 | ANSI_WARNINGS           |
|   16 | ANSI_PADDING            |
|   32 | ANSI_NULLS              |
|   64 | ARITHABORT              |
|  256 | QUOTED_IDENTIFIER       |
|  512 | NOCOUNT                 |
| 1024 | ANSI_NULL_DFLT_ON       |
| 4096 | CONCAT_NULL_YIELDS_NULL |
+------+-------------------------+

Unfortunately, I could not find a way to tell whether NOEXEC is on or off.


Data Migration and altering tables in one SSDT deployment script

I'm unsure if this is in the version of SSDT/Visual Studio you're using but as far as I know, in the Database Project type - you can add Pre/Post Deployment scripts by simply adding right clicking your project and adding a Script > Pre/Post-Deployment script.

When you publish this, and generate the script, you'll see the pre and post deployment scripts in the SQLCMD syntax.

In terms of doing it automatically, I don't think what you're asking is possible. Personally, I found generating DACPACs to be quite useful for deployment. Develop your database and publish to a DACPAC and you can almost automate your deployment. However, your specific example requires some knowledge of your implementation, so I can't think of a way it could programmatically know this. Apologies if I'm not reading you correctly.


SQL Server Row Count sp_spaceused rows discrepancy

Yes, this can potentially affect plan choice. Does running DBCC UPDATEUSAGE make everything right again? e.g.

DBCC UPDATEUSAGE('yourdb', 'dbo.tblEMPLOYEES_MASTER');

Make everything right again? That doc says:

  Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure.


My question is, why it's still doing key lookup instead of index scan/seek ?

The query specifies that results should be ordered by DateCreated.  Since you already had a nonclustered index on DateCreated, the optimizer decided that the cost of doing key lookups was lower than sorting all of the data by DateCreated.

  Does this mean key lookup was done due to ORDER BY on non-clustered index ?

Essentially, yes.  It was estimated to be cheaper* to read the data in the required order, and get any additional fields through a key lookup, rather than reading all of the fields from a single index and then sorting it by DateCreated.

You could confirm this by comparing the estimated costs between

the original query (with the original indexes), and
the original query with an index hint

The index hint would be like this on the FROM line:

FROM [dbo].[Watchdog] WITH (INDEX (LocationId_FirstTrigger))

This should produce a plan with no key lookups (since LocationId_FirstTrigger is covering for that query), and a Sort operator.  I'd expect the "Estimated Cost" to be higher, thus the other plan was chosen.

* To explain the optimizer's choice here:

The TOP (1) in your query means the optimizer sets a row goal, meaning the plan is geared toward producing one row quickly. The optimizer expects to find one row from the Index Scan matching your LocationId predicate very quickly, since it assumes values are distributed uniformly. This may or may not be true in reality. The cost of one Key Lookup following the Index Scan is pretty small.

The scan + lookup option therefore looks cheaper to the optimizer than finding matches using LocationId_FirstTrigger and sorting. You can turn the row goal logic off for the query as a test by adding an OPTION (QUERYTRACEON 4138) hint. You will likely find the optimizer then chooses the LocationId_FirstTrigger index without an index hint.

Still, the best alternative is to modify your index as Mikael Eriksson suggests.


what is mongodb telling me when it logs an update?

I think you meant upsert rather than upstart (upstart being a type of job on an Ubuntu system).  Upsert means "update the document if present; insert (a single document) if missing". MongoDB determines that the document is missing solely via examining the criteria document you pass to it.

That query to determine whether the document exists is likely the slow part here.  You should run an explain on just the find to determine if it is using indexes correctly and is as efficient as it should be:

http://www.mongodb.org/display/DOCS/Explain

If it is not using an index then you should add one to speed things up.  Depending on whether this example is typical of your query plan I would expect that an index on "corefEntityId" would be a good idea at a minimum.  If the find is more complex in other cases you may want to look into a compound index and use hint to see which is optimal:

http://www.mongodb.org/display/DOCS/Optimization#Optimization-Hint

In terms of the logging - when it logs a query like you have seen in your example, the reason is because the default "slowms" value is 100.  That is, MongoDB will log every query that takes longer than 100ms to complete.  This can be adjusted:

http://www.mongodb.org/display/DOCS/Database+Profiler#DatabaseProfiler-EnablingProfiling

If you wanted to set it to 200ms for example:

db.setProfilingLevel(1,200)


Want to change a timestamp date to an integer YYYYMMDD

As ypercubeᵀᴹ mentioned, this works in SQL Anywhere:

select CONVERT(char(8),current_timestamp,112) from dummy


Performance in a single SQL Server 2014 database is slower than the rest

Also try to look the estimated execution plan. Besides, while executing your SP open a new session and look for wait_stats. That will give you more hints on what is your query waiting on.


How to disable starting MySQL service on Windows7 startup?

In Control panel open Administrative tools then double click Services.  Scroll down to MySQL and double click to open the properties.  Change the Startup type to Manual.


Delete rows older than x days without locking table

You need to delete rows in batches to avoid lock escalation.  Lock escalation happens when SQL Server switches from row or page locks to locking the entire table.  Lock escalation conserves memory when SQL Server detects a large number of row or page locks have been taken, and more are needed to complete the operation. This may be why you're having blocking issues.  If I recall correctly, row locks escalate to table locks when more than 5,000 rows are involved.
If the ID column is also the clustered index you might benefit from using the ID column to perform the delete instead of using the date column.  To do that, obtain the ID value for the date you want to work with, then loop to delete fewer than 5,000 rows per batch, where the ID value is less than or equal to the desired ID.  This is only applicable assuming rows are inserted in date order, such that incrementing ID values match incrementing date values.
You can determine how many rows were affected by the DELETE by checking the @@rowcount variable, and you can set a maximum number of rows to be affected using SET ROWCOUNT, then loop until your DELETE no longer removes rows:
SET ROWCOUNT 4500;
DECLARE @i int;
SELECT @i = 1;

WHILE @i > 0
BEGIN
    Delete from dbo.my_table Where ID < 4356;
    SELECT @i = @@rowcount;
END

An alternate method that eliminates the use of the deprecated SET ROWCOUNT statement is to add a TOP clause to the DELETE query, as in:
DECLARE @i int = 1;
DECLARE @id int;
SET @id = COALESCE((
    SELECT TOP(1) mt.ID
    FROM dbo.my_table mt
    WHERE mt.date < '2021-01-04T00:00:00.000'
    ORDER BY mt.date DESC
    ), 0);
WHILE @i > 0
BEGIN
    DELETE TOP(5000) mt
    FROM dbo.my_table mt
    WHERE mt.ID <= @ID;
    SET @i = @@ROWCOUNT;
END

In the code above, I've added a query to obtain the maximum ID value for rows on or before January 4th, 2021, simply to illustrate how this works.
Both methods above result in the same outcome, all rows prior to the given @ID value are deleted from the table.  Locking should be reduced to using row-level locks, thereby preventing a table lock from causing blocking on other concurrent processes.
As an alternative, you could look at Read Committed Snapshot Isolation (RCSI), otherwise known as row-versioning, at the database level.  RCSI prevents writers from block readers, however there are many possible impacts to using row versioning. You should thoroughly test the applications that use the database if you decide to go this route. Read up carefully and consider the other impacts on your application before blindly enabling RCSI.


How to be sure that a SQL Script is running with ANSI encoding?

So, how can I be sure, that when the user press F5, all script is encoded with ANSI, the same way we sent it, without these strange characters?

You can't be sure. This is just the unfortunately complicated nature of text encoding, especially non-Unicode encodings. Everything is just bytes. What we see on the screen is just an interpretation of those bytes. One encoding may certainly display a different "character" than another encoding for the same byte, or byte-sequence (depending on the encoding), but technically, bytes are bytes and all bytes are valid.

In the case of Â, you can't detect a difference since there is no difference. You only see the xC2 in Notepad++ because that is the byte value of that character, yet that, by itself, is not a valid UTF-8 or UTF-16 / UCS-2 byte sequence, so Notepad++ shows you just the byte by itself.

Now, going between one of the Unicode encodings (UTF-8, UTF-16, or UTF-32) and an 8-bit encoding could be detectable by finding a character that is not in any 8-bit code page and comparing it to ? / CHAR(63), and if they match, then you are no longer using a Unicode encoding.

The deficiency here is that 8-bit encodings have no way to indicate what encoding / code page they are. You just have to know. But, the Unicode encodings have the option of placing a few bytes at the beginning of the file to indicate the type of encoding being used. This sequence of bytes is called the Byte Order Mark (BOM) and if in the correct encoding, will be non-visible.

So, your best-bet is to use one of the Unicode encodings and be sure to save files with the Byte Order Mark (BOM) since you usually have the choice to save in the Unicode encoding with or without the BOM. In Notepad++ (which I use) the two UCS-2 options are both BOM-only, but UTF-8 has the choice. If your scripts are currently using ANSI, then in the Encoding menu in Notepad++, select Convert to UTF-8-BOM and then save the file. Then, when copying and pasting into SSMS, all should be fine. And opening that file in most editors will automatically detect that it is encoded as UTF-8 because the BOM is there.

  Can we only achieve this with the right collate for the database?

This has nothing to do with SQL Server. This has to do with the client tool and what encoding it is using. SSMS is almost certainly using UTF-16 LE (Little Endian) as that is what Windows / SQL Server / .NET uses.

Regarding the query recently added to the end of the question:

The ã has a value of 0xE3 in ANSI encoding, which isn't valid in UTF-8 or UTF-16. In Notepad++, changing the encoding to be UTF-8 (using "Encode in", not "Convert to") results in it showing just the xE3. Copying and pasting that version of the query into SSMS takes that byte plus the next one (the one used for the closing ') and converts it to 㧩 which breaks the query due to no closing quote. You can fix that by adding 2 spaces after the ã so that it looks like:

CHAR(ASCII('ã  '))

That will still work as expected when the encoding has not been changed since the ASCII function only returns the value for the first character and the additional characters (the 2 spaces) are ignored.

If that script is imported or changed to be UTF-8, it will appear as follows in Notepad++:

CHAR(ASCII('xE3  '))

and that xE3 will be a single "character". Copying and pasting that version of the query into SSMS will show up as follows:

CHAR(ASCII('㠠'))

And running that will produced the desired "error" result.

HOWEVER, please be warned that this is not a fool-proof / guaranteed test. It mainly just indicates that the script was incorrectly opened as being UTF-8, UTF-16, or an 8-bit code page that does not contain the ã character.

This approach does not indicate an error if the script is opened as an 8-bit encoding that is not ANSI but still contains the ã character, and could be misinterpreting (i.e. changing) other characters.

The only way to guarantee ANSI encoding is to find a character that is a) not available in any other 8-bit code page, and b) not the same in UTF-8 or UTF-16. I am not aware of any such character, though I also have not checked them all against all available code pages.

BUT, if you are only dealing with people opening the files as UTF-8, then making the adjustment shown above should work for that situation.


Undrop tool for InnoDB can scan you disk and retrieve records that are not overwritten yet.
I didn't test it on OSX though, only Linux-es are supported.


Postgresql: How to get row type of a table as text?

There is no way to do this, since a type name is always an identifier, and only constants (literals) can be parameters.
The only resort is to use dynamic SQL like
EXECUTE format('json_populate_record(null::%I, $1)', 'schematable') USING json;


What are the most compelling reasons to upgrade from SQL Server 2005 to SQL Server 2012?

Typically the features that you don't hear about from marketing are the ones that don't bring in all the money (e.g. pushed as "enterprise feaures" in order to sell Enterprise Edition). I answered a similar question here that provided a list of my favorite new features in 2012, that also aren't limited to Enterprise Edition:

What are Objective Business Reasons to Prefer SQL Server 2012 over 2008 R2?

(I could list them here but I don't think duplication makes sense. And there are some other good answers on that page as well.)

The only difference would be you could add to the list the things that have been added from 2008 on: date/time data types, data compression (Enterprise), backup compression (Standard+), some syntax improvements (multi-row values, inline declare + assign, +=/-=, etc). These are things you would get, though, whether you went 2005 -> 2008 R2 or 2005 -> 2012. Guess I knee-jerked listing those because you won't get them if you stay where you are.

In addition, moving to 2012 now instead of 2008 R2 buys you a longer lease toward end-of-life / end-of-mainstream support. However if you are using CAL licensing you might consider 2008 R2, because this option isn't available anymore in 2012 Enterprise. And core licensing can be more expensive if your CPUs have more than 4 cores per socket. They recently updated the "How to Buy" part of the site; you should stay on top of that and keep in good touch with a licensing rep. They're not perfect (and we can have a different discussion about this) but they're the only ones who can give you plausible deniability should anything they sell you end up putting you out of compliance, as long as you haven't lied to them. :-)

If you're going to take advantage of CPU-intensive things like compression, then having faster, fewer cores may be tolerable overall especially since it will make your SQL licensing (quite likely the most expensive part of your solution) that much cheaper. More, slower cores will be more expensive in terms of licensing, and spreading the work over more cores won't necessarily do any better for an OLTP-type workload - since many tasks cannot be parallel anyway. Also, AMD cores are subject to a core factor, which means the pricing is adjusted. This means you pay less per AMD core than per Intel core, but this is probably reflected in the performance, as well (at least if my conversations with Glenn Berry are any indication). No discount like this is in effect for 2008 R2 (at least AFAIK) since you're paying by the socket. Personally, I would rather spend the money on the better cores, but at 48x + 1.33x for licensing I'm not sure I could sell the bean-counters on it. :-)

But there's no way anyone here can tell you which way to go. If you can get some spec hardware in both configurations that would be ideal - you really should test your full workload cycle to see if fewer, faster processors are better for your workload - or at least close enough that they are justified by the licensing savings. On modern hardware I'd expect that to be a better option but I am not going to sign anything that guarantees it. :-)


Retrieve Data that do not exist in the primary table, exist in the secondary only

Use EXCEPT:

SELECT [id]
  ,[name]
  ,[email]
  ,[phone]
  ,[cellphone]
  ,[none]
FROM [test].[dbo].[datatable1]
EXCEPT
SELECT [id]
  ,[name]
  ,[email]
  ,[phone]
  ,[cellphone]
  ,[none]
FROM [test].[dbo].[datatable]

This will give you all the rows that exists in datatable1 but do not exist in datatable.

If you want it only for the keys then you should use

select t1.[id]
from   [test].[dbo].[datatable1] AS t1
where  not exists (select t2.[id]
                   from   [test].[dbo].[datatable] AS t2
                   where t2.[id] = t1.[id]);


Can I create a SQL Job to run on specified dates?

You can't do this using the point-and-click UI, because it is primitive and inflexible, but you can set the job to run once a month, on the first, and put this in your job step:

IF DATEPART(MONTH, GETDATE()) IN (1,4,7,10)
BEGIN
  -- DO THE THINGS!
END

If you have multiple steps and want the whole job to exit, rather than having to put that logic in each step, you can reverse the condition and raise an error, and set the step properties to quit the job on error. If you want more graceful failure, you could add a step at the end that quits the job with success, and make that the next step for the first step on error.

IF DATEPART(MONTH, GETDATE()) NOT IN (1,4,7,10)
BEGIN
  RAISERROR('This is not the beginning of a quarter, silly.',11,1);
END


Query an intermediate table and join to child table

This sounds like a job for a correlated subquery.

SELECT
    mp.id AS id,
    p.name AS productName,
    m.name AS marketName,
    mp.identifier,
    (SELECT the_price_column
       FROM price pr
      WHERE pr.marketproduct_id = mp.id
      ORDER by the_date_column DESC LIMIT 1) as latest_price
FROM
    marketproduct mp
INNER JOIN
    market m ON mp.market_id = m.id
INNER JOIN
    product p ON mp.product_id = p.id
WHERE
    productName = 'SomeProduct'

Your question said "productmarket" but the query you posted used "marketproduct" so I went with the latter in the example.

If the price table has no prices for a particular product, this query will return null for the price of that product.

The price table needs a multi-column index on (marketproduct_id, the_date_column) to optimize this query.

Update: You can get other columns, too, by repeating the subquery and asking for something different...

...
    m.name AS marketName,
    mp.identifier,
    (SELECT the_price_column
       FROM price pr
      WHERE pr.marketproduct_id = mp.id
      ORDER by createddate DESC LIMIT 1) as latest_price,
    (SELECT createddate
       FROM price pr
      WHERE pr.marketproduct_id = mp.id
      ORDER by createddate DESC LIMIT 1) as latest_date
FROM
    marketproduct mp
...

Or, you could try it with a join, moving the subquery to a join condition in the where clause to get as many columns as you need from the price table.

SELECT
    mp.id AS id,
    p.name AS productName,
    m.name AS marketName,
    mp.identifier,
    pr.the_price_column,
    pr.createddate
FROM
    marketproduct mp
INNER JOIN
    market m ON mp.market_id = m.id
INNER JOIN
    product p ON mp.product_id = p.id
INNER JOIN
    price pr ON pr.id =
    (SELECT id
       FROM price prx
      WHERE prx.marketproduct_id = mp.id
      ORDER by createddate DESC LIMIT 1)
WHERE
    productName = 'SomeProduct';

Both of these feel slightly messy, because in the first example, we're dipping into the price table twice, and in the second example, we're referencing the price table twice with two different aliases ... but it's hard to see a way to avoid that.  The performance of the two queries should be fairly comparable, though.  In both cases, having to go twice isn't as bad as it sounds because whichever element hits the table first will have already brought the index and row data into the buffer pool (InnoDB) or OS/key cache (MyISAM) so the penalty for the second hit on the same row shouldn't be significant.


It is not allowed to have arbitrary expressions in FOREIGN KEY constraints, only columns. That's why you get an error when you try the 2nd approach.

You can however, use a VIEW to access the table:

CREATE VIEW v_session_groups
    (group_id, session_range, description) AS
SELECT
    group_id,
    int4range(first_session, last_session, '[)'),
    description
FROM
    session_groups ;

You could even add triggers, and then be able to INSERT/UPDATE/DELETE from the view, as if it was a normal table.

Side notes:

The first design has 2 UNIQUE constraints, on (first_session) and (last_session), while the 2nd has 1 UNIQUE constraint, on (first_session, last_session). These are not equivalent designs, so re-examine your requirements.
I used the default '[)' (inclusive-exclusive) parameter for the int4range column. You may want to change that to '[]' (all-inclusive), depending on how you want to be saving ranges.
Examples:int4range(1,1) and int4range(1,1,'[)') is the empty range while int4range(1,1,'[]') is the [1,2) range.
If you want to enforce / avoid overlapping ranges, then you don't need a UNIQUE constraint at all but an EXCLUDE constraint:

CREATE TABLE session_groups (
    group_id      SERIAL  PRIMARY KEY,
    first_session INTEGER NOT NULL,
    last_session  INTEGER NOT NULL,
    description   TEXT,

    FOREIGN KEY (first_session)
                REFERENCES sessions (session_id)
                ON UPDATE CASCADE
                ON DELETE RESTRICT,
    FOREIGN KEY (last_session)
                REFERENCES sessions (session_id)
                ON UPDATE CASCADE
                ON DELETE RESTRICT,

    CONSTRAINT non_overlapping_sessions
        EXCLUDE  USING gist
        ( int4range(first_session, last_session, '[)') WITH && )
);

See also the documentation on constraints on range types. You probably need to add the extension as well:

CREATE EXTENSION btree_gist;


Inner Join until null on self-referencing tables

There is a chapter in SQL Server called: Recursive Queries Using Common Table Expressions. It is very well explained and has a very clear and simple example.

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms186243(v=sql.105)

Based on this documentation, my query solution is as follows (tested OK):

WITH CTE_RecursiveOU (ID, ParentID, DepCode,DivCode)
AS
(
-- Anchor member definition
    SELECT
        u.ID as ID,
        u.ParentOrganizationalUnit as ParentID,
        u.Department_Code as  DepCode,
        u.Division_Code as DivCode
    FROM dbo.OrganizationalUnit AS u
    WHERE u.ParentOrganizationalUnit IS NULL
    UNION ALL
-- Recursive member definition
    SELECT
        u.ID as ID,
        u.ParentOrganizationalUnit as ParentID,
        r.DepCode as  DepCode,
        ISNULL(r.DivCode,u.Division_Code) as DivCode
    FROM dbo.OrganizationalUnit AS u
    INNER JOIN CTE_RecursiveOU AS r
        ON u.ParentOrganizationalUnit = r.ID
)
-- Statement that executes the CTE
SELECT
    p.ID as PersonID,
    c.ID as OrganizationalUnitID,
    c.DepCode as Department_Code,
    c.DivCode as Division_Code,
    p.FirstName,
    p.LastName
FROM CTE_RecursiveOU AS c
INNER JOIN dbo.Person AS p
    ON p.OrganizationalUnitID = c.ID;

GO


When was a table trigger disabled and re-enabled?

SQL Server doesn't track this information (I half expected to see it in the default trace, but it's not there either). Enable/disable will update modify_date, but you won't be able to distinguish that from a rename or code modification. Also, it will only show you when the last change took place. If you want any other tracking (such as who did it), you'll need to implement auditing or trace. I thought perhaps a DDL trigger could be used as well, but this seems like an intentional functionality gap:

http://connect.microsoft.com/SQLServer/feedback/details/509242/fire-a-ddl-trigger-when-the-new-syntax-disable-trigger-is-executed (abandoned)
https://connect.microsoft.com/SQLServer/feedback/details/434951/ddl-trigger-still-not-working-disable-trigger-is-this-not-an-event (abandoned)
https://feedback.azure.com/forums/908035-sql-server/suggestions/32898904-fire-a-ddl-trigger-when-the-newer-syntax-disable

Here is how you can capture this information going forward using an audit:
USE master;
GO

CREATE SERVER AUDIT MyAudit
  TO FILE (FILEPATH = 'C:\temp\', MAXSIZE = 1 GB)
  WITH (ON_FAILURE = CONTINUE);
GO

ALTER SERVER AUDIT MyAudit WITH (STATE = ON);
GO

USE [your_database];
GO

CREATE DATABASE AUDIT SPECIFICATION schema_db_change
  FOR SERVER AUDIT MyAudit
  ADD (SCHEMA_OBJECT_CHANGE_GROUP),
  ADD (DATABASE_OBJECT_CHANGE_GROUP);

ALTER DATABASE AUDIT SPECIFICATION schema_db_change WITH (STATE = ON);
GO

Now go and disable your trigger, and then run:
SELECT event_time, succeeded, server_principal_name, [object_name], [statement]
FROM sys.fn_get_audit_file ('C:\Temp\MyAudit*', NULL, NULL);

If you can't use audit due to edition or other issues, you could do this relatively inexpensively using a server-side trace. Just capture SQL:BatchCompleted and optionally filter on TextData LIKE '%disable%trigger%' (You'll have to test if it's better in your scenario to pay the cost for the filter to avoid collecting too much, or just collect more and whittle it down later. Filters can be quite expensive but it depends on the system.)
I'm sure there is also a way to do this with extended events. But XEvents and audit both require 2008+ and you didn't specify version...
Better yet, remove the ability to modify triggers for users who bypass change management. Ideally, you should be able to determine who enabled or disabled a trigger without ever looking at the database, because nobody should be doing that without documenting it.