can sql generate a good plan for this procedure?

SQL Server 2005+ has statement level recompilation. Therefore, IIRC you'll get an optimal plan depending on branch.

However, your code isn't concurrency-safe. Two overlapping calls can get true for the NOT EXISTS: you get an error. This is why you use MERGE on SQL Server 2008.

Or assorted JDFI patterns for older versions. Also see Need Help Troubleshooting Sql Server 2005 Deadlock Scenario which has several options: it depends on your load too


Reg: Default Privileges on PostgreSQL Schemas

The documentation about ALTER DEFAULT PRIVILEGES tells you a possible reason - it is not that clearly described, though.

Let's see, what is said:

  You can change default privileges only for objects that will be
  created by yourself or by roles that you are a member of.

This means that the default privileges defined by this statement applies only to objects that are created by you (or the role you are a member of).  Let's see this in action!

alice's schema

First, we are logged in as alice.  Then, in a newly created schema, we create a table and grant some rights to bob:

SELECT current_user;
 current_user
──────────────
 alice

SHOW search_path ;
 search_path
──────────────
 test, public

CREATE SCHEMA alicetest;

ALTER DEFAULT PRIVILEGES
    FOR ROLE alice
    IN SCHEMA alicetest
    GRANT ALL ON TABLES TO alice;

GRANT SELECT ON alicetest.a TO bob;
-- this I do only for showing the privileges -
-- the owner has by default ALL and is not shown by \dp

\dp alicetest.a
                             Access privileges
  Schema   │ Name │ Type  │  Access privileges  │ Column access privileges
───────────┼──────┼───────┼─────────────────────┼──────────────────────────
 alicetest │ a    │ table │ alice=arwdDxt/alice↵│
           │      │       │ bob=r/alice         │

alice now has all rights on her table, as expected.

bob' table in the same schema

Now, after obtaining access to this schema, bob tries to create a table:

SELECT current_user;
 current_user
──────────────
 bob

CREATE TABLE alicetest.b (id integer);

GRANT SELECT ON alicetest.b TO alice;

\dp alicetest.b
                            Access privileges
  Schema   │ Name │ Type  │ Access privileges │ Column access privileges
───────────┼──────┼───────┼───────────────────┼──────────────────────────
 alicetest │ b    │ table │ bob=arwdDxt/bob  ↵│
           │      │       │ alice=r/bob       │

As you can see, despite creating the table in alice's schema where she set the default privileges, bob's table doesn't have all those permissions.  This happens because alice is not a member of bob.

Let's check this membership thing, too, and try to define default privileges by alice again, this time for another role:

ALTER DEFAULT PRIVILEGES
    FOR ROLE charlie
    IN SCHEMA alicetest
    GRANT ALL ON TABLES TO alice;
ERROR:  must be member of role "charlie"

So, some mighty enough user grants her a membership in charlie, then she tries again, with success:

ALTER DEFAULT PRIVILEGES
    FOR ROLE charlie
    IN SCHEMA alicetest
    GRANT ALL ON TABLES TO alice;
ALTER DEFAULT PRIVILEGES

charlie's round

Then charlie creates a new table:

CREATE TABLE alicetest.c (id integer);

And the privileges:

\dp alicetest.c
                               Access privileges
  Schema   │ Name │ Type  │    Access privileges    │ Column access privileges
───────────┼──────┼───────┼─────────────────────────┼──────────────────────────
 alicetest │ c    │ table │ charlie=arwdDxt/charlie↵│
           │      │       │ alice=arwdDxt/charlie   │

As you see, alice, as a member of charlie, gets her access to this table.

To answer your question,

I guess you defined the default privileges for yourself (alice in the example), but you developers act as a bunch of bobs here, not getting the necessary privileges.  One way to get around this (as we do it at work) is to do a

SET ROLE TO schema_owner;

every time before creating a new object in the schema.  This should be a role that all developers are a member of (otherwise you'd get an error).

NOTE that \dp is a psql command.


SQL Full Text Search - Result Order changing

Try This:

DECLARE @SearchString VARCHAR(200)
SET @SearchString = 'Adam OR West OR 60608'

SELECT Id, First_Name, Last_Name, Address,City,State,Zip, CT.Rank
FROM Patient_Ex PAT
    INNER JOIN CONTAINSTABLE(Patient_Ex,*, @SearchString) CT
        ON PAT.Id = CT.[Key]
ORDER BY CT.RANK DESC

Explanation:
The table produced by CONTAINSTABLE includes a column named RANK. The RANK column is a value (from 0 through 1000) for each row indicating how well a row matched the selection criteria.

Update:

Try adding weights to your keyword and see if it changes things:

DECLARE @SearchString VARCHAR(200)
SET @SearchString ='ISABOUT (Adam WEIGHT (.8), West WEIGHT (.4), 60608 WEIGHT (.2) )'
SELECT Id, First_Name, Last_Name, Address,City,State,Zip, CT .RANK
FROM Patient_Ex PAT
INNER JOIN CONTAINSTABLE(Patient_Ex,*,@SearchString,LANGUAGE N'English' ) AS CT
ON PAT.Id = CT .[KEY]
ORDER BY CT.RANK DESC;
GO

If the above doesn't works either try rebuilding your Catalog if you have  very few rows in database as people have reported wrong rank outputs with databases having less no of rows while the ranking working fine with database having large no. of rows,another workaround is to create dummy table in your database and index it in your catalog.
Please read this if you want to learn more about how ranks are calculated.
Source : 1,2


Error when trying to access linked server from another machine

You put no details about the involved paths in your post so I'm just making wild guesses.

You can run the query from any machine in your network but the path you save the excel file into must be accessible by the user executing the RDBMS service (usually local network or local service but may be customized at will).

The path you put into the linked server configuration is always local to the server.  Even when running the query from a client machine the excel file must be on the server. (Well, you may specify a UNC network path when creating the linked server but that  would make the whole thing more 'funny' to debug and handle because of permissions.)


No different.

Space Reserved for your MDF file: 100.21 GB, which is equal to 105,082,944 KB (105,082,944 / 1024 / 1024 = 100.21)

Space Reserved for *_1.ndf: 148.62 GB, which is equal to 155,843,968 KB (155,843,968 / 1024 / 1024 = 148.62)

The numbers match up just fine.  The Operation System is going to see Space Reserved, and that's how you would need to make your comparison.

Space used is going to be how much space is used within the database file, but Windows doesn't know or care about that.  The allocation and space reserved is the significant part reported by SQL Server which is what represents the footprint on the file system.


Dynamic Data Masking Doesn't Seem To Work Correctly With ISNULL

As a test, I created a custom user function that does nothing except pass the string back to the caller; it also exhibits this behavior.
Be aware, this code will drop users and objects, so only run it in a test database.
First, we create the required objects, users, and insert some data:
IF OBJECT_ID(N'dbo.EmployeeDataMasking', N'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.EmployeeDataMasking;
END
IF OBJECT_ID(N'dbo.SomeFunc', N'FN') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.SomeFunc;
END
GO

IF EXISTS (
    SELECT 1
    FROM sys.database_principals dp
    WHERE dp.name = N'SomeUser'
    )
BEGIN
    DROP USER [SomeUser];
END
CREATE USER [SomeUser] WITHOUT LOGIN;
ALTER ROLE db_datareader ADD MEMBER [SomeUser];

CREATE TABLE [dbo].[EmployeeDataMasking](
    [RowId] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeId] [int] NULL,
    [LastName] [varchar](50) MASKED WITH (FUNCTION = 'partial(2, "XXXX", 2)') NOT NULL,
    [FirstName] [varchar](50) MASKED WITH (FUNCTION = 'partial(2, "XXXX", 2)') NOT NULL,
 CONSTRAINT [PK_EmployeeDataMasking] PRIMARY KEY CLUSTERED
(
    [RowId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
) ON [PRIMARY]
GO

GO
CREATE FUNCTION dbo.SomeFunc(@i varchar(50))
RETURNS varchar(50)
AS
BEGIN
    RETURN @i;
END
GO
ALTER AUTHORIZATION ON dbo.SomeFunc TO [SOmeUser];
GRANT EXECUTE TO [SomeUser];
GRANT SHOWPLAN TO [SomeUser];

Insert Into dbo.EmployeeDataMasking (EmployeeId, LastName, FirstName)
VALUES( 1,'Smithsonian','Daniel'),( 2,'Templeton','Ronald')

Next, we'll query the data from the perspective of the non-privileged user:
EXECUTE AS USER = N'SomeUser';
Select
    EmployeeId,
    ISNULL(LastName,'') as LastName,
    ISNULL(FirstName,'') as FirstName,
    dbo.SomeFunc(LastName) as LastName,
    dbo.SomeFunc(FirstName) as FirstName,
    COALESCE(LastName,'') as LastName,
    COALESCE(FirstName,'') as FirstName
FROM dbo.EmployeeDataMasking
REVERT

The results:
╔════════════╦══════════╦═══════════╦══════════╦═══════════╦══════════╦═══════════╗
║ EmployeeId ║ LastName ║ FirstName ║ LastName ║ FirstName ║ LastName ║ FirstName ║
╠════════════╬══════════╬═══════════╬══════════╬═══════════╬══════════╬═══════════╣
║          1 ║ xxxx     ║ xxxx      ║ xxxx     ║ xxxx      ║ SmXXXXan ║ DaXXXXel  ║
║          2 ║ xxxx     ║ xxxx      ║ xxxx     ║ xxxx      ║ TeXXXXon ║ RoXXXXld  ║
╚════════════╩══════════╩═══════════╩══════════╩═══════════╩══════════╩═══════════╝
As you can see, the custom user function, dbo.SomeFunc exhibits the same behavior as ISNULL.  At this point, I'm unsure why, since COALESCE does not exhibit the unwanted behavior.


I would like to emulate the Microsoft SQL Server function GetSqlBinary() in MySQL version 5.6

I don't quite understand the problem you are having - let me explain why. The SqlBinary class is just a wrapper around a byte[] which adds some nice programmer-friendly methods and properties to it. But underneath the hood it is just a byte array. You can find this out by viewing the source code here:

http://referencesource.microsoft.com/#System.Data/System/Data/SQLTypes/SQLBinary.cs,2cc17fbd58a1a375

Also, you can view the source code to the SqlDataReader class here:

http://referencesource.microsoft.com/#System.Data/System/Data/SqlClient/SqlDataReader.cs,e46454446a9e2777

In short, it just takes the value of the field, casts it to a byte[] and constructs a new SqlBinary value from it. There is nothing stopping you from doing the same or implementing it yourself. Heck, if you implemented a MySqlBinary class you could even submit it as an enhancement to the library if you wanted to, I'm sure you are not the only person who would like it.

In short, doing this:

var binary = (byte[])reader.GetValue(i);

Is pretty much all you really need here.


SSRS Reports Manager Security Configuration

Normally in SSRS you would use the SSRS management interface (/ReportsManager IIRC) to set user roles/perms by folder. They are normally inherited by subfolders though you can override that. The role 'browser' is most appropriate for end users as it allows them to run / view reports but not much more. If you grant a user 'browser' access for folder  but not B then they will not be able to access folder B unless it is either a sub-folder of A (by default granted the same perms as A) or unless B is a sub-folder of another folder the user has 'Browser' access to.

You probably should not be granting domain users any rights directly with-in SQL Server. The various accounts that SQL Server itself uses as well as administrative accounts should be configured with-in SQL Server. End user access to SSRS is controlled by SSRS based on rules entered into SSRS.


Retreive data from two tables of the same structure

The outer-level ORDER BY will probably cause the entire combined row set to be sorted – at least to the extent that will enable the server to find the last 100 TimeStampCome entries starting from the penultimate one. Sorting a million entries cannot be cheap, of course.

I would try reducing the combined set by applying a LIMIT to each leg of the UNION ALL query. Note that an ORDER BY cannot be applied directly to an individual SELECT that is a UNION leg. You will have to use more nesting – either a derived table or a CTE – to apply the ORDER BY and then select from that derived table/CTE. Here is a derived table solution:

SELECT
  ie.*
FROM
  (
    SELECT
      *
    FROM
      (
        SELECT
          *
        FROM
          History
        WHERE
          Station = @station
          AND TimeStampCome <= @till
          AND TimeStampCome >= @from
        ORDER BY
          TimeStampCome DESC
          LIMIT 101
      ) AS s
    UNION ALL
    SELECT
      *
    FROM
      (
        SELECT
          *
        FROM
          Pending
        WHERE
          Station = @station
          AND TimeStampCome <= @till
          AND TimeStampCome >= @from
        ORDER BY
          TimeStampCome DESC
          LIMIT 101
      ) AS s
  ) AS ie
ORDER BY
  TimeStampCome DESC
  LIMIT 100
  OFFSET 1
;

And this is a variation that uses CTEs, if you are using a SQLite version that supports Common Table Expressions:

WITH
  HistoryFiltered AS
  (
    SELECT
      *
    FROM
      History
    WHERE
      Station = @station
      AND TimeStampCome <= @till
      AND TimeStampCome >= @from
    ORDER BY
      TimeStampCome DESC
      LIMIT 101
  ),
  PendingFiltered AS
  (
    SELECT
      *
    FROM
      Pending
    WHERE
      Station = @station
      AND TimeStampCome <= @till
      AND TimeStampCome >= @from
    ORDER BY
      TimeStampCome DESC
      LIMIT 101
  )
SELECT
  ie.*
FROM
  (
    SELECT * FROM HistoryFiltered
    UNION ALL
    SELECT * FROM PendingFiltered
  ) AS ie
ORDER BY
  TimeStampCome DESC
  LIMIT 100
  OFFSET 1
;

Each individual SELECT is using LIMIT 101 rather than LIMIT 100, because at first it is unknown which of them will end up having the latest TimeStampCome entry that will be omitted by the outer OFFSET 1. Therefore, each source row set must provide as much data as necessary for the ultimate SELECT.


Not sure what you mean by "manage"... one of them has to wait for the other.

Imagine you need to change your tires or get an oil change, but your son took the car to the mall.

That is what is happening here. The metadata change has to wait for the data change to commit. You can control which has higher priority, though, by adding a wait option to the switch:

WITH
(
  WAIT_AT_LOW_PRIORITY
  (
    MAX_DURATION = n MINUTES,
    ABORT_AFTER_WAIT = BLOCKERS
  )
);

At the specified timeout you can choose to kill the blockers (BLOCKERS) or die gracefully (SELF). Number of rows in the partition is largely irrelevant (unless you are waiting for ~12M rows to actually update).

See Kendra's post here (do look through the comments) and this background post on low priority.


Note: Before I get into sample code on one way to go about this, I recommend you make every attempt at grasping the programmatic interaction between SQL Server and PowerShell.  It is not hard, and by muscling through a few scripts from given requirements like the ones above your proficiency will exponentially grow.

  1) we have to connect to that particular database/server using ip and the credentials--For this I need powershell to ask user to give the required parameters(such like server/database name/ip and credentials)

Add-Type -Path "C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"

$SqlServerName = Read-Host "Server name"

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)

$SqlServer.ConnectionContext.LoginSecure = $false

$SqlServer.ConnectionContext.Login = Read-Host "Login"
$SqlServer.ConnectionContext.SecurePassword = Read-Host "Password" -AsSecureString

  2) The next step is to get the sql scripts from 3 different folders based on 3 different inputs from user--Here I need powershell to ask user to enter the parameters(Eg:a=1st folder, m=2nd folder and x=3rd folder)

