Does it ever make sense to create an index with additional columns after the primary key?

Indexes have more than one use. The primary use (in most cases) is to quickly identify the rows that meet a query's conditions. When doing this, the leftmost columns of the index are considered.

Say you have a table, users, with 100 columns, a mix of dates, strings, and numbers. Five of the columns in the table are: id, username, country, acct_expir_date, and access_level (an int). id is the primary key, both it and username are unique.

Let's say there's a (non-clustered) index on country and username. The query:

SELECT `username`, `acct_expir_date` FROM `users` WHERE country = 'UNITED STATES';

could use this index to identify all users from that country, locate their records in the actual table, and return the requested data.

However, the query:

SELECT `username`, `country` FROM `users` WHERE `username` LIKE 'g%';

would not be able to use the index - the leftmost column in the index, country, is not a part of the WHERE clause.

That said, indexes do have a second use. If all the columns in a query exist in an index, then the query can ignore the actual table, and use the index as if it were the table itself. this is called a covering index.

Let's assume we now have another non-clustered index on our table, on the columns acct_expir_date, username, id, and access_level, in that order. And, we have the query:

SELECT `username`, `access_level`, `acct_expir_date`
  FROM `users`
 WHERE `acct_expir_date` >= '2017-07-01'
   AND `acct_expir_date` < '2017-08-01'
;

This query would probably use our index even if the SELECT column list was * (all columns); however, since our index contains all the columns in the query, the users table itself will not be touched - the query will simply use the information in the index to provide the requested data.

So, in your case, the columns in the index up to the primary key are there to be used for the index's first use - locating records. The remaining columns may be there for the second use; providing a covering index.

