Best practice for entry position indicators

No matter what solution you choose, you're going to have to "refresh your product cache" anyway, since cached information won't reflect your new product either.  You should let the DBMS do its job and just query against the base table.

In terms of managing arbitrary (manually imposed) sequences, one solution to consider which allows for easy inserts with only periodic recalculation of the whole sequence is to use double instead of int for your sequence number.

When you insert a record between two existing records, set the sequence number of the new record to half way between the existing records.  This can go on for a very long time before you have to break down and recalculate the whole sequence.

You could have a nightly/weekly/monthly process that recalculates all of your sequence numbers so that they use natural numbers again.


Merge replication supports bidirectional subscriptions, with changes propagated from the subscribers to the publisher.

Other types of replication support this scenario: Transational Replication with updateable subscriptions (deprecated) and Peer-To-Peer Transactional Replication (more complicated to maintain, requires Enterprise Edition).

Keep in mind that Merge Replication keeps track of changes using triggers on each published table (article), which can determine substantial overhead to all write operations against those tables. Merge Replication also requires a unique uniqueidentifier column on each article: if you don't have one, it wil add it for you (more space needes, heavy fragmentation after adding the column).

Make sure you test performance before going down this road.


SQL Server Activity Monitor - Resource Waits - Logging > 1400 ms/sec, how?

I would check disk health / performance for volumes holding your transaction logs. You are seeing a delay due to the durability requirement, logging is backed up.

Check for I/O delays on tlog files in activity monitor as well.


How to Split 24 hours into hour slab in Sql Server

So if I'm right you always want to return 24 rows, one for each hour slot, regardless of data existing for that slot?  I assume you only have one row per hour slot in your data?

Based on this answer here, create a table with a row for each hour slot then outer join it to your results table, which will always give you a time row regardless of rows existing in your table

WITH Hours AS
(
  SELECT 0 as hour
  UNION ALL
  SELECT  hour + 1
  FROM    Hours
  WHERE   hour + 1 < 24
)
SELECT  CONVERT(varchar, h.hour) + ' - ' + CASE WHEN h.hour + 1 = 24 THEN 'Midnight' ELSE CONVERT(varchar, h.hour + 1) END + CASE WHEN h.Hour >=12 THEN ' PM' ELSE ' AM' END, y.*
FROM    Hours h LEFT JOIN yourtable y ON DATEPART(HH, y.START_TIME) BETWEEN h.hour AND h.hour + 1

You will need to add a clause to the join to restrict yourtable to one days data

Note: you could replace the table in the WITH statement with a real table containing 24 rows, one for each hour if you want.


Is there any performance benefit to minifying MySQL queries?

I'm going to post an answer just to put an end to this bizarre debate.

TLDR: No, "minification" will not provide any performance benefit that you would notice.

"Minification" refers to a process of removing any optional whitespace from a program source code and giving program identifiers (variables, functions, etc.) shortest possible names, all while retaining the original semantics of the program. The original purpose of it was to reduce the download time of JavaScript code embedded in (or linked from) HTML pages, with the aim of improving user experience on many web sites. As a side effect, minified JavaScript code is practically unreadable by humans, which brought about the secondary usage of the practice, that of obfuscating the source code, often for malicious purposes.

SQL statement syntax differs from other programming languages in that it rarely has any variables1; it contains mostly keywords and SQL object identifiers, which obviously cannot be replaced by arbitrary shorter tokens without stripping a statement entirely of its meaning. That leaves only two minification targets: optional whitespace and, possibly, correlation names when such are used, which makes minification "improvement", in terms of the overall statement length, significantly less impressive.

Further, unlike transferring sources of HTML pages over long range internet links from web servers to residential clients, the part of the overall SQL statement processing cycle that can be theoretically improved by reducing the length of the statement string is negligible, compared to other steps, such as optimizing the statement, executing it, and sending statement results back to the client, if applicable. Besides, SQL statements normally travel over backbone networks, which are much faster and experience much less contention than public internet.

Finally, if you decide to apply minification to dynamically composed SQL statements, you might even see overall performance degradation, because a poorly written minification code2 will probably have overhead that  outweighs whatever "benefits" it produces.

As a result, you3 are unlikely to perceive any difference in effective statement processing time (from submission to receiving first results) between minified and non-minified statements.

1 - PSM and compound SQL statement code can, of course, contain variable names, but in the cases when such code is used, potential network throughput improvements from minification are even less significant.
2 - I would find it doubtful that people engaging in such practice can produce high quality code.
3 - Yes, even if you are developing a High Frequency Trading application.


Properly delete data forever and free up the space it was using?

The first step is worthless because delete operations are completely logged.
Use truncate instead if you want to empty a table.
About the task 4 you have to reorganize pages because the release pages free only pages ad the end of the mdf file. It will takes a lot.
I suggest you add task 5: rebuild the indexes because shrinking the data file causes the indexes to fragment.


Method 1,

DECLARE @TopSize INT = 10000
DECLARE @BatchSize INT = 10000
DECLARE @MaxLimit INT = 1
DECLARE @RowCount INT = 0

BEGIN TRY
    WHILE (@TopSize <= @MaxLimit)
    BEGIN
        DELETE TOP ((@TopSize) Audit
    OUTPUT deleted.*
    INTO AuditArchive
    WHERE EVENTYEAR = '2016';

        SET @RowCount = @@RowCount

        --PRINT @TopSize
        IF (
                @RowCount = 0
                OR @RowCount IS NULL
                )
            BREAK;
        ELSE
            SET @TopSize = @TopSize + @BatchSize
    END
END TRY

BEGIN CATCH
    --catch error
END CATCH

where EVENTYEAR  is index column


How to import multiple CSV files to a postgres table using pgadmin or other method?

Use your computer's command-line environment to list the names of all the files in your directory

On windows, use cmd + dir or PowerShell+gci
On macOS, probably terminal+ls
This might also be a good opportunity to familiarise yourself with psql and the meta-commands which you could use for this purpose

Use the list of files to create the commands you want

Some people use excel for this
I prefer multi-line editing in something like VSCode

Manually tweak your commands line-by-line or change the formula/process you used in step 2 until you get the output you want.

Gl;hf ;)


Restore database from tape error 15105, cannot open backup device

You need to use the HP Data Protector console software to perform the restore operation.


How do cardinality estimates affect CPU and Reads in SQL Server?

First of all, it would be really great if you could provide an actual execution plan for the query you're working on tuning.  Otherwise, all of the answers you will get will be somewhat general, and may not perfectly apply to your situation.

  The question is why, in a production environment with heavy executions, would cardinality estimates affect CPU and reads so much?

The effect is indirect.  In general, better cardinality estimates will allow the query optimizer to make better choices about the execution plan - which can result in less reads and CPU.

Consider this simple query:

SELECT *
FROM dbo.TableA a
ORDER BY a.[high];

The results should be ordered by high, and there is a nonclustered index on the high column.  Since SQL Server only expects to see 50 rows come out of the index scan, it decides to do read the data from the pre-sorted index, and do 50 key lookups to get the rest of the columns (to avoid sorting the data).

If SQL Server instead expects 10,050 rows to come out of the index scan, we get this plan instead:

It scans the clustered index, which is sorted by the number column.  It then has to sort that data by high in order to satisfy the ORDER BY part of the query.  This query has the following CPU and IO characteristics:

CPU time = 0 ms,  elapsed time = 326 ms.
Table 'TableA'. Scan count 1, logical reads 95

If I force the key lookup plan when there are 10,000 rows (in order to simulate a "bad cardinality estimate"), the query has these characteristics:

CPU time = 15 ms,  elapsed time = 305 ms.
Table 'TableA'. Scan count 1, logical reads 20768

As you can see, the bad cardinality estimate caused I/O and CPU to increase.

This is a really simplified example, but hopefully it helps to illustrate the general point.

  The average duration of each run was much better but 'only' 34% faster. I say 'only' because the CPU was 98% less usage and the reads were 95.5% fewer

If reads and CPU both dropped, but duration didn't drop as much as you expected, the cause is probably related to concurrency in the live environment.

David Browne makes this point well:

  Often this indicates a reduction in the degree of parallelism used in the query. A parallel scan across multiple cores generates a lot more CPU and Reads per second than a single-threaded plan. This is one of the problems with focusing on duration for query tuning.

If this is a parallel query, you may be getting a lower degree of parallelism (DOP) in the live environment than in your test.  Check the execution plan XML for the DegreeOfParallelism property (e.g., <QueryPlan DegreeOfParallelism="8") to see if it's different between the two environments.

The stored procedure you've tuned might be blocked waiting on other queries to release locks.  If you are able to measure wait statistics, this problem would show up as LCK_* waits.  This is a typical cause of "low CPU, high duration" problems.

Or maybe it takes a while for the stored procedure to even begin running in some cases.  If other queries are running that need large memory grants, you could be encountering RESOURCE_SEMAPHORE waits.  If there are lots of parallel queries running at once, especially if they are running for a long time and waiting on locks, you might be encountering THREADPOOL waits.

Here are some resources that might help track down what's going on in production for this situation:

Use the SQL Server First Responder Kit

run sp_Blitz to get a general sense of what's going on on the server, and see if there are any of those really bad waits
run sp_BlitzFirst during representative times of the day (when issues are occurring) to get info on waits, what queries are running, etc

If you want to reproduce the query results I showed in order to play around with this yourself, here are the definitions:

CREATE TABLE [dbo].[TableA]
(
    [name] [nvarchar](35) NULL,
    [number] [int] NOT NULL,
    [type] [nchar](3) NOT NULL,
    [low] [int] NULL,
    [high] [int] NULL,
    [status] [int] NULL
);
CREATE CLUSTERED INDEX CX_number
ON dbo.TableA ([number]);

CREATE NONCLUSTERED INDEX IX_high
ON dbo.TableA ([high]);

INSERT INTO dbo.TableA
SELECT TOP (10000) v1.*
FROM master.dbo.spt_values v1
CROSS JOIN master.dbo.spt_values v2;

SET STATISTICS TIME, IO ON;

SELECT *
FROM dbo.TableA a
ORDER BY a.[high];

SELECT *
FROM dbo.TableA a WITH (INDEX (IX_high)) -- force the key lookup plan
ORDER BY a.[high];


Always On Instance Service Broker/Database Mirroring transport connection endpoint Error

Do you have in place read-only routing?
Try to review read-only routing URL in the Availability Group Properties / Read-Only Routing.
Maybe the connection at that time is using this feature and the routing URL for the secondary is not right.
Eventually try to verify what encryption algorythm you are using.
SELECT name ,
type_desc ,
state_desc ,
role_desc ,
is_encryption_enabled ,
connection_auth_desc ,
encryption_algorithm_desc
FROM sys.database_mirroring_endpoints;

Usually AES. Veryfy if you are using the same algorythm in both node.