$SqlScripts = @()

while ($true) {
    $ScriptLocation = Read-Host "Enter location (""x"" if no more needed)"
    if ($ScriptLocation.ToLower() -eq "x") {
        break
    }
    $SqlScripts += $ScriptLocation
}

  3) Then the execution of the script.After the execution, then it should again ask user to continue or exit.

foreach ($SqlScript in $SqlScripts) {
    try {
        $SqlServer.Databases["master"].ExecuteNonQuery((Get-Content -Path $SqlScript))
    }
    catch {
        Write-Error $_.Exception
    }
}

As for your last statement "After the execution, then it should again ask user to continue or exit", I'm not quite sure what you're looking to do there.  Ask the user to continue doing what?  Regardless, the above sample code should give you quite a head start on your requirement.  Test this logic out in a non-production environment to ensure it does what you want and rework accordingly.  There are a couple of assumptions here (such as you're not looking for a return from the script, which is why I used ExecuteNonQuery(), I don't know your databases layout so it's going to execute in the context of master, so on and so forth).


Linux Slackware MySQL can't move datadir

The easiest way is to create a symbolic link to new data directory.
When your new data directory is ready to use, stop mysql server and type:

rm -r /var/lib/mysql
ln -s /mnt/C/mysql /var/lib/mysql

Now you can restart mysql server with standard datadir.


Print table documentation with comments?

You can do this with normal DML/DDL.

Create a table with comments:

mysql> create table testcomments (
    -> col1 integer comment 'comment on integer column',
    -> col2 varchar(20) comment 'comment on varchat col');
Query OK, 0 rows affected (0.01 sec)

Select the information from the data dictionary (documentation link):

mysql> select column_name, column_comment
    -> from INFORMATION_SCHEMA.COLUMNS
    -> where TABLE_NAME = 'testcomments';
+-------------+---------------------------+
| column_name | column_comment            |
+-------------+---------------------------+
| col1        | comment on integer column |
| col2        | comment on varchat col    |
+-------------+---------------------------+
2 rows in set (0.00 sec)

mysql>

Add/modify a comment:

mysql> alter table testcomments
    -> change column col1
    -> col1 integer comment 'modified comment on integer column';

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select column_name, column_comment  from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'testcomments';
+-------------+------------------------------------+
| column_name | column_comment                     |
+-------------+------------------------------------+
| col1        | modified comment on integer column |
| col2        | comment on varchat col             |
+-------------+------------------------------------+
2 rows in set (0.00 sec)

mysql>

Modify a table comment:

mysql> alter table testcomments comment 'foo';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

View a table comment:

mysql> select TABLE_COMMENT
    -> from INFORMATION_SCHEMA.TABLES
    -> where TABLE_NAME = 'testcomments';
+---------------+
| TABLE_COMMENT |
+---------------+
| foo           |
+---------------+
1 row in set (0.00 sec)

mysql>


Crashing CLR on SQL Server 2014 (windows 2012R2)

The problem is a locale conflict between the Windows OS and SQL Server (specifically the database where the Assembly is loaded). You can run the following query to see what they are both set to:

SELECT os_language_version,
       DATABASEPROPERTYEX(N'{name of DB where Assembly exists}', 'LCID') AS 'DatabaseLCID'
FROM   sys.dm_os_windows_info;

If they are different then you can definitely get some "odd" behavior, such as what you are seeing. The issue is that:

SqlString includes more than just the text itself: it includes the default collation of the database in which the assembly exists. The collation is comprised of two pieces of information: the locale info (i.e. LCID), and the comparison options (i.e. SqlCompareOptions) which detail the sensitivity to case, accents, kana, width, or everything (binary and binary2).
String operations in .NET, unless explicitly given a locale, use the locale info of the current thread, which is set in Windows (i.e. the Operating System / OS).

The conflict usually occurs when referencing a SqlString parameter without using .Value or .ToString() such that it does an implicit conversion to SqlString. In that case it would cause an exception saying that the LCIDs do not match.

There are apparently other scenarios, such as performing (some / all?) string comparisons, including when using Regex as this case shows (though so far I have not been able to reproduce this).

Some ideas for fixes:

Ideal (expectations will always be met regarding how the comparisons work):

Change either the Windows or SQL Server LCID (default language) so that both match

Less than ideal (the behavior of the Windows locale might not be the same rules for equality and sorting and so there could be unexpected results):

Use the .ToString method or .Value property, which both return the string without the SQL Server LCID so the operations will all be using the OS LCID.

Might help:

Maybe use SqlChars instead of SqlString as it does not bring along the LCID and collation info from SQL Server
Specify that culture doesn't matter via StringComparison.InvariantCulture:

String.Compare(string, string, StringComparison.InvariantCulture) or String.Compare(string, string, StringComparison.InvariantCultureIgnoreCase)
For Regex, specify RegexOptions.CultureInvariant


Oracle 10g dump file push into Oracle 11g database

Your problem might very well have to do with incorrect versions or wrong default connection. Make sure that you use the exp utility from the 10g installation and the imp utility from the 11g installation.
Also make sure that the environment variables like ORACLE_HOME are correct and that PATH is adjusted for the correct ORACLE_HOME/bin

How does your connect string look like?
Is your TNS_ADMIN variable defined? (if not, you might need to have multiple tnsnames.ora files in place, or tnsnames.ora is in a platform specific location (/etc, /var/opt/oracle etc.)) tnsping shows where it gets the sqlnet.ora (next to tnsnames.ora) from.

Even better is to start using expdp and impdp. You can use impdp to directly import your data in the 11g database, using a datasbase link.


CAST() might not be needed, try with and without it.

SELECT student.StudentID
    ,student.NAME
    ,CONCAT (
        cast(ROUND(Count(attendance.AttendanceID) * 100 / 6) AS CHAR(20))
        ,'%'
        ) AS Total
    ,student.WorkshopID
FROM student
LEFT JOIN attendance ON student.StudentID = attendance.StudentID
GROUP BY student.StudentID
    ,student.Name``

The ROUND function returns integer data and CAST is needed to use the result as a string parameter of CONCAT function. CHAR(20) is really arbitrary - you can use any other string data type.


SSIS Data Flow Task Excel to SQL table NULL value will not work for small INT datatype

My original answer was working (changing int to nvarchar) but I ran into another column which contained dates in the excel source file where some cells contained the string "NULL" (I did not want to have dates in a NVARCHAR column.) When SSIS got to this date column it was generating an error because it could not convert the string "NULL" to a date. It was not properly reading the "NULL" as a NULL value, instead it was reading it as a string. The way I was able to resolve this issue is by adding a derived column component to the package that replaced the date field by using the following expression

(DT_WSTR,255)Auth_from_date == "NULL" ? NULL(DT_WSTR,50) : (DT_WSTR,255)Auth_from_date

this is just an IF statement that will change the string "NULL" to an actual NULL value. The date is then passed thru a data conversion component and converted into DATETIME format.


Let me know what to check/verify

You have a performance problem. Follow a performance troubleshooting methodology like Waits and Queues to identify the bottleneck. The linked methodology shows you what to measure and how. Post here the findings and we can help with specific advice based on your actual measurements. As it is is too open and is anybody's guess. Narrowing it down to a specific issue will eliminate guesswork.

After update

The plans are quite different. The old plan had a stream aggregate low on the stack which actually has a bad  cardinality estimate (141k vs. 108k) and the hash math further mispredicts, the other way (35k vs. 108k). The new plan does not have the stream aggregate and has accurate estimates all the way to the top. Of course, this does not explain why the old plan was executing faster.

The bottom scans have a slightly different row number (not significant) but quite different costs: old is 2.49884 (IO 2.28979 CPU 0.20905) vs new 1.59109 (IO 1.53868 CPU 0.0524084). Again would point toward a better 2012 execution (the index rebuild has perhaps reduced fragmentation?).

What is very different is the number of threads: 32 in new (each getting ~23k rows) vs. 8 in old (each getting ~95k rows). The table is fairly narrow. It could be that the large number of threads is actually hurting performance because of much much more frequent cache invalidations. I would try:

eliminate HyperThreading in the new server config (if any) and/or
try the query with a DOP 8.

Noticed your comment:

  Added the execution plan with maxdop 8 Query is actually faster this way

It probably is just CPUs stepping on each other toes. With SSDs in place the IO is probably next to nothing so and the table is definitely too small to warrant 32 scanners. That exchange swap is probably invalidating L1/L2 constantly.


MySQL GIS functions strange results from ST_Distance_Sphere

I think you're getting this wrong. ST_Distance_Sphere calculates the ST_Distance and returns the result after converting the radians to units of radius on a sphere of radius.

  Calculations use a spherical earth and a configurable radius. The optional radius argument should be given in meters. If omitted, the default radius is 6,370,986 meters. An ER_WRONG_ARGUMENTS error occurs if the radius argument is present but not positive.

As a matter of trivia, if you're wondering where that radius comes from -- it's pretty amusing. So let's play

d(A,B) = R a /180

So...

a = ST_Distance( POINT(0,0), POINT(0,1) ). This means a=1.
r = 6370986 meters
pi = 3.141593 ish... Using pi() here

So your call to ST_Distance_Sphere( Point(0,0), Point(0,1) ); is the same as,

SELECT ((6370986 * 1 * pi()) / 180);
+------------------------------+
| ((6370986 * 1 * pi()) / 180) |
+------------------------------+
|            111194.6822984635 |
+------------------------------+
1 row in set (0.00 sec)

So as you can see ST_Distance_Sphere is a Cartesian distance using basic geometry to get to a sphereacle distance. That makes this not a bug. in fact, PostGIS will return the same results.

SELECT ST_DistanceSphere( ST_MakePoint(0,0), ST_MakePoint(0,1) );
 st_distance_sphere
--------------------
    111195.07973463
(1 row)

PostGIS calculates on a sphere using the average mean radius of the WGS84 reference ellipsoid. MySQL uses a totally random value that originates from a constant in PostgreSQL 20 years ago.

In MySQL, this isn't a GIS function. It's a geometry function, you could argue MySQL doesn't support GIS at all as it does not even use Spatial Reference Systems in its calculations. It makes no assumption about an ellipsoidal projection or spatial reference system.


What's the difference between mysql-community-server and mysql-community-server-minimal?

It looks like mysql-community-server-5.7.9-1.el7.x86_64.rpm includes the following additional features (list may not included all) that are not available in the mysql-community-server-minimal-5.7.9-1.el7.x86_64.rpm:

innochecksum
myisam_ftdump
myisamlog — Display MyISAM Log File Contents
myisampack — Generate Compressed, Read-Only MyISAM Tables
mysql_plugin — Configure MySQL Server Plugins
mysql_secure_installation — Improve MySQL Installation Security
mysqld_pre_systemd
mysqldumpslow — Summarize Slow Query Log Files
perror — Explain Error Codes
resolve_stack_dump — Resolve Numeric Stack Trace Dump to Symbols
resolveip — Resolve Host name to IP Address or Vice Versa

I think debugging option is also limited in the mysql-community-server-minimal-5.7.9-1.el7.x86_64.rpm version.


How can I minimize SQL Server data loss in AWS in the event of failure?

Additional to your Full backups you can take transaction log backups every 15 minutes. A transaction log backup contains a list of the changes that happened. During the restore those changes get re-applied.

In the case of a disaster you would restore the newest full backup and then all of the log backups that were taken since that full backup in chronological order. With that you would have a maximum of 15 minutes of data loss. The additional storage is dependent on the amount of changes in your system. Usually it is significantly smaller than another full backup.

You could also add differential backups into the mix: Full backup every 6 hours, differential every 2 hours, log backup every 15 minutes. Most likely that wont safe storage, but it could make the restore a lot faster, depending on your situation of course.

No matter which solution you end up taking, make sure you practice the restore regularly, as it is not completely trivial and a disaster is not the right time to try to figure out how it works.


Capturing mongo replica set state from mongo shell

The output you are trying to capture is the default prompt for the mongo shell in interactive mode.

As at MongoDB 4.0 there isn't a built-in helper to return replica set member status as a string, but you can derive the same status in JavaScript.

For example, create the following as getstate.js:

    var mystate = 'UNKNOWN';

    // Note: isMaster command does not require authentication
    var isMaster = db.isMaster();

    if (isMaster.msg == "isdbgrid") {
        mystate = "mongos";
    } else if (isMaster.ismaster && !isMaster.setName) {
        mystate = 'STANDALONE';
    }

    if (isMaster.setName) {
        if (isMaster.secondary) {
            mystate = 'SECONDARY';
        } else if (isMaster.arbiterOnly) {
            mystate = "ARBITER";
        } else if (isMaster.primary === isMaster.me) {
            mystate = 'PRIMARY';
        }
    }

    print(mystate);

Now invoke mongo using --quiet to suppress any startup messages or warnings:

$ mongo localhost:27017 getstate.js --quiet
PRIMARY

$ mongo localhost:27018 getstate.js --quiet
SECONDARY

$ mongo localhost:27019 getstate.js --quiet
ARBITER


Oracle: calling udf that modifies the data. How to do it right?

In #1 and #2, something uses the output of test_function, but in #3 nothing uses it. The optimizer knows this and does not call test_function. Since test_function isn't called, the side effect of updating the table does not happen.

This is definitely intended behavior.

I rewrote your #3 to the following, and test_function got called (the UPDATE happened). The reason is that the result of test_function was needed for output.

SELECT 1, tf FROM dual
INNER JOIN (SELECT test_function as tf FROM dual)a ON (1=1)
;

However, generally speaking relying on the side effect of a function to do real work from inside a SQL query is not a good idea. The optimizer is always looking for a reason to do as little work as possible, and you might be surprised sometimes to learn that the optimizer sees the necessity of your function differently than you do.


If a MusicFile has multiple Artists and an Artist has multiple MusicFiles then yes you need a link table between the two (as per the answer in your comment). However, if, for example, you only ever want to attach a single Genre to any given MusicFile, then you could just have a field for genre inside the MusicFile table.


Is it possible to write a function in postgresql which directly writes its parameters to a text file?

Using COPY, this boils down to one simple SQL command:

COPY (SELECT 'a','b','c','d','e') TO PROGRAM 'cat >> /path/to/file/my.csv';

You can wrap it into a function if required:

CREATE OR REPLACE FUNCTION public.f_dump_row(varchar,varchar,varchar,varchar,varchar)
  RETURNS void LANGUAGE plpgsql AS
$func$
BEGIN
EXECUTE format($$COPY (SELECT %L,%L,%L,%L,%L) TO PROGRAM 'cat >> /path/to/file/my.csv'$$
             , $1,$2,$3,$4,$5);
END
$func$;

Why the complication with EXECUTE?

How to set column DEFAULT using value of a variable

cat is a standard UNIX utility (shell command).

Your Postgres role needs appropriate privileges. The manual:

  COPY naming a file or command is only allowed to database superusers
  or users who are granted one of the default roles
  pg_read_server_files, pg_write_server_files, or
  pg_execute_server_program, since it allows reading or writing any file
  or running a program that the server has privileges to access.

If you need this for under-privileged roles, consider a SECURITY DEFINER function. But do it properly as instructed in the manual to avoid misuse.

And the target file must be writable by the system user running the Postgres process, typically postgres.

Related:

copy command in postgresql to append data


column timecol new_value timestamp
column spool_extension new_value suffix
SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')||'.csv' spool_extension
FROM dual;

SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')||'.csv' spool_extension -- Solved the problem after I added TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')