NOTE: as stated by Balazs Papp in the comments, columns to the right of the primary key in the index can be used to help identify specific rows in a query where a range of primary key values are of interest; however, each individual row in the index would have to be checked, since the rows cannot be sorted on anything to the right of the primary key (since it's unique). Since the index is already in memory, narrowing down the actual selection of rows further via the index would indeed probably be faster than loading the full records and then narrowing things down; however, if the extra columns are going to be used in searches regularly, they might be more useful to the left of any unique columns, where they can narrow things down more quickly.

UPDATE: tables that can be loaded into memory in their entirety don't generally need covering indexes. If your table is not all that large, then any performance gain from the indexes may be lost in maintaining them and storing them. Consider the size of the table overall, the size of the rows of the table vs. the rows of the index, and how often queries are performed that might use the index, vs. how often the data in those columns is added or updated. If the indexes seem like they might be causing more harm than good, you may want to consider dropping them. However, do so with great care; if there is some critical query they're boosting performance on, removing them could cause significant issues. I would remove no more than one per month or so; over the course of a month of normal business, whatever use the index might have should be encountered. And, only do so with the knowledge of key people in your department; not just your boss, but someone who deals with reports of performance issues, so any problems that may arise can be resolved. Also, keep in mind how long it would take to recreate the index; there may actually be cases where creating the index, using it for one monthly report, then dropping it would still give a performance benefit, without the maintenance and (long-term) storage costs.

UPDATE 2: For the sake of (improved) completeness, as joanolo mentions in the comments, indexes also have a third general use - they do sort the values in indexes columns by those columns. If that sort matches your ORDER BY clause, the engine can avoid having to sort the records by retrieving them in the necessary order to begin with. This isn't particularly relevant to the OP's question, since a unique column means that any values to the right in the index aren't being sorted (because the position of the rows is fixed once the index reaches a unique column), but it is true.


How to verify if a SQL Account is still used?

An additional, easy, option is to log all successful & failed logins.  Under the server properties tab

This will write any use (or attempted use) of the login to the SQL Server log.  From there you can programatically search the log.

USE Test
GO

-- P1 is the file number starting at 0
DECLARE
@p2 INT = 1,
-- P2 1 for SQL logs, 2 for SQL Agent logs
@p3 NVARCHAR(255) = 'UserName',
-- P3 is a value to search on
@p4 NVARCHAR(255) = ''
-- P4 is another search value

IF OBJECT_ID('IOErrorLog') IS NULL
    CREATE TABLE IOErrorLog (
            ServerName nvarchar(256),
            LogFileName nvarchar(4000),
            Occurances int,
            LogDate datetime,
            ProcessInfo nvarchar(50),
            ErrorText nvarchar(max),
        CONSTRAINT pk_IOErrorLog PRIMARY KEY (ServerName, LogDate, LogFileName)
    )

----------------------------------------------------------------------
-- This part of the code was found here:
-- https://ask.sqlservercentral.com/questions/99484/number-of-error-log-files.html

DECLARE @FileList AS TABLE (
    subdirectory NVARCHAR(4000) NOT NULL
    ,DEPTH BIGINT NOT NULL
    ,[FILE] BIGINT NOT NULL
);

DECLARE @ErrorLog NVARCHAR(4000), @ErrorLogPath NVARCHAR(4000);
SELECT @ErrorLog = CAST(SERVERPROPERTY(N'errorlogfilename') AS NVARCHAR(4000));
SELECT @ErrorLogPath = SUBSTRING(@ErrorLog, 1, LEN(@ErrorLog) - CHARINDEX(N'\', REVERSE(@ErrorLog))) + N'\';

INSERT INTO @FileList
EXEC xp_dirtree @ErrorLogPath, 0, 1;

DECLARE @NumberOfLogfiles INT;
SET @NumberOfLogfiles = (SELECT COUNT(*) FROM @FileList WHERE [@FileList].subdirectory LIKE N'ERRORLOG%');
-- SELECT @NumberOfLogfiles;
----------------------------------------------------------------------

CREATE TABLE #temp (
    LogDate datetime,
    ProcessInfo nvarchar(500),
    ErrorText nvarchar(max))

DECLARE @p1 INT = 0

WHILE @p1 < @NumberOfLogfiles
BEGIN

BEGIN TRY
    INSERT INTO #temp
    EXEC sys.xp_readerrorlog @p1,@p2,@p3,@p4
END TRY
BEGIN CATCH
    PRINT 'Error occurred processing file ' + cast(@p1 as varchar(10))
END CATCH

SET @p1 = @p1 + 1
END

INSERT INTO IOErrorLog
    SELECT DISTINCT @@SERVERNAME,
        SUBSTRING(ErrorText,charindex('[',ErrorText)+1,
            charindex(']',ErrorText)-charindex('[',ErrorText)-1),
        SUBSTRING(ErrorText, 27, patindex('%occurrence%',ErrorText)-28),
        LogDate, ProcessInfo, ErrorText
    FROM #temp
    WHERE #temp.LogDate NOT IN (
        SELECT LogDate
        FROM IOErrorLog
        WHERE ServerName = @@SERVERNAME)

DROP TABLE #temp

SELECT * FROM IOErrorLog

The down side here is that you are logging the login successes & failures for everyone.  It could bloat your error log a bit.  It's generally considered a best practice to pull at least the failed logins though.


Why is the output of this query being returned like this?

Syntax for CONVERT:

 CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

[style] for float data type :

0 (default)   A maximum of 6 digits. Use in scientific notation, when appropriate.
1 Always 8 digits. Always use in scientific notation.
2 Always 16 digits. Always use in scientific notation.
126, 128, 129 Included for legacy reasons and might be deprecated in a future release.

You can use below command to get expected result :

CONVERT(nvarchar(max),CAST(0 as float(53)),0)


As @pifor mentioned the issues was $LD_LIBRARY_PATH . Following code has fixed my issue.

Thank you @pifor

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib


Is there any way to clear all tables using Liquibase?

I guess the fastest way would be to use a custom sql tag that uses  truncate ... cascade on all "top-level" tables.

Using the cascade keyword for truncate will also (recursively) delete all rows from all dependent tables (all tables referencing the one that is being truncated).

Something like:

<sql>
   truncate table parent_1, parent_2, foobar cascade;
</sql>

Don't know if that is feasible with the number of tables you have or not.


Remove scientific notation from an XML column

Specify float when fetching the value from the XML.

select @XML.value('(//Balance)[1]', 'float');

To change the value in a XML column with decimals included you need to first extract the value as a float and write it back as an numeric with the appropriate precision and scale.

Something like this.

update T
set XMLCol.modify('replace value of (//Balance/text())[1]
                   with (sql:column("X.Balance"))')
from YourTable as T
  cross apply (select convert(numeric(18,4), T.XMLCol.value('(//Balance/text())[1]', 'float'))) as X(Balance)

SQL Fiddle

The reason for the scientific notation is because it is the way SQL Server handles xs:float when using untyped XML.

From Type Casting Rules in XQuery

  When casting a value of type xs:float or xs:double, or any one of
  their subtypes, to a string or untypedAtomic type, the value is
  represented in scientific notation. This is done only when the value's
  absolute value is less than 1.0E-6, or greater than or equal to 1.0E6.
  This means that 0 is serialized in scientific notation to 0.0E0.


Oracle: How to get list of objects accessible to the user?

Community wiki answer:

ALL_OBJECTS lists all the object types, at least those which you need, except triggers, for which you can query ALL_TRIGGERS.


Well you can stop the job and make changes in the table and later continue the job either skipping the current object or not.

Demo:

 [oracle@localhost]$ impdp jay/password directory=dp_dir  tables=imptest, imptest2 content=data_only dumpfile=exp_jay_imptest.dmp
 ...

 ORA-02374: conversion error loading table "JAY"."IMPTEST"
 ORA-12899: value too large for column NAME (actual: 12, maximum: 10)

 ORA-02372: data for row: NAME : 'mysamplename'

 ^C--Press CTRL+C keys.
 Import> stop_job=immediate
 Are you sure you wish to stop this job ([yes]/no): yes

Now alter the table definition.

 SQL> alter table imptest modify name varchar2(20);

 Table altered.

 SQL> exit

 [oracle@localhost]$ impdp jay/password attach=SYS_IMPORT_TABLE_01--- you can get job name from log file.

 Import> start_job

 Import> continue_client
 Job SYS_IMPORT_TABLE_01 has been reopened at Tue Oct 11 07:39:12 2016
 Restarting "JAY"."SYS_IMPORT_TABLE_01":  jay/password directory=dp_dir tables=imptest,imptest2 content=data_only dumpfile=exp_jay_imptest.dmp
 . . imported "JAY"."IMPTEST"                             19.00 MB 1049211 out of 1048576 rows
 . . imported "JAY"."IMPTEST2"                            7.710 KB     396 rows
 Job "JAY"."SYS_IMPORT_TABLE_01" successfully completed at Tue Oct 11 07:40:13 2016 elapsed 0 00:01:02


MySQL Many to Many with additional data in intermediary table

If we have Person and School and a Person can enroll in zero to many schools and a School can enroll one to many people the many-to-many relation exists, but we would refer to the resulting entity as something like Student with its own properties (enrollment date, student id number, etc).
So outside of maybe changing the name of the entity, there's nothing wrong with that portion of the data model.
Other notes:

You need at least one alternate key on User, definitely one for Username, possibly one for Email.
You will need an alternate key on Deck, (user_id,name).
You will need an alternate key on Card, (name).


Event Store Summing Number of Expired Items using SQL

SELECT shop_id, COUNT(DISTINCT card_id) number_of_expired_cards
FROM sourcetable
WHERE card_expires < GETDATE()
GROUP BY shop_id


What does it mean that a "clustered index does not store pointers to the data"?

No, that's not a bad question.
In MS SQL Server (and others that work similarly), a clustered index doesn't store pointers to the data because it doesn't need to, a clustered index actually contains the table data itself.
A clustered index is the table, just ordered in a particular way.
If you create and populate a brand new table with no clustered index (called a "heap"), then add a non-clustered index, you're creating a new (ordered) object that still has to point to the rows in the actual table.
But if you take that heap and add a clustered index, you are literally re-ordering the table itself.


How to better backup MySQL 5.6 database?

Better choice is to depend upon a backup utility with which you shall configure backup frequency, etc. be it two slaves or...

Alternatively...

If you have enabled mysql log, you shall pretty well re-create those entries since mid night backup till the current time by running all the pertaining queries.

2a. Either you shall revert the whole data to midnight backup and then run the rest of the log to re-create all and getting upto the current level

2b. Or you shall revert the particular data table to midnight backup and run the rest of the log (by screening the queries).

Depending upon your database / program flow structure you may choose any one of...


This is often one of the most mythical subjects of MySQL. To separate fact from fiction, you need to remember the strengths and weaknesses of each Storage Engine. Each Storage Engine and application will govern the workload, read I/O, write I/0, and tuning options.

I have written many posts about how and when to pick one storage engine over the other:

Sep 20, 2011 : Best of MyISAM and InnoDB
May 03, 2012 : Which is faster, InnoDB or MyISAM?
Jul 05, 2012 : InnoDB vs MyISAM with many indexes
Sep 26, 2012 : Choosing MyISAM over InnoDB for these project requirements; and long term options

I have many more posts but you can start with these.


SQL Server transaction log file refuses to grow

To fix the issue in the short term manually grow the log file since that's best practice anyway.

If the log file will end up being multiple GB grow it in 1 - 4 GB increments to get there.

If you don't know how big it's going to get I would recommend using at least 1 GB increments.  This is a blocking operation so it should be done during a low use time if at all possible.

To discover why your problem is happening check for errors in the SQL error log, not just error message from the failed statement.  There may be an error stating why it couldn't grow.

You can also look at activity monitor to see what the sessions that are having problems are waiting on.  Right click on the instance in SQL Server Management Studio (SSMS) and click on "Activity Monitor."  Once you have it open look at the blocking chain (Blocked By and Head Blocker) and the Wait Type to see what is causing the issue.

To determine why the DB is getting created with a MAXSIZE of 70 run Profiler to catch the CREATE DATABASE statement.  That will tell you what query is getting run so you know if SQL is getting the correct query or not.


You need to make sure that when you are "storing the data in the database", the client you use is using utf8. Then setting the http header to utf8 completes the round trip.

The surprise is that it doesn't matter much what character set is used in the database (although it might well be convenient for that to be utf8 too).


sqlplus inherits the encoding from calling cmd.exe codepage. You must tell Oracle which codepage is used with NLS_LANG value. Value of NLS_LANG and used codepage have to match.

Try this:

c:\>chcp 1252
Active code page: 1252

c:\>set NLS_LANG=.WE8MSWIN1252

c:\>sqlplus ...

Note, if you run sql-files then ensure they are saved as "ANSI" (i.e. CP1252 for "Western" Windows). If you use a different encoding then set chcp and NLS_LANG accordingly, for example:

c:\>chcp 65001
Active code page: 65001

c:\>set NLS_LANG=.AL32UTF8

c:\>sqlplus ...

See also https://stackoverflow.com/questions/33783902/odbcconnection-returning-chinese-characters-as/33790600#33790600


I believe the problem is that you are executing all 5000 update statements as a single transaction. You need to break them up so each one is done in its own transaction. Then there are two options:

If you are in simple recovery mode (or can temporarily switch the database to simple recovery mode), then breaking them up should be enough for the database to automatically truncate the log after each transaction.
If you are in full recovery mode, you'll probably need to do a transaction log backup after each update statement to allow the log to be truncated.

So the last part of your query would look like this:

DECLARE @SQLquery AS VARCHAR(MAX)
DECLARE @name AS VARCHAR(250)
DECLARE @tablename AS VARCHAR(250)

DECLARE columns_cursor CURSOR FOR
  SELECT name, tablename
  FROM #temp2
OPEN columns_cursor

FETCH NEXT FROM columns_cursor INTO @name, @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
  @SQLquery ='UPDATE ['+@tablename+'] SET ['+ @name+'] = NULL WHERE LTRIM(RTRIM(['+@name+']))='''';'
  --SELECT @SQLQuery
  EXECUTE (@SQLquery)
  BACKUP LOG db_name TO DISK = 'backup_path' -- Not needed in simple recovery mode
  FETCH NEXT FROM columns_cursor INTO @name, @tablename
END

CLOSE columns_cursor
DEALLOCATE columns_cursor
DROP TABLE #temp
DROP TABLE #temp2


Truth be told, not only will you not see much performance loss from having foreign key constraints in the database, but you will see performance enhancements. The SQL Server query optimizer is built around the concept of primary and foriegn keys as well as other types of data constraints. If these are in place and enforced the optimizer can take advantage of them to get you better performance. Here's a blog post with a simple example that shows it in action.

If you are in an edge case where you truly have more inserts than reads (and updates & deletes require reads, so they usually end up adding to the read count), then it might make sense to remove constraints from the data for performance, maybe. But since the overwhelming majority of databases are read oriented, you're sacrificing performance, not enhancing it.

And none of this mentions the fact that data integrity is better handled at the database since you only have to create it once where as if you do all the work in code, you may have to do it multiple times for multiple apps (unless you design your data access layer carefully and require every app access the db to go through that same layer).

If you're using a relational database system, I say, why not really use it. If you don't need relational data, go with Hadoop or something else.


How can I run a T-SQL Script using SQL Credentials?

As you describe it, it's not going to work.  The T-SQL script needs to run on a SQL Server which means that it needs to be run in an appropriately-authenticated session.

One option would be to write the TSQL script, and then provide it with a VBScript or CMD file or PowerShell script that'll launch the TSQL at the correct server with the right credentials.


Reference the database from a stored proc?

If db_id() is called with no parameter it returns the id of the current database  - http://msdn.microsoft.com/en-us/library/ms186274.aspx

so you could just use:
set @db_id=db_id()

And having read the rest of the question, you could call object_id() without the db name. If the object exists you'll get the id, if not NULL. So:
SET @object_id = OBJECT_ID(N'dbo.Users');


Oracle Database 11g and TFS (2010) integration

If TFS is in the picture then I assume Visual Studio is there also and Oracle creates development tools for Visual Studio (VS) that will allow you to VS with TFS. With this, you could then create Oracle Database Project in VS and maintain SQL scripts.


Compare order of columns between two databases

If I have a simple ascii list of both sides, I can use a diff tool to compare both db schemas.

To get a list of all table columns you can use something like this:

select concat_ws('.', table_schema, table_name, column_name) as column_name
from information_schema.columns
where table_schema not in ('information_schema', 'pg_catalog')
order by table_schema, table_name, ordinal_position;

Of course you can limit that to the schema(s) you are interested in.

You can use psql's \copy statement to spool that to a file. Or use whatever export feature the SQL client you are using offers you.


How to find all positions of a string within another string

I think this will be slightly more efficient than the looping method you've chosen (some evidence here), and definitely more efficient than the recursive CTE:

CREATE FUNCTION dbo.FindPatternLocation
(
    @string NVARCHAR(MAX),
    @term   NVARCHAR(255)
)
RETURNS TABLE
AS
    RETURN
    (
      SELECT pos = Number - LEN(@term)
      FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@string, Number,
      CHARINDEX(@term, @string + @term, Number) - Number)))
      FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
      FROM sys.all_objects) AS n(Number)
      WHERE Number > 1 AND Number <= CONVERT(INT, LEN(@string)+1)
      AND SUBSTRING(@term + @string, Number, LEN(@term)) = @term
    ) AS y);

Sample usage:

DECLARE @name NVARCHAR(MAX);

SET @name = N'ali reza dar yek shabe barani ba yek'
    + '  dokhtare khoshkel be disco raft va ali baraye '
    + '1 saat anja bud va sepas... ali...';

SELECT pos FROM dbo.FindPatternLocation(@name, 'ali');

Results:

pos
---
  1
 74
113

If your strings will be longer than 2K then use sys.all_columns instead of sys.all_objects. If longer than 8K then add a cross join.


How can I restore a master database from its bak file?

The master database is special, different than other databases. It's a system database where SQL Server stores internal objects. The only time you'd normally restore it is if you're bringing back a server from the dead - you wouldn't usually want to restore master from one database to another.

In your scenario, when you're just curious about the contents of the database, restore master with a different database name, like this:

RESTORE DATABASE master_copy
FROM DISK = 'C:\Foo\<path>\master.bak'
WITH MOVE 'master' TO 'C:\Foo\master_copy.mdf',
MOVE 'master_log' TO 'C:\Foo\master_copy_log.ldf',
GO

Then, after the restore finishes, you can query objects in the master_copy database.


How to get for a percentage here in below query?

I'll approach it with a new DB design that suits the purpose.

So the Movie entity has a n:m relation with the Person entity. A person is cast in many movies and a movie has many persons in its cast. The movie entity has an year column and the person entity has a gender column.

So create this database as done at SQLFiddle here. Create two tables by queries as follows.

DROP TABLE IF EXISTS temp1;
DROP TABLE IF EXISTS temp2;
CREATE TABLE temp1 (
  SELECT COUNT(Pname) AS pcount,
    Pname,
    YEAR
  FROM Movie m,
    Person p,
    M_Cast MC
  WHERE  gender="Female"
  AND m.mid=mc.mid
  AND p.pid=mc.pid
  GROUP BY YEAR,pname
);

create table temp2
select Year, sum(pcount) as sumyear
from temp1
group by Year;

Then query the two temp tables as so:

select
  t1.pname,
  t1.year,
  t1.pcount as MoviesByPerson,
  t2.sumyear MoviesInYear,
  t1.pcount*100/t2.sumyear as percentage
from temp1 t1, temp2 t2
where t1.year=t2.year

You can run the SQL Fiddle snippet and see the result.


Why does this OPTIMIZE FOR UNKNOWN improve my query by several seconds?

"To investigate I ran the query in SSMS..."
That's the issue.  Local variables use the density vector of statistics yielding a much better row estimate, and consequently already OPTIMIZING for UNKNOWN.  Parameterized dynamic SQL uses the histogram, which pulls the entire row count for a given section.

Look at the estimated number vs actual number of rows for each of your Paste the Plan links.
The second link has waaaayyyy better estimations than the first.

I'd deploy your SSRS query to a dev instance and run some tests as I suspect you might have performance issues.

BTW, update stats or rebuild indexes on those beast tables, if you can.

Links:
Inside the Statistics Histogram & Density Vector


Primary key auto increment best practices

You're using a BIGINT which is 8 bytes. The maximum value you can store in a BIGINT is 18446744073709551615 if unsigned. At your rate of insertion of approximately 100 thousand rows per day, it will take you 459445680541 years to overflow.

In short, you have nothing to worry about.

In fact, you should consider changing this field to INT which is 4 bytes, with maximum value of 4294967295, which you can use for approximately 110 years before overflowing.


What's the correct syntax for this Postgres query in MySQL?

It can be

UPDATE items, ( SELECT 1 id, 12 position UNION ALL
                SELECT 2   , 13          UNION ALL
                SELECT 3   , 11                    ) i_vals
SET items.position = i_vals.position
WHERE i_vals.id = items.id;

or:

UPDATE items
       JOIN   ( SELECT 1 id, 12 position UNION ALL
                SELECT 2   , 13          UNION ALL
                SELECT 3   , 11                    ) i_vals
       ON  i_vals.id = items.id;
SET items.position = i_vals.position ;


Single Identity column and composite key, which to make primary?

This seriously depends on the expected half-life of your project, on how agile your environment is and so on. If your project is there to last, and changes are possible, I would not assume that your composite PK is never going to change. I have seen too many projects burned by such assumptions.

One good example would be the use of Social Security Number. Long ago it was a common practice to use it as a natural PK. Later on, many systems had to restrict its use for privacy reasons, and it became not possible to propagate SS#s all over child tables.

The systems that used identities adjusted to the change easily. The systems that used SS#s in child tables had to go through a major overhaul.

So, in many cases it is cheaper to use identities as PKs, adding a UNIQUE constraint on what currently is considered to be unique, and referring child tables to ParentID. This way we have to do less work to adjust when the situation changes.


Sql Server 2005 - sqlcmd with output to shell and file

In order to implement what you want you need something functionally equivalent to tee or grep on unix.  Windows does ship with a utility called find.exe that has similar functionality to grep, so you may be able to use that to extract relevant lines from your output file.  As far as I am aware there is no equivalent to tee shipped with base windows builds.
This sort of thing is trivial in a unix shell scripting environment but can cause issues on a raw Windows build.  If you can't implement what you want using find.exe, three basic approaches come to mind:

cmd.exe actually has significant pipelining capabilities, although not as rich as a unix shell.  You can get Win32 ports of tee and grep from the GNU Win32 collection, or from Microsoft Services for Unix (SFU).  The GNU Win32 ones are better because they use msvcrt.dll and understand native Windows paths.  SFU places an emulation layer over the base O/S in much the same way as cygwin, so it emulates /cygdrive/c style paths that behave in a more unix-like manner.
An alternative to this would be to use WSH, powershell or even a small .Net console app to implement a small utility to read the output from the log file and present your prompt to the user.

Reimplement the process in a way that allows the control to be automated.  If you can agree business rules for this control it can be run automatically and simply log the result, generating a status report when the process has finished running.

It seems quite strange that you are developing a server-side process that requires interactive input from an operator.  If this process has to be run interactively, is there any reason why the interactive parts could not simply be run from somebody's PC?  This would probably allow you considerably more flexibility in installing third party components or bespoke client-side code.


Create a linked server with SQL Server 2012 x64

Drivers for dBase (or Access or Excel) are not installed as part of the SQL Server install. It is likely that VS 2010 installed on your workstation is connecting through the old Jet drivers, which are installed on developer's machines. The problem with Jet is that it was never ported to 64 bit. I don't think that the old Visual FoxPro drivers were ported to 64 bit, either.

Microsoft replaced Jet with "ACE", which is available in 32 and 64 bit packages. ACE drivers were first released with Office 2007 and supplant the older (and probably deprecated) Jet drivers.

You can download the ACE 2010 drivers here. Since you are using a 64 bit server, you want the 64 bit drivers for linked servers. If you plan on running 32 bit packages on the server, you would need to install the 32 bit ACE as well.

You might be able to find a similar package for 2013 by now. I have not used these more recent drivers, so I don't know if the the older formats (like dBase, Fox, etc) are still supported.

After you install the drivers, they generally need additional configuration inside of SQL Server. IIRC, if you see errors that seem to be security-related, you need this additional configuration. In short:

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

Note that running drivers in-process could affect the stability of the instance, if those drivers are buggy.

After the drivers are installed, you will need to configure a valid linked server.

You also need to be sure that SQL Server has permission to read (and maybe write) the files.


Database design : hotel room and its detail

You basically answered the question already regarding the Type 2 approach:
Looking at the cons of Type 2, you already stated that this solution would violate referential integrity and therewith a normalized database structure. So I don't recommend using it.

I'd start with analyzing the requirement:
There are different rooms and different room descriptions. Different rooms may refer to the same description. So we have a 1..n relationship between descriptions and rooms.
A single room does not only have a description. It might also have a status (e.g. occupied, booked, not available, ...) and a base price (depending on the requirement the base price may be part of the room description).
A description of a room may have attributes like room size and room type, but also attributes like smoker/non-smoker, available for wheel-chairs y/n, dogs allowed y/n, ...

I'd probably go with the following schema:
rooms (room_no, room_desc_code, room_status_code, ...)
room_descriptions (room_desc_code, Room_Type, Room_Size,...)

Depending on the number of allowed values and the "expected change rate" I'd decide either to use a constraint or a lookup table. Especially for "Yes/No" columns (e.g. dogs allowed) I'd go for the constraint. The room_status_code would most probalby refer to a lookup table, since there are several allowed values and a "risk" of new values being required in the near future.


How to fetch last two year annual data in oracle

Sample data:

with data as
(
  select 1000 id, 'ABC' name, 2016 year, 50000 sales from dual union all
  select 1000 id, 'ABC' name, 2017 year, 80000 sales from dual union all
  select 1000 id, 'ABC' name, 2015 year, 90000 sales from dual union all
  select 1000 id, 'ABC' name, 2014 year, 45000 sales from dual union all
  select 1000 id, 'ABC' name, 2013 year, 30000 sales from dual union all
  select 2000 id, 'PQR' name, 2017 year, 80000 sales from dual union all
  select 2000 id, 'PQR' name, 2015 year, 90000 sales from dual union all
  select 2000 id, 'PQR' name, 2014 year, 75000 sales from dual union all
  select 2000 id, 'PQR' name, 2013 year, 60000 sales from dual union all
  select 3000 id, 'XYZ' name, 2015 year, 123000 sales from dual union all
  select 3000 id, 'XYZ' name, 2013 year, 56000 sales from dual union all
  select 3000 id, 'XYZ' name, 2012 year, 45000 sales from dual union all
  select 3000 id, 'XYZ' name, 2011 year, 30000 sales from dual
)

Result:

select
  id, name,
  case when rn = 1 then year else fy - 1 end as year,
  case when (rn = 1 or fy - 1 = year) then sales else 0 end as sales
from
(
  select
    id, name, year, sales,
    rank() over (partition by name order by year desc) as rn,
    first_value(year) over (partition by name order by year desc) as fy
  from data
)
where rn <= 2;

        ID NAM       YEAR      SALES
---------- --- ---------- ----------
      1000 ABC       2017      80000
      1000 ABC       2016      50000
      2000 PQR       2017      80000
      2000 PQR       2016          0
      3000 XYZ       2015     123000
      3000 XYZ       2014          0


Any advantages if we use multiple database instead of only one in PostgreSQL?

If it needs to be cross-verified, put it in the same db.  That way you can do your cross-verification in the db, taking advantage of indexes, query optimization, etc. which you can't do as easily in separate db's without a fair bit of extra work.

In theory you can spread out your disk/IO loads by putting it in another machine but typically, if this is virtually all write, I don't think it will be worth it.

If it is a different db on the same machine, you will not get any better write performance and you will likely get worse read performance.


How do you verify that all InnoDB tables are in good shape?

innodb is very very very unlikely to have any corruption caused by the storage engine.  (corruption caused by applications isn't something mysql can help with).
Upon each start up it will check it's log files and automatically recover any from any partially completed transactions in the event of a crash.
That being said it is still very possible for a replication slave to become out of synch.  For that reason, or even if you just want to be uber paranoid you can use the tool pt-table-checksum
This will sometimes intelligently checksum your live tables in chunks so performing checks doesn't require downtime (either through explicitly taking the server down or having tables locked for a checksum table; query).  These numbers as a one off don't really tell you much.  As with any checksum you need something to compare it against to have any value.  If you run periodic checksums you can compare against previous values to alert you of unexpected changes.
Now, obvious you will be making changes in a database so this isn't a catchall.  This could be a good method for checking archival tables were data is not updated once it is written.
Note:  I said it "sometimes intelligently" does checks in chunks.  You might run into issues if you only have a PK/unique key that is multi column.


When does Log shipping restore job mark itself to completion in standby mode

I would try utilizing the built-in stored procedures to investigate and hopefully correct this.

For example, try running this on the Secondary server while in the master database:

EXEC sp_help_log_shipping_secondary_database @secondary_database = '<database name>'

This will return a result set that contains a column named 'restore_all' that may be of interest to you.  If that is 0, then when the restore job runs it will stop after restoring only 1 transaction log file.  Otherwise, it will apply as many as possible.

If it is configured incorrectly for your situation, you can change it like this:

EXEC sp_change_log_shipping_secondary_database @secondary_database = '<database name>', @restore_all = 1

Hopefully, this will help you correct your issue or at least give you a boost in your search for the root cause.


Can we reduce row lock contention on an index

If this third party app is holding those row locks long enough to cause contention, and

you can't change that behavior,
and the blocked queries are read queries, then

One option would be to enable Read Committed Snapshot Isolation in that database:
ALTER DATABASE [YourDatabaseName]
SET READ_COMMITTED_SNAPSHOT ON;

This should stop those UPDATE queries from blocking your read queries.
Note that this will affect the results of the read queries, since they will be seeing a point-in-time snapshot of the data being read, rather than being blocked waiting for the update.  It will also increase load on tempdb.  For a detailed treatment of implementing RCSI, check out Kendra Little's post here:
Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide


How to transform results of a select statement formatted as XML?

The query doesn't know (or care) how its results might be formatted by the client (the mysql program in this case), so no, you cannot influence the XML in the query itself. What you're looking for is called XSL transformation, for which there are command line tools (e.g. xmlstarlet) through which you can pipe your output.


Extract new lines from log file to MYSQL

Perhaps something like the following is what you are looking for?

LOAD DATA LOCAL INFILE '/path/to/raw/data/file.log'
INTO TABLE TABLE_NAME
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
;


MySQL: What does event `/io/table/sql/handler` mean?

From documentation of solorwinds

  .../sql/handler = work has been delegated to a storage engine

  examples:

  io/table/sql/handler = storage engine is processing an I/O request against a table
  lock/table/sql/handler = storage engine is processing a table lock operation

Reference

See the MySQL reference manual for more information on Wait Instrument naming (look at the section for "Wait Instrumented Components"):

https://dev.mysql.com/doc/refman/5.6/en/performance-schema-instrument-naming.html


Replication is tied to the machine name of the distributor.  If the new instance will have the same machine name then stop the agents, backup the distributor, and restore it using the keep_replication setting as on.  Then restart the agents.

If the hostname of the distributor is changing you'll want to tare down the replication and rebuild it.


Combining 2 SELECT queries and printing the results in PostgreSQL

The primary solution is to add parentheses:

(  -- parentheses required!
SELECT dt1.name AS name1, dt1.time AS time1
     , dt2.name AS name2, dt2.time AS time2
FROM   departure_times dt1
JOIN   departure_times dt2 USING (tram_id)
WHERE  dt1.name = '$name'
AND    dt2.name = 'CitySquare'
-- no ORDER BY?
LIMIT  5
)
UNION ALL  -- see below
(
SELECT dt1.name, dt1.time, dt2.name, dt2.time  -- see below
FROM   departure_times dt1
JOIN   departure_times dt2 USING (tram_id)
WHERE  dt1.name = 'CitySquare'
AND    dt2.name = '$name2'
-- no ORDER BY?
LIMIT  5
);

Without extra parentheses LIMIT is only allowed once at the end of the query and applies to the result of the UNION. To attach a LIMIT to each SELECT parentheses are required.

The manual:

  The UNION clause has this general form:

select_statement UNION [ ALL | DISTINCT ] select_statement

  select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, or FOR KEY SHARE clause. (ORDER BY and LIMIT can be attached to a
  subexpression if it is enclosed in parentheses. Without parentheses,
  these clauses will be taken to apply to the result of the UNION, not
  to its right-hand input expression.)

Other notes:

I switched to UNION ALL - assuming you don't actually want to try and fold duplicate rows.
Names of result columns are determined by first SELECT of a UNION query. Names (or aliases) in all following SELECT statements are ignored.
You may want to add ORDER BY to each SELECT. LIMIT without prior ORDER BY returns an arbitrary selection that can change with every execution - in simple queries determined by the physical order of rows in the table (or in the index), which can change any time.


MySQL using swap during conversion to InnoDB

To switch of MySQL engine you could :

1 Make a ALTER TABLE myTable ENGINE=InnoDB

2 Make a mysqldump of your table, then edit the CREATE TABLE statement to replace MyISAM by InnoDB and restore the dump in the new table (i called it myTable_InnoDB):

DROP TABLE IF EXISTS `mytable`;
CREATE TABLE `mytable` (
  `id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

.

DROP TABLE IF EXISTS `mytable_InnoDB`;
CREATE TABLE `mytable_InnoDB` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Best regards


Updating large table (~6.5 million rows) & indexes

You say that the UPDATE is running in a loop and was still running after 2.5 hours. Did that loop update any rows at all? Was it the loop itself that was taking a long time or a single UPDATE statement? That is a somewhat important distinction and is currently ambiguous in the given information. However, here are some things to note based on things said in the question:

No, SQL Server should not be updating indexes that do not use the new column
Disabling indexes is not simply a matter of turning them off. It actually drops all of the index pages and keeps only the structure so that you don't need to run the full CREATE statement again. But re-enabling an index will have to rebuild it.

And here are some things to look for:

Are there any UPDATE triggers on the Child table?
What is the auto-growth setting on the transaction log? If set to a very low number it could be that the UPDATEs are impeded by a large number of auto-growth operations.
Of those 5 indexes on the Child table, do any of them have FKId as the leading column?
Have you tried lower batch sizes, or just 20k? I have seen several people randomly recommend large (10k - 50k) batch sizes for these types of operations without considering that not only does it limit the operation to that number, but it also hints to the Query Optimizer that the number is possible to hit in the first place. Hence, if there are only 5000 rows to find then it might keep looking past those to see if any others meet the c.NewColumn IS NULL criteria. And considering that the field is not indexed, all the Q.O. has to go on are the auto-generated statistics which don't tell it where to find the remaining NULL rows. Assuming that the first several iterations of the loop did succeed, SQL Server still has to find 20,000 rows that are NULL, out of the 6.5 million, by scanning as many as it takes, in no particular order, before it finds them. Hence the first few iterations probably happened quickly, but this type of operation slows down quickly as each successive pass needs to scan more and more of the records before it can find the 20,000 that meet the IS NULL criteria.

So, two things to consider:

Start out with a small batch size, maybe 10 rows, and run that statement by itself (i.e. not in the loop). Does it come back? If not then you need to answer @Aaron's question about what the WAIT_RESOURCE is. If it does come back, increase the batch size to 100, then 500, 1000, 2000, 5000, and then 10000. It will take more loops to complete, but finding the required number of records to UPDATE will be easier to accomplish. Once you find a size that seems responsive, then you can run the loop again (I am guessing that somewhere in the 2000 - 5000 range is gonna work :)
Rather than get rid of indexes, you might actually want to create an index, temporarily, to support this particular UPDATE operation. A filtered index (which was introduced in SQL Server 2008 so the compatibility mode shouldn't pose a problem) will allow you to target the remaining rows to update. Something like:

CREATE NONCLUSTERED INDEX [IX_Child_FKId_temp]
  ON dbo.Child ([FKId] ASC) -- you might need to include [NewColumn] ASC after [FKId]
  WHERE [NewColumn] IS NULL
  WITH ( FILLFACTOR = 100 );


Which postgres design for a nested resource that can link to the same type of resource

Two methods of querying hierarchical data:

Ltree. You can see an example of me using it in a similar question here.. The problem with ltree is you have to serialize the relationship yourself. It's a flat table. This is a pita. Updating it also kind of sucks.
Self-referencing table. You can see an example of me using it in a similar question here.. This is probably ideal because you're only storing the identifier of the parent. This create a simple hierarchical data type that you can query with a RECURSIVE CTE.

Example table,

CREATE TABLE foo (
  idfoo          serial    PRIMARY KEY
  idparent       int       REFERENCES foo
  description    text,
  key            text,
  restriction    text,
  title          text,
  default        text
);

Then you insert your root, and your children which point to the parent.


I'm thinking having two copies of that table, one partitioned by genotype_id, one partitioned by snp_name and adding a few constraints and triggers to keep it consistent might be a good enough solution.


WITH t AS (
SELECT 1 user_id, '2019-05-15'::date AS created_at, 4 as score
UNION ALL
SELECT 1 user_id, '2019-05-13'::date AS created_at, 12 as score
UNION ALL
SELECT 2 user_id, '2019-05-15'::date AS created_at, 7 as score
UNION ALL
SELECT 3 user_id, '2019-05-13'::date AS created_at, 6 as score
)
SELECT
    md5(user_id::varchar) user_id,
    created_at signup_date,
    MIN(created_at) OVER (PARTITION BY user_id) first_score_created_at,
    FIRST_VALUE(score) OVER (PARTITION BY user_id ORDER BY created_at ASC) first_score,
    MAX(created_at) OVER (PARTITION BY user_id) last_score_created_at,
    FIRST_VALUE(score) OVER (PARTITION BY user_id ORDER BY created_at DESC) last_score
FROM t


SSIS Catalogue Inaccessible After Server Collation Change

Two things to consider:

Changing the Collation of an Instance via the -q method changes the Collation for all Databases, not just the system DBs. ( This option is undocumented and hence, unsupported. Please see Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong? for a detailed description of what that option does, as well as the potential scope of impact )
SSISDB, distributed by Microsoft, will have the same Collation for every installation since SSISDB is created from a backup file instead of a T-SQL script. There is an implication there that the Collation should probably not be changed. In addition, I found the following thread on the MSDN Forums -- SSISDB collation default: how to change? -- in which a Microsoft employee states:

  We do not support the changing of collation for SSISDB.

To be fair, it is also possible that a T-SQL install script could specify the Collation for the SSISDB Database, which would then have the same implication of it probably being best to keep that Collation. But, while a T-SQL install script can go either way, a backup being restored will be the Collation that the Database was when it was backed-up (hence the problem that sometimes occurs when restoring something like MSDB from an Instance that had a different default Collation: Example #1 and Example #2).

With those two points in mind:

You can try to change the Database and columns back to SQL_Latin1_General_CP1_CI_AS via ALTER DATABASE [SSISDB] COLLATE SQL_Latin1_General_CP1_CI_AS and then several ALTER TABLE ... ALTER COLUMN statements.

PRO: Existing data is not lost, with no need to export / import.

CON: You could miss one or more columns and the effect(s) might be hard to detect. While there are very few differences between those two Collations, they do not behave exactly the same, and I am not sure what expectations are in the code based on expecting the SQL_Latin1_General_CP1_CI_AS Collation. I'm not sure that I would be comfortable with this, mainly due to lack of certainty.
You can drop and recreate SSISDB (i.e. delete and re-deploy the catalog). If you have existing packages defined in there, you can export the data to import after the DB is recreated.

PRO: Know for sure that the issue is fixed.

CON: Extra work in exporting / importing data (and getting all tables, and in the correct order due to FKs).


Find the OverLapped (Common) Date Range from Group of Date Ranges

Something along the following lines should do what you need (DEMO)

WITH DistinctCounts
     AS (SELECT EventCount = COUNT(DISTINCT EID),
                PID
         FROM   #EventsTBL
         GROUP  BY PID),
     T1([PID], EID, Date, Flag)
     AS (SELECT [PID],
                EID,
                StartDate,
                1
         FROM   #EventsTBL
         UNION ALL
         SELECT [PID],
                EID,
                EndDate,
                -1
         FROM   #EventsTBL),
     T2
     AS (SELECT *,
                ActiveCount = SUM(Flag) OVER (PARTITION BY [PID] ORDER BY Date ROWS UNBOUNDED PRECEDING)
         FROM   T1),
     T3
     AS (SELECT *,
                PrevActiveCount = LAG(ActiveCount, 1, -1) OVER (PARTITION BY [PID] ORDER BY Date)
         FROM   T2),
     T4
     AS (SELECT T3.*,
                RN = ROW_NUMBER() OVER (PARTITION BY T3.[PID] ORDER BY T3.Date) - 1
         FROM   T3
         WHERE  ActiveCount <> PrevActiveCount
                AND EXISTS (SELECT *
                            FROM   DistinctCounts DC
                            WHERE  DC.PID = T3.PID
                                   AND DC.EventCount IN ( T3.ActiveCount, T3.PrevActiveCount )))
SELECT PID,
       StartDate = MIN(Date),
       EndDate = MAX(Date)
FROM   T4
GROUP  BY PID,
          RN / 2


Postgresql: Disallow duplicate value to be inserted if already in any of multiple columns?

How do I normalize it in postgres? could you please elaborate – Pax

Structure and predefined data:
-- Create table with priorities.
CREATE TABLE priorities (priority_id INT NOT NULL PRIMARY KEY,
                         priority VARCHAR(255));
INSERT INTO priorities VALUES (1, 'Critical'),
                              (2, 'High'),
                              (3, 'Low');

-- Create table for data. Series column added.
CREATE TABLE data (series INT NOT NULL,
                   priority_id INT NOT NULL,
       -- reference to priorities table
                   CONSTRAINT fk_priority FOREIGN KEY (priority_id) REFERENCES priorities (priority_id),
                   value VARCHAR(255) NOT NULL PRIMARY KEY,
       -- forbid duplicated values
                   UNIQUE (series, priority_id));

Insertion pattern:
INSERT INTO data
    VALUES {values list}
    ON CONFLICT DO NOTHING;

Retrieving the information:
SELECT series,
       MAX(value) FILTER (WHERE priority_id = 1) Critical,
       MAX(value) FILTER (WHERE priority_id = 2) High,
       MAX(value) FILTER (WHERE priority_id = 3) Low
FROM data
GROUP BY series
ORDER BY series;

fiddle
PS. The scheme does not allow to insert more than one value for the same priority in the same series also (see last insert).
PPS. priorities table is not used in shown code (it is used for reference integrity only), but you may...


MongoDB data directory space is increasing

The short answer: No

No, you can't do so easily.

The long answer: As always, it depends

First: The reason for those data files to be created is simple: The mongod preallocates a new datafile as soon as the first document is written in the previously preallocated datafile in order to prevent unnecessary latency if when the space of a new datafile is needed. So you will always have at least 2GB more space allocated then you need as soon as the file size of the data files reaches 2GB. As space is cheap, this is a reasonable procedure.

What can happen is that the data in the data files are fragmented. When a document is deleted, it is marked as such in the data files and the space it occupied is used again. So a new document may be written, either fitting into the formerly occupied space or not. When it doesn't fit into that space, a matching space is searched. If there isn't any, the new document is written to the preallocated data file (as documents are never fragmented) and the preallocation of a new data file is triggered. So this is why it can happen that a new data file is preallocated despite of the fact that there is mathematically enough space in the data files for the new document. There are a few possibilities to reclaim disk space, but all of them require some time and effort.

Option 1: using mongodump

You can create a dump of your data using mongodump, drop the database, delete the data files if necessary and use mongorestore to restore your database. Make sure you use the --oplog parameter, if applicable. So what happens during the restore is that all documents are written in contiguous order, and without datafile fragmentation, which may result in a smaller number of data files! effectively reclaiming some disk space. However, there is no guarantee that disk space will be freed, obviously.

There are a few drawbacks using this option:

you need the according disk space to create a full dump
without the --oplog option, you will have a point in time snapshot of the data, resulting in data written to the database from the start of the dump until it's completion will be lost

Indices aren't dumped, but rebuilt on restore. Hence, a restore may take a Very Long Time.

Option 2: "Abusing" the initial sync mechanism of replica sets

You may use MongoDB's mechanism for initially synching a new member of a replica set to achieve the same result as described above, without the risks. On the other hand, arbitrarily removing a member from the replica set reduces your redundancy. The procedure is documented in the MongoDB docs.

Option 3: (Ab)use repairDatabase

This option may be used to compact the database and reclaim disk space! but it has a lot of caveats as described in the documentation of the repairDatabas command.  Don't use it to reclaim disk space, unless you are absolutely positively sure that your database and it's collections are in a working state and every other option can't be taken. If you do, do it at your own risk. You have been warned.


why innodb generate table-lock when two sql using different index?

Every InnoDB table should be given an explicit PRIMARY KEY.  It must be "unique" and can either be a column (or combination of columns) or can be an AUTO_INCREMENT surrogate.

GEN_CLUSTER_INDEX is the fallback when you don't provide a PK.

VALUES(1, 'prod1') -- You are locking the one row from two different approaches (the secondary indexes).  Hence, it is quite proper to have a lock.  This lock will either lead to delaying one of the transactions until the other finished, or lead to a deadlock which will abort (and rollback) one of the transactions.

I don't believe you getting a "table lock", but instead a "row lock".

If your table is a "many-to-many" mapping table, please follow the advice in http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table


How to estimate REDO phase time during RESTORE

I am thinking how to estimate time of completion for this phase. But because SQL have no build-in way to do it,

If you have not enabled any trace flag you have no option but to look at DMV sys.dm_exec_requests and look for column percent_complete to gauge how much your restore operation has completed. Sample script from here

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

  I am restoring database now and get it hang on 100% but still going (doing redo phase).

Are you sure this is the redo phase, I think this is the UNDO phase and depending on how much VLF's are there in database log file the UNDO process can take time. Of course the whole process of restore can further be delayed if Instant file initialization is not there. You can check using This script whether database is having IFI . If not provide SQL Server service account perform volume maintenance tasks rights and this will require a SQL Server service restart

As already pointed by Gameiswar you can enable trace flags to get more information. You can also read Blog by Bob Dorr for more information about internal restore process.

Please note these are undocumented commands so don't enable it on prod servers.. These should be used for testing purpose only

DBCC TRACEON(3004);

DBCC TRACEON(3014);

DBCC TRACEON(3604);

DBCC TRACEON(3605);

GO

Below is what I got when I restored a database with trace flags enabled

RestoreDatabase: Database logicalreads

Opening backup set

Restore: Configuration section loaded

Restore: Backup set is open

Restore: Planning begins

Halting FullText crawls on database logicalreads

Dismounting FullText catalogs

X-locking database: logicalreads

Restore: Planning complete

Restore: BeginRestore (offline) on logicalreads

Restore: PreparingContainers

Restore: Containers are ready

Restore: Restoring backup set

Restore: Transferring data to logicalreads

FileHandleCache: 0 files opened. CacheSize: 12

Restore: Data transfer complete on logicalreads

Processed 168 pages for database 'logicalreads', file 'Logicalreads' on file 1.
Processed 1 pages for database 'logicalreads', file 'Logicalreads_log' on file 1.
Restore: Backup set restored

Restore-Redo begins on database logicalreads

Rollforward complete on database logicalreads

Restore: Done with fixups

Restore: Transitioning database to ONLINE

Restore: Restarting database for ONLINE

Restore: Database is restarted

Resuming any halted fulltext crawls

Restore: Writing history records

Writing backup history records

Restore: Done with MSDB maintenance

RESTORE DATABASE successfully processed 169 pages in 0.159 seconds (8.303 MB/sec).
RestoreDatabase: Finished


Query runs in parallel but it is showing as blocked by itself

Multiple parallel threads for the same query all share the same session_id, or spid.
Kendra Little did a great write-up showing that here.
Erik Darling also has a great, if only tangentially related, post about it here
If your query is blocking itself, that indicates one thread is taking longer to complete its work; this might mean you have out-of-date statistics on the tables in question, or simply that the data distribution is skewed such that one thread is receiving far more work than the other threads.
Also, @Josh Darnell mentioned on a very similar question, when you see multiple connection_ids for the same spid, it is because they have an application using Multiple Active Result Sets (MARS).
In reference to the number of threads being 5, even though the Maximum Degree of Parallelism is set to 4, the 5th thread is a coordinator thread that is waiting for results from the 4 parallel threads.  It is completely normal for the scheduler_id to be the same for multiple queries since schedulers are related to processors, not threads.  See the Microsoft Docs for details.  This query will show you the count of requests for each scheduler:
SELECT dos.scheduler_address
    , dos.scheduler_id
    , dos.active_workers_count
    , RequestCount = (
          SELECT COUNT(1)
          FROM sys.dm_exec_requests der
          WHERE dos.scheduler_id = der.scheduler_id
      )
FROM sys.dm_os_schedulers dos
ORDER BY dos.scheduler_id;

Results from my laptop look like this:
╔════════════════════╦══════════════╦══════════════════════╦══════════════╗
║ scheduler_address  ║ scheduler_id ║ active_workers_count ║ RequestCount ║
╠════════════════════╬══════════════╬══════════════════════╬══════════════╣
║ 0x00000017F7280040 ║            0 ║                    6 ║            4 ║
║ 0x00000017F72A0040 ║            1 ║                    7 ║            5 ║
║ 0x00000017F72C0040 ║            2 ║                    7 ║            5 ║
║ 0x00000017F72E0040 ║            3 ║                    6 ║            6 ║
║ 0x00000017F7A00040 ║      1048576 ║                    1 ║            0 ║
║ 0x00000017F7880040 ║      1048578 ║                    1 ║            1 ║
║ 0x00000017F1980040 ║      1048579 ║                    1 ║            1 ║
║ 0x00000017F19A0040 ║      1048580 ║                    1 ║            1 ║
║ 0x00000017F19C0040 ║      1048581 ║                    1 ║            1 ║
║ 0x00000017F78A0040 ║      1048582 ║                    1 ║            0 ║
║ 0x00000017F78C0040 ║      1048583 ║                    1 ║            0 ║
║ 0x00000017EB020040 ║      1048584 ║                    1 ║            1 ║
╚════════════════════╩══════════════╩══════════════════════╩══════════════╝


You are doing the right way,

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

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


Why can't I read my transaction log backup file using fn_dump_dblog?

@Alan Please check once by disabling the audit.

  That server has an AUDIT on a database. If the audit is enabled, you
  will get that error.  If you disable the audit,query will run fine.


Check out this article on MSSQLTips.com on how to configure readable secondaries and read-only routing in SQL Server 2012 AlwaysOn Availability Groups.

Basically, as long as you're only doing read workload, you enable readable secondaries and set up routing in your AG and any requests with Application Intent=ReadOnly in the connection string will connect to the secondary and perform its read workload there.

If your application needs to do any write workload, it must use a different connection that does not specify read-only intent in the connection string otherwise it will error when trying to perform the writes.

Also, make sure you validate your licensing scenario to make sure your secondary is properly licensed for performing read workloads.


Unable to start mysqld service after enabling log_bin

According to the manual entry for the mysqld option log-bin:

  If you specify this option without also specifying a --server-id, the server is not allowed to start.

So it seems you need something like:

[mysqld]
server-id=1
log-bin


What is the meaning of & in where clause

It's a bitwise and operator.

See the documentation here.

Wikipedia goes into lots of detail on bitwise operations.

Logically, the query you give:

select id, name
from test
where num & 1 = 1;

... will only return odd numbers, due to the lower bit in any integer always being 1 when a number is odd.


CREATE TABLE in Postgres locks pg_class forever

Normal operations don't place a persistent lock on pg_class.

There must be a DDL command (CREATE TABLE, DROP TABLE or similar) that is blocked because of a lock, so that it cannot complete the operation.

Check for active sessions in pg_stat_activity, their query and if they are hanging.

Your CREATE TABLE is not blocked by a table lock, but by a row lock in pg_class.


Optimize MySQL average query for millions of rows

I would add an array to monitor_node, containing the most recent 100 response times.  When adding recent response times, drop those exceeding 100.  No need for extra data when a new average has to be calculated.

As an image may say more than a thousand words, I've updated the DB Fiddle.  A trigger does the job, but that logic could be incorporated in the job doing the inserts.

create trigger monitor_checks_air
  after insert on monitor_checks for each row
  begin
  update monitor_node mn
    set mn.rec_resp_times =
          json_extract(
             json_array_insert(mn.rec_resp_times,'$[0]',new.response_time),
             '$[0 to 99]'),
        mn.average_response_time = (
            select sum(jt.rt)
              from json_table(mn.rec_resp_times,
                             '$[*]' columns( rt double path '$[0]')) as jt
          )
          / json_length(mn.rec_resp_times)
    where mn.monitor_id = new.monitor_id
      and mn.node_id    = new.node_id;
  end;

The fastest query is the one you don't execute.


How to optimize 'group by' query and indexing on big table?

Calculating three aggregates on a 300 million row table without a filter is going to take some time, especially if the rows have to read from disk. Without knowing anything about types and indexes, it is difficult to give any specific advice, but you may try to replace store_idx with an index like:

CREATE INDEX store_idx ON basket
    (store_region, store_format, store_code, basket_id, shop_date);


How to increase the performance of this 3 minute reporting query

Instead of 7 Subqueries, you can just use one query. This should be a large performance gain:

SELECT owner [User],
       SUM(CASE WHEN OrderType = 1 THEN 1 ELSE 0 END) [First],
       SUM(CASE WHEN OrderType = 2 THEN 1 ELSE 0 END) [Second],
...
       COUNT('x') [Total]
FROM MyTable mt
WHERE status NOT IN (2,3)
  AND ActionDate >= CAST(GETDATE() AS DATE)
  AND ActionDate < CAST(DATEADD(DAY, 1, GETDATE()) AS DATE)
GROUP BY owner

You'll also want to make sure that DATEDIFF(day, ActionDate, GETDATE()) = 0 is doing what you want. What you're actually getting is anything that happened on the day that the date is run- the period between the start of the current day and the current time. If you're looking for anything else, say, transactions that took place YESTERDAY, i.e. before midnight this morning and after midnight the previous morning, you'd want to use different logic.

In order to get all users and the related records for them, whether or not there are any related records in the timeframe, you'd have to use a subquery failing another source for the users:

   SELECT  user_lst.owner [User],
           SUM(CASE WHEN mt.OrderType = 1 THEN 1 ELSE 0 END) [First],
           SUM(CASE WHEN mt.OrderType = 2 THEN 1 ELSE 0 END) [Second],
    ...
           COUNT(mt.owner) [Total]
    FROM (SELECT DISTINCT owner FROM MyTable) user_lst
    LEFT JOIN MyTable mt
      ON mt.owner = user_lst.owner
      AND mt.status NOT IN (2,3)
      AND mt.ActionDate >= CAST(GETDATE() AS DATE)
      AND mt.ActionDate < CAST(DATEADD(DAY, 1, GETDATE()) AS DATE)
    GROUP BY user_lst.owner

Also note that we had to change the aggregate logic for [Total], as otherwise COUNT('x') would return 1 as [Total] for users with no activity in the timeframe.


Performance of JSONB vs. integer field storage in PostgreSQL

Is the speed of queries (with WHERE) the same when using JSONB vs. normal integer fields to filter the data?

Of course not. The JSONB adds indirection. You have to resolve the key to the value.

That said, if you create an index on a boolean column, or a jsonb's boolean key it should be the same speed for an index lookup.


Memory Allocation in Shared DB & IIS Environment

When there's nothing else on the machine besides SQL you need to leave at least 2-4 Gigs of RAM for the OS.  When running a bunch of different IIS sites you'll need at least 4 Gigs for the OS and IIS.  As some of your machines have only 4 Gigs that's a bit of a problem.  Can you add some RAM to these servers to get them up to 16 or 24 Gigs?

I'm assuming x64 here?


Strange behaviour of a query with order by and rownum on Oracle's Exadata server

This answer  is wrong, because the value for rownum is assigned before the order by is executed.

Below is the correct one,

SELECT start_date,
       end_date,
       resort_id,
       ROWNUM
FROM   (SELECT   start_date,
                end_date,
                resort_id
       FROM     employee_activity
       WHERE    employee_id = 27
       AND      start_date < TO_DATE ('2012-11-09', 'YYYY-MM-DD')
       AND      is_overridden = 1
       ORDER BY start_date DESC
      )
WHERE  ROWNUM = 1


import your dump with INSERT IGNORE OPTION (skip duplicates)

on your dump file (assuming you're using linux OS)

cat /path/dir/to/dumpfile.sql |
  sed s/"^INSERT"/"INSERT IGNORE"/g > /path/dir/to/newdump.sql

then issue import

mysql -u username -p databasename < /path/dir/to/newdump.sql


Retreive a Customized List with input data

My request is to add a parameter that is the name of the constraint and you retrieve a list that shows the name of the table and its column.

use AdventureWorks2012;

declare @ConstraintName varchar(max)

set @ConstraintName = 'CK_Product_ProductLine' --<--- Change  here with your constraint Name

if exists (
        select 1
        from sys.objects
        where OBJECT_NAME(OBJECT_ID) = '' + @ConstraintName + ''
        )
begin
    select OBJECT_NAME(OBJECT_ID) as NameofConstraint
        ,SCHEMA_NAME(schema_id) as SchemaName
        ,OBJECT_NAME(parent_object_id) as TableName
        ,[type_desc] as ConstraintType
    from sys.objects
    where type_desc like '%CONSTRAINT'
        and OBJECT_NAME(OBJECT_ID) = '' + @ConstraintName + ''
end
else
    select 'There is no constraint with the name that you specified'

Below will be output :


Transaction Log is big! But simple recovery mode is chosen!

In the simple recovery mode, as soon as a transaction finishes its log records are marked so that they can be overwritten but the transaction is still written to the log.

There is no way to prevent SQL Server from writing to the transaction log.

This means that a large transaction can still expand the log, if the current size of the log is too small. The tasks in your maintenance plans are performing operations in the database which are being written to the log, which is causing the log to expand, hit the 3GB limit you have set and that generates the error.

Remove the 3GB limit on the size of the log and make sure that the drive your log is on has enough adequate free space for the log to expand, or (and this is what I recommend) expand your log file manually to a size that will avoid auto-growth.

Finally, even though your database is small, I would not recommend using maintenance plans. The tasks in them are not "smart" and will perform operations even if they are not needed.

For example, the REBUILD INDEX task will perform a index rebuild even if it is not required (the index's fragmentation is low). This will cause a lot of information to be written to the database's log file.

Have a look at other maintenance solutions, I use Ola Hallengren's scripts.
https://ola.hallengren.com/


sqlagent job - using system folder path with CmdExec

SQL Agent doesn't shell out the command like that.  You'll need to write a batch file with the system variable in it, then call the batch file from the SQL Agent job.  The system variables don't get resolved if they are actually in the agent job step.


is there some way to do this sql script, in mysql?

You should only change create and grant statements, everything else is ok.
CREATE USER example_work_srv IDENTIFIED BY 'P4ssw0rd.';
GRANT SELECT, INSERT, UPDATE ON public.* TO example_work_srv;


How can I improve query performances using Using temporary; Using filesort

For queries this complex, you may have to think outside the box. In this case, I mean think outside the query itself.
ASPECT #1 : Join Buffers
Please note what the MySQL 5.7 Documentation says about join_buffer_size:

The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. Normally, the best way to get fast joins is to add indexes. Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible. One join buffer is allocated for each full join between two tables. For a complex join between several tables for which indexes are not used, multiple join buffers might be necessary.
Unless Batched Key Access (BKA) is used, there is no gain from setting the buffer larger than required to hold each matching row, and all joins allocate at least the minimum size, so use caution in setting this variable to a large value globally. It is better to keep the global setting small and change to a larger setting only in sessions that are doing large joins. Memory allocation time can cause substantial performance drops if the global size is larger than needed by most queries that use it.
When BKA is used, the value of join_buffer_size defines how large the batch of keys is in each request to the storage engine. The larger the buffer, the more sequential access will be to the right hand table of a join operation, which can significantly improve performance.
The default is 256KB. The maximum permissible setting for join_buffer_size is 4GB−1. Larger values are permitted for 64-bit platforms (except 64-bit Windows, for which large values are truncated to 4GB−1 with a warning).
For additional information about join buffering, see Section 8.2.1.6, “Nested-Loop Join Algorithms”. For information about Batched Key Access, see Section 8.2.1.11, “Block Nested-Loop and Batched Key Access Joins”.

You have four(4) JOIN statements in this query, which means you have four JOIN buffers. There is a lot of nested loop processing going on. You could speed this up by increasing the join_buffer_size. Be careful, though. If the join_buffer_size is 1M, this query requires 4M before the query can even start.
ASPECT #2 : Sort Buffer, Temp Tables
In the comments above, Using temporary; Using filesort was mentioned. You could eliminate this somewhat by by increasing the following values:

tmp_table_size
max_heap_table_size
sort_buffer_size

The idea is to lower the number of times sort tables and temp tables go to disk.
For example, you can set them globally for incoming connections:
SET SESSION tmp_table_size = 1024 * 1024 * 64;
SET SESSION max_heap_table_size = 1024 * 1024 * 64;
SET SESSION sort_buffer_size = 1024 * 1024 * 64;

Then, try the EXPLAIN plan and run the query. Hopefully, it will work.
Once you know it worked for you, you can set them globally for incoming connections:
SET GLOBAL tmp_table_size = 1024 * 1024 * 64;
SET GLOBAL max_heap_table_size = 1024 * 1024 * 64;
SET GLOBAL sort_buffer_size = 1024 * 1024 * 64;

Then, go back to my.cnf and add them.
[mysqld]
tmp_table_size = 64M
max_heap_table_size = 64M
sort_buffer_size = 64M

Give it a Try and let us know !!!


It's doable and quite safe approach is

Store form definitions in form_type table with JSONB and proper indexing. The table should have a marker telling if the form is already SQL-ized with create table
Write a asynchronous processing tool (cron worker) which will update table structure and issue proper CREATE TABLE requests, marking form_typeas deployed to SQL
form submit requests can be issued only after the form table is deployed
you can and should limit permissions / quota for this deployment task

Why async? I would avoid doing this in trigger due to locking issues under high load. Also, easy to DDOS so permissions should be separate for form insert and form create.


Fastest way to find (rare) rows with a null value among millions of rows?

There is a very fast way, create a partial index:

CREATE INDEX foo_idx ON addresses (address_id)  -- arbitrary pick
WHERE address_id IS NULL;

The indexed column is largely irrelevant. Use any small column(s) that might be relevant to your queries. The important point is that only the few rows with address_id IS NULL end up in the index.

The index incurs a (very small) cost on write performance, obviously.

Related:

Very large btree index with few rows (openstreetmap gis data)


How to detect the "Server admin" user on Azure SQL Server

With Microsoft SQL Azure Databases you're user won't be a member in any server role and you won't have access to that information. So, IS_SRVROLEMEMBER will, in fact, return zeros. You only have access at the database level and not the server level--unless you build and use a SQL VM instead.

From the Microsoft documentation you can use the following to test this:

IF IS_SRVROLEMEMBER ('sysadmin') = 1
print 'Current user''s login is a member of the sysadmin role'
ELSE IF IS_SRVROLEMEMBER ('sysadmin') = 0
print 'Current user''s login is NOT a member of the sysadmin role'
ELSE IF IS_SRVROLEMEMBER ('sysadmin') IS NULL
print 'ERROR: The server role specified is not valid.';

However, your user will have access to the database level roles. From Microsoft documentation you can test this example:

IF IS_ROLEMEMBER ('db_datareader') = 1
print 'Current user is a member of the db_datareader role'
ELSE IF IS_ROLEMEMBER ('db_datareader') = 0
print 'Current user is NOT a member of the db_datareader role'
ELSE IF IS_ROLEMEMBER ('db_datareader') IS NULL
print 'ERROR: The database role specified is not valid.';

There is a caveat though, you need to be in the context of your personal database and not the master database (my database is called TESTING):

Once you're in the correct database context, the following functions, and the like, work as expected:

select HAS_DBACCESS('testing')

select IS_MEMBER('db_owner')

Note:

select HAS_DBACCESS('db_name') will return a NULL value because there's no database called 'db_name'. You can change this to the db_name() function. For example,

select HAS_DBACCESS(db_name())

or, hard code the database name--TESTING in my case.

select HAS_DBACCESS('TESTING')


Your syntax is wrong you need  parenthesis around the value(s)
Please remember not to post Images for data, queries and errors

CREATE TABLE table1 (

  id VARCHAR(50)
);

INSERT INTO table1
VALUES
  ('PL4418.01301006-H01-D-03013O322241');

INSERT INTO table1
VALUES
  ( '04l801301006-I401•0013ccO27I0');

  INSERT INTO table1
VALUES
  ( 'P104I8.013016-H0l-D0l3c60O22l1');

  INSERT INTO table1
VALUES
  ( 'P104I8013016-I40l-D013c0c0224V');

  INSERT INTO table1
VALUES
  ( 'P10418-013016-HO1-D-010X2141');

  INSERT INTO table1
VALUES
  ('PL-0418-013016-H01-D-0132241');

  INSERT INTO table1
VALUES
  ( 'PL.5410.01301006-H0l-D.0201300022');

SELECT * FROM table1 WHERE id IN ('P104I8013016-I40l-D013c0c0224V');

| ID                             |
| :----------------------------- |
| P104I8013016-I40l-D013c0c0224V |

db<>fiddle here


Subscribing to individual articles within a publication

The stored procedure sp_addsubscription allows for you to specify that you want all articles (i.e. tables) or just one.  If you want a subset of articles in a publication, you just need to run the sp_addsubscription once for each article that you want.  It looks like the sp_addsubscription defaults to @article = 'all'
My suggestion is to go through the New Subscription Wizard and have it generate the script only.  Then you can go in and modify the script as you need it.  You would need to add multiple sp_addsubscription lines.


Salvaging data from corrupt tables PostgreSQL

If you have a recent backup, consider using that.

Otherwise, before you do anything else, make a copy of the directories containing the data you wish to recover. You may wish to use http://www.forensicswiki.org/wiki/Ddrescue to make a very low-level copy of the partition. It works in a way that will tolerate faults in the underlying storage medium.

From there you may wish to hire data recovery experts or PostgreSQL experts. If that's not an option, I would be prepared to get your hands dirty with the documentation for PostgreSQL's on-disk storage, so you can consider hand-repairing some of the data to make it valid again (to the extent that this is possible).


Is "many database writes" an anti-pattern?

There are no free lunches. If there is work to be done it has to be done at some point. The question is when is it best for the system as a whole to perform the work. This depends on the total workload and access patterns.
For write-mostly / read-seldom patterns, such as logs, it makes sense to optimize for write throughput. Since most data is never read it would be a waste of resources to perform additional processing to optimize for future reads which likely will never happen.
For write-once / read-many patterns, such as e-commerce, it makes sense to do the additional work once on write rather than perform it redundantly for every read.
There are many gradations between these two extremes. Perhaps the initial write is latency sensitive? It may be better to enqueue a request to perform the additional processing asynchronously. And so on.
Be aware that duplicating values (and aggregates do duplicate data) introduces risks. There will be a finite latency between the detailed row being written and the aggregates being updated. Other processes may be sensitive to this and report inconsistent results. The aggregates can become a concurrency bottleneck as they are touched may many activities, by definition. Every program ever written against either source has to consider the existence of the other values, too.


Is storing archive logs on local disk and backups on vendor storage possible?

Yes you should be able to have backups and archived logs go to different places (ie, backups could go to your VENDOR tool and archived logs to disk).

To send archived logs to disk you can do one of two things:

The first would be to set LOGARCHMETH1 to DISK:

db2 "update db cfg using LOGARCHMETH1 'DISK:/dbarch/dblogs'"

The second method would be to have two forms of archiving logs. You can leave LOGARCHMETH1 to your VENDOR and set up LOGARCHMETH2 to point to DISK.

db2 "update db cfg using LOGARCHMETH2 'DISK:/dbarch/dblogs'"

I've not played with two forms of log archiving before, but I know that it is available.

In our company we write our backups and archived logs to disk and then sometime after midnight we have IBM's Tivoli Storage Manager (TSM) come along and take a file system snapshot so it will have a copy of the backups and archived logs. So then we have a backup of our backups. We probably could have worked in LOGARCHMETH2 in our company, but so far this has been simplest for us.

Just giving you ideas of what you can do. :)


How to grant permission to view database audit logs to non-sysadmin login

Reference:

sys.fn_get_audit_file (Transact-SQL)
View a SQL Server Audit Log

  Microsoft recommends viewing the audit log by using the Log File
  Viewer. However, if you are creating an automated monitoring system,
  the information in the audit file can be read directly by using the
  sys.fn_get_audit_file (Transact-SQL) function. Reading the file
  directly returns data in a slightly different (unprocessed) format.
  See sys.fn_get_audit_file for more information.

In order to use sys.fn_get_audit_file users requires the CONTROL SERVER permission.

You can deny privilege on a principle who has been assigned CONTROL SERVER permission which is not possible in case of SYSADMIN.

More about this:

Security Questions: Difference between sysadmin and CONTROL SERVER Permission by Jason Strate.


How do I write a hex literal in PostgreSQL?

Arbitrary Data / Integers (int)

You can write the hexidemical byte using Bit-string constants

SELECT x'CC';  -- same as b'11001100'

Which is essentially the same as bit x'CC' returning a Bit String Type but there is a cast available to int so you can do x'CC'::int * 5

UTF-8 Byte Sequence

If the byte sequences is a valid UTF-8 character, you can also use E'' with a backslash-escape sequence (single backslash \)

SELECT E'\x41';
 ?column?
----------
 A
(1 row)

If the sequence is invalid, you'll get an error

# SELECT E'\xCC';
ERROR:  invalid byte sequence for encoding "UTF8": 0xcc

bytea-specific.

PostgreSQL has a variable-length binary data type that allows arbitrary bytes. This type is called bytea. You can move into this format using the bytea hex format. In this we either use,

Use a double backslash \\
Use the cast to bytea from from the hex representation.

Here are both syntax,

SELECT bytea E'\\xDEADBEEF', bytea '\xDEADBEEF';
   bytea    |   bytea
------------+------------
 \xdeadbeef | \xdeadbeef
(1 row)

Storing large numbers

If you only need to store large numbers, rather than storing them as bytea I would check out pg_bignum which stores them using the openssl implementation of Big Numbers.


Hosting ASP.Net session database on same same instance as the user database?

Personally, in addition to having an extra machine for the session DB, I would consider

moving the session database files onto different disk arrays (not partitions on the same volume)
adding more RAM in case it is read load, not write load causing the problem

The first option includes moving your user databases and tempdb to different disk arrays too. That is, one file per array basically.

I wouldn't think about either of these because you don't add any new resources:

another SQL Server instance on the same machine
using virtualisation for my SQL Servers


The order of the options matter.

Once you placed IGNORE 1 LINES, the next thing should be the list of columns.

LOAD DATA LOCAL INFILE '/John/league_seasons.csv'
INTO TABLE lwl_league_players
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(id, location_id, game_id, league_season_id, player_id);

See the MySQL Documentation on the syntax and you will see the correct order for the options.


Keeping things cached with PostgreSQL is a balance between what you can have in shared_buffers and what you have in the filesystem cache, and avoiding both of them being pushed out by other processes.

One of the first things you can do on PostgreSQL 9.4 is set huge_pages=on in your postgresql.conf.

This also involves doing some OS changes to allow PostgreSQL to start with this enabled. The appropriate sysctl variable is vm.nr_hugepages

You can do a quick and dirty calculation of what you should set vm.nr_hugepages to with the following bit of shell scripting.

echo $(grep ^VmPeak /proc/$(cat /var/run/postgresql/9.4-main.pid)/status | sed -e 's/VmPeak://' | sed -e 's/kB//' | sed -e 's/[ \t]//g')/$(grep Hugepagesize /proc/meminfo | sed -e 's/Hugepagesize://' | sed -e 's/kB//' | sed -e 's/[ \t]//g') | bc -l | awk '{print int($1+1)}'

This line grabs the VmPeak from the running PostgreSQL process in /proc/$pid/status, which tells you the peak virtual memory size for the PostgreSQL process. This number is in kB. It cleans up the spacing and extra output, then gets the measurement of Hugepagesize from /proc/memstat (which in my case was 2048 kB), then does the appropriate division, rounds the output to the nearest integer, then adds one for a safety margin.

In my case, it was 2147.

Make it permanent by doing the following:

echo "vm.nr_hugepages=2147" >>/etc/sysctl.d/postgresql.conf

This is important for the following reasons, excerpted from the documentation:

  /proc/sys/vm/nr_hugepages indicates the current number of "persistent" huge
  pages in the kernel's huge page pool.  "Persistent" huge pages will be
  returned to the huge page pool when freed by a task.  A user with root
  privileges can dynamically allocate more or free some persistent huge pages
  by increasing or decreasing the value of 'nr_hugepages'.

  Pages that are used as huge pages are reserved inside the kernel and cannot
  be used for other purposes.  Huge pages cannot be swapped out under
  memory pressure.

So, pages grabbed by the postgres process for shared_buffers won't be swapped out under memory pressure. And you'll have less stress on the TLB for your processor too.

Following that, we adjust the OOMKiller, because something randomly murdering postgres processes is not a good citizen. These settings strongly encourage it to not fire.

echo "vm.overcommit_memory=2" >>/etc/sysctl.d/postgresql.conf

echo "vm.overcommit_ratio=100" >>/etc/sysctl.d/postgresql.conf

Next we set a couple of kernel scheduler sysctls:

echo "kernel.sched_autogroup_enabled=0" >>/etc/sysctl.d/postgresql.conf

echo "kernel.sched_migration_cost=5000000" >>/etc/sysctl.d/postgresql.conf

The migration cost is the total time the scheduler will consider a migrated process "cache hot" and thus less likely to be re-migrated.

Autogroup enabled basically groups tasks by setsid() so perceived responsiveness is improved. But on server systems, large daemons like PostgreSQL are going to be launched from the same setsid(), and be effectively choked out of CPU cycles in favor of less important tasks.

After that, we can adjust the swappiness, which is a knob that controls the degree to which a system swaps processes out of physical memory. A higher number more aggressively swaps out, and a lower number keeps things in the filesystem cache longer. Setting this to zero will increase the chance of OOM killing under memory and I/O pressure.

echo "vm.swappiness=10" >>/etc/sysctl.d/postgresql.conf

These control how often dirty data is synced back to disk from the filesystem cache. Since dirty objects in the pagecache can't be freed, more aggressively writing back can keep more pages available for use, and help avoid large bursts of disk IO.

echo "vm.dirty_ratio=2" >>/etc/sysctl.d/postgresql.conf

echo "vm.dirty_background_ratio=1" >>/etc/sysctl.d/postgresql.conf

Since your PostgreSQL version output shows GCC 4.9, I'm assuming that you're running Debian Jessie or a derivative, so the 3.16 kernel will avoid some truly dreadful I/O issues that the 3.2 kernel had, so that's most certainly good news.

Once you've adjusted all these settings, reboot the system. This will help make sure that the hugepages aren't fragmented when allocated. The best and most reliable way though is to put hugepages=2147 (the number I needed on my server), in /etc/default/grub under GRUB_CMDLINE_LINUX, and run update-grub, then reboot. But I would test the necessity of this, as it is much harder to change than the sysctl.

These should give you a reasonable amount of pages in the filesystem cache and in shared_buffers. After this, making sure the fewest number of processes are running on your server (because they all compete for resources), should give you much of the page cache miss minimization you're looking for.

Further reading on huge pages and their effects are in the hugetlbpage documentation.

The vm settings and their effects are in the vm documentation.

This is a very approachable treatment of the Linux Memory system Understanding and Optimizing Memory Utilization

The PostgreSQL documentation goes into detail on the effects of huge pages at the bottom of the kernel resources page

Hope that helps. =)


Current SQL Server services configuration

I would use Powershell for this task.  Here's a function that leverages Get-WMIObject, WQL, and some syntax to take a list of servers to analyze.

function Get-SQLServiceInfo{
    param([string[]] $servers)
    $return = @()
    $wql = "SELECT * FROM Win32_Service WHERE name like '%SQL%' and (startmode != 'Auto' or state != 'Running')"

    foreach($server in $servers){
        $return += Get-WmiObject -ComputerName $server -query $wql | select name,startmode,state
    }

    $return
}

Get-SQLServiceInfo 'localhost'

Now, there are several ways to approach the matter of scanning all your SQL instances.  I like to use Central Management Server for this task.  The code I would use looks something like this(with the call to the above function):

$CMS=’CMSSERVER’
$servers=@((dir "SQLSERVER:\SQLRegistration\Central Management Server Group\$CMS").Name)

$servers+=$cms

Get-SQLServiceInfo $cms

So you could run this report manually, or implement it in some additional code to build and email reports out to you.  And with it automated, I'd actually say you could have this run once a day instead of quarterly.


Summary statistics: Multiple joins and group by

Here you go:

select
  si.insp_id, sd.def_length, so.obs_type_a_length, so.obs_all_length
from
  sidewalk_insp si
  left join
    (select insp_id, sum(def_length) as def_length from sidewalk_deficiency group by insp_id) sd
    on (si.insp_id = sd.insp_id)
  left join
    (select insp_id, sum(case when obs_type = 'a' then obs_length else 0 end) as obs_type_a_length,
            sum(obs_length) as obs_all_length from sidewalk_observation group by insp_id) so
    on (si.insp_id = so.insp_id)
order by
  si.insp_id
;

   INSP_ID DEF_LENGTH OBS_TYPE_A_LENGTH OBS_ALL_LENGTH
---------- ---------- ----------------- --------------
         1          3
         2        4.5                 5             26
         3                           19             30


Need a “pg_dump” command to take the backup of the child tables along with its master table for PostgreSQL database

You can specify multiple tables to "pg_dump" by specifying multiple "-t" flags.

If you are hoping to specify only one "-t" flag and have pg_dump automatically include all the dependent tables, that is not a feature pg_dump has.  You will have to enumerate the tables yourself, either manually or by querying the system catalogs.  I can't give you the query, because your question is not well defined.  We don't know how you want to handle recursive dependents or cross-dependencies, for example.


Question about a two-node MariaDB Galera Cluster

I'd say highly unlikely. During the network partition, when switch was pulled, (on an equally weighted quorum calculation) quorum cannot be achieved and the two node cluster loses primary component status. Exactly the reason not to have an equally weighted two node setup. When there's no prim component, there's no DML. After the situation is fixed the nodes find each other to form prim component and continue synchronous replication.

Highly unlikely that they get out of sync this way.


MYSQL fulltext - What's the right way to get the relevance from one column?

MyISAM? or InnoDB?

4 FULLTEXT indexes will work in either engine.

You could find out by creating an empty table and running that SELECT against it.  It should give you an error if the set of indexes you tried is wrong.  The ALTER to add/drop indexes.


If I understand your question correctly, let me try providing an alternate solution.

You could write a quick powershell script to automate the whole process -
-reading source .sql files
In a loop -
-replacing 'Alerts' word with A (B and C).
-run those files calling sqlcmd within the powershell script


Bad Practice to Store Calculated Data in Database?

If computers were infinitely fast, then, No, you would never store a value that could be calculated from other columns in the database. Storing calculated values is a violation of database normalization.

Examples would include:

The extended cost on an invoice line that carries price and quantity fields.
The total cost of an invoice’s lines.

In the real world, we do sometimes choose to violate normalization. The motivation is usually for performance reasons. Never do so without much thought, and hopefully a consultation with another DBA or database developer. And always document thoroughly your decision and its specific motivation.

  is there a general rule of thumb?

Yes: Normalize, and test/profile.

Always start with a normalized design. Load with fake data, test performance. Verify any discovered bottleneck is indeed due to your on-the-fly calculations.


Possible to run multiple Reporting Services (SSRS) instances on different machines with databases on same machine?

Yes, you can have several Report Servers installed on a single SQL Server.  As you noted, the multi-instance support on a single machine is available, but not what you are after.

For multiple distinct ReportServers on different servers, but using a single database, you simply have to configure this in the Reporting Services Configuration Manager.

As noted by GShenanigan this is described in the Scale Out Deployment. http://technet.microsoft.com/en-us/library/ms159114.aspx (same link)

In that phase of installing a Report Server, you must choose the database server that you want to use.  Then you can separately use the RS Configuration Manager to either choose the existing ReportServer datbase or choose a new database named to whatever you want to call it.

When a new database is set up, the ReportServerTempDB will be named similarly.  For example, creating ReportServerHQ would have a ReportServerHQTempDB also created.


Question Regarding Proper Failover to Asynch Server in Availability Group

On our cut over day we are going to failover our Primary to SQL03 so we can run our system in the New Datacenter

Before you do this, change the synchronization type to be synchronous and let the secondary catch up. Then, when the secondary is synchronized you can failover without issue.

Should you failover while the replica is still set to be asynchronous it will not guarantee that the databases in the AGs are all caught up to the same time. Additionally it'll cause you more issues as all database replicas will have their data movement suspended.

  Should I remove SQL01 and SQL02 from the availability group first then re-add them once I bring them online at the new location?

If it were me, I would. Even though driving them there doesn't "sound" like it's very far away - who knows what else will happen. When you remove the replicas the databases will go back into a restoring state so it should be fairly easy to add them back in.

  Secondly How will this work with my voting seeing I currently have 4 vote and will be going down to 2?

If you're using Windows Server 2012+ then dynamic quorum will be enabled by default. If you shut the servers down one by one in a controlled fashion then dynamic quorum will kick in and automatically readjust the votes. You won't have to do anything.

Additionally if you're using Windows Server 2012R2+ dynamic witness will also be on by default, thus giving or taking away witness votes as needed. Again, because you're doing this in a controlled fashion there shouldn't need to be any extra involvement or customization for the short period of time.


I want a select to give me id's that are not in a consecutively order

create table index_test(id number, idx number);

   insert into index_test (id, idx) values (1,0);
   insert into index_test (id, idx) values (1,1);
   insert into index_test (id, idx) values (1,2);
   insert into index_test (id, idx) values (1,3);
   insert into index_test (id, idx) values (1,4);
   insert into index_test (id, idx) values (2,0);
   insert into index_test (id, idx) values (2,1);
   insert into index_test (id, idx) values (2,2);
   insert into index_test (id, idx) values (2,4);
   insert into index_test (id, idx) values (2,5);
   insert into index_test (id, idx) values (3,1);
   insert into index_test (id, idx) values (3,2);
   insert into index_test (id, idx) values (3,3);
   insert into index_test (id, idx) values (3,4);
   insert into index_test (id, idx) values (3,5);
   insert into index_test (id, idx) values (3,6);

select id from index_test group by id having max(idx)+1!=count(*) or min(idx)!=0;