Customer just bought an Oracle license - am I allowed to install Oracle 11.2.0.4 without support contract?

You can install the latest version available at the time of purchase, but no newer versions after that.

The "funny" part is, these versions (like 11.2.0.4) can be obtained from https://support.oracle.com/, but you can not download them, because you do not have a support contract. In this case, when you request the installation media, an Oracle representative goes on-site with the requested software (just an example I know of, there may be other ways).

This part took quite long the last time I was in such a situation with a customer.

Without a support contract, you can not download patches, and you can not open service requests. When you hit a bug, you have to live with it or apply an acceptable workaround.


file not found - how to remove its configuration?

Shut the database down. Start it up again in mount mode:

connect / as sysdba;
startup mount;

Drop the datafile:

ALTER DATABASE DATAFILE '/full/path/of/file.dbf' OFFLINE DROP;

Open the DB:

alter database open;

Drop the tablespace:

DROP TABLESPACE <TS Name> INCLUDING CONTENTS;


What is the difference between ASYNC_IO_COMPLETION and IO_COMPLETION wait types?

I think you should first understand the difference between Sync IO and Async IO. The information about the basic nature of both the I/O's can be found in Bob Dorr's I/O presentation blog see the section Async vs Sync IO.
In very simple meaning Async IO is one in which after putting I/O request the program or calling code will not wait for the I/O operation to complete but will get busy with other task, later it can come and check and see if the I/O request has been completed or not. Similarly for Sync IO the calling code will wait for acknowledgement that I/O request is done. Quoting from the Bob Dorr's blog

In Windows the I/O APIs allow sync and async requests. Sync requests are calls to the API such as WriteFile that will not return control to the calling code until the operation is complete. Async hands the request off to the operating system and associated drivers and returns control to the calling code. The calling code is free to execute other logic and later come back to see if/when the I/O completes.
SQL Server uses mostly Async I/O patterns. This allows SQL Server to
write or read a page and then continue to use the CPU and other
resources effectively. Take the example of a large sort operation. SQL
Server can use its read ahead logic to post (async request) many pages
and then start processing the first page returned by the request. This
allows SQL Server to use the CPU resources to sort the rows on the
page while the I/O subsystem is fetching (reading) in other pages at
the same time. Maximizing the I/O bandwidth and using other resources
such as CPU more effectively.

Now the wait time here represents time spent in waiting Async/Sync IO request to complete.


Grouping Data Using Joined Tables with Certain Distinct Values

Remove the DISTINCT.  But... Then you will probably find the results are too big.  Here's why:

Think of gathering all the data for all the Orders + OrderLines -- one row for each combination.  Notice how that will include o.grossValue is multiple times.

What you need to do is to the o. summations before JOINing to ol.

But now I am wondering about something.  Your schema allows for one order to be fulfilled from multiple stores.  However, since your data does not have an example of such, I wonder which is true:

An order can come from only one store.  In this case moving the storeID to orders would be 'right' and make the query much simpler.
An order can come from multiple stores.  In this case, please augment the data set so that a proposed solution can be checked.

I don't think you need 'LEFT'.


Current state of INTEGER vs NUMBER in Oracle

The BLOG

The logic behind that post is so ludicrous that it deserves a sarcastic answer.

  Oracle does the "is it an integer?" check while it is waiting for the
  I/O call to the disk to return the data it needs to validate the
  Primary Key constraint.

  I believe this Performance Enhancements was implemented back in Oracle Version 2.

In actuality, the number of clock-cycles needed to perform such check is so insignificant that it is easily hidden within the noise of normal operations of the computer. You probably couldn't measure the performance difference even on a VAX/VMS.  (much older than "10 years")

INTEGER vs NUMBER vs PLS_INTEGER

As you have seen INTEGER is NUMBER(38,0).  There should be no measurable performance difference.

A difference can come in when you are talking about PLS_INTEGER vs NUMBER.  Most of the benchmarks I've seen that prove this to be true are computationally intensive.  Since you aren't doing Differential Equations with this value, the "performance gain" wouldn't apply to your case.

I recommend you stick with INTEGER or NUMBER(38,0)

Data Model Suggestion

The number and the flag should be in two different columns.  Feel free to use a virtual column (3rd column) to get back the original string.  I recommend that you record the original string value in a "comments/notes" like field so that the humans can search for it in case your virtual column logic does match what the human actually typed in.


What's the exact meaning of `innodb_max_dirty_pages_pct`, and how does it relate to the redo log?

You say:

logs are flushed one per second, so how can 90% of the buffer pool be changed in one second? [...] such high number of dirty pages don't fit in the redo log

You seem to think that the log buffer and the buffer pool mean the same thing; that is not true.
There is no relation between flushing dirty pages from the buffer pool and the redo log. Log buffer and the buffer pool serve different purposes and are managed separately.
As the manual points out:

The buffer pool is an area in main memory where InnoDB caches table and index data

and

The log buffer is the memory area that holds data to be written to the log files on disk

(Emphasis mine.)
By the time a dirty data or index page is flushed, the corresponding log records will have been written out already -- this is the nature of write-ahead logging. Flushing dirty pages from the buffer pool means these pages are written to the appropriate ibdata* and/or .ibd files.
EDIT By RolandoMySQLDBA
Adding Visual Representation of InnoDB from Percona CTO Vadim Tkachenko


How do I find a long running query with process ID, process name , login time, user , start time and duration?