Hope this can help someone else who is looking for a solution


How to add setParameter in mongod config?

Here's a working example that I just tested using 2.6.6 - I specified two parameters just to show how it is done for reference:

storage:
    dbPath: "/data/db/test"
systemLog:
    destination: file
    path: "/data/db/test/mongodb.log"
processManagement:
    fork: true
setParameter:
    enableLocalhostAuthBypass: 1
    enableTestCommands: 1


Failed SQL Login - Source server same as destination cannot locate SPID SQL Server 2014

I've seen this before.  I believe that it was due to autoclose.  But I could not recall from memory.  Credit here goes to Aaron Bertrand.

  Usually this means the login's default database is offline (perhaps due to AutoClose) or no longer exists.  Resolve by fixing the missing database, or changing the login's default database using ALTER LOGIN (for older versions, use sp_defaultdb, which is now deprecated).  This is reported as state 16 prior to SQL

  Server 2008. Error: 18456, Severity: 14, State: 40. Login failed for
  user ''.  Reason: Failed to open the explicitly specified database.

Blog post regarding SQL login errors


What happens behind the scenes when adding a nullable column in SQL Server?

No.

This is a metadata only change in SQL Server 2008. See Size-of-data operations when adding and removing columns

SQL Server 2012 can also add non nullable columns with a default without having to update all rows however there are also some circumstances in 2012 in which adding a nullable column is no longer just a metadata change.


PostgreSQL streaming replication with slots works for one slave, but not for another slave

Community Wiki answer generated from a comment left by the question author. Feel free to edit to improve

Nevermind, I fixed it. There needs to be one replication slot per slave. I added another replication slot "master2". So slave #1 is using the "master" replication slot and slave #2 is using the "master2" replication slot. Works fine now.


How can I lock rows FOR UPDATE without returning data over the connection?

About the goal to avoid deadlocks: see my answer to your related question:

Why am I getting a deadlock for a single UPDATE query?

About the question in the title:

You can use an empty SELECT list with a locking clause:

SELECT FROM foos WHERE owner_id = 123 AND unread ORDER BY id FOR UPDATE;

Still locks all selected rows, but does not return any data. Still returns empty rows, though, so you still get a row count like usual.

Or, inspired by a_horse's comment:

SELECT count(*) AS locked_rows
FROM  (SELECT FROM foos WHERE owner_id = 123 AND unread ORDER BY id FOR UPDATE) sub;

This way you get a single result row with the number of locked rows. (You can abort in case of 0 rows.) Rows are locked all the same. The SELECT list in the subquery can still be just empty (shortest, cheapest).

Aside: and unread=true is just a noisy, less efficient way of saying AND unread.

If unlocked can be NULL, consider to use unread IS NOT true in both statements.


Backup/restore using OPENDATASOURCE OR OPENROWSET

You can automate all of below using SQL Agent.

There are many alternatives that you can use :

SQLCMD ==> sqlcmd -E -S server_name -q "backup command"
 You can use it with dynamic sql to connect to different servers for backup and restores.
PowerShell ==> There are tons of scripts found on internet that will tell you how to do it. SimpleTalk has -- Backup and Restore SQL Server with the SQL Server 2012 PowerShell
SSIS ==> Automate Database Restore to Remote Instance with SSIS

When doing backup - make sure to use compression in sql server 2008 R2 and up which is even supported in standard edition. Also to speed up restore, use Instant File Initialization.


How to get server cpu, memory, network usage from SQL Server

If you are running SQL Server 2008 and up, then you can use Performance Data Collector on SQL Server. This will get you all the metrics (and even more) that you need.

Below is from my TEST server :

If you want for webserver, then your best bet is to use Powershell. Even you can generate nice reports with charts out of your collection.


Difference between Seek Predicate and Predicate

Let's throw one million rows into a temp table along with a few columns:

CREATE TABLE #174860 (
PK INT NOT NULL,
COL1 INT NOT NULL,
COL2 INT NOT NULL,
PRIMARY KEY (PK)
);

INSERT INTO #174860 WITH (TABLOCK)
SELECT RN
, RN % 1000
, RN % 10000
FROM
(
    SELECT TOP 1000000 ROW_NUMBER () OVER (ORDER BY (SELECT NULL)) RN
    FROM   master..spt_values v1,
           master..spt_values v2
) t;

CREATE INDEX IX_174860_IX ON #174860 (COL1) INCLUDE (COL2);

Here I have a clustered index (by default) on the PK column. There's a nonclustered index on COL1 that has a key column of COL1 and includes COL2.

Consider the following query:

SELECT *
FROM #174860
WHERE PK >= 15000 AND PK < 15005
AND COL2 = 5000;

Here I'm not using BETWEEN because Aaron Bertrand is hanging around this question.

How should SQL Server optimizer that query? Well, I know that the filter on PK will reduce the result set to five rows. SQL server can use the clustered index to jump to those five rows instead of reading through all million rows in the table. However, the clustered index only has the PK column as a key column. Once the row is read into memory we need to apply the filter on COL2. Here, PK is a seek predicate and COL2 is a predicate.

SQL server finds five rows using the seek predicate and further reduces those five rows to one row with the normal predicate.

If I define the clustered index differently:

CREATE TABLE #174860 (
PK INT NOT NULL,
COL1 INT NOT NULL,
COL2 INT NOT NULL,
PRIMARY KEY (COL2, PK)
);

And run the same query I get different results:

In this case, SQL Server can seek using both columns in the WHERE clause. Exactly one row is read from the table using the key columns.

For one more example consider this query:

SELECT *
FROM #174860
WHERE COL1 = 500
AND COL2 = 3545;

The IX_174860_IX index is a covering index because it contains all of the columns needed for the query. However, only COL1 is a key column. SQL Server can seek with that column to find the 1000 rows with a matching COL1 value. It can further filter down those rows on the COL2 column to reduce the final result set to 0 rows.


Very slow DELETE in PostgreSQL, workaround?

I had a similar problem. As it turns out, those ON DELETE CASCADE triggers were slowing things down quite a bit, because those cascaded deletions were awfully slow.

I solved the problem by creating indexes on the foreign key fields on the referencing tables, and I went from taking a bunch of hours for the deletion to a few seconds.


Optimize query to minimize tmp copying time

Review indexing on cours table as you're getting a full table scan there.

http://mysql.rjweb.org/doc.php/ricksrots

Great indexing tips there ^


Columnstore index query performance based on number of columns in SELECT statement

So I want to know, does number of columns affect performance of a
  query for a partitioned table with clustered columnstore index?
  If yes, then is there any way to measure performance difference?

Yes, the number of columns is often a significant factor for columnstore query performance as shown in shown in the last line your STATISTICS IO output. This is because columnstore allows data for only the columns needed by the query to be scanned. The elapsed time difference (247ms versus 52ms) indicates query performance is proportional to the number of columns (as I would expect), with elapsed time being the primary measure of performance.

Although one can generally use elapsed time as the primary performance indicator, there are other factors to consider. Cached data or other activity on shared resources could affect timings.  Both plans are serial in this case so elapsed times are comparable for relative performance, with the assumption of a repeatable test in an isolated environment. However, if one plan were serial and the other parallel, the parallel plan could consume more CPU time than the wall clock time and result in a biased or incorrect conclusion (e.g. the query with more columns runs faster).


Can I defer a configuration change until the next time SQL Server is restarted?

Create a stored procedure in master:

USE master;
GO
CREATE PROCEDURE dbo.FixMaxDop
AS
BEGIN
  SET NOCOUNT ON;
  EXEC sys.sp_configure N'show advanced options', 1;
  RECONFIGURE WITH OVERRIDE;
  EXEC sys.sp_configure N'max degree of parallelism', 8;
  RECONFIGURE WITH OVERRIDE;
  EXEC sys.sp_configure N'show advanced options', 0;
  RECONFIGURE WITH OVERRIDE;
  -- optionally, drop or alter the procedure so it doesn't run again
END
GO

Now, you can set the procedure to be a startup procedure (see here and here):

EXEC sys.sp_procoption N'dbo.FixMaxDop', 'startup', 'true';

If you only want this to run once, then you'll want to flip that option off after the configuration option has been set correctly:

EXEC sys.sp_procoption N'dbo.FixMaxDop', 'startup', 'false';