As commented, best tool around is sp_whoIsActive by Adam Machanic. It can be used in several ways, to see what is running at the moment you launch the script or you can run it in loops to monitor some specific action, as slow queries for example.
To run in a loop take a look here: How to Log Activity Using sp_whoisactive in a Loop
To detect slow queries: How to Use sp_WhoIsActive to Find Slow SQL Server Queries
You can directly use DMV's to get your slowest queries and act from there. Check Glenn Berry's diagnostic queries.
And finally you can use this query to find most time consuming queries. You can play around with the dm_exec_query_stats to add more data or join with other ones to get more information. Be aware that dmv's gets flushed away and refreshed each time the server is restarted.
SELECT  creation_time
        ,last_execution_time
        ,total_physical_reads
        ,total_logical_reads
        ,total_logical_writes
        , execution_count
        , total_worker_time
        , total_elapsed_time
        , total_elapsed_time / execution_count avg_elapsed_time
        ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
         ((CASE statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
          ELSE qs.statement_end_offset END
            - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_elapsed_time / execution_count DESC;

EDIT
A new option is available from some time already, First Reponder Kit. Is a set of scripts, freely provided under MIT license by BrentOzar team, that will help on various tasks, including the one asked by the OP. Mainly sp_BlitzFirst and sp_BlitzWho scripts for this case.


Are they normalised?

No.

Student has Mob1 and Mob2 fields.


Debugging why postgres is snowballing with system cpu usage

It has been about 24 hours now since I have implemented the change that fixed this, so I think it is safe to call. I will update this however if this kind of spike happens again.

In the system logs we were getting this message and I had my data center replace the RAM in the machine.

Mar  3 18:40:24 hubble kernel: sbridge: HANDLING MCE MEMORY ERROR
Mar  3 18:40:24 hubble kernel: CPU 6: Machine Check Exception: 0 Bank 5: 8c00004000010092
Mar  3 18:40:24 hubble kernel: TSC 0 ADDR 1087cc7780 MISC 20400e0e86 PROCESSOR 0:206d7 TIME 1393900824 SOCKET 1 APIC 20
Mar  3 18:40:24 hubble kernel: sbridge: HANDLING MCE MEMORY ERROR
Mar  3 18:40:24 hubble kernel: CPU 6: Machine Check Exception: 0 Bank 5: 8c00004000010092
Mar  3 18:40:24 hubble kernel: TSC 0 ADDR 1087cc7780 MISC 20400e0e86 PROCESSOR 0:206d7 TIME 1393900824 SOCKET 1 APIC 20
Mar  3 18:40:24 hubble kernel: EDAC MC1: CE row 9, channel 0, label "CPU_SrcID#1_Channel#3_DIMM#0": 1 Unknown error(s): memory read on FATAL area : cpu=6 Err=0001:0092 (ch=2), addr = 0x1087cc7780 => socket=1, Channel=3(mask=8), rank=0

After the RAM replacement, the issue has not occurred since.

Also the IOwait in the CPU graph has gone a way since the ram change. Previously it was my assumption that his was just the profile of the machine since I had more than a year of uptime with this IOwait being detected my CPU profiling.


Replace function removing Ones / 1 when removing char(185) (superscript 1)

CHAR(185), being VARCHAR data and using an 8-bit code page, can be a different character depending on the code page being used (which is determined by the collation of the current database when using the CHAR() function). In many code pages, including the common Windows-1252 (Latin1_General), the character is a superscript 1 (i.e. "1" ). This character, depending on the collation / code page being used, might have a "best fit" mapping to a regular "1".

Also, when using a Windows collation, case-sensitivity can affect the outcome of matches between subscript, superscript, and regular versions of the same characters (seriously! see example below). This also covers NVARCHAR / Unicode data (including UTF-8 in VARCHAR starting in SQL Server 2019).

When needing to match on specific characters you need to use a binary collation (i.e. one ending in _BIN2 unless on SQL Server 2005 or older, in which case only the _BIN collations are available).

SELECT CHAR(185)
WHERE  CHAR(185) = '1' COLLATE SQL_Latin1_General_CP1_CI_AS
-- no rows (SQL Server collation, code page 1252, case insensitive)

SELECT CHAR(185)
WHERE  CHAR(185) = '1' COLLATE SQL_Latin1_General_CP437_CI_AS
-- 1 row (SQL Server collation, code page 437, case insensitive)

SELECT CHAR(185)
WHERE  CHAR(185) = '1' COLLATE SQL_Latin1_General_CP437_CS_AS
-- 1 row (SQL Server collation, code page 437, case sensitive)

SELECT CHAR(185)
WHERE  CHAR(185) = '1' COLLATE Latin1_General_100_CI_AS
-- 1 row (Windows collation, code page 1252, case insensitive)

SELECT CHAR(185)
WHERE  CHAR(185) = '1' COLLATE Latin1_General_100_CS_AS
-- no rows (Windows collation, code page 1252, case sensitive)

SELECT CHAR(185)
WHERE  CHAR(185) = '1' COLLATE Latin1_General_100_BIN2;
-- no rows (Windows collation, code page 1252, _BIN2 collation; always works!)

As noted above, Unicode data (regardless of type of collation) works the same as VARCHAR data used with a Windows collation (because both scenarios use the same Unicode rules):

SELECT NCHAR(185)
WHERE  NCHAR(185) = N'1' COLLATE Latin1_General_100_CI_AS
-- 1 row (Windows collation, Unicode data, case insensitive)

SELECT NCHAR(185)
WHERE  NCHAR(185) = N'1' COLLATE Latin1_General_100_CS_AS
-- no rows (Windows collation, Unicode data, case sensitive)


Redgate SQL Compare vs Visual Studio 2010 Premium/Ultimate database project

I tried to use the VS tool yesterday with my production SQL 2000 instance, comparing to my dev 2008 instance, and it refused to work with anything prior to SQL 2005.  Red Gate definitely does not have such a restriction.  It even works reasonably well (not 100%) with another database we have that runs in 6.5 Compatibility Mode.


I found this tool which is doing exactly what I asked for
https://github.com/rvflash/publicsuffix-sql
It uses publicsuffix.org which maintains a list of public suffixes such as .com, .co.uk and pvt.k12.ma.us.
Using the function provided by the tool, we can simply do something like this:
select DOMAIN('115.20.106.216'); -- null
select DOMAIN('cpe-45-37-121-56.nc.res.rr.com'); -- rr.com
select DOMAIN('CPE00fc8d22x-CM023d22f4f0.cpe.net.cable.rogers.com'); -- rogers.com
select DOMAIN('c-208-168-15-86.wifi.ties.k12.mn.us'); -- k12.mn.us
select DOMAIN('CPE-128-122-18-181.hdbq1.win.bigpond.net.au'); -- bigpond.net.au


Output count percentage in 2 decimal places

Are you looking for something like this..

SELECT
    Deal_type,
    Price_type,
    count(isnull(Deal_type, 'x')) * 100 / (
        SELECT count(1)
        FROM users
        WHERE Deal_type in ('RMBS','abs', 'cmbs', 'clo', 'cdo')
    ) as results
FROM [dbo].[all]
WHERE Deal_type in ('RMBS','abs', 'cmbs', 'clo', 'cdo')
GROUP BY Deal_type,Price_type
ORDER BY Deal_type


You're looking for UNION and UNION ALL.

select my_column from table1
union all
select my_column from table2
union all
select my_column from ...

But needing to select one column from every table is likely the sign of a significant design error.


You raise a good and important point. I think it is crucial to understand what happens when data is deleted. I've seen many applications, where this was not thought about until the storage was full or slow or the data protection people came round and complained.

If you DELETE rows in Oracle, the space is marked as free, but left as "holes" in the storage. If you INSERT again afterwards, these holes might be filled if the new data fits in.

Some but not by far not all queries are sped up by indexes. Those that are not faster need then to read all data, including holes (so called full table scan). Reading an 8K block from disk that is full of data takes the same amount of data than reading an completely empty block which contains only "holes". Your time measurement points to this scenario.

Regarding statistics: Oracle updates the optimizer statistics at the nightly maintenance window (usually 10pm/22:00). Personally, I'd certainly gather stats after deleting a third of a large table, especially if this is a regular job, for instance in a procedure.

Regarding row movement: I leave it always on for most tables, but I'd judge it as not so important. It hurts only code that stores rowids in tables or longer time in memory and we don't do that. It is necessary if you do a ALTER TABLE xxx MOVE.

Regarding shrink: I wouldn't be bothered to shrink or reorganize for 300k rows. For 10GB+, it may be worth the effort.


Is it possible to define a function to prevent repeating lots of `case when` in transact sql?

Use APPLY, like the examples I show in: http://blogs.lobsterpot.com.au/2011/12/13/a-t-sql-tip-working-calculations/

...then you can use it everywhere else as a regular column - just a calculated one.

Select n.Num, left(title,25) --, etc
From posts
Cross apply (select case
  when lower(title) like '%open source%' then 0
  when lower(title) like '%recommend%'   then 1
  when lower(title) like '%free%'        then 2
  when lower(title) like '%good%'        then 2
  when lower(title) like '%best%'        then 2
  when lower(title) like '%better%'      then 2
  when lower(title) like '%any%'         then 2
  when lower(title) like '%what%should%' then 3
  when lower(title) like '%which%should%'then 3
  when lower(title) like '%is there%'    then 3
  when lower(title) like '%looking for%' then 4
  else 5 end as Num) n
-- where ...
Order by n.num


MongoDB 3.0 : Configure storage engine at collection or database level

You can only use a single storage engine type per mongod instance. There is only a single dbPath setting for a mongod deployment, and you cannot mix & match data files from different storage engines within the same data path.

However, you can use different storage engine configurations within members of a replica set (for example, mmap primary and WiredTiger secondaries). If you do this, you should be wary of differences in performance and be sure to test thoroughly in a QA/staging environment with representative workload.

Another available option is to set storage-engine specific options when creating a collection. For example, you could override the instance-level defaults for WiredTiger and set different options for specific collections (i.e. higher level of compression, or perhaps no compression).


Compare strings from two tables SQL Server 2014

Wildcard matching strings with good performance will always be tricky, but here's a best-effort attempt.

First off, your indexing can be improved. The index on #PARAMETERS only includes the actual ParameterName column, which is fine if you need a list of parameters, but whenever you need any other column, the index will be useless to SQL Server, and it'll revert to scanning the entire table instead. I would propose fixing this by changing the index to a clustered index:

CREATE UNIQUE CLUSTERED INDEX [IX_PARAMETERS]
    ON #PARAMETERS (ParameterName, [Datetime])
    WITH (DROP_EXISTING=ON);     -- Remove this row if the index doesn't already exist.

Clustered indexes include every column in the table, which automatically makes a clustered index a covering indexes.

From here, I've updated your query to do three things:

Collect each unique parameter in #PARAMETERS.
For each unique parameter, find matching formulas in #FORMULAS. This is the expensive part, and no index will really improve this search because the search condition (LIKE) is not sargable. This means that, index or not, SQL Server will go through each and every row in the table (a so-called scan), for every parameter.
Finally, join #PARAMETERS to get all the dates and values. This is a relatively cheap and efficient operation. This is where the modified index on #PARAMETERS helps.

Here's the resulting query:

SELECT  A.[Datetime],
        p.[ParameterName],
        B.[Description],
        A.[Value]
FROM (SELECT DISTINCT ParameterName FROM #PARAMETERS) p
CROSS APPLY (
    SELECT *
    FROM #FORMULAS B WITH (FORCESCAN)
    WHERE B.DataSourceAddress LIKE N'%'+p.ParameterName+N'%') AS B
INNER JOIN #PARAMETERS AS A ON
    p.ParameterName=A.ParameterName;

I added WITH (FORCESCAN) to eliminate key lookups. You can try the query with or without this hint.

If I had a lot more time to work on this and it was a super-critical query, I would probably (a) remodel the tables and/or (b) consider storing the data in #FORMULAS as XML data and add XML indexes, which would eliminate the LIKE match.

Footnote: Text matching like you do here (LIKE and CHARINDEX are pretty much the same here) will include formulas with the parameters "parameter10" and "parameter11" even when you're just searching for "parameter1".


How do I access ETW Traces on SQL Server for linux?

Unfortunately, ETW (Event Tracing for Windows) is not available on Linux.  It is a Windows-only function.  So for the time being you are stuck with the SQL and filesystem options for view the EE data.

The .NET Core team chose LTTng for trace support when .NET apps are running on Linux.  You could explore that as an option for correlating OS events with those recorded by SQL Server.

After your update (super interesting that ETW events might be firing in Drawbridge), I see that the .etl file is getting created on a file path that probably doesn't exist on your Linux-based Docker container.  You should try setting a known-good file path when you create the XE session:

ADD TARGET package0.etw_classic_sync_target
    (SET default_etw_session_logfile_path = N'/temp/sqletw.etl' )


Automatic deduplication (normalization) of strings in MySQL

MySQL has no such feature.

It is left to the user to "normalize" the data either for avoiding having to update multiple spots and/or for saving space.

In your example, it is generally not practical to do such with first/last name.  But it may be advisable for "locations".

It is not practical to dedup names (for example) because the payoff is poor. With "Mary" sitting somewhere else, the code has to implicitly (as you hypothecate) or explicitly (via a JOIN) reach for the string. In large datasets, this is likely to cause an extra disk hit -- costly. Also, "Mary" is 4 characters; the first_name_id might be a MEDIUMINT UNSIGNED, which is 3 bytes -- not much savings. For bigger strings that repeat a lot (company names), the tradeoffs might be better.

The main purpose of "normalization" is to put things in a single location.  A company name should be spelled out only once in a system that talks about companies.  In its place an id would be used -- possibly an integer, possibly a ticker symbol (as in a Stock database).  When the company changes its name, only one spot needs to be changed.  (If the ticker changes, as happened with AOL, then the code is messy.)


Why is the query_hash zero for EXEC statements?

To explain why the hash is created:

When we submit a query to the server, the algebrizer (yes that's what it is called) process creates a hash, like a coded signature, of the query. The hash is a unique identifier. An identifier is unique for any given query, including all the text that defines the query, including spaces and carriage returns, the optimizer compares the hash to queries in the cache. If a query exists in the cache that matches the query coming into the engine, the entire cost of the optimization process is skipped and the execution plan in the plan cache is reused.

EXEC starts a stored procedure which can have its code change, since SQL Server knows that it does not need to compare EXEC to optimize it, SQL Server does not create a hash.


Install SQL Server 2008 Express on same machine as normal SQL Server 2008 install?

As Shark already pointed out install or downgrade are distinct actions:

install side by side means to have a new session of Express edition installed WHILE the current running edition is working (btw, what is the version of the current instance?);
DOWNGRADE/UPGRADE means running the installation option over the currently running instance;

Any operation involving the live instance means it will interact and stop with the current instance. Some planning questions are in order now:

What are the dimensions of the database(s) that are in work? (Express edition can handle small dbs only);
What hardware necessities do you have for your current load? (Express edition has some strict limitations in CPU, RAM, hdd space);
What features do you need from the current set? (eg: you won't have SQL Agent jobs with Express Edition);

At first you need to answer all these questions and only then see what your options is.

EDIT: after all your details specified, I'd suggest you to install a new version along with the current working one. Then stop the working instance service, ATTACH the database files to the Express Edition, then move on with the uninstall of the other instance. This will make the database unavailable only for the attach operation.


If you do not want to return both Yes/Nos and 1/0s, then exclude columns like

t1.'Domestic Violence' as DomesticViolence

from your query and have your CASE expression reference the source columns rather than their aliases:

case
  when (t1.'Domestic Violence' = 'Yes') then 1
  else 0
end as Domestic_Violence


MySQL+Web-App Performance Issues using different servers

The OS even shows that we have an IO WAIT problem.

We think we have found the issue - the LSI MegaRAID SAS 9260-8i (Supermicro
SMC2108) RAID adapter. Either we have it configured incorrectly or it's just a poor performer.

We stumbled onto this blog:
http://artipc10.vub.ac.be/wordpress/2011/09/12/megacli-useful-commands/

and disabled the Patrol Read function and it increased our TPS by almost 130/second.

Trying to solicit tweaking recommendations here: https://askubuntu.com/questions/304488/tweaking-recommendations-for-smc2108-lsi-megaraid-sas-9260-8i-raid-adapter

This link http://www.randombugs.com/linux/tuning-linux-system-database.html was
very very useful. We implemented these changes which resulted in significant TPS improvements.


New query slower even if it has less logical reads than the old one

The percentage costs on an execution plan are from the optimizer estimates, even when an actual execution plan produced. The actual execution plan does use the exact plan and include both the estimated rows and the actual row counts. Discrepancies between the row counts can be useful to determine how accurate the estimate was.

Somewhere between the subquery and comparing it to the column in derived, the optimizer wasn't able to correctly estimate how many rows would match. It guessed that there would be 18 rows from derived when there were actually over 220,000. An additional clue is the warning message Cardnality Estimate: CONVERT(nvarchar(35),[mssqlsystemresource].[sys].[spt_values].[name],0) on the SELECT node.

If you were to check the query run length with something else, such as STATISTICS TIME, I would expect them to be much closer, and likely the second query running faster.

Here's another plan analysis with a somewhat similar situation. (SQL Server Plan Explorer) (with hat tip to Kendra Little on how to fool the optimizer)

The estimate shows a 93%/7% cost split, but by looking at the actual CPU, time, or IO, the difference is not that extreme. IO is about 75%/25% and CPU is roughly 60%/40%. (I tried to come up with something more even, but wasn't able to.)


What DML commands can be allowed / limited on a reporting server?

As far as I know, replicated database clusters are complete replicants of the original cluster (e.g. does not support replicating only a certain subset of databases/schemas etc ) and may only host read only transactions.


What is the relevance of 'SET default_with_oids = true' in a Postgresql dump?

The documentation states the following:

  default_with_oids (boolean)

  This controls whether CREATE TABLE and CREATE TABLE AS include an OID column in newly-created tables, if neither WITH OIDS nor WITHOUT
  OIDS is specified. It also determines whether OIDs will be included in
  tables created by SELECT INTO. The parameter is off by default; in
  PostgreSQL 8.0 and earlier, it was on by default.

  The use of OIDs in user tables is considered deprecated, so most installations should leave this variable disabled. Applications that
  require OIDs for a particular table should specify WITH OIDS when
  creating the table. This variable can be enabled for compatibility
  with old applications that do not follow this behavior.

Note that the feature was deprecated with the coming of 8.0.

This means that if you plan to use the DB as it is, you can safely remove this line before restoring (you have to be really sure that they are not referred to).  It will be left set to the default false.

If you have a legacy application working on this DB, you will want to check it, too, if it uses the OIDs in any way.  (At the moment I cannot really think of such a use case, but it may be the weakness of my imagination.)

A side note: you may experience some other issues restoring the dump - I remember some problems with changed implicit cast behaviour.


Will cleaning data cache help to resolve compiler memory pressure?

Take a look at this reference on MSDN regarding sys.dm_os_wait_stats.  Here is a quote from the above reference regarding RESOURCE_SEMAPHORE_QUERY_COMPILE:

  Occurs when the number of concurrent query compilations reaches a throttling limit. High waits and wait times may indicate excessive compilations, recompiles, or uncachable plans.

This wait type, as quoted above, is from too many compilations happening at a time.  What it sounds like, if you are consistently seeing this wait type (unlike going from a cold plan cache) then you have too high of an ad hoc workload.  In other words, you may be experiencing the lack of plan reuse (a bunch of plans that aren't parameterized/prepared, or not using stored procedures and therefore a new ad hoc query comes in since there is no plan that can be reused it'll compile a new one).

There are a few ways to remedy this, if that is indeed the case.  The real way to fix this is to ensure that prepared plans and stored procedures are being used.  A brute force method is by setting the database to utilize forced parameterization.  I would save the latter as a last resort, as there could be some downsides to forcing parameterization.

There are a few ways to keep an eye on how your plan reuse is looking.  The easiest (in my opinion) would be to capture a few perfmon counters for the instance:

\SQLServer:SQL Statistics\Batch Requests/sec
\SQLServer:SQL Statistics\SQL Compilations/sec
\SQLServer:SQL Statistics\SQL Re-Compilations/sec

It's a pretty good rule of thumb that compilations per sec shouldn't exceed 10% of batch requests per second, and recompilations per second shouldn't exceed 10% of compilations per second.  I typically go from there when troubleshooting plan cache reuse.  Feel free to monitor these counters, and post the results in your question and I can help interpret.

And to answer your direct questions, no reducing index sizes or clearing the data cache will not alleviate your issues.


SQL Server - Move data from secondary ndf file to primary mdf file

If the datafile are in different filegroup the approach number 2 won't work, it will only work on the same filegroup.

For the Approach 1, please check: https://stackoverflow.com/questions/2438019/how-i-can-move-table-to-another-filegroup/31351806

I wouldn't recommend to move data back to primary filegroup, this should be only used for system objects and should be kept small for in case where you need to do piece meal recovery.


Make a boolean false after every n number of minutes in MySQL

You should use the MySQL event scheduler to run a a query to update all rows where the age of the row is greater than your threshold and have it execute the update at a suitable interval.


Restart event scheduler and alter event when slave being promoted to master

As much I understand your question to Automatically restart event scheduler and alter event when slave being promoted to master. You have always have to run the script to manually fill the losing data for the time event is not running. AS much MySQL documents states my Answer is No

You have to follow these steps manually:

To check  event scheduler status on promoted master, use SHOW EVENTS, like this:

 SHOW EVENTS
 WHERE STATUS = 'SLAVESIDE_DISABLED';

Alternatively, you might wish to query the INFORMATION_SCHEMA.EVENTS table as shown here:

  SELECT EVENT_SCHEMA, EVENT_NAME, ORIGINATOR
  FROM INFORMATION_SCHEMA.EVENTS
  WHERE STATUS = 'SLAVESIDE_DISABLED';

When promoting a replication slave having such events to a replication master, use the following query to enable the events:

 UPDATE mysql.event
 SET STATUS = 'ENABLED'
 WHERE STATUS = 'SLAVESIDE_DISABLED';

Before executing either of the previous two UPDATE statements, you should disable the Event Scheduler on the slave

  using SET GLOBAL event_scheduler = OFF;

run the UPDATE, restart the server, then re-enable the Event Scheduler afterwards

  using SET GLOBAL event_scheduler = ON;

If you later demote the new master back to being a replication slave, you must disable manually all events
enabled by the UPDATE statement. You can do this by storing in a separate table the event names from the
SELECT statement shown previously, or using an UPDATE statement to rename the events with a common prefix
to identify them, as shown in this example:

 UPDATE mysql.event
 SET NAME = CONCAT('replicated_', NAME)
 WHERE STATUS = 'SLAVESIDE_DISABLED';

When demoting this server back to being a replication slave, you can then rename and disable the events like this:

 UPDATE mysql.event
 SET NAME = REPLACE(NAME, 'replicated_', ''),
 STATUS = 'SLAVESIDE_DISABLED'
 WHERE INSTR(NAME, 'replicated_') = 1;


What is the most efficient way to get the minimum of multiple columns on SQL Server 2005?

I tested the performance of all 3 methods, and here's what I found:

1 record: No noticeable difference
10 records: No noticeable difference
1,000 records: No noticeable difference
10,000 records: UNION subquery was a little slower. The CASE WHEN query is a little faster than the UNPIVOT one.
100,000 records: UNION subquery is significantly slower, but UNPIVOT query becomes a little faster than the CASE WHEN query
500,000 records: UNION subquery still significantly slower, but UNPIVOT becomes much faster than the CASE WHEN query

So the end results seems to be

With smaller record sets there doesn't seem to be enough of a difference to matter. Use whatever is easiest to read and maintain.
Once you start getting into larger record sets, the UNION ALL subquery begins to perform poorly compared to the other two methods.
The CASE statement performs the best up until a certain point (in my case, around 100k rows), and which point the UNPIVOT query becomes the best-performing query

The actual number at which one query becomes better than another will probably change as a result of your hardware, database schema, data, and current server load, so be sure to test with your own system if you're concerned about performance.

I also ran some tests using Mikael's answer; however, it was slower than all 3 of the other methods tried here for most recordset sizes. The only exception was it did better than a the UNION ALL query for very large recordset sizes. I like the fact it shows the column name in addition to the smallest value though.

I'm not a dba, so I may not have optimized my tests and missed something. I was testing with the actual live data, so that may have affected the results. I tried to account for that by running each query a few different times, but you never know. I would definitely be interested if someone wrote up a clean test of this and shared their results.


I'm not sure what are you trying to achieve with this script, but you should fetch next records inside WHILE block just to be sure that @@FETCH_STATUS=0

dbo.ALLAARTI_MIS_SUMMARY @IN_SYSDIFF = @IN_DIFF

DECLARE @IN_DIFF int
DECLARE vendor_cursor CURSOR FOR
SELECT date_time FROM dumy_mis
OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor
INTO @IN_DIFF

WHILE @@FETCH_STATUS = 0
BEGIN
    exec dbo.ALLAARTI_MIS_SUMMARY @IN_SYSDIFF = @IN_DIFF

    FETCH NEXT FROM vendor_cursor
    INTO @IN_DIFF
END
CLOSE vendor_cursor
DEALLOCATE vendor_cursor


Why did PostgreSQL choose an elephant as it's logo?

In the email thread postgreSQL and history of relational databases, Oleg Bartunov states that the logo idea probably originated from a PostgreSQL logo thread on the pgsql-hackers mailing list. Quote:

  [...]
  but if you want an animal-based logo, how about some sort of elephant?
  After all, as the Agatha Christie title read, elephants can remember ...

  David Yang


Oracle 11g, Will droping partition reuse free space on bigfile tablespace

It will be available. Depending on your space management settings it can nearly fit the new partition entirely in the space or there might be some gaps, but the majority of space will be reused.

I would not expect that the grow stops immediately after 30 days depending on the fragmentations but it will eventually. Using uniform extends or at least a big minimal size for extends will help, especially if you expect large partitions.

When using LOBs retention might come into play as well.


How about encrypting the vote using a key that only the user and/or the system knows?

Adding some more information:

Use a symmetric encryption so that you can decrypt it.
Encrypt outside the database. Do not use database encryption functions if you want to protect it from DB admin because admin has several ways to snoop into the queries and read the keys. Instead, encrypt the data in your programming language of choice, and just store the encrypted data in the database. Admin never gets access to the actual data.
You can either have one secret key per user, or one common secret key for the entire system. However, you have to keep the key secret from the DB admin.I prefer going for one key for the entire system because that requires me to maintain only one key.
If you use a single key, you should add some random data to the votes before encrypting those. This will ensure that encrypted values for votes would look different for each user. One way to do it is to prepend 5 (>0) random characters before the vote data every time before encryption, and discard first 5 characters after decryption. Other option is to add the userid before the vote data before encryption. In the absence of such randomization, the admin may be able to 'guess' and 'alter' votes.

Hope this helps.


Update view using derived key in Database

The with some modification the view should be updateable, but not in its current form.
https://msdn.microsoft.com/en-au/library/ms187956.aspx gives the rules for Partitioned Views

Some things you would need to change:

MessageTech column would need to be part of the underlying tables
MessageTech needs to be part of the primary key of each underlying table
MessageTech needs to have constraints on it in each table such that a given value could not possibly refer to more than one table e.g.

ALTER TABLE MessageCentre Add
    MessageTech char(1) Not Null Default 'S',
    Constraint chk_MessageTechIs_S Check (MessageTech = 'S');

There are some other conditions too, which you can read about in that link, but those are the obvious changes that would need to occur.


How to get distinct day number with window function (Postgres 9.6)

dense_rank() seems to be a perfect fit.

SELECT date,
       id,
       dense_rank() OVER (ORDER BY date) day_num
       FROM elbat;

db<>fiddle


Find order that has deliveries in multiple weeks

Try following query

SELECT OrderNumber
FROM @DeliveryTable
GROUP BY OrderNumber
HAVING DATEPART(WEEK, MIN(DeliveryDate)) < DATEPART(WEEK, max(DeliveryDate))

Calculating date-diff can lead to incorrect results because if we compare difference of Sunday and next Monday it will return 1, but these days are in different weeks.


INSERT inside PL/pgSQL function gives different result

Merlin Moncure from the PostgreSQL General mailing list has an answer:

  The SQL standard mandates that rows containing all null
  values satisfy 'IS NULL = true'.  However, postgres internally has
  nullibitily of container types that is distinct from their contents.
  I personally find this to be a good thing for various reasons but the
  facts are that postgres has some historical baggage in this area that
  crashed into the standard.


Execution Plan : Table Insert Cost 90% vs Clustured Index Insert Cost 86%

The decision about whether to use a heap or a clustered index for your underlying table structure is a lot more complicated than the speed of insertion, although that certainly can be a factor.

When you insert a new row, it is typically going to be quicker to get the row into the heap, but there are a lot of things going on that can affect both the estimate and the actual cost.

In your case, it sounds like you have additional indexes in play, and the maintenance of these would be weighing into those estimates. If you look in the Properties (hit F4), you will see the list of objects affected by Insert, and if you have made a non-clustered index instead of your clustered one, you are probably doing more work in your Table Scan, as there is one extra object to maintain.

If performance isn't actually a problem, I'd suggest leaving things as they are. If you're wanting to tune the pants off it, you're already getting deeper than you're likely to get in a forum like this. Heaps can be good, but they can also be awful. Clustered Indexes have drawbacks too, so your decision is a lot more complicated.

As to why it's higher - check the object list.


Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server

When you set up a linked server, you have the choice to re-use the credentials of the user, use a specific account or be anonymous.  When you tested it from the same machine, and SQL Server was running as a local account, it worked because it was able to re-use the admin privileges of you while you were logged-in.

To get it to work in a network scenario, things get tricky.  If you are using AD, you need to use AD (principalName) to configure the Kerberos trust between the SQL Server machine and the machine hosting the MSAccess file.  This usually takes some fiddling to get it right.  This TechNet article is long-winded, but contains a lot of config steps and suggestions: http://support.microsoft.com/kb/319723.

Also, you will need to set the server back to use the NETWORKING SERVICE account again so you can grant it permissions.  On the other machines, your SQL Server will be using a hidden account like [domain]\[MachineName]$.  If the MS Access DB is on the same machine as your SQL Server, you still need to grant (NTFS) read/write permissions on the MSAccess DB (file) to the accounts for the SQL Server process and to the network users.


How to change Postgres primary key column values?

I would add another column temporarily.

The first part can run while the database is active:

ALTER TABLE keyvaluehistory ADD new_id bigint;

CREATE SEQUENCE keyvaluehistory_new_id_seq OWNED BY keyvaluehistory.new_id;

/* update in batches to avoid table bloat */

UPDATE keyvaluehistory SET new_id = id - 39999999
   WHERE id BETWEEN 40000000 AND 40999999;

VACUUM keyvaluehistory;

UPDATE keyvaluehistory SET new_id = id - 39999999
   WHERE id BETWEEN 41000000 AND 41999999;

VACUUM keyvaluehistory;

...

SET maintenance_work_mem = '1GB';

CREATE UNIQUE INDEX CONCURRENTLY keyvaluehistory_new_pkey (new_id);

The following part locks the table and requires down time.

The most time consuming part is adding the primary key, because that requires scanning the table.

/* downtime starts here */

BEGIN;

LOCK TABLE keyvaluehistory IN ACCESS EXCLUSIVE MODE;

/* catch up */

UPDATE keyvaluehistory SET new_id = id - 39999999
   WHERE new_id IS NULL;

ALTER TABLE keyvaluehistory
   DROP CONSTRAINT keyvaluehistory_pkey;

ALTER TABLE keyvaluehistory
   DROP COLUMN id;

ALTER TABLE keyvaluehistory
   ADD CONSTRAINT keyvaluehistory_pkey USING keyvaluehistory_new_pkey;

ALTER INDEX keyvaluehistory_new_pkey RENAME TO keyvaluehistory_pkey;

ALTER TABLE keyvaluehistory RENAME new_id TO id;

ALTER SEQUENCE keyvaluehistory_new_id_seq RENAME TO keyvaluehistory_id_seq;

SELECT setval('keyvaluehistory_new_id_seq', 10000001);

COMMIT;

Please test befor running it in production; I may have forgotten something.


SQL 2012: Import DB changes original data scheme

Take a backup of the SQL Server 2008 databases and restore it on SQL Server 2012. It is a much faster and reliable approach to migrating a database. Do not attach files between the instances, the SQL Server 2012 will upgrade them to a new format and you will loose the ability to use them again on SQL Server 2008.

As a general advice try to follow the procedures and guidelines, including the pre-migration validation steps from Upgrade to SQL Server 2012

Make sure you migrate all login information and SQL Agent jobs relevant for your database(s).


Allow non-sysadmin, non-owner of a SQL Server Agent job to execute it

It is possible to set up a method to grant rights to run a job that a user does not have enough authority to run on its own.

EDIT: For clarity on the three options presented by explicitly mentioning the SQLAgentOperatorRole as an option and by adding some explanation on the third solution.

(1) If the user is allowed to manage the execution of all jobs, then make that user member of SQLAgentOperatorRole.  The user will be able to start (as well as stop, enable, and disable) any SQL Agent job on that server.   (This solution turned out to satisfy the original asker.)

(2) Erland Sommarskog has written a lot on how to grant permissions through stored procedures using counter-signatures.  He has a solution at:

http://www.sommarskog.se/grantperm.html#countersignatures

The key point is: "To be able to start a job owned by someone else, you need to be member of the fixed role SQLAgentOperatorRole in msdb. A start is to write a stored procedure that calls sp_start_job for this specific job, sign that procedure with a certificate, and then create a user from the certificate and make that user a member of SQLAgentOperatorRole."

(3) My general resolution was to create a StartAgentJob stored procedure in the msdb database allowing a user to start jobs owned by someone else.

This requires a table to maintain the configuration of who can run which job. Since the following dbo.msdbJobMap table is SQL Server Agent Job specific, I would create the table in msdb.  But it could be created in some other service database if desired.

USE msdb;

/* Create a table to hold configuration of who can start jobs. */
CREATE TABLE dbo.msdbJobMap
 (job_name NVARCHAR(128),
  group_name NVARCHAR(256));

/* Populate the table of allowed groups for a job
   A group may be a single user or a Windows group. */
INSERT INTO dbo.msdbJobMap Values (N'Test it out',N'Domain\Group');
INSERT INTO dbo.msdbJobMap Values (N'Another job',N'Domain\OtherGroup');
INSERT INTO dbo.msdbJobMap Values (N'Special job',N'Domain\Joe');
INSERT INTO dbo.msdbJobMap Values (N'Special job',N'Domain\Andre');

The stored procedure also allows any member of a specified group to start a job since it uses IS_MEMBER to check group membership.

CREATE PROCEDURE dbo.StartAgentJob
@Job_Name NVARCHAR(128)
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON;

DECLARE @Allowed INT;
SET @Allowed = 0;

/* Since this runs as sysadmin need to check group membership of original login*/
EXECUTE AS LOGIN = ORIGINAL_LOGIN();
IF EXISTS (SELECT * FROM dbo.msdbJobMap
           WHERE job_name = @Job_Name
           AND IS_MEMBER(group_name) = 1 )
   SET @Allowed = 1;
REVERT;

/* Back to sysadmin so that we can start the job. */

IF @Allowed = 1
    EXEC sp_start_job @job_name = @Job_Name;
ELSE
    PRINT 'Invalid attempt to start ''' + QUOTENAME(@Job_Name)+'''';
RETURN;

As you can see, the procedure depends on running as sysadmin in msdb. By switching to the context of the ORIGINAL_LOGIN it is able to use IS_MEMBER to check that the ORIGINAL_LOGIN has indeed been granted rights through the dbo.msdbJobMap table. Then it goes back to being sysadmin so that it can start the job.


Correct way to stop SQL Server (through automation)

You are safe either which way you go. SERVICES.MSC and POWERSHELL command lines are EQUAL functions to MSSQL Configuration manager...

Configuration manager is also used to control instances, etc etc but the function of starting and stopping a service is the same no matter which method you choose.

Some people have their own ways of doing things, for example to quit an application, you can do File > Exit. Or you can do ALT+F4, they are both the same function...

Please view this link below for additional information and helpful details. This covers all of the different methods (SQL configuration mgr, SSMS, Commandline etc)

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

UPDATE regarding Failover Cluster Instance...

If the instance is clustered, use of any of the above suggestions does not apply and should not be used. For a FCI you need to utilize Cluster Manager or the FailoverClusters PowerShell module commands.


Are operators also considered to be keys?

"$push" is a key in the JSON context. Don't forget everything in mongo (queries, projections, documents, method parameters) are represented as JSON objects. So, although $push is an operator for Mongo, it is still a key on the JSON object it belongs to.

JSON's specification demands key names to be surrounded by double quotes, although the Mongo shell, which is actually a Javascript interpreter, is more flexible and allows key names (field names and operators) without them, as the language does.


Innodb issue is preventing MySQL from starting

MySQL can not open file ./datumklok_web/wp3_postmeta.ibd . Check if it exists, but most probably it doesn't. It might have gone after file system check. If so, restore the database from backup. If you don't have them - mount MySQL partition read-only or take image from the partition.


Managing schema changes between transactional and analytical databases

You won't be able to automate this process.  Any release of the base system will need to include an impact analysis and change to the analytic system, and this will be a manual process.  You will have to update the ETL process to source data from the new structure and possibly modify the data mart as well.

This will add latency to the release process, and I've seen plenty of occasions where this was neglected and the analytic system broke.  The business or owners of the transactional system will have to do one of three things:

Incorporate impact analysis on the analytic system and ETL processes into their change control for the operational system.
Accept that releases will sometimes break the analytic system, possibly in ways that are not obvious (unrecognised semantic changes to the data).  Fixes to the analytic system may or may not be quick to implement.  It is possible they could do somethng that takes days or weeks to fix in the analytics.
Build a stable interface layer that the system exports data into and refactor the data mart ETL to populate via that interface.

Option 1 is almost always preferable unless the business cannot tolerate delay to the releases.  This might be the case with a .com type business where fast release schedules matter.

In the case where that impact on the release is not acceptable, then the business must either implement (3) or sign off that they are happy with (2) and accept responsibility for any downtime caused by changes that break the analytic system.

Option 3 will bog down changes to the analytic systems as they may become dependent on changes to the interface and therefore require releases of the operational system with changes to populate the updated interface.

If the business insists on sticking with (1) then make them sign off something accepting responsibility for unscheduled downtime on the analytics.  If you don't have a paper trail showing that you've raised this issue with them then you're at risk of copping the blame for something you have no control over.


Which table should a foreign key live on

As a rule of thumb, you should add a foreign key on the child table referencing the parent table. In your case it appears that WorkingHoursDetail is the child table and WorkingHours the parent table.

You can identify the parent table by asking which table can exists on its own without the presence of the other table. In your case WorkingHours can exists without WorkingHoursDetails (but not the other way around, so WorkingHours appears to be the parent table.

Alternatively you can look at the data flow when inserting new records. If both tables are empty, you should have to insert a row in WorkingHours first and then a record in WorkingHoursDetail. This makes WorkingHours the parent table and therefore the foreign key should be added on WorkingHoursDetail referencing WorkingHours.


How can I import CSV data stored in a bytea?

Precaution

The only reason I can see storing a CSV as a bytea is if you created it with COPY TO BINARY. You'll want to check to make sure this isn't binary data. If so the whole question is a off the mark as you don't have to do this and can just feed the bytea to COPY FROM STDIN

Using convert_from

The question is mostly how do you convert a bytea to text. To do that you'll use convert_from. See my answer here for more information on that process.

After you have a method to get the bytea to text you can either,

Wrap a psql -d myDb -c "SELECT convert_from(col ...) FROM tbl" in a call to COPY FROM STDIN and make use of it.
Parse it from within the INSERT/UPDATE using something that can process a CSV-row (suggest Text_CSV::XS). Doing it this way will save you from having to call a second a session to read and output in a format that COPY can accept.


That depends-- are your data files set to autoextend?  If they are, they'll just grow themselves up to whatever limit you may have set (or to the size of the available disk).  If not, whenever the database tries to allocate the next extent in the tablespace, the operation will fail and whatever is trying to write data to a table in the SYSAUX tablespace will fail.  There are many products that you might install in SYSAUX and any one of them might be the unlucky one that needs to allocate the next extent so you can't really predict what impact that would have.  I'd guess that you'd stop being able to write audit information since the audit trail is commonly in SYSAUX and commonly the most prevalent user of space among products that are commonly installed in SYSAUX.

You can check whether your data files are set to autoextend by looking at the autoextensible attribute in dba_data_files.

select file_name, autoextensible
  from dba_data_files
 where tablespace_name = 'SYSAUX';


Why does TLS1.2 break connections to SQL Server?

Older versions of SQL Server and SQL Server clients simply don't support TLS 1.2.  Please check out  TLS 1.2 support for Microsoft SQL Server to see what you might need, or just upgrade SQL Server to the latest service pack/update, and the client to the latest version.  Since I see you're using SQL Server 2017, you should be fine on the server side and you should just need to address this on the client side.  Based on your stack trace, I'm thinking that a .NET upgrade will address this for you.

We have numerous connections using various versions and platforms of SQL clients (including jTDS) connected using TLS 1.2 with various versions of SQL Server.  The latest official release of jTDS doesn't support TLS 1.2, so you have to download an unreleased dev version, or convert it to JDBC, which is really the best option as jTDS is a dead project.


In which tables values will be inserted if I insert values into a view that its result is made out of inner join of 3 tables?

A view that contains a join is not automatically updatable, that is, you cannot simply insert into such a view. The manual says (emphasis mine):

A view is automatically updatable if it satisfies all of the following conditions:

The view must have exactly one entry in its FROM list, which must be a table or another updatable view.
The view definition must not contain WITH, DISTINCT, GROUP BY, HAVING, LIMIT, or OFFSET clauses at the top level.
The view definition must not contain set operations (UNION, INTERSECT or EXCEPT) at the top level.

The view's select list must not contain any aggregates, window functions or set-returning functions.

The only way to insert into a view that does not meet these requirements is to create an INSTEAD OF INSERT trigger on it, in which case what valuees go to what tables is determined by the logic implemented in that trigger.


Do I risk losing the benefits of indexing if I have an index on every column?

When you say "for performance" you're taking a narrow view - "performance" as SELECT performance only, one query at a time, without considering caching and I/O contention issues.

Yes, having an index on every column might improve read performance. It'll certainly slow write performance (INSERT, UPDATE and DELETE), though that's not a concern for your DB. More subtly, more indexes means more contention for space in RAM to cache the indexes, which means there's a greater chance that a scan of any given index will result in slow disk reads.

Then there's the fact that you can index more than just individual columns. It's often most productive to create multicolumn, partial, expression and/or descending indexes to best satisfy the needs of your queries. You simply cannot create every possible index. Here's a recent example of a real world index you'd never create without analysis of the queries that required it:

CREATE INDEX contfloattable_tag_and_timeseg
ON contfloattable(tagindex, (floor(extract(epoch FROM dateandtime) / 60 / 15) ));

So: you could create an index on every column, but it's a bad idea. Use EXPLAIN AND EXPLAIN ANALYZE, possibly via the auto_explain module, to analyse your workload and queries, and make indexing decisons based on how you actually query the tables. An index you don't use will hurt you in a read/write environment, and won't do any good in a read-only environment.

Don't view queries in isolation. Look at patterns. If query1 seems to want an index on col1 and query2 seems to want an index on both col1 and col2, don't bother creating an index on just col1. Create a composite index on col1, col2; it'll be almost as fast for a search on col1 alone and way better than maintaining two indexes and having them fight over cache space and disk I/O.

BTW, this sounds like it's more of an OLAP workload than an OLTP one, so you might want to consider schemas designed for OLAP workloads like star schema with fact and dimension tables.


Transactional Replication snapshot locking Publisher db from beginning

To get this to work I scheduled switching off the 'immediate sync' to very low traffic period

EXEC sp_changepublication
@publication = 'statics',
@property = 'Immediate_sync',
@value = 'false'
GO

I then ran a snapshot which only took a fraction of the time at 6 mins.

I then had to drop the current subscription for that article only

    EXEC sp_dropsubscription
    @publication = 'statics',
    @article = 'dbo.SupplierCorporatePayAccountLink_tbl',
    @subscriber = 'SURVIVOR'

and rerun the snapshot. Then i could add the subscription back into the publication

    EXEC sp_addsubscription
    @publication = 'statics',
    @article = 'dbo.SupplierCorporatePayAccountLink_tbl',
    @subscriber = 'SURVIVOR',
    @destination_db = 'MLT-Replicated',
    @reserved='Internal'

but it would not allow this without the fairly obscure setting

@reserved ='Internal'

or I got an error message saying I needed to subscribe to all articles.... replication then automatically sync'd the new table. I'm a happy man as this has been causing me some long nights!


How do I merge similar records with different validity dates?

If this is a table of back-to-back ranges only, your case can be treated as a classic "gaps and islands" problem, where you just need to isolate islands of consecutive ranges and then "condense" them by taking the minimum [from] and the maximum [to] per island.

There is an established method of solving this using two ROW_NUMBER calls:

WITH islands AS
(
  SELECT
    id,
    data,
    [from],
    [to],
    island = ROW_NUMBER() OVER (PARTITION BY id       ORDER BY [from])
           - ROW_NUMBER() OVER (PARTITION BY id, data ORDER BY [from])
  FROM
    #mergeTest
)
SELECT
  id,
  data,
  [from] = MIN([from]),
  [to]   = MAX([to])
FROM
  islands
GROUP BY
  id,
  data,
  island
;

This query will work in as low version as SQL Server 2005.


Why is the auto created statistic on this column empty?

I was able to reproduce this, both with an empty statistic, and a populated statistic. I arranged for an automatic statistic to be created on an empty table, and the index was created later:

IF OBJECT_ID(N'dbo.Heap', N'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.Heap;
END;
GO
CREATE TABLE dbo.Heap
(
    id integer NOT NULL IDENTITY,
    val integer NOT NULL,
);
GO
-- Add 1000 rows
INSERT dbo.Heap
    WITH (TABLOCKX)
    (val)
SELECT
    SV.number
FROM master.dbo.spt_values AS SV
WHERE
    SV.[type] = N'P'
    AND SV.number BETWEEN 1 AND 1000;
GO
SELECT COUNT_BIG(*)
FROM dbo.Heap AS H
JOIN dbo.Heap AS H2
    ON H2.id = H.id
WHERE H.id > 0
AND H2.id > 0;
GO
-- Empty table
TRUNCATE TABLE dbo.Heap;
GO
-- Repeat exact same query (RT = 500 + 0.2 * 1000 = 700)
GO
SELECT COUNT_BIG(*)
FROM dbo.Heap AS H
JOIN dbo.Heap AS H2
    ON H2.id = H.id
WHERE H.id > 0
AND H2.id > 0;
GO
-- Add 1000 rows
INSERT dbo.Heap
    WITH (TABLOCKX)
    (val)
SELECT
    SV.number
FROM master.dbo.spt_values AS SV
WHERE
    SV.[type] = N'P'
    AND SV.number BETWEEN 1 AND 1000;
GO
-- Add index
ALTER TABLE dbo.Heap ADD
    CONSTRAINT [PK dbo.Heap id]
    PRIMARY KEY NONCLUSTERED (id);
GO
SELECT
    S.[name],
    S.auto_created,
    DDSP.stats_id,
    DDSP.last_updated,
    DDSP.[rows],
    DDSP.rows_sampled,
    DDSP.steps,
    DDSP.unfiltered_rows,
    DDSP.modification_counter
FROM sys.stats AS S
CROSS APPLY sys.dm_db_stats_properties(S.[object_id], S.stats_id) AS DDSP
WHERE
    S.[object_id] = OBJECT_ID(N'dbo.Heap', N'U');

I found that modifications continue to be tracked accurately on all non-empty duplicates, but only one statistic is updated automatically (regardless of the asynchronous setting).

Automatic statistics updates only occur when the query optimizer needs a particular statistic, and finds that it is out of date (an optimality-related recompile).

The optimizer chooses from duplicate statistics as mentioned in the Plan Caching and Recompilations in SQL Server 2012 paper:

  An issue not directly related to the topic of this document is: given multiple statistics on the same set of columns in the same order, how does the query optimizer decide which ones to load during query optimization? The answer is not simple, but the query optimizer uses such guidelines as: Give preference to recent statistics over older statistics; Give preference to statistics computed using FULLSCAN option to those computed using sampling; and so on.

The point being that the optimizer chooses one of the available duplicate statistics (the "best one), and that one is automatically updated if found to be stale.

I believe this is a change in behaviour from older releases - or at least the documentation suggests that all out-of-date statistics for an object would be updated as part of this process, but I have no idea when this changed. It was certainly after August 2013 when Matt Bowler posted Duplicate Statistics, which contains a handy AdventureWorks-based repo. That script now results in only one of the statistics objects being updated, while at the time both were.

The above explanation matches all the behaviours I observed while attempting to reproduce your scenario, but I doubt it is explicitly documented anywhere. It does seem like a sensible optimization, since there is little value in keeping duplicates fully updated.

This is probably all at a level of detail below that which Microsoft are willing to support. This also means it could change without notice.


You should set MAXDOP and "Cost Threshold for Parallelism" according to best practices.

MAXDOP should generally be set to the number of cores per NUMA node.  See my question here for more details.

Cost Threshold for Parallelism should likely be set to at least 50.


SQL Query - Set Date Variable by Table/Column Reference

If I understand your question correctly, there are two ways that I can think of:

mysql> SET @date = (SELECT source_col FROM source_table WHERE whatever_condition);
Query OK, 1 row affected (0.03 sec)

or

mysql> SELECT source_col FROM source_table WHERE whatever_condition INTO @date;
Query OK, 1 row affected (0.03 sec)

These two are functionally equivalent when only one value is involved, but the second form can also be expanded to handle multiple values.

mysql> SELECT col1, col2 FROM source_tbl WHERE whatever_condition INTO @var1, @var2;

But your question involved using the resulting @date in your query.

mysql> SELECT *
         FROM target_table
        WHERE target_column = @date;

If that's the intended purpose, you can eliminate the variable entirely and use the first example, above, as a subquery inside the final query.

mysql> SELECT *
         FROM target_table
        WHERE target_column = (SELECT source_col FROM source_table
                                WHERE whatever_condition);

http://dev.mysql.com/doc/refman/5.5/en/comparisons-using-subqueries.html


Grant access to all tables of a database

The privilege on DATABASE only grants general connection rights to the database and no more. A user with just that privilege can only see what the general public is allowed to see.

To grant read access to all tables, you also need privileges on all schemas and tables:

GRANT USAGE ON SCHEMA public TO myuser; -- more schemas?
GRANT SELECT ON ALL TABLES IN SCHEMA public TO myuser;

You may also want to set default privileges for future schemas and tables. Run for every role that creates objects in your db

ALTER DEFAULT PRIVILEGES FOR ROLE mycreating_user IN SCHEMA public
GRANT SELECT ON TABLES TO myuser;

But you really need to understand the whole concept first.
And it's almost always better to bundle privileges in group roles and then grant/revoke the group role to/from user roles. Related:

Why can a new user select from any table?
How to grant all privileges on views to arbitrary user
Grant privileges for a particular database in PostgreSQL
How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?


SQL Server Ignoring Instance name when using port number of different instance

A port number specification overrides the instance name, effectively ignoring the instance name altogether. For example, you can connect using SSMS using "YourSqlServer\badname,1433 even if the instance name is invalid.

When the port number is omitted with an instance name, the client queries the SQL Server browser to determine the port needed.


Migrating database from oracle 11.2.0.03 to 11.2.0.04.

Your best bet is to look at the Oracle patching advisor (in Oracle parlance this is a patch, not an upgrade, as you're updating to a new patch set):

Doc ID: 331.1 (My Oracle Support login required)


Is there any performance issue with including the "COLLATE" as part of the query as opposed to setting it for the column/table?

While not an expert on PostgreSQL collations, I have used PostgreSQL a little bit (I work mostly with SQL Server) and do not see how this particular info would/could be different than what I am about to describe:

Short answer: It Depends (standard answer).

Actual answer: specifying a collation at the column level (assuming that the collation is completely independent of the encoding / character set, which is the case for PostgreSQL and others, but in SQL Server the encoding is part of the collation) is merely a default that will be used for all sorting and comparison operations that do not explicitly provide a collation via a COLLATE clause. The storage of the data is impacted by the encoding / charset (i.e. Windows-1252 vs UTF-8 vs UTF-16 vs etc), but collation has no impact on data at rest because collation is merely rules for working with the data.

That said, the "depends" part is that if you create an index, it will physically store that data (i.e. the index keys in a separate structure) using the rules of the column's collation (unless you specify the COLLATE clause when creating the index, and not all RDBMSs allow for that). If you then specify a COLLATE clause in the query that is different than the collation used for the index, you will have a performance penalty because you won't be using that index. But, to specify a collation in a query that is different than the column's collation when an index is not available for the column's collation is not a performance issue as the data was not sorted in either collation prior to the query.


I actually answered a question like this on July 9, 2011

Suggest a MySQL book for me

Let me expand on this answer:

When it comes to MySQL, the must have book is the MySQL 5.0 Certification Study Guide. I have studied this book with a fine-toothed comb. At the time I bought the book, there was an errata page (list of typos in the book) provided my MySQL (the original MySQL, pre-Oracle, pre-Sun).

It forms a very good base for learning MySQL. However, to keep things in persepctive, let me tell you my experience using the book.

I actually purchased the book in September 2006 along with MySQL Stored Procedure Programming and starting using it for everything tasks as a first year MySQL 5.0 DBA (I already had 2 years with MySQL 4.0 as Developer/DBA). After 10 months, I took the exams July 28, 2007 and August 11, 2007. I passed both exams on the first try because of two things: (1) Thorough study of the book and (2) practical experience.

Nothing can substitute for actual usage of MySQL. After proper due diligence and logging enough miles in MySQL, then this book will definitely help you get certified.

Other books will enlighten you further

MySQL Administrator's Guide and Language Reference
MySQL Database Design and Tuning
High Performance MySQL
Understanding MySQL Internals
MySQL High Availability

BTW Yes, I do own all these books. I have read them and still use them !!!


Visual Explain Not working in MySQL Workbench

The user raising this question got an answer in the MySQL Workbench Forum:

  Visual Explain is only available if your server supports the JSON explain output (MySQL 5.6.5 or newer).

The reason VISUAL EXPLAIN didn't work for the user with 5.7, was the query being already prepended with explain at input time. Workbench transformed this into explain explain format=json - what wouldn't work.


1) How can it be?

If you check the documentation for server status variables, this number (3551496) is:

  The number of connection attempts (successful or not) to the MySQL
  server.

i.e. the number of connections (plus attempts to connect) since the server was booted!

The number of active connections can be verified by looking at the Threads_connected variable, which gives you:

  The number of currently open connections.

(which is at the bottom of your image and is 29!)

The show processlist command gives you more information about your active connections!

You need to make sure that you're using a privileged user:

  Like the output from the corresponding SHOW statement, the PROCESSLIST
  table will only show information about your own threads, unless you
  have the PROCESS privilege, in which case you will see information
  about other threads, too. As an anonymous user, you cannot see any
  rows at all.

You will find more relevant information here - the processlist table!

2) What should I do about it?

Congratulate yourself on a site which appears to be running nicely! :-)


How to get last 12 months values when some months have no records in the table

Start with a table of numbers, which you then use with dateadd to make a table of months. Then you can left join it.

If you don’t have a table of numbers, you can use ROW_NUMBER on a large enough table.

SELECT TOP (DATEDIFF(month, @month1, @month2)+1)
    DATEADD(month, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) -1, @month1) AS TheMonth
    FROM dbo.MyTable

You’ll also need your list of PartNumberKey and Depot_ID values.

DECLARE @month1 date = ‘20171201’, @month2 date = ‘20181101’; -- the semicolon is important!!
WITH months as (
    SELECT TOP (DATEDIFF(month, @month1, @month2)+1)
    DATEADD(month, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) -1, @month1) AS TheMonth
    FROM dbo.MyTable
),
PartsDepots as (
    SELECT DISTINCT PartNumberKey, Depot_ID
    FROM dbo.MyTable
)
SELECT pd.PartNumberKey, pd.Depot_ID, m.TheMonth, COUNT(*) AS Total
FROM months m
CROSS JOIN PartsDepots pd
LEFT JOIN dbo.MyTable t
    ON t.TheDate >= m.TheMonth
    AND t.TheDate < DATEADD(month,1,m.TheMonth)
    AND t.PartNumberKey = pd.PartNumberKey
    AND t.Depot_ID = pd.Depot_ID
GROUP BY t.TheMonth, pd.PartNumberKey, pd.Depot_ID
;


How do I fix the definer problem The user specified as a definer ('fred'@'192.168.0.%') does not exist

If you will recreate all your views this way (and will create all new ones with this syntax):

CREATE
SQL SECURITY INVOKER
    VIEW applications
AS
SELECT id, product_id
FROM modules     WHERE item_type='application';

you will only need to make sure a user on another instance where you restored your database has grants to EXECUTE on your views and SELECT on tables involved.

Typical database user in a web application will have SELECT, INSERT, UPDATE, DELETE, EXECUTE until your web application creates database objects.

You do not need 'fred' to be a database user anywhere in this case even though this database user created this view on your web site.


Check ronr.blogspot.com for a plug of and link to a book by Ben Prusinsky and Syed Jaffar Hussain that is very practical. It explains lots of things, and how to do them in great detail. There is lots of hands on experience in it. Very - VERY - nice.


Replication with Repmgr + native streaming replication

As per my experience, NO. it does not affect on repmgr.

i never tested on Production server. So please be careful when applying on Prodn server.


Does the order of fields in SELECT query matter when using composite indexing?

No, you can specify the 'params' (the parts of the where clause) in any order and the query optimizer will handle it. The optimizer will do the filtering in the order that it estimates is most efficient, but note that this is more complex than just choosing which order to filter: filtering might be done before or after joining for example.

You can't exactly prove this, but you can demonstrate it is true for a particular query by experimenting and seeing if the plan changes. It may even be true that there are edge cases where the order does matter, but my advice would be to ignore the possibility and assume it never happens as otherwise you will expend a lot of effort trying different permutations. Much better to focus on the kind of tuning which you know can pay dividends (eg correct indexing).


Change 'root' Password forMySQL but Change Lost after Reboot

There appeared to be no way out of this! Eventually after backing up Mint 17.3 was re-installed and MySQL added. MySQL did ask for a password to be set and everything has worked fine since. I doubt the reason for the problem will ever be known!


What is the difference between Oracle clusterware and Real Application Clusters?

Oracle Clusterware is the clustering software that is a component of an Oracle RAC setup. Rather than being seen as a separate software product, Clusterware is part of the software stack (which includes the OS, ASM, Oracle RDBMS) that makes up an Oracle RAC cluster/deployment. Oracle support other clustering products in a RAC environment on some operating systems.


SQL 2016 SP1 Mirroring - slow database list refresh and repetitive automatic failovers

It looks like CU1 corrects the issue I was having with the spontaneous failovers.  The mirror now seems stable and can fail manually back and forth correctly.

Refreshing the database list on the secondary is still slow, presumably because it is trying to log into the database but can't; log files contain "Login failed for user. Reason: Failed to open the explicitly specified database 'MirrorTest' when I try to refresh the list.


Dynamically changing tables in datasource in SSRS

Given that you expect the number of data sets to grow over time, your best bet would be to create a stored procedure in the database that uses a parameter value to determine the source tables and dynamic SQL to build the query.

Create an SSRS parameter to match and pass this value through to the data set and the stored procedure will return the results from the table specified in the parameter.

Basic Example:

CREATE PROCEDURE spGetData
(
    @SourceTable NVARCHAR(255)
)
AS
BEGIN

    DECLARE @SQL NVARCHAR(MAX)

    SET @SQL = 'SELECT [Column1], [Column2], [Column3]
    FROM [dbo].[' + @SourceTable + '_Audit]'

    EXEC sp_executesql @SQL
END


This is an operating system error, reported to the SQL Server service. The SQL Server service account, which creates the files, needs to have at least change permissions on the d:\data folder and it needs to exist.


USER_TAB_PARTITIONS Query Runs Incredibly Slow

Found in support.oracle.com:

Bug 8334498 Query on DBA_TAB_PARTITIONS does full table scan on TABPART$

This bug fixed in version 11.2.0.2.


how do i create Periods and weeks (think business reports) using 2 date ranges

I would suggest that you create a Calendar Table that you can use for decades to come.  You create it once and use if for almost forever.

Examples:

Aaron Bertrand's Creating a date dimension or calendar table in SQL Server

Himanshu Sharma & Mohammad Nizamuddin in TechNet T-SQL: Calendar Table


ORA-00904: Invalid Identifier when selecting from view

Your test case works perfectly as-is: DB Fiddle

Is there another object called LINE_DISCOUNT_AUDIT in the database? There may be a synonym in play here. Recreate the view and qualify LINE_DISCOUNT_AUDIT with the schema name (eg: YOURUSER. LINE_DISCOUNT_AUDIT).


I haven't done it myself, but there are several articles on using SSIS to transform the data. The principle will apply to other ETL tools I guess

http://www.databasejournal.com/features/mssql/article.php/3650326/Using-SQL-for-IIS-Logs-Part-2.htm
MS Log Parser
How-To: SQL query IIS Logs with Log Parser

and

maybe for reporting only?


could not connect to server: can't connect postgres on the server to postico on my local

fixed the issue in postgres 9.5 postgresql.conf and pg_hba.conf files are located in
/etc/postgresql/9.5/main/

not in
/var/lib/postgresql/9.5/main/

you have to login as server root user and run each one of the command and that will give where are the files
find / -name postgresql.conf

find / -name pg_hba.conf

in my instance when i ran the command i got this
/var/lib/postgresql/9.5/main/postgresql.conf
/etc/postgresql/9.5/main/postgresql.conf
/usr/lib/tmpfiles.d/postgresql.conf


Why can't user with SQLAgentOperatorRole delete his own SQL jobs

According to the SQL Server documentation, a user with a SQLAgentOperatorRole can only modify jobs that it created itself.


Secure Linked Server - Non privledged user possible? Registry corruption?

Have you granted the SQL Server accounts the right to impersonate other users?  It's a Windows setting on the domain account.


How do I open Data Pump Import Wizard in SQL Developer 4.1?

View > DBA

Add a Connection

Expand the tree.

Go to the Data Pump node.

Expand, expand again to get to existing DP Import Jobs.

This will show the results of

SELECT
    name,
    fname,
    type
FROM
    (
        SELECT
            job_name name,
            owner_name
            || '.'
            || job_name fname,
            'DATAPUMP_IMPORT_JOBS' type
        FROM
            dba_datapump_jobs
        WHERE
            TRIM(operation) = 'IMPORT'
    )

To create a new one, right-click and use the Wizard.


Why these characters are all equal in SQL Server?

All character data in SQL Server is associated with a collation, which determines the domain of characters that can be stored as well as the rules used to compare and sort data.  Collation applies to both Unicode and Non-Unicode data.

SQL Server includes 3 broad categories of collations: binary, legacy, and Windows. Collations in the binary category (_BIN suffix) use the underlying code points to compare so equality comparisons return not-equal if the code points differ regardless of the character.  Legacy (SQL_ prefix) and Windows collations provide sorting and comparison semantics for the more natural dictionary rules.  This allows comparisons to consider case, accents, width, and Kana.  Windows collations provide more robust word-sort rules that closely align with the Windows OS whereas legacy collations consider only single characters.

The example below illustrates the differences between Windows and binary collation with the Teth character:

CREATE TABLE dbo.WindowsColationExample
    (
      Character1 nchar(1) COLLATE Arabic_100_CI_AS_SC
    , Character2 nchar(1) COLLATE Arabic_100_CI_AS_SC
    , Character3 nchar(1) COLLATE Arabic_100_CI_AS_SC
    , Character4 nchar(1) COLLATE Arabic_100_CI_AS_SC
    );

CREATE TABLE dbo.BinaryColationExample
    (
      Character1 nchar(1) COLLATE Arabic_100_BIN
    , Character2 nchar(1) COLLATE Arabic_100_BIN
    , Character3 nchar(1) COLLATE Arabic_100_BIN
    , Character4 nchar(1) COLLATE Arabic_100_BIN
    );

INSERT  INTO dbo.BinaryColationExample
VALUES  ( NCHAR(65217), NCHAR(65218), NCHAR(65219), NCHAR(65220) );
INSERT  INTO dbo.WindowsColationExample
VALUES  ( NCHAR(65217), NCHAR(65218), NCHAR(65219), NCHAR(65220) );

--all characters compare not equal
SELECT *
FROM dbo.BinaryColationExample
WHERE
    character1 = character2
    OR character1 = character3
    OR character1 = character4
    OR character2 = character3
    OR character2 = character4
    OR character3 = character4;

--all characters compare equal
SELECT *
FROM dbo.WindowsColationExample
WHERE character1 = character2;
SELECT *
FROM dbo.WindowsColationExample
WHERE character1 = character3;
SELECT *
FROM dbo.WindowsColationExample
WHERE character1 = character4;
SELECT *
FROM dbo.WindowsColationExample
WHERE character2 = character3;
SELECT *
FROM dbo.WindowsColationExample
WHERE character2 = character4;
SELECT *
FROM dbo.WindowsColationExample
WHERE character3 = character4;

Reasons as to why Unicode may contain different code points for identical glyphs are outlined in  http://en.wikipedia.org/wiki/Duplicate_characters_in_Unicode.  I summary, it may be for legacy compatibility or the characters are not canonically equivalent.  Note that the Teth character ﻁ is used in different languages (http://en.wikipedia.org/wiki/Teth).


SQL Server Mirroring can't connect to mirror database

I got this same error a few weeks ago, despite the fact the connection was fine; it was actually due to an authentication issue.

If you don't have domain connectivity between the two machines, you can use certificates to create credentials that can be used for mirroring endpoints. To do this, you create a certificate on the database on each end, create an endpoint based on the certificate, then export each certificate to the opposite end.  The public key in the certificate is thus used to authenticate with the private master key on the remote database.

Create a master encryption key on each server.  This is only done once per server instance.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

Create a certificate for each instance with an arbitrary subject.  I will provide T-SQL for endpoint 1, but you will need to do this on the other endpoint as well.

CREATE CERTIFICATE Endpoint1_Cert WITH SUBJECT = 'CERT for endpoint 1'

Create the endpoint using the certificate:

CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT=7023, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE Endpoint1_cert,
ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL);

Back up the certificate from each end for export:

BACKUP CERTIFICATE Endpoint1_cert TO FILE = 'C:\BACKUP\Endpoint1.cer';

Now move each file to the opposite server using your favored method.  Here I assume you use the C:\backup directory as both source and target on each server.
Create server logins, based on the moved files.  Note the full credential consists of a server level login and a user credential on the master database.  (Kind of strange but that's the way it works...).  Here's the code you'd run on endpoint 2 to import the certificate from endpoint 1.  The password is totally arbitrary and is not used later.  The logins must be granted mirroring rights.

 --note, run on endpoint 2
 USE master;

 CREATE LOGIN Endpoint1_login WITH PASSWORD = 'userpassword';

 CREATE USER Endpoint1_user FOR LOGIN Endpoint1_login;

 CREATE CERTIFICATE Endpoint1_cert AUTHORIZATION Endpoint1_user
 FROM FILE = 'C:\backup\Endpoint1.cer'

 GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Endpoint1_login];

Run a converse process for creating a certificate on endpoint 2 and using it to create a credential on endpoint 1.
Now, it's time to fire up the mirroring.  You start with the server whose database is not in recovery mode.

ALTER DATABASE TESTING
SET PARTNER = 'TCP://endpoint1host:7023';

Next run the converse command on the alternate server. (If you get a database is not configured for mirroring at this point, there may be an environment-specific security problem that can be worked around with a sysadmin rights grant to the endpoint -- see comment by AllenKing below.  This did not occur in our lab.)

I ran this process successfully on 2012 SP1 on 21 March.  After I corrected an error I made on the account creation -- I did not use master but instead used the mirror database -- everything worked fine.

In troubleshooting configuration, I ended up using the following dynamic views:

-- To check on endpoints status -- is the port right, etc?
SELECT * FROM SyS.endpoints
SELECT * FROM SYS.tcp_endpoints
SELECT * FROM sys.database_mirroring_endpoints

-- This shows the endpoint rights
SELECT EP.name, SP.STATE,
CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))
AS GRANTOR,
SP.TYPE AS PERMISSION,
CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))
AS GRANTEE
FROM sys.server_permissions SP , sys.endpoints EP
WHERE SP.major_id = EP.endpoint_id
ORDER BY Permission,grantor, grantee;


website performing slow once mysql connection RDS reached 50

There may be many causes here are some suggestions:

1) Check slow query log in mysql:

https://www.a2hosting.com/kb/developer-corner/mysql/enabling-the-slow-query-log-in-mysql

2) Change mysql driver in your application

Even if you downgrade it, you can have better results. Change a major version if possible.

3) Look for locks

mysqladmin debug

It will dump a lot of information, including locks, but  you can get lock information by queries too:

https://dev.mysql.com/doc/refman/5.7/en/show-open-tables.html

Hope it helps