Alternatively, you can do as MichaelK suggested - create a job with a step that calls the above procedure, make sure it is owned by sa or someone in the sysadmin role, and then set it to start when SQL Server Agent starts. However, you're relying heavily on SQL Server Agent being set to start automatically (it might not be, and that might change before the next restart), and that Agent will start successfully after the next service restart (it might fail for a variety of reasons, including service account changes). You also can't create a schedule that runs when Agent starts and only runs once (it's one or the other), so you'd have to do something else like script an additional job step that disables the job or removes the schedule.

Not really a big deal for the procedure not to run again, I suppose, unless you make some other manual change later and forget about this - then you'll revert to this explicit setting the next time you restart after that change...


How to get rows only contains multiple ID using SQL?

You can try this:

SQL> select * from dummy;

    ID    FOO
---------- ----------
     1    100
     1    200
     1    200
     2    300
     2    300
     3    200
     4    200

7 rows selected.

SQL> select * from dummy where id in (select id from dummy having count(*) > 1 group by id);

    ID    FOO
---------- ----------
     1    100
     1    200
     1    200
     2    300
     2    300

SQL>


Referential Integrity in schema, cascading the renaming

Referential integrity is one, and is an attribute of the table data, but it is not an attribute of the table's schema. A table's schema shouldn't be changed in a cascading style, because it can break existing working code. A table's column is not used only in foreign keys, triggers, etc. It's used in scripts, maintenance plans, jobs, applications, dlls, even web pages etc. If you change an object's name you can create a mess, that's why it shouldn't be done automatically. This is something you want to control.

This is where refactoring tools can enter the scene. Eg for MS SQL Server there's Visual Studio db projects that can help here, but not by much. There may be tools that aid in refactoring the db objects and fix anything automatically, but they are not part of any standard db package.


AWS RDS showing large growth in Write Ops/sec, despite low # DB connections?

If those processes are the only DB Connections in the MySQL Instance, then you can only look to two sources of Write I/O

The VM housing MySQL

Since you are using Amazon MySQL RDS, you can access only the MySQL Instance inside their provisioned VM. There can be lots of reads from MySQL just for their internal purpose. Note that process ID 7 is rdsadmin. They could be rolling up stats on disk for MySQL Usage to report back to the RDS API they provide to have stats to present.

I am sure you could eliminate that by moving our data to MySQL on an Amazon EC2 instance.Of course, you would have to be responsible for your own stats.

InnoDB Architecture

InnoDB has many moving parts that require writes.

Buffer Pool

Data and Index Pages From InnoDB tables That Have Been Modified (Dirty) Must Be Systematically Written to the Double Write Buffer within System Tablespace (a.k.a. ibdata1)
Insert Buffer, which are changes to Secondary Indexes, Must Be Systematically Written to the Insert Buffer within System Tablespace

Log Buffer: When full, the changes within the Log Buffer must written to the Redo Logs (a.k.a. ib_logfile0, ib_logfile1). In a high write system, you should increase innodb_log_buffer_size to 256M to reduce writes to Redo Logs.
Rollback Segments / Undo Space: There are 1023 rollback segments inside System Tablespace. Every time you do INSERTs, UPDATEs, and DELETEs, one transaction will use a single Rollback Segment to save the previous state of data the transaction is about to change. There are internal threads dedicated to cleaning up the UNDO space. Therefore, lots of big writes (or even little writes) will require disk I/O from those internal threads that do the UNDO space cleanup.

I have written about this before

Jul 21, 2012 : InnoDB - High disk write I/O on ibdata1 file and ib_logfile0
May 17, 2012 : why is mysql still writing data two hours after all transactions have stopped


Disable dbo schema fallback when table doesn't exist in default schema

In a word, no this is not possible.

I've struggled to find an official line on this, but the name resolution algorithm in SQL Server is something like the following, as defined by Bob Beauchemin in Dude, Where's my table?

If batch or dynamic SQL:

Look in 'sys' schema for system objects
Look in user's default schema
Look in dbo schema

If procedural code:

Look in 'sys' schema for system objects
Look in procedure schema
Look in dbo schema

In your case, SQL Server is failing to find an MS Shipped object firstly, then failing to find the object in the user schema (DataTeamSchema), and finally it finds the object in the dbo schema.

One way around this would be to change the original object to a schema other than dbo, as the dbo schema is a special case in terms of object-name resolution.

There does not appear to be a documented way to avoid this behaviour.


Why is ASC 100x faster than DESC and what can I do about it?

Ideal for those queries would be in index on (court_id, id), and with the columns in that order.  It should be extremely fast in either direction.  And once you have it, you should be able to get rid of the plain index on court_id as it wouldn't be much good anymore.


Rolling Back DB Changes After Deployment Edge Cases

Well I think that having a test system that supports the workload, data and edge cases you have in production should mitigate the need to roll back. If you can properly test the deployment in a similar system, you should have very few scenarios where you have to roll back.

That said, you can also do many things to make your code changes backward compatible. I wrote a series of tips on this a while back, including one where I change a relationship:

http://www.mssqltips.com/sqlservertip/2074/make-your-sql-server-database-changes-backward-compatible-when-changing-a-relationship/

http://www.mssqltips.com/sqlservertip/2071/make-your-sql-server-database-changes-backward-compatible-when-renaming-an-entity/

http://www.mssqltips.com/sqlservertip/2050/make-your-sql-server-database-changes-backward-compatible-when-dropping-a-column/

http://www.mssqltips.com/sqlservertip/2035/make-your-sql-server-database-changes-backward-compatible-when-adding-a-new-column/

Every application is different, so you're not going to find many "industry standard" solutions to this. But you should be able to whip together plenty of common sense that will reduce or eliminate your need to worry about it.

And no, I'm not saying you shouldn't have a rollback plan, but when changes are properly planned and tested, the rollback plan shouldn't require a significant effort to devise, and should rarely - if ever - be used.


How to build a table for a private messaging system that supports replies?

Hierarchic Self-Referential Data

Syntax shown using the free and open source database, PostgreSQL

You need a self-referential table, which creates hierarchy in the database. This is how it looks.

CREATE TABLE messages (
  ts_entered  timestamp DEFAULT now(),
  reply_to    int       REFERENCES messages,
  id          int       PRIMARY KEY
                        GENERATED BY DEFAULT AS IDENTITY,
  content     text
);
CREATE INDEX ON messages (reply_to, id);

Notice that reply_id reference an id on the very table. In a more enterprisey example, we'd probably make it EXCLUDE the same id.

Then we enter test data like this.

INSERT INTO messages ( reply_to, id, content )
VALUES
  ( null, 1, 'SYN'),
  ( 1,    2, 'SYN-ACK'),
  ( 2,    3, 'ACK'),
  ( null, 4, 'We should give free advertising to PLAN EXPLORER'),
  ( 4,    5, 'Admins should disclose their affiliations with products'),
  ( 5,    6, 'BANNNN'),
  ( 4,    7, 'Plan Explorer only works on one database'),
  ( 7,    8, 'BANNNN'),
  ( 4,    9, 'Plan Explorer does not support Linux'),
  ( 9,   10, 'BANNNN'),
  ( 4,   11, 'Plan Explorer only does what decent databases already do'),
  ( 11,  12, 'BANNNN')
;

Now in order to query this, we need a RECURSIVE CTE.

WITH RECURSIVE t(reply_to, id, content, root, level)
AS (
  SELECT reply_to, id, content, ARRAY[id], 0
  FROM messages
  WHERE reply_to IS NULL
  UNION ALL
    SELECT messages.reply_to, messages.id, messages.content, root + ARRAY[messages.id], t.level+1
    FROM t
    JOIN messages
      ON (messages.reply_to = t.id)
)
SELECT * FROM t
ORDER BY root;

And you're done..

 reply_to | id |                         content                          |   root    | level
----------+----+----------------------------------------------------------+-----------+-------
          |  1 | SYN                                                      | {1}       |     0
        1 |  2 | SYN-ACK                                                  | {1,2}     |     1
        2 |  3 | ACK                                                      | {1,2,3}   |     2
          |  4 | We should give free advertising to PLAN EXPLORER         | {4}       |     0
        4 |  5 | Admins should disclose their affiliations with products  | {4,5}     |     1
        5 |  6 | BANNNN                                                   | {4,5,6}   |     2
        4 |  7 | Plan Explorer only works on one database                 | {4,7}     |     1
        7 |  8 | BANNNN                                                   | {4,7,8}   |     2
        4 |  9 | Plan Explorer does not support Linux                     | {4,9}     |     1
        9 | 10 | BANNNN                                                   | {4,9,10}  |     2
        4 | 11 | Plan Explorer only does what decent databases already do | {4,11}    |     1
       11 | 12 | BANNNN                                                   | {4,11,12} |     2
(12 rows)


How to restore data after MySQL Upgrade?

Open up an Command Line Window as Administrator. Then, follow these steps

STEP 01 : Shutdown MySQL

net stop mysql

STEP 02 : Get the MySQL 5.5 Data into the MySQL 5.6 Data Folder

cd "C:\ProgramData\MySQL\MySQL Server 5.6"
move data data_empty
mkdir data
cd "C:\ProgramData\MySQL\MySQL Server 5.6\data"
xcopy /s "C:\ProgramData\MySQL\MySQL Server 5.5\data\*.*" .

STEP 03 : Upgrade All MySQL 5.5 Files in the MySQL 5.6 Folder

cd "C:\ProgramData\MySQL\MySQL Server 5.6\bin"
mysql_upgrade --upgrade-system-tables

STEP 04: Startup MySQL

net start mysql

Review These Steps

Give it a Try !!!


what's practical example / use case SELECT SLEEP() for DBA

I have used it to create backups where I sleep until I can get a process ID from the processlist that is holding a database lock. I mentioned this idea back on Apr 17, 2011 in my answer to the post How can I optimize a mysqldump of a large database?

I wrote the answer to another post Jun 20, 2012 that has the actual code where I demonstrated the use of SELECT SLEEP (Safest way to perform mysqldump on a live system with active reads and writes?)

I have done things like this in the OS making physical backups using bash. I simply did the same in MySQL itself.


Inserting data into a second table with some time lag

Given the constraints of Express (http://msdn.microsoft.com/en-us/library/cc645993(v=SQL.110).aspx), your best option is to fake an agent job by using Windows Scheduled Tasks and call SQLCMD on a stored procedure every n units of time.  Here is a basic SQLCMD usage rundown: http://msdn.microsoft.com/en-us/library/ms180944.aspx.


How do I select distinct on one column and return all other columns?

Some SQL databases let you do something like:

SELECT
  DISTINCT ON(Version)
  FirstName, LastName
  /* INTO NewTable */
FROM
  Table ;

This "DISTINCT ON(x)" is implemented only by a limited number of databases. I don't think it is implemented by "Access", and I would think that most probably is not implemented by the "dBase IV" interface.

The SQL used by Access (don't know if the dBase IV variant will work) might let you do something equivalent:

SELECT
    Version, first(FirstName) AS FirstName, first(LastName) AS LastName
FROM
    (SELECT
       *
    FROM
         TableName
    ORDER BY
          Version  /* and whichever columns make sense for your purpose */
    ) AS OrderedTable
GROUP BY
    Version ;

First is an aggregate function, that, together with GROUP BY, will make sure that you get one value for each group. This value will be the "first one found for the group", so you need to order first your table in a sensible way.

If none of these forms work... My guess is that you will have to solve the problem outside of SQL, getting a RecordSet into your program, looping checking, and inserting into NewTable.


PAGEIOLATCH wait and Page Life Expectancy very low

It seems that you haven't reproduced the question from the training materials here with complete accuracy.
Assuming that the question was about PAGELATCH_** waits, and that the latched pages are in the tempdb database, then those waits can be a common sign of tempdb allocation contention.  And the most common solution to that is to increase the number of tempdb files.
See this Microsoft Support article:
Recommendations to reduce allocation contention in SQL Server tempdb database

To improve the concurrency of tempdb, try the following methods:

Increase the number of data files in tempdb to maximize disk bandwidth and reduce contention in allocation structures. As a general rule, if the number of logical processors is less than or equal to eight (8), use the same number of data files as logical processors. If the number of logical processors is greater than eight (8), use eight data files. If contention continues, increase the number of data files by multiples of four (4) up to the number of logical processors until the contention is reduced to acceptable levels. Alternatively, make changes to the workload or code.

If we already suspect tempdb contention (because of the latch waits), then PLE = 30 could be caused by the heavy tempdb usage (if you think of lots of tempdb pages being loaded into memory and released repeatedly, driving down PLE).
Thus option 1 would most likely be the correct answer, although more information would be needed to confirm (for example, knowing that the latch waits are in fact in tempdb).


Is storing data that can be inferred go against data normalization?

Storing an attribute derived from a different table does introduce some risk of inconsistent data, if you fail to update projects after updating jobs for some reason, so you need to develop means to maintain integrity, e.g. by adding a trigger to jobs.

It does not necessarily goes against normalization principles per se, because you can model the project entity to include the last_used attribute and then your projects table remains in the same normal form as it was before.

Storing a bit of redundant data is a valid optimization technique when warranted. Computed columns, materialized views etc. fall in that category. If you exhaust other ways to make your application perform according to the specifications, you can use denormalization, knowing and mitigating its risks.


Why evaluates "IS NULL" in SSMS interactive mode to "TRUE" but the same statement being executed in a SP to "FALSE"?

Initialize @OUT__USER_ID with NULL, since it is probably being set to a non-null value earlier in your script somewhere.


Set Simple and shrink the .ldf without having to re-create...I've done it many times over the years, although its been awhile


If I understand your requirements correctly, you want to join the other table on polyname and then get the max() of poly_version, like

SELECT rule_name, rule_version, polyname, state, max(poly_version)
  FROM rule r JOIN gtest_version g ON r.polyname = g.polyname
 GROUP BY rule_name, rule_version, polyname, state
 ORDER BY rule_name, rule_version DESC;

However, your query and description does not tell how you want to pick the right row from the rule table.  My query will return all of these, while your DISTINCT ON will not...


This command is not a SQL server command, as others have commented. However, if you did need to fetch 3 lines into 3 different sets of variables, you could do so using:

FETCH ABSOLUTE 1 FROM cursor_name INTO @Variable1;

FETCH ABSOLUTE 3 FROM cursor_name INTO @Variable3;

FETCH ABSOLUTE 2 FROM cursor_name INTO @Variable2;

FETCH ABSOLUTE 1 FROM cursor_name INTO @Variable1;

Remember that each FETCH always resets the current row to the position of the cursor. This requires that you declare your cursor like: DECLARE cursor_name SCROLL CURSOR FOR .... Careful here though, this requires your SQL statement for the cursor declaration to have an ORDER BY to ensure you receive the cursor in an expected order, if that is important.

Lastly - try your hardest to NOT use cursors! Especially those that require scrolling. They are evil by nature, have black souls and each time you use one Edgar Codd turns over in his grave. OK, maybe not that bad, but you get what I mean :-)

EDIT:

Actually a better implementation would be to use a variable instead of the number after the ABSOLUTE key word, so your loop would look like:

WHILE @@FETCH_STATUS = 0
BEGIN
    FETCH ABSOLUTE @Ctr FROM contact_cursor INTO @var1;

    SELECT @Ctr = @Ctr + 2
    FETCH ABSOLUTE @Ctr FROM contact_cursor INTO @var2;

    SELECT @Ctr = @Ctr - 1
    FETCH ABSOLUTE @Ctr FROM contact_cursor INTO @var3;

    SELECT @Ctr = @Ctr - 1
    FETCH ABSOLUTE @Ctr FROM contact_cursor INTO @var1;

    --Increment the counter
    SELECT @Ctr = @Ctr + 1
END


Mongo shell 3.2 has different results on sh.isBalancerRunning() as mongo shell 3.4

The difference is that 3.4 balancing work is done by config server primary and at 3.2 it was done by one of the mongos services.

To prevent "older" (<3.4) mongos services to start balancing work, config server (>=3.4) makes "lock" document to the local collection, what "states" that one of the mongos services have "balancing lock" set.

That document doesn't prevent (>=3.4) config server to do balancing. That is the reason why older mongos "report" that there is always balancing ongoing!


SQL Job Fails Logging In to Integration Services

In SQL Server 2012, you should typically use the SSIS Catalog (database name is SSISDB) for storing your packages for execution. You can still store them in MSDB, but this is not recommended.

Double-check your SQL Agent job that it is not trying to connect to the SSIS catalog to execute the package, in which case it will fail because A) you probably don't have access to SSISDB (it may not even be configured), and B) the package is not in SSISDB, but MSDB.

Check the agent job and confirm that the package source is set to SQL Server and not SSIS Catalog to verify you're trying to run from the correct location.

Also, if you can post the full details of the error message it will help to identify your root issue quicker.

Link


Microsoft Baseline Configuration Analyser on Production

We had the same debate at our office before installing them as we also use the Best Practice Analyzer and the Baseline Security Analyzer as well. We use them once a year to baseline our systems and to check for problems/changes compared to previous baselines that we have taken.

I have never had a problem with them personally, but we never run it on an active Production machine - we normally run it against the servers once we have performed our annual DR test so they are technically not in use at the time.

They are useful tools, and can give you a good hint into what you should be looking for. However, they should not be taken as gospel and anything it comes up with really shouldn't be much of a surprise to you.

Sometimes you will get systems that require a precise configuration to get an application to work, sometimes this requires to not follow a best practice - but you should have documented this anyway so that when the analyzer raises it you already know why it is like that.

Even if you do decide to run the tools - don't go changing things before you fully understand the impact that it will have - sometimes something that seems so small can have a very large impact. Remember, anything that is comes up with is really just a recommendation and even though in the main they are good practices to follow ultimately it is you who decides whether it is feasible to do so or not.

Also you mention running these on a regular basis - these tools are just a guideline and should not be used as your sole way of checking - on-going monitoring of your systems, permissions and access in conjunction with a well implemented change management process will give you a better indication of problems before they arise.


rn cannot be referenced in WHERE because it's defined in the SELECT clause. Try using a subquery instead:

SELECT *
FROM (
    select payee_id,start_dt,
           row_number() over(partition by payee_id order by start_dt) as rn
    from xxx
) AS sub
where rn = 1;

For more reading, google "logical query processing order".


Meaning of FETCH_CURSOR jconnect_implicit_1 in Sybase

Even if fetch size is set to 1, it doesn't mean a cursor will be created for each row; it simply means the driver will request rows of an open cursor be sent to the client one by one instead of in batches. Still inefficient, but not that inefficient. However, there's no indication there that fetch size is set to 1; jconnect_implicit_1: seems to be simply an automatically generated name of a  bind variable. I believe Sqoop fetch size can be controlled with the --fetch-size command line argument.


WHERE CONTAINS (Note, ' "word*" ' );

Assuming "word" is the word that you are actually searching for.


I think the behavior is that the CTE is "expected" to be evaluated multiple times, but it doesn't always work that way (e.g. it is not guaranteed). There are probably a lot of variables that dictate the behavior one way or the other. Can you share an actual execution plan?


Like @RolandoMySQLDBA said, this is a Microsoft SQL Server script.  Find a SQL Server that you can play with, create a new Database on that instance called [Sunwave], then run your script above.  I don't believe you really wanted to migrate this database into MySQL.  I'm thinking this might be another case of the usual MSSQL =? MYSQL confusion that us multi-RDBMS admins encounter.


This may appear extrememly convoluted by here is the query you need

select * from
(select count(1) dupcount,emp_name,dept_name from table_a group by emp_name,dept_name) as a
left join
(select count(1) dupcount,emp_name,dept_name from table_b group by emp_name,dept_name) as b
on a.emp_name = b.emp_name and a.dept_name = b.dept_name where a.dupcount <> IFNULL(b.dupcount,0);

I loaded you sample data and I got this:

mysql> select * from
    -> (select count(1) dupcount,emp_name,dept_name from table_a group by emp_name,dept_name) as a
    -> left join
    -> (select count(1) dupcount,emp_name,dept_name from table_b group by emp_name,dept_name) as b
    -> on a.emp_name = b.emp_name and a.dept_name = b.dept_name where a.dupcount <> IFNULL(b.dupcount,0);
+----------+----------+------------------------+----------+----------+-----------+
| dupcount | emp_name | dept_name              | dupcount | emp_name | dept_name |
+----------+----------+------------------------+----------+----------+-----------+
|        2 | amar     | finance                |        1 | amar     | finance   |
|        1 | anthony  | information_technology |     NULL | NULL     | NULL      |
+----------+----------+------------------------+----------+----------+-----------+
2 rows in set (0.02 sec)

mysql>

Here is it without the dupcount values

mysql> select a.emp_name,a.dept_name,b.emp_name,b.dept_name from
    -> (select count(1) dupcount,emp_name,dept_name from table_a group by emp_name,dept_name) as a
    -> left join
    -> (select count(1) dupcount,emp_name,dept_name from table_b group by emp_name,dept_name) as b
    -> on a.emp_name = b.emp_name and a.dept_name = b.dept_name where a.dupcount <> IFNULL(b.dupcount,0);
+----------+------------------------+----------+-----------+
| emp_name | dept_name              | emp_name | dept_name |
+----------+------------------------+----------+-----------+
| amar     | finance                | amar     | finance   |
| anthony  | information_technology | NULL     | NULL      |
+----------+------------------------+----------+-----------+
2 rows in set (0.00 sec)

mysql>

Give it a Try !!!


Creating a database under a different owner

I found a solution involving a few extra steps.  The "tenant_admin" role is still created the same way, but it is now used as follow:

postgres=> SET ROLE tenant_admin;
SET
postgres=> CREATE ROLE "owner3";
CREATE ROLE
postgres=> GRANT "owner3" TO "tenant_admin";
GRANT ROLE
postgres=> CREATE DATABASE "Tenant3" OWNER "owner3";
CREATE DATABASE
postgres=> REVOKE "owner3" from "tenant_admin";
REVOKE ROLE


Postgresql wal saving for standby servers that go offline

Set up WAL archiving. If the master no longer retains enough WAL your replica will fall back to its archive_command.

See the documentation and this post I wrote earlier.


If your goal is to look at every row in A and do something with it, there is a much more efficient way.  (It seems that B is merely there to see what you have already processed.)

The reason for it getting slower is that it has to do more work as it gets farther into A -- namely skipping over the rows it has processed.  A processed flag might suffer the same malady.

So...

Walk through A processing chunks as you go.  Then remember where you left off so that the next 10000 will be right there, no searching.  I discuss that in more detail with an eye to DELETEing, but it can be adapted for other purposes:  http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks  With that, B is unnecessary.

Partitioning

If you are thinking about partitioning the data by months, I have to ask you "Why?".  Here are some answers:

For performance?  You won't get any.
For rapid deletion of "old" data?  This is a good use case, but be sure to use PARTITION BY RANGE(...) and include the year, too.  More:  http://mysql.rjweb.org/doc.php/partitionmaint


YYYY-MM-DD has changed to YYYY-DD-MM after moving SQL Server to new domain how do I switch back?

The Windows setting do not affect SQL Server parsing dates. There is a server level default language that can be overridden by one set at the login level.

As it stands, the "safe" format for SQL Server is yyyymmdd anyway. Try to use that

From CREATE LOGIN

  Specifies the default language to be assigned to the login. If this option is not included, the default language is set to the current default language of the server. If the default language of the server is later changed, the default language of the login remains unchanged.

For the server, you'd use "default language" via sp_configure

And you can run SET LANGUAGE or SET DATEFORMAT anytime you want of course


I want to make a new column based on another column

Use DECODE:

SELECT ID, PROGRAM_ID, DECODE(PROGRAM_ID, 666, 0, 667, 1, PROGRAM_ID)     AS PATTERN
FROM (
  SELECT ID, PROGRAM_ID
  FROM YOURTABLE
);

Documentation link to DECODE().

Using CASE, alternatively:

SELECT ID, PROGRAM_ID,
       CASE PROGRAM_ID WHEN 666 THEN 0
                       WHEN 667 THEN 1
                       ELSE PROGRAM_ID
       END AS PATTERN
FROM (
  SELECT ID, PROGRAM_ID
  FROM YOURTABLE
);

Example SQL Fiddle here.


You can do it using powershell.

$serverName = "servername\instanceName"
$outputFolder = "D:\data\"

## load the AMO and XML assemblies into the current runspace
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") > $null
[System.Reflection.Assembly]::LoadWithPartialName("System.Xml") > $null
$dateStamp = (get-Date).ToString("yyyyMMdd")

## connect to the server
$svr = new-Object Microsoft.AnalysisServices.Server
$svr.Connect($serverName)
foreach ($db in $svr.Databases)
{
    write-Host "Scripting: " $db.Name
    $xw = new-object System.Xml.XmlTextWriter("$($outputFolder)DBScript_$($db.Name)_$($dateStamp).xmla", [System.Text.Encoding]::UTF8)
    $xw.Formatting = [System.Xml.Formatting]::Indented
    [Microsoft.AnalysisServices.Scripter]::WriteCreate($xw,$svr,$db,$true,$true)
    $xw.Close()
}
$svr.Disconnect()

References :

Automating the Scripting of an SSAS database
PowerShell to Script SQL Analysis Service Database(XMLA Script) and ReApply on a different Server
SQL Server Analysis Services Backup in Powershell


To make a LVM backup of a MySQL database, would I have to reduce the logical volume size?

Unfortunately, yes, you'll need to reduce the size of your root LV.

(You could reduce the size of swap, too, but that's already a fairly small volume)

Even if you can use that 4MB, a snapshot of your root volume will become invalid quite quickly when LVM runs out of "free" disk space.

You get prompted with a text box asking how much of your disk to use when you do an Ubuntu install (even if you select "use entire disk"). If you're building systems to muck around on, or virtual machines, I recommend setting this to 90% (leaving 10% for LVM) - you probably won't miss the space, and you never know when it'll come in handy.

The mechanics of how to shrink the root volume aren't too hard to find on the web, but questions about that are probably best suited to unix.se.

Update: Make sure you don't rely on LVM as your only backup strategy - if you change enough data on disk and your snapshot runs out of free LVM space to use, the snapshot will simply stop working. Using LVM is one of the only ways you can take file-based backups of MySQL (esp. in conjunction with FLUSH TABLES WITH READ LOCK) without shutting down the server, though, which is quite handy.


Decision Tree English Rules and Dependency Network in MS SSAS

I have found a way to get the list of ALL English Rules from the Mining Structure.

SELECT * FROM [MyModel].CONTENT WHERE [CHILDREN_CARDINALITY] = 0

[CHILDREN_CARDINALITY] = 0 defines the DT leaves only.

The following links may be helpful: Decision Trees Model Query Examples, Mining Model Content for Decision Tree Models

"FLATTENED" keyword will split [NODE_DISTRIBUTION] outcomes into separate rows.

SELECT FLATTENED [MODEL_CATALOG],[MODEL_NAME],[NODE_CAPTION],[CHILDREN_CARDINALITY],[NODE_DESCRIPTION],[NODE_PROBABILITY],[NODE_DISTRIBUTION],[NODE_SUPPORT] FROM [MyModel].CONTENT WHERE [CHILDREN_CARDINALITY] = 0 ORDER BY [NODE_SUPPORT] DESC


Simply inserting to temp table and failing

The error is happening because the stored procedure also has a #temp table or table variable and inserts into it using insert...exec. You can see it by looking at its OBJECT_DEFINITION():

insert into @countab (pendingcmdcount)
  exec @retcode = sys.sp_MSget_repl_commands

So basically you asking for this (pseudo-code):

INSERT INTO #outer EXEC
  (SELECT * FROM (INSERT INTO #inner EXEC ... ));

This nesting of insert...exec calls is prohibited in SQL Server, for reasons that are unknown/undocumented. The typical workaround is:

SELECT * INTO #UndistributedCmds
  FROM OPENQUERY([LoopbackLinkedServer], N'EXEC procedure...');

You'll need a linked server that references the local server machine, and it will need the attribute "data access" set to true...


Import in postgres json data in a csv file

The PostgreSQL COPY command is seldom ideal, but it often works. For reference there are better methods to figure this out than guessing.

CREATE TEMP TABLE baz AS
  SELECT 1::int, '{"a":"b"}'::jsonb;

This is your exact sample data. Now we can test different settings..

# COPY baz TO STDOUT;
1   {"a": "b"}

COPY baz TO STDOUT DELIMITER ',';
1,{"a": "b"}

You'll see that the above generates the exact data your questioning...

COPY baz TO '/tmp/data.csv' DELIMITER ',';

There is no problem. At least not with PostgreSQL 9.5.

CSV Mode

So where is your problem, it's with CSV-mode. Observe,

# COPY baz TO STDOUT;
1   {"a": "b"}
# COPY baz TO STDOUT CSV;
1,"{""a"": ""b""}"

You can see these two are different now. Let's try to load the non-CSV file in CSV mode which assumes the format that CSV mode generated above.

TRUNCATE baz;
COPY baz FROM '/tmp/data.csv' DELIMITER ',' CSV;
ERROR:  invalid input syntax for type json
DETAIL:  Token "a" is invalid.
CONTEXT:  JSON data, line 1: {a...
COPY baz, line 1, column jsonb: "{a: b}"

Now we error. The reason for that comes from RFC 4180

  Each field may or may not be enclosed in double quotes (however
         some programs, such as Microsoft Excel, do not use double quotes
         at all).  If fields are not enclosed with double quotes, then
         double quotes may not appear inside the fields.

So JSON RFC 4627 specifies an object's names in name/value pairs must be strings which require double quotes.
And CSV RFC 4180 specifies that if any double quotes are inside the field, then the whole field must be quoted.

At this point you have two options..

Don't use CSV mode.
Or, Escape the inner quotes.

So these would be valid inputs under the same options in CSV mode.

#COPY baz TO STDOUT DELIMITER ',' CSV ESCAPE E'\\';
1,"{\"a\": \"b\"}"

# COPY baz TO STDOUT DELIMITER ',' CSV;
1,"{""a"": ""b""}"


Find records size in kb/mb/gb for one client from multiple table in one database

Seems you want to know the size utilization per row, that's not possible as the data basically stores in page format not in row format, each page can have maximum ~8KB of storage (may vary depending on fill factor), and NOT always a page can store same number of rows. It would be easy in case if you got separate partition per client

Otherwise, you can only get an idea how much utilized per each client based on average calculation factor, but it's NOT precise number. i.e. if total rows in a table 1,000 and stored in 1024 KB (128 pages), the average per client while having 200 rows would be 204.8 KB (20% of total rows in table). You can use same calculation with non-clustered indexes as well

Following query help you to get size utilization of every user table in database. Consider the clustered index and heap utilization space is actual data size, nonclustered index utilization space is additional storage of table other than actual data.

select  OBJECT_NAME (s.object_id) as ObjectName,
        sc.name SchemaName,
        i.name as IndexName,
        s.partition_number,
        SUM (CASE WHEN s.index_id = 0 -- HEAP (actual data)
                then (used_page_count * 8.00) / 1024 end
            ) as HeapData_Mb
        ,SUM (CASE WHEN s.index_id = 1 -- Clustered Indexes (actual data)
                then (used_page_count * 8.00) / 1024 end
            ) as CluIndex_Mb
        ,SUM (CASE WHEN s.index_id > 1 -- Clustered Indexes (actual data)
                then (used_page_count * 8.00) / 1024 end
            ) as NCluIndex_Mb
        ,max (row_count) as [RowCount]
from sys.dm_db_partition_stats as s
    join sys.indexes as i on s.object_id = i.object_id and s.index_id = i.index_id
    join sys.tables as t on s.object_id = t.object_id
    join sys.schemas as sc on t.schema_id = sc.schema_id
GROUP BY s.object_id, sc.name, i.name, s.partition_number
ORDER BY  ObjectName, IndexName

Additional References:
Partitioned tables and Indexes:
Horizontal Partitioning


After successful installation, Is it possible create a database without DBCA?

Yes. First you need a parameter file (init.ora). The only mandatory parameter is db_name to have a fully working database.

Steps

1 - Create the parameter file:

[myhostname@oracle $] vi $ORACLE_HOME/dbs/initorcl.ora

db_name='orcl'

:wq

2 - Export ORACLE_SID to reflect the db_name you which to create:

[myhostname@oracle $] export ORACLE_SID=orcl

3 - Login to sqlplus:

[myhostname@oracle $] sqlplus / as sysdba

4 - Issue the "startup nomount" command, pointing to the parameter file you have created.

SQL> startup nomount pfile = '/u01/app/oracle/product/<version>/dbs/initorcl.ora'

5 - Issue the create database command

SQL> create database;

6 - Run the post database creation scripts:

SQL> @?/rdbms/admin/catalog.sql -- Create dictionary views

SQL> @?/rdbms/admin/catproc.sql

SQL> @?/sqlplus/admin/pupbld.sql

@ means "run an external script in sqlplus"
? is a wildcard character that represents the ORACLE_HOME path, so if you correctly installed the software and set the environment variables, it must work, otherwise provide the full ORACLE_HOME path.

You may also want to know the "whys" of each stage of the database creation process...

At step 1, you don't have a database (datafiles, controlfiles...), so in order to create a database, you first must have an instance, which is a set of memory structures and background processes, and for that purpose, the only need is a parameter file to startup the instance in nomount. To proceed to the mount stage, you need either a controlfile if the database already exists, or issue the create database command to let Oracle take care of creating the necessary files, tablespaces etc. After that, the database can be mounted and opened, but still the data dictionary views aren't populated. So you just need to run the post scripts to do the job.

Reference


This SQL works fine in SQL Server (with appropriate syntax modifications):

UPDATE Visits
SET Duration = TimeDiff(
         ( SELECT ReqTime FROM Visits N WHERE n.ReqTime > R.ReqTime ORDER BY ReqTime LIMIT 1)
        ,R.ReqTime
    )
FROM Visits R ;

Unfortunately, the syntax hits a limitation of MySQL that an updated table cannot be referenced (again) in the WHERE clause of the UPDATE. A common workaround is to rewrite with a JOIN:

UPDATE Visits R
  JOIN Visits U
    ON U.pk = ( SELECT N.pk                 -- the Primary Key of the table
                FROM Visits N
                WHERE N.ReqTime > R.ReqTime
                ORDER BY N.ReqTime
                LIMIT 1
              )
SET R.Duration = TimeDiff(U.ReqTime, R.ReqTime) ;


Stored proc returning customers a user cannot support

This will get everything a user is qualified for. Add a TOP 1 and an order by based on priority to get a single request.

select CustomerRequests.customerReference
from CustomerRequests
join userSkills on  userSkills.skillid = CustomerRequests.requestTypeId
and userSkills.userId = 1 --@pinUserId
--join RequestAttributes on RequestTypeId = CustomerRequests.requestTypeId
where requestid not in (
    select CustomerRequests.requestid
    from CustomerRequests
    left outer join userSkills
        on userSkills.skillid = CustomerRequests.requestTypeId
    where userSkills.skillid is null
    )
and CustomerRequests.status = 0


Move database from windows server 2003 to windows 7/8?

It looks like your question revolves around 3 parts to me:

1-Licensing compliance/support/(security compliance?).

2-Best Practices to serve this.

3-Test it. It's a uncommon scenario.

For #1 I contacted MS Support and they said:

"MS REP: Hello, how may I assist you today?
Ali: at 11:49:20
Hello MS REP, may I use SQL Express in a corporate environment for commercial purposes and will it be supported by MS in that scenario?
MS REP: at 11:50:41
I am glad to assist, as long as you don't go over a 10 gigabyte database, only have a 1 processor/CPU computer running it, and don't need any of the additional features of the full editions, then yes it will be fine.
Ali: at 11:50:55
Perfect.
Ali: at 11:51:02
Let's see what they do when it hits 10gb lol. TY"
You're welcome, how large of a database are you using now?  The server will actually start crashing and failing a bit before you hit 10 GB, so if you are already at 9 GB, it would be a good idea to invest in a paid edition.
Ali: at 11:52:24
that's a great idea, they should do that even before 9GB IMO. do you know if the transaction log also counts as part of the DB Size or just the data files?
Danforth T: at 11:53:13
MS REP: data will count towards the total for as long as it is in the system.  If it gets stored permanently, then yes, transactional logs, applications, other servers, and data files will all contribute to the total.
Ali: at 11:54:22
thank you. thats good to know!"

So according to the rep not only do your data files count towards it but so does the log and temp data as well.  You're probably aware of the limitations of express though if you're already using it.

CPU:
As far as best practices go, can you give it a really good CPU in your hypervisor of choice since yo're limited on that.  Perhaps setup your own log shipping using powershell or another option to have some sort of 'HA' if needed.  This is more to ensure you don't have data loss since it seems with so few users and such small database size restraints that restore time might not be an issue.

RAM:
You are limited to 1GB of RAM so make sure your execution plan cache isn't full and you work around that, otherwise or also make sure you have fast disks.  10GB disk with 1GB ram means we're back to the 32bit days of optimizing disks.  Now we have SSDs so it's a nice cheat.

Disks/IOPS: They don't want to pay licencing?  Fine, throw down $200 on a nice Samsung 850 which has excellent life span.  Put your tlog and data onto there if they want to be really cheap and lose the tail of the log backups.  You'll still get the IOPS and you are doing frequent log backups off disk and preferably off VM, correct?  You are because if you aren't, you now will lose everything.  Backup on to a disk on another VM all together if possible or a NAS device just in case the actual VM get's corrupted and the virtual harddrive file for some reason isn't accessible.  Remember to test restore.

Monitoring:
Monitor your data size and disk sec/read and write.  Windows Server has optimization in many areas in the kernal that Windows 7 just doesn't have.  It could creep up in all places.  Grab a good baseline of performance from the old system and compare the performance to the new system.

Test Test Test: Can you replay some of the load on the new VM while you migrate?  Replay the production load on the new machine. Put up a new parallel instance and restore last nights backup to then document the security and configuration steps needed so you can script them and repeat it if the VM goes down or you need to redo.  Once it's scripted well restore the EOD backup and test with the end users.  Have them all go on your new test instance and hammer at it hard.  Make load, collect metrics.  Have them do the same thing on the current prod server and collect metrics.  How's it doing?  Good?  You're closer to implementing this properly, or as 'properly' as one can get using sql express on windows 7 in prod...

Cover Your Butt: Let the business know this is Windows 7 and might actually cost them more in support over the long term.  You might want to collect metrics to show them in 6 months.  Let them know their SLAs such as time to recovery, acceptable data loss, and make sure to test your backups/log shipping also preferred.  Homebrew log shipping on express also tests tlog backups.  If you put tlogs on 1 disk let them know there is a maximum x minutes of potential data loss due to no tail of the log backups.


How do I make binlog-format=ROW the start up default in the /etc/my.cnf file?

I think your problem may stem from where you wrote the directive

Make sure you place the option under the [mysqld] header in /etc/my.cnf

[mysqld]
binlog_format = ROW

If you place it under any other header, the mysqld process will not see it.

Give it a Try !!!


Postgresql: Insert trigger function fails on partial insert statement

Try this form:

 EXECUTE 'insert into ' || child || ' values ($1.*)' USING NEW;

It requires at least PostgreSQL 8.4, but previous versions ought to be retired nowadays.

An even more modern and cleaner version (quote the table's name if necessary):

 EXECUTE format('insert into %I values ($1.*)', child) USING NEW;


Do management operations such as database copy affect our current db performance?

After executing copy database operation and database export operations, this is the data that I gathered. I used:
select * from sys.dm_db_resource_stats to real time query performance information.

I used a completely isolated Premium P6 (old P3) Database.

During the create database as copy, the source database was not affected at all. That means constant 0%cpu, 0%data_io, 0%log_write. At least in premium P6.

During the database export, %cpu and %data_io oscilated both from 2% to 5%. This means export operation effectively has some performance impact (very low, at least on a premium P6).


I would first run a repair installation of your SQL Server installation on the node that was evicted.  This should at least get the appropriate binaries installed.  Then, run a Discovery Report to validate what is and is not installed.  If your SQL node installation is no longer available, the reinstall the node.


Permanent solution for Fixing buffer size

To find the slowness of the application, we need to dig deeper on many other aspects, such as table design, query, indexes, data type, etc...
To set the innodb_buffer_pool_size value to 4 GB, edit the configuration file my.ini and add the following statement, then restart the MySQL server:
[mysqld]
innodb_buffer_pool_size = 4G

Or you can set it online (restart not required) by running the following statement:
SET GLOBAL innodb_buffer_pool_size = 1024 * 1024 * 1024 * 4;

There are few things to consider, before changing this variable. My blog post contains the detailed explanation on allocating innodb_buffer_pool_size.


Reusing literal string in different parts of query

Use unnest() after converting string to array with string_to_array():

SELECT char_i, ascii(char_i) AS ascii_char_i
FROM   unnest(string_to_array('The character between the xes is evil: xx', NULL)) char_i;

NULL as separator splits after every character in string_to_array().

There is also regexp_split_to_table(), it's more versatile, but also more expensive.

To guarantee original order of characters throw WITH ORDINALITY into the mix:

SELECT pos, char_i, ascii(char_i) AS ascii_char_i
FROM   unnest(string_to_array('The character between the xes is evil: xx', NULL)) WITH ORDINALITY t(char_i, pos)
ORDER  BY pos;

How to preserve the original order of elements in an unnested array?
How get all positions in a field in PostgreSQL?

But while the query is that simple the order is preserved anyway - by the current implementation.

About tricky whitespace and non-printing (UNICODE) characters:

Trim trailing spaces with PostgreSQL
ERROR: "sql " is not a known variable


Different calculations in EXCEL and TERADATA SQL

Teradata rounds after each calculation based on the resulting datatype (Standard SQL compliant), to get the expected reault you can either CAST the 1st operand to a NUMBER (no need to think about precision) and finally back to DECIMAL:

Cast((Cast(75 AS NUMBER)/979.86) *979.86 AS DECIMAL(10,2))

Ot you rearrange the order of calculation to multiply first and then divide:

979.86 * 75 / 979.86


Generally not a good idea, for the following reasons:

If someone knows UserID and times, they may be able to match that back to transactions (depending on how this ID is exposed - if it's exposed externally via an API, someone knowing UserID could generate candidate TransactionIDs from guessed times)
High amount of entropy in the key means that your indexes on this key are going to fragment rapidly. If your DB has a page cache, the cache efficiency will drop as your data volume grows (this can be a killer).
If you can handle the performance concerns, then why not just generate a GUID?


Inserting data into table in server B whenever table in server A gets updated

Normally you would use materialized views for sync'ing tables, however your table structures are different which probably rules out this solution. The next solution would be to implement insert/update/delete triggers on your source table. This can cause performance bottlenecks on high volume transactions. The third solution is to implement ETL (extract/transform/load). There are a number of commercial and open source solutions. Informatica, GoldenGate from Oracle are a couple of commercial options, Telend, Jaspersoft ETL, KETL are great open source. http://www.butleranalytics.com/30-etl-tools/ has a list and links to some ETL tools.


Missing execution plans for stored procedures

There are two plan cache DMFs:

sys.dm_exec_query_plan - returns cached plans in XML format, but only up to a certain size (and only as long as they can be formatted as XML in SQL Server, which means up to 128 nested levels.)

sys.dm_exec_text_query_plan - returns cached plans in text format, of any size. But the drawback is that when plans are large, you can't convert them to XML inside SQL Server, and even TRY_CONVERT as XML returns a null.

sp_BlitzCache only hits the former DMV (because it needs to analyze query plans as XML to do all kinds of slicing and dicing.) I made Github issue #838 to improve this so we could at least alert users to go check sys.dm_exec_text_query_plan for their bigger queries. We still won't be able to do XML analysis on it, though.


SQL Server: TSQL or safe steps needed: Change collation en masse for group of tables

I had to do this recently and it was a big pain.  I ended up doing a write up on changing collations: Collation: Correction -> Expression, Column, Database

My personal preference for method in your situation would probably be to

Create a new database (with the correct collation if needed)
Script out the old database
Do a search and replace on the script file to change the collation from one to another
Run the script on the new database
Move the data across
Do a rename swap to put the new database in place of the old

Your other options are to either create a script that generates alters for each column or to use the GUI.  The GUI is going to be slower but more certain.  The script method generates the risk of making changes you didn't intend if your script isn't letter perfect.

The script method also has the problem that you have to drop any constraints, indexes etc that touch the column before you can alter it to change the collation.  You might be able to generate a script to do that for you but it wouldn't be easy.

If you decide to go the script method I can probably help you generate one to create the alters but for dropping and re-creating indexes/constraints you would be on your own. Like I said I would go with the re-create the database method.  It's easy and relatively fast unless you have a huge amount of data.  The only real drawback is that it requires there to be double or even triple (space for logs) the size of your database free.


Backup sqlserver 2019 and restore to Sqlserver 2017

A backup is overkill for only one table and I'm not sure what compatibility issues you can run into restoring to a lower version of SQL Server (since backups occur at the database level).
Instead you should look to just copy the data between servers. There are a multitude of ways and tools out there to help you do this, but I've always found the SQL Examiner Suite simple enough for such a task. They offer a fully functional 30 day free trial, so you can utilize it as you need.
Alternatively you can setup a Linked Server between the two instances and then can easily pull the data across the linked server to fill the table on your other instance, as needed.
Finally, you can also export the data to a flat file from your SQL Server 2019 instance, and import that flat file using the same tool in SSMS on your SQL Server 2017 instance.
Outside of those options, you can also use BCP as recommended by Bob Klimes, or you can generate scripts with the data as this article walks you through.
Here's the key steps you need, to include the data with the schema scripts when you use the Generate Scripts feature:

(On the Set Scripting Options page) Click on the 'Advanced' button.

Scroll down and select 'Schema and Data' from the 'Types of data to script' dropdown.


you will find valuable information in pgAdmin III documentation, and to be more precise in the settings page about miscellaneous options. You can check that Guru hints are enabled (unchek the Do not show guru hints; I’m one myself box if it's checked). You'll notice that guru hints won't be available everywhere, but only on some objects in you database, for example tables (saying something like "hey, what about defining a primary key for this table?"), functions and so on.

Good luck!


Disaster recovery plan - Having multiple servers

It sounds like what you're talking about is more aligned with High Availability.

If you are already on SQL Server 2012, or you have the ability to upgrade then it sounds like AlwaysOn Availability Groups could be right up your alley.  This is a technology that will allow you to create a cluster and you could have two geographically separate servers (i.e. your current database server and your remote database server that you're planning on standing up).  If the network latency isn't a performance impact between the two servers you could set up a two node cluster and have each of those nodes hosting a replica of the Availability Group (or Groups).  With synchronous commit, you could configure automatic failover when there is a network outage or some other service-hindering event.

By creating an Availability Group Listener you could have the client applications connecting to the listener which will direct traffic to the current primary replica.

If you don't have the ability to get onto SQL Server 2012 to leverage AlwaysOn Availability Groups, then Database Mirroring could also be another high availability solution for you.  If the session is operating synchronously with a witness server, you could also have the ability for automatic failover in the event of the principal becoming disconnected.

The reason I am recommending these two is two-fold: replication is pushing out articles, and it sounds like you want your entire database (or databases) to be ready for a failover.  Traditional failover clusters could also be another solution but you would have to also consider shared storage between the two nodes.  That might get complicated with the geographically separate servers.

  if the network fails or the software throws a bug, it is very hard to come back to office and fix the problem.

What do you mean by "the software throws a bug"?  There will need to be the proper error handling and error recovery in order to ensure this type of behavior doesn't cause an outage.


Is it possible to decouple row size estimation from maximum column width?

No.

I don't believe this is possible.

In the case that the average length is greater than that calculated by the heuristic but still less than 4,000 bytes it is possible to massage this upwards with a CAST but even then you  might need some hacking about with the query syntax to encourage the CAST to happen before any memory consuming operator that depends on the estimated row size.

In the case that it is less the CAST to a shorter size obviously won't work without the possibility of truncating data.

The possible down side of a mis-estimate is that the memory grant may be calculated based on incorrect information.

I've wondered before why the statistics on string columns don't keep track of this. Maybe in the scale of things it simply isn't regarded as objectively that important.


Difference between a schema and a database in db2

This simple question has caused an unusual amount of debate, and I feel compelled to quote an authoritative source.

The database stores all data in tables that are related to one another. Relationships are established between tables such that data is shared and duplication is minimized.
A table space is a storage structure containing tables, indexes, large objects, and long data. They are used to organize data in a database into logical storage groupings that relate to where data is stored on a system.
A schema is a collection of named objects; it provides a way to group those objects logically. A schema is also a name qualifier; it provides a way to use the same natural name for several objects, and to prevent ambiguous references to those objects.