SQL Server target/total server memory is not equal to sum of database size in dm_os_buffer_descriptors I am having an issue in my SQL Server like high memory paging rate and more compilation/sec which lead me to a different observation in memory, so first i checked what is total size of memory of system and what are the threshold levels set in SQL server i.e. min server memory 4096 MB and max server memory 51200 MB, i checked few performance counters total server memory is 64 GB, target server memory is 53 GB and other performance counters as shown below images

When i checked sys.dm_os_buffer_descripters in SQL Server there are very less dirty pages, but when i check total size of memory occupied by all databases is 39 GB as shown in below images

My questions is why and how 14 GB of memory is additionally being occupied by SQL Server and the same i can in resource monitor or performance counter like below image.

Can any one tell me did i miss some thing to check, Total databases are occupying only 39 GB but if i check perfmon or resource monitor it shows that SQL Server is occupying total 53 GB, how 14 GB can be used internally.

sys.dm_os_buffer_descriptors

From the official documentation on sys.dm_os_buffer_descriptors (Transact-SQL):

  sys.dm_os_buffer_descriptors returns pages that are being used by the Resource database. sys.dm_os_buffer_descriptors does not return information about free or stolen pages, or about pages that had errors when they were read.

You are looking at the data cache usage. But there are more objects in memory than just the data.

sys.dm_exec_cached_plans

You have to consider the plan cache (sys.dm_exec_cached_plans (Transact-SQL)) and other objects.

  Returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.

Memory acquired by cached plans can be retrieved with the following script:

SELECT plan_handle, ecp.memory_object_address AS CompiledPlan_MemoryObject,
    omo.memory_object_address, pages_allocated_count, type, page_size_in_bytes
FROM sys.dm_exec_cached_plans AS ecp
JOIN sys.dm_os_memory_objects AS omo
    ON ecp.memory_object_address = omo.memory_object_address
    OR ecp.memory_object_address = omo.parent_address
WHERE cacheobjtype = 'Compiled Plan';
GO

sys.dm_os_memory_clerks

But instead of looking at individual caches, you might want to consider querying the view: sys.dm_os_memory_clerks

  The SQL Server memory manager consists of a three-layer hierarchy. At the bottom of the hierarchy are memory nodes. The middle level consists of memory clerks, memory caches, and memory pools. The top layer consists of memory objects. These objects are generally used to allocate memory in an instance of SQL Server.

sys.dm_os_memory_objects

  ... SQL Server components use memory objects instead of memory clerks. Memory objects use the page allocator interface of the memory clerk to allocate pages. Memory objects do not use virtual or shared memory interfaces. Depending on the allocation patterns, components can create different types of memory objects to allocate regions of arbitrary size.

Use the following query to see what sys.dm_os_memory_objects (Transact-SQL) are using in RAM.

SELECT SUM (pages_in_bytes) as 'Bytes Used', type
FROM sys.dm_os_memory_objects
GROUP BY type
ORDER BY 'Bytes Used' DESC;
GO

Summary

So while sys.dm_os_buffer_descriptors does provide an overview of the data cahce, you will have to query other sys views to determine the actual current memory usage.

Your SQL Server instance may at one time have occupied 53 GB of RAM, but it is now only currently consuming 39 GB of RAM (in data cache) and more GB in plan cache and memory objects, which may sum up to 53 GB RAM, but which may also sum up to less because of free pages in RAM, that may not yet have been released by SQL Server.


Select one item per matching ids(value) I have a mysql table(trips) with records about cars. I wish to query the data and return one item per carId. But am unable. Below is the query example getting 50 records, which should also have 50 carId

SELECT *
FROM `trips`
WHERE (   carId = 147
       OR carId = 62
       OR carId = 59
       OR carId = 42)
LIMIT 50

Below is an example that returns 8 records. How to return limit 8 records but one record per car. Example here

SELECT *
FROM `trips`
WHERE carId in (147, 62, 59, 42)
GROUP by carId;

Remove the LIMIT and be sure that you get one row per carId. If you put 20 ids within in ( ), you will get 20 exclusive rows.

But this gives a rough result in the sense that it might choose any one row matching the carId. You might add some condition to pickup any preferred row per carId.


Retrieving specific results from a query I have the following database table

Firstname  Varchar(40)
Surname    Varchar(40)
Department Binary
JobCode    Char(1)

Normally, pulling the records of a particular department is no problem.  The department field is a 7-part field, with each bit representing a specific department, allowing the system to easily show where people work over one or more departments. Lets say that John Smith has a job code of K and Freda Williams has a Job Code of M.

Again, returning the records for a specific department is fine and easy.  However, if John Smith has a temporary promotion to the same department, rather than change the record, an additional record is added with a Job Code of F.   In essence the lower the job code letter the higher the grade.

So, if we want to retrieve John Smith's current details, it's simple, select the record where the name is John Smith, ordered by Job Code Asc, Limit (1).

But what is the SQL statement to pull all records for a department, but making sure only one record appears for John Smith and it is the 'F' record?  Can anyone help? I had thought that a sub-query might sort it, but couldn't work out how.

Allow me to start with a review of your table design decisions (or of those that preceded you) that directly led you to this problem, and how to avoid it. If you do not want to read it, go directly to the end, where I suggest on an edit the actual solution.

You have a single table that is crammed by several design entities. While this is not bad per se, you must be aware that you are denormalizing your table design, and that can lead to problems.

I see 3 concepts on the same table:

Employee intrinsic details (First name, Family name)
Department membership
Job position holding by the employee within the department

According to your description, an employee can be part of several departments (and vice-versa) and you are implementing that as a bit field. I find interesting that you have chosen a binary data type, when a  bit or a set data types would be potentially smaller and more legible respectively. However, those data types are usually avoided, not only because they are not very standard, but because they are used to denormalize columns.

Assuming there is a departments table, a better option would be to create a separte table: DepartmentEmployees, like this

Employees Table
===============
EmployeeId Int Primary Key auto_increment
Firstname  Varchar(40)
Surname    Varchar(40)
JobCode    Char(1)

DepartmentEmployees
===================
EmployeeId Int
DepartmentId Int
Primary Key(EmployeeID, DepartmentId)

To query all employees from given deparment "?":

SELECT E.*
FROM Employees E
JOIN DepartmentEmployees DE
ON E.Id = DE.EmployeeID
WHERE DepartmentId = ?

Now let's have a look at JobCode. You have to ask yourself if a Job Position is a property of the employee or of the employee within a department. If the latter, please consider adding that property there:

Employees Table
===============
EmployeeId Int Primary Key auto_increment
Firstname  Varchar(40)
Surname    Varchar(40)

DepartmentEmployees
===================
EmployeeId Int
DepartmentId Int
JobCode Char(1)
Primary Key(EmployeeID, DepartmentId)

To query all employees from given deparment "?", with its Job Code:

SELECT E.*, DE.JobCode
FROM Employees E
JOIN DepartmentEmployees DE
ON E.Id = DE.EmployeeID
WHERE DepartmentId = ?

This way you can get the JobCode, which can be different for each employee-department tuple (if needed).

You can consider creating (if they do not exist already), separate tables for Department, Jobs (as in, positions, for JobCode) and add unique Primary key everywhere.

In general, creating an intermediate table whenever there is an N:N relationship is a good option, unless there is a reason to denormalize the relationship (leading to more complex code).

Edit: The problem you are facing is horrible from all points- aside from the normalization problems, adding one extra record makes no sense. But here is the wanted solution:

Knowing there is nothing you can do about it, you are lucky because it is a very common query- that not only has a name "GROUPWISE MAXIMUM", it even has its own page on the MySQL manual: http://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html

There is both a subquery and a JOIN solution, I usually like the JOIN solution more, it would go something like this:

SELECT E1.FirstName, E1.Surname, E1.Department, s1.JobCode
FROM Employees E1
LEFT JOIN Employees E2
ON E1.FirstName = E2.FirstName AND E1.Surname = E2.Surname -- use an id for the SELF JOIN if possible, whatever is unique
AND E1.JobCode < E2.JobCode -- change the direction of the comparison depending if you want the lowest or the highest JobCode
WHERE E2.FirstName IS NULL  -- again, use the id if available
  AND E1.Department = ...;  -- or whatever the department filter should look like in your current setup

Allow me to shower after writing such a solution.


Connect MySQL workbench with MySQL container I pulled MySQL container from Dockerhub and it is running on my macbook pro as you can see as follow

CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS               NAMES
12cd3deaa3f0        mysql:latest        "/entrypoint.sh mysql"   47 hours ago        Up 10 minutes       3306/tcp            mysqldb

Then I downloaded the MySQL workbench and I tried to connect it with MySQL container, but it does not work. It shows connection error.

Your connection attempt failed for user 'root' from your host to server at 192.168.99.102:3306:
Can't connect to MySQL server on '192.168.99.102' (60)

The IP from docker machine is

docker-machine ip default
192.168.99.100

and the port from MySQL is 3306 as you can see above.

To export MySQL port, I tried with the statement

docker run -d -P mysql:latest mysqldb

It does not work at all. What am I doing wrong?

It looks like you ran the container without setting the -p 3306:3306 parameter. Also, it seems that you mistyped the IP address in of the Hostname in MySQL Workbench. I recommend starting from scratch by following these instructions:

Run the mysql server. (Change the password admin to anything you want)

docker run -e MYSQL_ROOT_PASSWORD=admin --name mysql -d -p=3306:3306 mysql

get the IP address of the default docker virtual machine by running

docker-machine ip default

Copy the IP address that you'll get as the result of the previous command. (in your case, it should be 192.168.99.100)
Open MySQL Workbench and create a new connection. Paste the IP address that you copied into the "Hostname" field.

You should be all set now.


How to speed up Oracle to PostgreSQL data transfer I have an Oracle schema that I am transferring to PostgreSQL with ogr2ogr.
This particular table has 5 columns with number(22) each. There are some 300M rows. In under 10 minutes I can transfer this table between two oracle servers over the network. ogr2ogr took a day to transfer 10M records and will take a month to transfer the whole table.

I suspect the problem is on the receiving end. Data is transferred to AWS PgSQL with 4 Gb ram. How to debug the bottleneck and speed up the transfer?

It might be worth pushing a subset to a file and loading it with copy, just to get a sense of the throughput. Copy is...legendary....for high-speed bulk loading into an empty table.

https://www.postgresql.org/docs/current/sql-copy.html


Count Most Recent Browser Used By All Users I have a table with columns Type, Login, and Timestamp. It has been collecting information about the type of browsers users log into the system with for years. Obviously many of the users have since upgraded their browsers. I do not want to consider data for old browsers when users have upgraded. Here is the query I currently have to get a count of how many people use which browser:

select
  Type,
  count(Type) as Count
from
  (select
    distinct Login,
    Type
   from BROWSER) as q
group by Type

This appears to work fine, but I have a lot of old browsers that I know are no longer being used:

How would I only select the most recent browser used by each user based on timestamp (or Browser_ID could work also since they are written sequentially). I also intend to ignore data by users who have not logged in during the past year, but that is simple to add later (I only mention it so that no one brings up why I would want browser data that is old for inactive users - I do plan on addressing that as well but I know how to do that already).

If anyone is up for a challenge, there are some users who log in from a couple different computers - or perhaps from their phone and from their desk. It would be great if I could determine that they frequently log in from two different places and also collect that data, but this might be too complicated. If anyone figured out how to add this complicated step, I'll gladly award a 50 point bounty to the answer.

Not your exact requirements (instead of ignoring data from users inactive the last year, the following queries ignore all data older than a year) but you can get ideas and change the conditions to more complex if needed:

Count all Login and browser Type combinations, up to a year ago.

; WITH cte AS
( SELECT    *
  FROM      Browser
  WHERE     Timestamp >= DATEADD(Year, -1, GETDATE())
)
SELECT      Type,
            Count = COUNT(DISTINCT Login)
FROM        Browser
GROUP BY    Type ;

Count only most recent browser Type per Login, up to a year ago.

; WITH cte AS
( SELECT    Type,
            Login,
            Rn = ROW_NUMBER() OVER (PARTITION BY Login
                              ORDER BY Timestamp DESC)
  FROM      Browser
  WHERE     Timestamp >= DATEADD(Year, -1, GETDATE())
)
SELECT      Type,
            Count = COUNT(*)
FROM        cte
WHERE       Rn = 1
GROUP BY    Type ;

Count up to 5 recent browser Type per Login,  and up to a year ago.

; WITH cte AS
( SELECT    Type,
            Login,
            Rn = ROW_NUMBER() OVER (PARTITION BY Login
                                    ORDER BY MAX(Timestamp) DESC)
  FROM      Browser
  WHERE     Timestamp >= DATEADD(Year, -1, GETDATE())
  GROUP BY  Login, Type
)
SELECT      Type,
            Count = COUNT(*)
FROM        cte
WHERE       Rn <= 5
GROUP BY    Type ;

Tests at SQL-Fiddle


Does the row locking on slave database also apply to master database? In a transaction, for example,

START TRANSACTION;
SELECT count(*) as count FROM `order` WHERE user_id = 25286 LOCK IN SHARE MODE;
INSERT INTO `order` (`id`, `user_id`, `product_id`) VALUES (NULL, '25286', '36296');

we perform the SELECT LOCK IN SHARE MODE; the SELECT query will be executed on the slave database and lock a row.

Does the SELECT LOCK IN SHARE MODE also create a row locking on the master database so that the INSERT query will not run on a master database?

There is no communication from Slave to Master (aside from network protocols to receive the Replication updates from the Master.

Writing on a Slave is a no-no.  The basic principle of Replication is that the Slave will have exactly (except for a slight lag) the same data as the Master.  Writing to the Slave does not allow for keeping them in sync.

An exception is with Master-Master -- wherein two servers are acting both as Master and as Slave.  Still, writing to both leads to a lot of potential problems, and should be avoided.


Attach a trigger to 'sys.dm_db_missing_index_details' I am trying to attach a trigger to the sys table sys.dm_db_missing_index_details with the following query:

CREATE TRIGGER indexBackup
ON  sys.dm_db_missing_index_details
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for trigger here
EXEC PerformanceTest.[dbo].[usp_persist_missing_index_DMV_data]

END
GO

I am getting the following error:

  The object sys.dm_db_missing_index_details does not exist or is invalid for this operation.

Is it allowed to attach a trigger to a sys view or am i missing something?

No triggers on system DMVs, sorry.

Besides, you wouldn't really want to do this: that DMV is updated every time a query is run with a missing index request. That could be hundreds or thousands of times per second - and performance matters there.

Instead, consider running an Agent job every 5-15 minutes to capture the data you're looking for.


SQL Server upgrade single instance I have a server that is running SQL Server 2005 Express.

It currently has 2 instances of SQL Server running.

Instance 1 runs some old legacy software that is not supported by me.
Instance 2 runs my software.

Is it possible to upgrade instance 2 to SQL Server 2008 R2 Express independently of instance 1, i.e. keeping instance 1 on 2005 while instance 2 is upgraded?

Absolutely, when you run setup for SQL Server 2008 Express choose the instance you want to upgrade. By doing so you only upgrade that instance to SQL Server 2008 R2. Other instances will stay on 2005 however any client tools like Management Studio Express will be upgraded to 2008 R2.


Database normalization: Describing trait - foreign key to table, or varchar field with value? My colleagues and I are having a discussion about the normalization of descriptive traits in the database, such as "status" or "type". Let's call the central table of the discussion "Order".

In my regular design-approach, I would define another table, "OrderStatus", to describe the status of an order, and then create a foreign key with a relationship , i.e. "OrderStatusID", on the "Order" table.

This would give me referential integrity. I'd be able to join the status at all times, and my possible values are always present in the "OrderStatus" table.

My colleague doesn't like this degree of normalization, so he'll instead define a varchar field, "OrderStatus" on the "Order" table. This field would contain the values directly.

The possible values of status are defined in his application, more specifically in an Enum of OrderStatuses, and as such, are not available to me unless I have access to the source code of said application.

I'm used to having the entire context of the database exist in the database as relationships and tables, and having to write "WHERE OrderStatus = 'Sold'" as opposed to "WHERE OrderStatusID = 3" bugs me.

What do think? I'm looking for pro's and con's against both approaches, but I'm primarily concerned about performance and readability/maintainability.

You should use a lookup table

Not all clients will use the ENUM in the application
You will get a reporting or MIS or Excel app connecting at some point
How can do "NOT EXIST" otherwise?
You will be asked this
You won't know about client enum changes
Strings are inefficient compared to a tinyint, especially when you need to index it for your WHERE clause
The data and database will outlive the client application

Also, see these:

"Proper Use of Lookup Tables"
"INT or CHAR for a Type Field"
"Advantages and Disadvantages to using ENUM vs Integer types?"


Why does this seek on BIGINT col have extra constant scan, compute scalar, and nested loops operators? When I look at the actual exection plan of some of my queries I notice that literal constants used in a WHERE clause show up as a nested chain of calculate scalar and constant scan.

To reproduce this, I use the following table

CREATE TABLE Table1 (
    [col1] [bigint] NOT NULL,
    [col2] [varchar](50) NULL,
    [col3] [char](200) NULL
)
CREATE NONCLUSTERED INDEX IX_Table1 ON Table1 (col1 ASC)

With some data in it:

INSERT INTO Table1(col1) VALUES (1),(2),(3),
                               (-9223372036854775808),
                               (9223372036854775807),
                               (2147483647),(-2147483648)

When I run the following (nonsense) query:

SELECT a.col1, a.col2
  FROM Table1 a, Table1 b
  WHERE b.col1 > 2147483648

I see that it will do a Nested Loop drawing in the result of Index Seek and a scalar calculation (from a constant).

Note that the literal is larger than maxint. It does help to write CAST(2147483648 as BIGINT). Any idea why MSSQL is defrering that to the execution plan and is there a shorter way to avoid it than using the cast? Does it affect bound parameters to prepared statements (from jtds JDBC) as well?

The scalar calculation is not always done (seems to be index seek specific). And sometimes the query analyser does not show it graphically but as col1 < scalar(expr1000) in the predicate properties.

I have seen this with MS SSMS 2016 (13.0.16100.1) and SQL Server 2014 Expres Edition 64bit on Windows 7, but I guess it is a general behavior.

SELECT thing,
       sql_variant_property(thing,'basetype') AS basetype,
       sql_variant_property(thing,'precision') AS precision,
       sql_variant_property(thing,'scale') AS scale
FROM (VALUES (2147483648)) V(thing)

Shows you that the literal 2147483648 is interpreted as numeric(10,0). This behaviour pre-dates the introduction of the bigint in SQL Server (2000).

There is no syntax to indicate that a literal should be treated as bigint - adding an explicit CAST is the best solution. The article Dynamic Seeks and Hidden Implicit Conversions discusses the rest of the apparatus in the plan.

The plan itself shows that the nested loops has a seek predicate on

Seek Keys[1]: Start: [tempdb].[dbo].[Table1].col1 > Scalar Operator([Expr1005]),
                End: [tempdb].[dbo].[Table1].col1 < Scalar Operator([Expr1006])

You can use an extended events  session on query_trace_column_values to see that these are as follows.

The XML in the plan also shows this

  <DefinedValue>
    <ValueVector>
      <ColumnReference Column="Expr1005" />
      <ColumnReference Column="Expr1006" />
      <ColumnReference Column="Expr1004" />
    </ValueVector>
    <ScalarOperator ScalarString="GetRangeWithMismatchedTypes((2147483648.),NULL,(6))">
      <Intrinsic FunctionName="GetRangeWithMismatchedTypes">
        <ScalarOperator>
          <Const ConstValue="(2147483648.)" />
        </ScalarOperator>
        <ScalarOperator>
          <Const ConstValue="NULL" />
        </ScalarOperator>
        <ScalarOperator>
          <Const ConstValue="(6)" />
        </ScalarOperator>
      </Intrinsic>
    </ScalarOperator>
  </DefinedValue>

This does not mean that it is literally doing a comparison < null rather

  The range boundary expressions use NULL to represent 'unbounded' at either end. (Source)

So the net effect is that your query predicate of b.col1 > CAST(2147483648 AS NUMERIC(10, 0)) still ends up with a seek against b.col1 > CAST(2147483648 AS BIGINT)

  Does it affect bound parameters to prepared statements (from jtds JDBC) as well?

I haven't used jtds JDBC but I presume it allows you to define parameter datatypes? If so just make sure the parameters are the correct datatype that match the column (bigint) so there's no need for SQL Server to deal with mismatched datatypes.


Is it necessary to connect to a postgresql database in order to create a new server? I am writing a script in python 3.7, using psycopg2 to set up a new database from scratch. In all the tutorials I have seen, using psycopg2 starts by connecting to a database and getting a cursor object that can then be used to execute psql commands.

Example:

import psycopg2

conn = psycopg2.connect(database="postgres", user="postgres")
cur = conn.cursor()
cur.execute("CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);")

Is it necessary to connect to a database on one server in order to create a new server using psql (and psycopg2) or can you create a server first and then connect to it?

You always have to connect to a database to run CREATE DATABASE. This is the main raison d'être for the postgres database.

There is the createdb executable that does this for you automatically.


Unable to start SQL Server Instance 2008 R2 - DB file corrupt I was not able to start the SQL Server 2008 R2 production DB instance. After reading the log file error message is

" The log scan number passed to log scan in database ‘master’ is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication."

After reading several post i realize that my MASTER DB file is corrupted. I have followed the below setup

Copy the Master.mdf and Masterlog.ldf file from Template location to My Database Data folder.

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\Templates to D:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA

Note: Same error occur when i copy the all DB file like Master, MasterLog, MSDBData, MSDBlog, Model and ModelLog

When i run my MSSQLSEVER instance different problem occur.

In My server i had only C, D- Drive i dont have the E drive. How can i override these below error path.

Error LOG

2012-10-24 02:51:12.79 spid5s Error: 17204, Severity: 16, State: 1.

2012-10-24 02:51:12.79 spid5s FCB::Open failed: Could not open file e:\sql10_main_t.obj.x86fre\sql\mkmastr\databases\objfre\i386\MSDBData.mdf for file number 1. OS error: 3(The system cannot find the path specified.).

2012-10-24 02:51:12.79 spid5s Error: 5120, Severity: 16, State: 101.

2012-10-24 02:51:12.79 spid5s Unable to open the physical file

"e:\sql10_main_t.obj.x86fre\sql\mkmastr\databases\objfre\i386\MSDBData.mdf". Operating system error 3: "3(The system cannot find the path specified.)".

2012-10-24 02:51:12.79 spid5s Error: 17207, Severity: 16, State: 1.

2012-10-24 02:51:12.79 spid5s FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'e:\sql10_main_t.obj.x86fre\sql\mkmastr\databases\objfre\i386\MSDBLog.ldf'. Diagnose and correct the operating system error, and retry the operation.

2012-10-24 02:51:12.79 spid5s File activation failure. The physical file name "e:\sql10_main_t.obj.x86fre\sql\mkmastr\databases\objfre\i386\MSDBLog.ldf" may be incorrect.

Look here  http://msdn.microsoft.com/en-us/library/dd207003.aspx.  It's for 2012 but 2008 is basically the same.  It describes how to use the setup.exe to rebuild the system databases.  From there you have to do restores for your system databases & patch your instance but it works.  If you don't have backups of your system databases you can copy the files off (not master obviously) and copy them back after the rebuild.  It's not recommended but it will work.


Merge Table Row Values to Header of Another Table Table 1

id   |  Field           | Values
----------------------------------------
1    | 'Field01'        |Name
2    | 'Field02'        |Age
3    | 'Field03'        |Gender
4    | 'Field04'        |Qualification

Table 2

id   |  Field01  | Field02 |Field03 |Field04
----------------------------------------
1    | Ajay      |18       |Male    |Bsc

2    | Arun      |19       |Male    |BA
3    | Aruna     |18       |Female  |Bsc

The OutPut Format is:

id   |  Name     | Age     |Gender  |Qualification
----------------------------------------
1    | Ajay      |18       |Male    |Bsc

2    | Arun      |19       |Male    |BA
3    | Aruna     |18       |Female  |Bsc

Check this where I answered a similar question on Stack Overflow.

You can do it with dynamic sql:

DECLARE @fieldName varchar(32), @title varchar(32)
DECLARE @sql_cmd varchar(512)
SET @sql_cmd='SELECT id, '

DECLARE sql_cursor CURSOR FOR
SELECT Field, Values FROM table1 ORDER BY id

OPEN sql_cursor

FETCH NEXT FROM sql_cursor INTO @fieldName, @Title

WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @sql_cmd = @sql_cmd + @fieldName + ' AS ' + @Title + ', '

    FETCH NEXT FROM sql_cursor
    INTO @fieldName, @Title
END;

CLOSE sql_cursor
DEALLOCATE sql_cursor

SET @sql_cmd = SUBSTRING(@sql_cmd,0,LEN(@sql_cmd)-2) + ' FROM table2'
EXEC(@sql_cmd)


Two composite foreign keys have identical columns which must be equal value Let's say I have a relation: CourseRegistrations with composite primary key: (course_id, sem_id, student_id)

I have another relation: CourseEvaluations with composite primary key: (course_id, sem_id, section, type, serial)
.The CourseEvaluations relation will contain records of the different evaluations done in a course.

Now I want to make a third relation: CourseStudentEvaluations with two composite foreign keys from both the above tables. Both the above tables' primary keys contain course_id and sem_id. How do I enforce that they are equal in a row/record so that I can ensure only those students' evaluations are entered against only those courses in which they're registered.

The CourseStudentEvaluations relation will contain records of all students' performance in each evaluation entered in CourseEvaluations.

I think your issue is a modeling one, as you skipped a few relations along the way. You can enforce your data consistency exclusively with the schema, no need for code enforcement with triggers or otherwise.
Also, why use surrogate keys for such a model? Try using the natural keys which in your case are short, stable, and familiar, and see hot it simplifies your queries.
Here is my take:

Courses:

Course PK <-- This is the name of the course, the one you use in the 'real world'

Semesters:

Semester PK <-- Summer, Spring, Winter, Fall or whatever you use to distinguish them

Course_Schedules:

Course REF Courses,

Semester REF Semesters,

Year ( in case not all courses are offered in the same semesters every year),

PK (Course, Semester, Year)

Students:

Student PK <-- Whatever you use to distinguish students in the real world

Course_Registrations:

Course,

Semester,

Year,

Student,

FK (Course, Semester, Year) REF Course_Schedules <-- Only 'real' scheduled courses,

PK (Course, Semester, Year, Student) <-- Student may repeat the course in different years

Course_Evaluations:

I think you are missing part of the key here, in the model you showed every course can have only one evaluation...
Who evaluates it? add to PK as needed

Course,

Semester,

Year,

-- other properties --

PK (Course, Semester, Year, {missing attribute?}),

FK (Course, Semester, Year) REF Course Schedules

Course_Student_Evaluation:

Course,

Semester,

Year,

Student,

PK (Course, Semester, Year, Student),

FK (Course, Semester, Year) REF Course_Schedules

Now you can see that since both Course_Evaluations and Course_Student_Evaluations have DRI to Course_Schedules, it enforces everything you asked for, and more..

HTH


How to filter out Scalar Valued User-Defined Function usage from SQL Server Audit Data? We have a SQL Server database which has a database audit specification which audits all execute actions on the database.

CREATE DATABASE AUDIT SPECIFICATION [dbAudit]
FOR SERVER AUDIT [servAudit]
ADD (EXECUTE ON DATABASE::[DatabaseName] BY [public])

We've found that some queries will write to the audit log the use of a scalar function for every row in a result set.  When this happens, the log fills up before we can ETL it into it's final resting place and we have a gap in our logging.

Unfortunately due to compliance reasons, we cannot simply stop auditing every EXECUTE statement.

Our first thought for approach to this problem is to use WHERE clause on the Server Audit to filter out the activity.  The code looked like this:

WHERE [object_id] not in (Select object_id from sys.objects where type = 'FN' )

Unfortunately, SQL Server doesn't allow relational IN operator (probably because it doesn't want to query every time it has to write to the audit log).

We would like to avoid writing a stored proc which hard codes the object_id in the WHERE clause, but that is our current thinking on the best way to approach this problem.  Is there an alternate approach that we should consider?

We've noticed that when the scalar function is in use in a recursive CTE, then it causes the query to write to the audit log for every row in the result set.

There are some Scalar Valued Functions that are delivered by a vendor which we cannot delete or move to an alternate database.

There are a few options that I was able to get working. All of the options deal with variations of filter predicates. NOTE: you must disable the Server Audit in order to make changes, and then re-enable it.

First, the most generic approach is to filter out all Scalar UDFs. You can do that by using the class_type audit field. The documentation indicates that this field is VARCHAR(2), but it doesn't allow specifying a string. However, I did get the following to work:

ALTER SERVER AUDIT [servAudit]
WHERE ([class_type] <> 20038); -- EXECUTE Scalar UDF

(more info on that investigation here: Server Audit Mystery: Filtering class_type gets Error Msg 25713)

The next most generic approach is not an option since it was stated that this is a vendor-supplied database and hence no changes can be made. So I will cover that last.

The least generic approach (but one that definitely works) is to filter out the specific function name:

ALTER SERVER AUDIT [servAudit]
WHERE ([object_name]<>'function_name');

Or, if multiple names:

ALTER SERVER AUDIT [servAudit]
WHERE ([object_name]<>'function_name1' AND [object_name]<>'function_name2');

While not very generic, this approach should be fine since the number of functions to filter out should be fairly small, and it won't be very often that new functions are introduced.

Finally, to others who face this situation and are not restricted from making changes: you can place functions into their own Schema and then filter out just that Schema. This is more generic than filtering out the functions individually. Assuming that you create a Schema named fn and place the function(s) into it:

ALTER SERVER AUDIT [servAudit]
WHERE ([schema_name]<>'fn');

ALSO, regarding the following two comments in the question:

  Unfortunately, SQL Server doesn't allow relational IN operator (probably because it doesn't want to query every time it has to write to the audit log).

and:

  We would like to avoid writing a stored proc which hard codes the object_id in the WHERE clause

The IN operator isn't the issue. True, it is not supported, but it's just shorthand for a list of OR conditions. The actual issue is the use of T-SQL. Only literals — strings or numbers — are allowed. So you wouldn't have been able to execute a Stored Procedure anyway. Nor can you use the built-in functions.


Anything obvious in optimization missed in my left join query? I've really simple join statement, which take a lot time to complete. I would like to know if I'm missing any obvious optimization steps i could take.

So I've got two tables, with the following create statements:

CREATE TABLE `objects` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `positive` tinyint(1) DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT NULL,
  `feature_id` int(11) DEFAULT NULL,
  `cell_size` int(11) DEFAULT NULL,
  `edge_size` int(11) DEFAULT NULL,
  `file_name` char(255) DEFAULT NULL,
  `frame_number` int(11) DEFAULT NULL,
  `left_coord` tinyblob,
  `right_coord` tinyblob,
  `image_info_id` int(11) DEFAULT NULL,
  `image_data_id` int(11) DEFAULT NULL,
  `image_data_size` tinyblob,
  `opcode` char(50) DEFAULT NULL,
  `object_name` char(50) DEFAULT NULL,
  `pose` char(50) DEFAULT NULL,
  `tilt_angle` float DEFAULT '0',
  `weight_id` float NOT NULL,
  `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `video_data_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `image_info_id` (`image_info_id`),
  KEY `feature_id` (`feature_id`),
  KEY `on_ind` (`object_name`),
  CONSTRAINT `feature_link` FOREIGN KEY (`feature_id`) REFERENCES `features` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `image_info_link` FOREIGN KEY (`image_info_id`) REFERENCES `image_info` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1283145 DEFAULT CHARSET=latin1;

And this one:

CREATE TABLE `uchog7x7` (
  `id` int(11) NOT NULL,
  `uchog7x7` blob,
  `iuchog7x7` blob,
  `needs_update` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `needs_update` (`needs_update`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I'm performing the following query:

SELECT
    objects.id
FROM
    objects
        LEFT JOIN
    uchog7x7 ON objects.id = uchog7x7.id
WHERE
    uchog7x7.needs_update = 1
        AND uchog7x7.id IS NOT NULL
        AND object_name = 'face'

This query takes a lot of time to complete. Table named objects has 1,035,526 records, table uchog7x7 contains 639,322 objects.

When I do explain I get the following:

I'm not using file-per-table.

Am I missing something obvious explaining why does it take a lot of time to complete? My guess is that somehow non-desirable full table scans happen, but I do not know for sure.

Thank you.

SELECT  o.id
    FROM  objects AS o
    JOIN  uchog7x7  ON o.id = u.id  -- Not LEFT
    WHERE  u.needs_update = 1
      -- AND  u.id IS NOT NULL   -- not needed
      AND  o.object_name = 'face'

Don't use CHAR() unless the value is a constant length.

What fraction of the rows have 'face'?  What percentage have needs_update?

The optimizer, when given no good choice, starts with the smaller table.  According to EXPLAIN, it found about 1 row with "needs_update".  Probably that is far off, and you need to remove that index.

What version of MySQL are you using?  5.7 has improved the 'cost models', and might do a better job.

Other notes:

Don't use tinyblob; use VARBINARY(255) instead.

Why are *coord and image_data_size going into blobs; they sound like numeric values?

What will you do with the ids that come back from the query?  Some heavy-duty image processing?  If so, consider adding LIMIT 1 (or LIMIT 10) so that the query comes back faster.  I assume you will pick up others later.


How can I always get Timestamp in certain format without having to explicitly define it everytime When I run a select on a timestamp I get something like:

2015-01-02T23:22:36.000Z, 1/7/2015 11:03:31 AM

In order to format properly I have to do:

SELECT to_char(entered, 'MM/DD/YYYY HH12:MI:SS AM')
FROM analytics WHERE business_id = 371746;

which is kind of a pain, since I often just do SELECT * and don't specify the fields.

Is there a way to tell Postgres to always return this kind of format 'MM/DD/YYYY HH12:MI:SS AM' from a SELECT without having to specify it every time?

For general preferences you have the DateStyle and LC_TIME settings.

But it would be madness to always truncate fractional seconds automatically - hiding part of the relevant information.

A possible syntax shortcut would be to create a tiny function once:

CREATE OR REPLACE FUNCTION my_ts(timestamp)
  RETURNS text LANGUAGE sql IMMUTABLE AS
$$SELECT to_char($1, 'MM/DD/YYYY HH12:MI:SS AM')$$;

Then:

SELECT my_ts(entered) FROM analytics WHERE business_id = 371746;


Merging almost unique rows together I have this table

And i need have this (In source table are thousands values)

How can i do that ?

Here is example database: http://sqlfiddle.com/#!3/4278d

Thank you.

Assuming that each row in the source table only has either variable1, 2 or 3 populated, and there is only one such row for each combination, then this is probably the simplest approach:

SELECT Name, Column2,
  MAX(Variable1),
  MAX(Variable2),
  MAX(Variable3)
FROM dbo.DataTable
GROUP BY Name, Column2;

SQLfiddle


DBeaver's Auto Increment field max is 2147483647 - why? In DBeaver, when right-clicking the 'Foreign Keys' to select 'View Foreign Keys', the following informational page is displayed:

I would think that the 'Auto Increment' field value would read 1 (or some other small number) to indicate the step value by which that foreign key is auto-incremented.

Instead, I see a value of MAX INT 32 bit (2147483647).

What does that value mean?

2 to the power of 32 is 4294967296.

If you divide that by 2, you get:

(2^32/2) = 2147483648

This second value minus 1 is 2147483647 = 2^31 - 1, i.e. your value of interest.

Mathematically, it's the 8th Mersenne prime and is also equal to the hexadecimal (base 16) value 7FFF,FFFF. It's also the largest known double Mersenne prime and (probably) the biggest of that class of number!

In databases and computing, it's the maximum size of a 32 bit SIGNED INTEGER. The signed/unsigned INTEGER type is one of the few areas in which MySQL is superior to PostgreSQL. I think that unsigned INTEGERs have many potential uses, but then again, PostgreSQL has CHECK CONSTARAINTs which mean that it's not obligatory to enforce positive integers as a type. Still (personal opinion), I feel it's a good type to have, if only for brevity and clarity!

Previously (in many 32 bit systems), it was the maximum number (i.e. 2147483647) of records allowed in a table and/or the maximum that an AUTO_INCREMENTing (i.e. SERIAL or SEQUENCE) integer field could reach - but this is not now normally the case with 64 bit machines.

When this number was reached, either for the number of records, or for a key of some sort, then the behaviour of many database systems was/is either undocumented or erratic or both. The value could start again from zero (possibly breaching integrity constraints) or start at an arbitrary value or cause the system to crash completely!

On the 19th of January 2038 (at 03:14:07 UTC), there will be more than that many (2147483647) seconds elapsed since Jan 01, 1970, i.e. the beginning of time as far as Unix is concerned. Aircraft will drop out of the sky, nuclear missiles will be launched without provocation and there will generally be a great wailing and gnashing of teeth - unless, of course, you're a progrmamer who can make a mint out of this (just like the COBOLers in 2000!).

  The Year 2038 problem relates to representing time in many digital
  systems as the number of seconds passed since 1 January 1970 and
  storing it as a signed 32-bit binary integer. Such implementations
  cannot encode times after 03:14:07 UTC on 19 January 2038. Just like
  the Y2K problem, the Year 2038 problem is caused by insufficient
  capacity of the chosen storage unit.

Best to avoid completely and use 64 bit these days!


How can delete an orphaned temporary table on Mysql Innodb? I was issuing an alter table statement and suddenly the server crashed. Now I can not run any alter table command on that table, because it shows me error 1050, and says that a table named #sql1504d already exists.

How can I drop that InnoDB temporary table?

Go into the filesystem (DOS prompt, shell prompt, whatever) and delete (rm, whatever) any files starting with #sql

Also check to see how much free space exists on disk.  ALTER usually copies the entire table over.


using pg_prewarm after an ETL load has finished - worth it? I have a database with many small reference data tables and a few large time series tables. The reference data tables can easily fit into memory. I have various ETL jobs that load data, mainly by left joining incoming data onto the existing tables, and insert/update/delete where required.

Does it make sense to call pg_prewarm on all the reference data tables after an ETL job has finished, so as to ensure that all of them are loaded into memory the next time an ETL load fires? Will I notice much of an improvement?

Assume that the memory is sufficiently large so as to not cause an issue with buffer eviction.

Based on your description, prewarm is unlikely to be harmful.  With the information you provide, there is no way to know how helpful it will be.  It probably takes less time to implement than it does to think about whether it is worth implementing it. So just do it and be done with it.

  Does it make sense to call pg_prewarm on all the reference data tables after an ETL job has finished, so as to ensure that all of them are loaded into memory the next time an ETL load fires?

Well, who would be driving this data out of memory in the first place? If you are going to prewarm, it would make more sense to do it immediately before you start a load, not immediately after you finish one in the hopes it sticks around until the next one starts.


Cannot fetch the rowset from OLE DB provider "SQLNCLI" for linked server X I am getting this error while doing concurrent read from same table through linked server. Time out setting is alright. When this error happens, I also start getting the following error for the same query:

"Transaction (Process ID X) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.."

Does this mean that there is a limitation to the number of reads that can be performed through linked server ? If so, what is the limit ? or could this be happening because of some other reason ?

Also the query used is as follows :

SELECT  T.Col1, T.Col2, T.Col3, PG.Col1, PG.Col2, PG.Col3
INTO #TMP_OUTPUT
FROM  linkedserver1.db1.dbo.table1 T WITH (NOLOCK)
      INNER JOIN #temp2 PG
      ON T.Col1 = PG.Col1
WHERE T.Col3 = somevalue

Thanks,
Kanu

No, this is happening because you have a deadlocked resource.  What you need to do is capture the deadlock information with a SQL Trace (you can use SQL Profiler) or with trace flags.

This will give the information (and with a trace, you can get the deadlock graph) to show you what two sessions were having the conflict resulting in the deadlock and a victim to be chosen (which is why you see the error you posted).

Deadlocks happen.  If they are extremely infrequent then it's probably not an issue to worry about.  But if they are happening all the time, then you need to drill down into what query(ies) is(are) causing them.  You may need to rework your DML, or alter your transaction isolation level if it is appropriate (allowing dirty reads is not the answer), or even go with an optimistic isolation level like Snapshot Isolation or Read Committed Snapshot Isolation.  But great consideration needs to be made here, as those optimistic isolation levels utilize row-versioning which will hammer tempdb, so if you don't have an optimial configuration for tempdb you could bring your performance to its knees.


Viewing MySQL Account Resource Limits Is there any way of viewing an account's remaining resources that are allocated to it? I setup an account that's allowed 7200 queries an hour. At any point, could I then run a query to find out how many remaining queries it's allowed?

MySQL must be storing this information somewhere as FLUSH USER_RESOURCES; will reset the counters however, I tried a few variants such as SHOW USER_RESOURCES and they don't seem to display anything. I've also hunted around information_schema and mysql tables.

Is it just not possible to retrieve that information?

User resources are store in "mysql.user" table :

SELECT max_questions,
       max_updates,
       max_connections
FROM   mysql.user
WHERE  user = 'myuser'
AND    host = '%';

Max.


Selecting only one duplicate I have a database of purchase orders and stockroom checkouts, and it seems that the stockroom checkouts tends to get duplicates inserted (we source this data from an older system nightly). What would be the select to get all rows from this table, but only select 1 of the duplicates at a time? I have around 100k duplicates, and my DB guy is still on christmas vacation :|

the tables do have unique keys, but other than the keys the data is duplicated a few thousand times. I'd like to avoid deleting the data (just in case). Server is running MSSQL2012, and i'll edit this post with a table structure when I get back to my desk.

You can use a CTE for this, if you want the row that's returned to be a complete, intact row rather than aggregates of any of the other columns. You can change the ORDER BY to prefer rows by any of the columns (the grouping is by the ones you think should be unique).

;WITH x AS
(
  SELECT col1, col2, col3,
    rn = ROW_NUMBER() OVER
    (
        PARTITION BY unique_columns
        ORDER BY unique_columns, tie_breaker_if_you_care
    )
  FROM dbo.source_table
)
SELECT col1, col2, col3 FROM x WHERE rn = 1;


Help with execution plan analysis I need some help interpreting this execution plan -
https://www.brentozar.com/pastetheplan/?id=SJ5NYLLSL

Whenever this query is ran it uses up a huge amount of CPU and kills the server.

I have little experience in interpreting execution and am not sure what to look at/suggest to improve.  I notice the amount of left joins and the size of some of the tables and would suggest adding an index somewhere but am unsure on what table and what fields.
Thanks.

Comment out the left join to AppHisTeams as you do not use that table.

Creating indexes tends to be an art, you should not just create indexes. You may be able to adjust existing ones.

You should index AppHisScanCodes as it is not using one, maybe on scanid and include [Description] or use scanid as the clustered index.

Can you change the left joins to inner joins? Especially to AppHisActivigity, 30,000,000+ rows, as you are only using it to get to AppHisTeams.

Is MOdate a date field or is it a number?

Can you inner join to AppHisOutput, 20,000,000+ rows?

Look at the existing index IX_AppHis_ABR. Can you add the fields TitleNumber, AppType or IntakeDate with an include statement if they are not in the index already?

Add an index to AppHisOutput on MODate. Index on abr include modate.


PostgreSQL on Desktop vs. Server How does running PostgreSQL on a Windows desktop with pgAdmin III compare to running it on a dedicated server? I am trying to develop a database with very large tables (~60 million rows) with the following hardware:

64-bit Windows 7
Dual quad-core Intel Core i7-4770 CPU @ 3.40 GHz
32 GB RAM

I would like to know if it would be worthwhile to invest in a server or whether the desktop should be sufficient. The tables will be static, so I mainly concerned with the time it will take to read the data and perform basic aggregate and join operations.

Wiki answer collecting partial answers originally left in question comments

That's more than enough, you don't need a server.

60 million rows isn't that big nowadays - unless you've got huge numbers of blobs. Stick with your desktop unless you start to have major performance issues.

PostgreSQL runs better on *nix than on Windows and the number of drives is a big factor too. Nothing beats a ton of SSDs in RAID 10, but you'll be fine with your current setup!

If you want help improving your queries, you should post a question with the query and query plan from explain (analyze). See postgresql-performance for more details on which information is required for questions regarding slow queries.


Multi-tenant reduce number of MongoDB connections Our multi-tenant app has about 4000 databases per tenant and we are having a problem with performance because of too many connections. Creating MongoDB connections on each database is a heavy operation.
We tried the following approaches but still could not reduce the number of connections.

Sharding only applying for the datasets
Increasing the pool size already applied

How to reduce the number of MongoDB connections? Is there any way to split databases into 1000,1000?

You can bind connections per DB at mongodb level but can limit at application end.
You can also increase max connection on mongodb with some tweaking on OS level. Still best approach is to put limit and application end with persistent properties.


Trying to drop database only if it does not exist by using the db_id function fails When the database foo is not yet created and I I run this

IF db_id(N'foo') IS NOT NULL
BEGIN
  ALTER DATABASE [foo] SET OFFLINE
  GO

  DROP DATABASE [foo]
  GO
END

I get a message

  Cannot drop the database 'foo' becuase it does not exist or you do not
  have permission.

Why?

I have confirmed that the sql statement select db_id(N'foo') returns NULL.

This is a parsing error, not a runtime error. You can avoid this by removing the GO commands (these can't exist inside an IF block anyway) and using dynamic SQL.

Why IF DB_ID() instead of just putting the DROP inside TRY/CATCH (or letting it error)?

You may want WITH ROLLBACK IMMEDIATE on the ALTER.

BEGIN TRY
  ALTER DATABASE foo SET OFFLINE WITH ROLLBACK IMMEDIATE;
  DROP DATABASE foo;
END TRY
BEGIN CATCH
  PRINT 'Failure or database does not exist.';
END CATCH

Or

IF DB_ID(N'foo') IS NOT NULL
BEGIN
  EXEC sys.sp_executesql N'ALTER DATABASE foo SET OFFLINE WITH ROLLBACK IMMEDIATE;
    DROP DATABASE foo;';
END

Of course this approach (OFFLINE first) will leave the data and log files intact, which means that if they were created in the default location, if you try to create the same database again, you'll first need to move or rename those files, or manually specify different names in the new file specification.


updating mysql primary keys I need to be able to swap values that are used in a primary key.  Meaning, in one update statement, I need to change id 1 to id 2, and id 2 to id 1.

In other relational databases, (Sql Server and Oracle), it seems that the transaction that does the update applies the Primary key contraint after checking the values if update succeeded.  This seems to be in line with what a transaction should do before it commits the change. In mySql, I get a duplicate entry error code.

Am I missing something?  Is there a way around this?  I'm using ver 5.1.60, InnoDB

CREATE TABLE testUpdateKeys (
 optId INT PRIMARY KEY
 ,_rowId INT
);

INSERT INTO testUpdateKeys SELECT 1,1;
INSERT INTO testUpdateKeys SELECT 2,2;
INSERT INTO testUpdateKeys SELECT 3,3;

SELECT * FROM testUpdateKeys;

-- this should fail
UPDATE testUpdateKeys
 SET optId = 2
WHERE _rowId = 1;

CREATE TABLE testUpdateKeys_oper ( _oper CHAR(1), _rowId INT, optId INT);

INSERT INTO testUpdateKeys_oper SELECT 'U', 2, 1;
INSERT INTO testUpdateKeys_oper SELECT 'U', 1, 2;

SELECT * FROM testUpdateKeys_oper;

-- This works in SQL Server and Oracle
UPDATE testUpdateKeys a, testUpdateKeys_oper b
 SET a.optId = b.optId
WHERE
 a._rowid = b._rowId
AND b._oper = 'U';

SELECT * FROM testUpdateKeys;

DROP TABLE testUpdateKeys;
DROP TABLE testUpdateKeys_oper;

I understand what you are saying, but the reason it works in SQL Server is because it's in a single statement, not because the transaction isn't committed.  In SQL Server you still don't have deferred integrity - EACH statement has to satisfy the constraints, regardless of whether there is an open transaction.  If there was to be a BEGIN TRANSACTION and two UPDATEs it would fail on SQL Server.

Having said all that, in your case, the UPDATE is done in a single statement, and I think you're running into a MySQL defect.

Also: https://stackoverflow.com/questions/6968247/mysql-giving-duplicate-entry-error-when-trying-to-increment-date-field


how big of a role does hardware play? I'm the developer, not the DBA. I don't know much about setting up databases but i'm concern of the setup at my current work.

The server is installed on desktop PC hardware(i7, 16GB RAM, 1 TB SATA disk) running windows server 2003(yup that's correct)

The system that's running on it has 25 users(will most likely increase to 30 soon) with (2 selects, 2 updates, 1 insert) at least twice every minute. Table isn't that big yet either, just reaching 2 million rows with 21 columns. I've checked every query and my indexes works fine.

As in all things SQL Server, the answer is "it depends".  A good database developer can design an application to mitigate SQL Server hardware requirements with attention to detail on database design, index/query tuning, and application design (e.g. appropriate data caching).  So it seems you may have covered that part if you see index seeks rather than scans in the execution plans with the actual number of rows being the minimum needed.

Whether or not your hardware is up for the task will depends much on response time service level agreements (SLAs).  Doing the math at 30 users and twice a minute, the server will need to be able to execute 120 selects, 120 updates, and 60 updates a minute.  This comes to 5 queries per second, which my gut indicates can achieve sub-second response time on the PC class hardware you mentioned, especially if the entire database will fit in RAM (which is probably can based on your description) and you are accessing a small number of rows per query.


What happens if the database node's network fails just after commit and before responding to the client I know the transaction will be rolled back if connection breaks before commiting.
But what if the commit operation reached the sql server and before it could respond to the client, the network breaks?

The transaction is committed on the sql server, how can the client then know if the transaction was committed or not?

The simple answer is, that a transaction is committed after responding to the client. And it's the client that's sending the commit, the server doesn't decide on its own, when a transaction is finished. (I'm having a hard time finding reliable sources for this, I must admit, but you have my word. It makes sense)

I tested something similar after it wasn't clear to me when a statement is written to the slow-query-log (the manual isn't clear about this). For this I throtteled my network bandwidth in Linux with netem. The result was, that the statement duration in the slow-query-log includes the time until the result is transmitted to the client.
Feel free to do a similar test :)


How to connect Microsoft SQL Database with Active Directory Password Authentication (AD Login) I have web application and I wanted to use Active Directory Password Authentication for Database login.

I already setup user login for Active Directory, but it show the error like below.

Help me! please...

Is this an Azure SQL Database or a database on a SQL Server instance?

Active Directory Password Authentication is only available for connecting to Azure SQL Database, so it seems like you're connecting to a SQL Server instance which won't work.

Check out this article for information on the various authentication methods in SQL Server and Azure SQL Database. If you're using an SQL Server instance then it sounds like what you're looking for is Windows Authentication.

Your web application will need to use integrated Windows authentication as well, here is a link to an older article that explains how to do this.


Apache Phoenix: Using MINUS throws an error I'm using Apache Phoenix to query Hbase. I tried to use a simple MINUS operator like we do in good old SQL but it produces an error that I couldn't wrap my head
around.

Here's the query:

select * from NOTIFICATION MINUS select * from NOTIFICATION where SUBJECT = 'datanode';

Here's a screenshot:

  Note: It's not this specific query I'm looking to solve, I just want
  to get MINUS to work. Thanks.

I am not sure which SQL is used by Apache Phoenix and I cannot exclude that your code above contains syntax error but what I know is that the MINUS operator can only be used in Oracle SQL.

I suggest trying it with the EXCEPT operator which is used in Transact SQL:
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sql

If the EXCEPT does also not work, try to imitate the MINUS like it is done in MySQL, which currently does not feature MINUS or EXCEPT at all:
http://www.gokhanatil.com/2010/10/minus-and-intersect-in-mysql.html


Postgres get function owner I would like to be able to get a table of function names and function owner names.

Currently, this query returns a list of functions for a given schema:

  SELECT  proname
  FROM    pg_catalog.pg_namespace n
  JOIN    pg_catalog.pg_proc p
  ON      pronamespace = n.oid
  WHERE   nspname = '<%= schema_name %>';

I can see there is a proowner column which returns the ID of the role.
https://www.postgresql.org/docs/10/catalog-pg-proc.html

What I would like is something that returns the name of the owner, I just don't know enough SQL yet to figure out how to JOIN the tables etc..

|function_name|function_owner|
______________________________
|func1        |func1_owner   |
|func2        |func2_owner   |

If someone could explain how to do this I would be very grateful!

Thanks

There is a simple and a complicated way.

The complicated way is to join with pg_roles and get the user name from there.

But since PostgreSQL hackers don't want to type more than necessary, they came up with something else:

Internally, every object is not identified by its name (which is mutable) but by its “object ID”. This is the number that is for example used in the proowner column of pg_proc.

Now there are the so-called “object identifier types”. Internally such a type is just the oid, but the type output function, which is used for display of the type, renders it as the object's name.

Put that together with PostgreSQL's type cast operator ::, and you can do:

SELECT proname,
       proowner::regrole
FROM pg_proc
WHERE pronamespace::regnamespace::text = 'public';

Additional hint: maybe you would prefer oid::regprocedure over proname.


Restore a Database to a New database (T-SQL) I have a database backup from a database, say SourceData which needs to be restored on another database, same schema, but different name (SourceData_1) using T-SQL. I don't know why I gets the following error, despite specifying REPLACE option. For example I used the links below:

Restore Database script

http://msdn.microsoft.com/en-us/library/ms190447(v=sql.105).aspx

Based on the posts I found on the internet, first I got the logical names of the backup file using script below:

RESTORE FILELISTONLY
FROM DISK = 'C:\Backups\SourceData_20121227.BAK'

Then used the logical names in the following script:

RESTORE DATABASE SourceData_1
FROM DISK = 'C:\DEV\SourceData_20121227.BAK'
WITH
   MOVE 'SourceDate' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SourceData_1.mdf',
   MOVE 'SourceDate_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SourceData_1_log.ldf',
   REPLACE
GO

Below is the error I'm getting:

  MODIFY FILE encountered operating system error 112 (failed to retrieve text for this error. Reason: 15105) while attempting to expand the physical file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SourceData_1_log.ldf'.
  RESTORE DATABASE is terminating abnormally.

I checked the disk space and can confirm it is fine. I assume the target database should exist before running the restore. Is that right?

Any suggestion how to fix this issue?

Thanks.

I believe it has something to do with inadequate disk space. Can you check your application/system event viewer logs if it's throwing an insufficient disk space error? Also figure out the mdf & ldf file sizes of the original database.

If you have created the database already on the 2nd server, check to make sure that file growth is not disabled.

One other option you can try is attaching the mdf file if you can copy that over to the restoring server and see if that works.


What are options for Bi-Directional Replication of SQL Azure Database This questions came from a developer friend who is working with SQL Azure Database.

Scenario: There are two Azure DB servers, one in North America, USA region and another one in South East Asia region. One way replication has been setup for a read-only replica to enable a local client to get data faster. The issue is whenever the client has to write data to the database, they need to write to North America, which incurs large latency issue. As a solution, they want to setup bi-directional replication between two servers to keep them in sync and thus allowing a client to read and write from a server located in their region.

I'm more of SQL Developer than DBA, thus, I'm not familiar with different options that could be available for bi-direction in SQL Azure DB. I have told him about SQL Broker and how I saw it used for near real-time replication and with some effort it could be set up for bi-directional messages, though not sure if it can be implemented in SQL Azure DB.

There is currently no option within SQL Azure for bi-directional (merge) replication. You could alternatively look at Azure Sql Data Sync or SQL Server on an Azure VM.


How can I override the default values for a parameter in a report? I have a report with a parameter that usually gets its default values by running a query that takes another parameter as it's input. This works fine when the report is run interactively.

However, I have a requirement to call this report from code where all the filters are supplied by the code. The code I have for the other parameters works fine:

   string report = "Analysis/Report.aspx?ReportPath=/MyReport";
   string fullReportPath = string.Format("{0}&LocationId={1}&BatchNumber={2}&SearchReference={3}", report, locationId, batchNumber, reference);
   RunReport(fullReportPath);

There is a drop down where the both the available values and default values are retrieved from the database via a query which has the locationId as a parameter. This is so all the values are selected by default.

What do I need to change in the report and/or the code so it works as it does now and I can supply the selected values for this drop down as a parameter?

I've found this blog post which uses the concept of internal or hidden control parameters to use when setting the values of other parameters, but the example given just sets the other parameter to a default value or leaves it blank. I need for the parameter to run the current query.

It appears that SQL Reports actually does what I want automatically - but only if the parameter with the dependent query immediately follows the first query.

We were modifying the report for other reasons and moved the offending drop down higher up the parameter list so it was second (after the query that filled in the location drop down). Having done that, the drop down was populated automatically.

The blog post I linked to in the question mentions that parameter order is important and there's a separate post about that by the same author.


Subquery gives no error for a non-existing column with the same name as in the outer query I have two tables in a MySQL database - t1 with a column c1, and t2 with a column c2.

I run this query:

select * from t1 where c1 in (select c1 from t2);

The above query should give an error as c1 is not present in t2. Instead, it returns all the rows from t1. Another version of the above query with delete which can be much more disastrous:

delete from t1 where c1 in (select c1 from t2);

The above query deletes all the rows from t1 when it is just supposed to give an error.

I have noticed this behavior occurs only when the column in the subquery has the same name as the outer one. Meaning,

select * from t1 where c1 in (select c3 from t2);

will throw an error as expected:

 ERROR 1054 (42S22): Unknown column 'c3' in 'field list'

By the way, I have checked for the same issue on PostgreSQL 9.6.3 and the behavior is exactly the same. Any explanation for this strange behavior?

I run this query:

select * from t1 where c1 in (select c1 from t2);

  The above query should give an error as c1 is not present in t2.
  Instead, it returns all the rows from t1.

No, the query should not give an error. It's a common mistake (thinking that the c1 in (select c1 from t2) refers to t2. It doesn't due to scope resolution, i.e. how column names are resolved (how it is found which table they are referring to). The query:

select * from t1 where c1 in (select c1 from t2);

Can resolve to three different options:

when t2 has a column named c1, it runs as:

select * from t1 where c1 in (select t2.c1 from t2);

when t2 does not have a column named c1, but t1 has, it runs as:
(this is your case!)

select * from t1 where c1 in (select t1.c1 from t2);

and when neither t2 nor t1 have a column named c1, it will throw an error:

select * from t1 where c1 in (select c1 from t2);

-- Error is thrown ("Unknown column c1" or something like that)

  Another version of the
  above query with delete which can be much more disastrous:

delete from t1 where c1 in (select c1 from t2);

  The above query deletes all the rows from t1 when it is just supposed
  to give an error.

For the same reasons, no. The query resolves to and runs as:

delete from t1 where c1 in (select t1.c1 from t2);

so it will delete all rows from t1 as long as t2 table is not empty.

How to avoid these problems?

Always prefix column references with their table names. By doing this, you will always have the result you want or get an error if the column doesn't appear in the table you are prefixing it with.

Your queries should be:

select t1.* from t1 where t1.c1 in (select t2.c1 from t2);

delete from t1 where t1.c1 in (select t2.c1 from t2);

Both of them will throw an error if there is no column c1 in table t2.


Postgresql, export to json Version: PostgreSQL 11.4 (Ubuntu 11.4-1.pgdg19.04+1).

Question: Is possible export to json file? Something like csv export.

COPY ( SQL )
TO stdout DELIMITER '"' csv;

Thanks.

Update: Or can I add escape character \ just for fields param_name and value for this?

string_agg(distinct '{name:"' || param_name || '",value:"' || value || '"}',',') as "params",

Result is:

{name:"připojení",value:"1/2""},

I need this (use \ before " only for fields param_name and value:

{name:"připojení",value:"1/2\""},

Update2: Whole sql command added. How to implement function row_to_json? Just add row_to_json to every item in select I have? For example:

select row_to_json(string_agg(distinct '{name:"' || param_name || '",value:"' || value || '"}',',') as "params"),

...etc...

Whole sql.

with recursive
cte as(
select
category_id,
category_parent,
category_name::text,
category_id::text category_ids
from s_category as c
where category_parent = 0
union all
select
c.category_id,
c.category_parent,
concat(cte.category_name, ' > ', c.category_name),
concat(cte.category_ids, ':', c.category_id::text)
from s_category as c,cte
where cte.category_id = c.category_parent
)

select distinct
s_product.product_id as "itemID",
s_product.product_id as "itemGroupID",
product_shop_id as "productCode",
product_ean as "ean",
product_name as "title",
product_short_label as "description",
concat('https://eshop.unihobby.cz/',product_url,'/',s_product.product_id,'p/') as "link",
concat('https://eshop.unihobby.cz/bin/product/4/',filename) as "image",
price_tax as "price",
price_rec as "priceOriginal",
available_count as "available",
case
when available_count >= 5 then 'SKLADEM > 5 KS'
when available_count < 5 then 'SKLADEM < 5 KS'
when available_couNt = 0 then 'NENÍ SKLADEM'
end as "availability",
producer_name as "brand",
concat('Úvod > ',cte.category_name) as "category",
s_category.category_id as "categoryID",
cte.category_ids as "hierarchy",
string_agg(distinct '{name:"' || param_name || '",value:"' || value || '"}',',') as "params",
case when price_tax = price_rec then '' else 'Akce' end as "label"

from
s_product
left join s_cf_j_product_value on s_product.product_id = s_cf_j_product_value.product_id
left join s_product_image on s_product.product_id = s_product_image.product_id
left join s_pricelist_generated_lists on s_product.product_id = s_pricelist_generated_lists.product_id
left join s_producer on s_product.producer_id = s_producer.producer_id
left join s_category on s_product.category_id = s_category.category_id
left join cte on s_product.category_id = cte.category_id
left join s_cf_j_product_value as a on s_product.product_id = a.product_id
left join s_cf_value as v on a.value_id = v.value_id
left join s_cf_param as p on v.param_id = p.param_id

where
image_order = '0'
and s_category.category_name is not null
and product_active = 'y'
and s_pricelist_generated_lists.group_id = '10'
--and s_product.product_id = '133471'

group by
s_product.product_id,
s_product_image.filename,
s_category.category_id,
s_pricelist_generated_lists.price_tax,
s_pricelist_generated_lists.price_rec,
s_producer.producer_name,
cte.category_name,
cte.category_ids
;

Is possible export to json file? Something like csv export.

Yes. Try this:

select row_to_json(tablename.*,true) from tablename;

or

copy (select row_to_json(tablename.*,true) from tablename) to stdout;

if you need copy.

See row_to_json along with other JSON functions in the documentation: JSON Functions and Operators.

I wouldn't recommend CSV on top of JSON because double quotes are special for both CSV and JSON, and they will be doubled for CSV, which makes the output less readable.

The same goes more or less for the text format of COPY. A plain SELECT without any additional quoting may be simpler.

Update: tablename can be a subquery. For the large subquery in the updated question, you may write

select row_to_json(v.*) FROM ( ...subquery here... ) AS v;

Alternatively, a temporary view may also be helpful to improve readibility or ease of use.

CREATE TEMPORARY VIEW tmpview AS ...subquery here...;

SELECT row_to_json(v.*,true) from tmpview;

The temporary view will be automatically removed at the end of the session.


Simulating having multiple Control Flows in a SSIS package I have a SSIS package that in its Control Flow I have quite lot of Data flows. To be able to organise them, I have wrapped the related Data flows into Groups. For example I have 5 groups as below which each one has got on average 8 Data flows. For example:

Students Group (includes a number of Data flows)

Courses Group (includes a number of Data flows)

Institutes Group (includes a number of Data flows)

Enrollments Group (includes a number of Data flows)

...

Now I see the need for adding more and more Data flows to each group is growing! Each of these groups are already big and adding more Data flows only make them more crowded and more difficult to maintain.

Based on my research there is no way to have multiple Control flows in a package, otherwise I would put each of the Data flows above in a Control flow, e.g.: one Control flow for Students, one for Courses, etc.

I know I can create a package for each area, for example one package for Students, etc, but I prefer to avoid adding two many packages as much as possible.

Do you have any idea how I can organise them in a package in a sustainable and clean manner?

Thanks.

Don't use groups, use Sequence Containers. That way, you can have the ones you want to run in parallel in the same container, and then have a subsequent container kicked off with additional data flows.

You can't have multiple Control Flows, but you can have multiple packages, and you can have a ton of containers to give you more control over the flow.


fragmented index not defragmenting after an index rebuild I have a weird behavior that I'm trying to explain to a client with SQL express 2014 (12.0.2269.0).  The table in question has 4.5 million rows and has over 60 thousand pages.  The average fragmentation for that index is 54% before and after the rebuild.  the only way I was able to fix the issue was to drop and recreate the clustered index.

Has anyone ever witnessed this behavior?
Can it be as simple as upgrading to SP2, although I didn’t find any direct correlation with the SP1 and SP2 release notes?

Any help would be greatly appreciated.

This is the query I use to look at the average fragmentation level:

SELECT t.name AS TableName, sch.name as SchemaName, i.name AS IndexName, s.avg_fragmentation_in_percent, ROW_NUMBER() OVER (PARTITION BY t.name order by t.name) as RowNum, TR.RowCnt
                FROM SYS.TABLES t
                INNER JOIN sys.schemas sch on t.schema_id=sch.schema_id
                JOIN SYS.INDEXES i ON t.object_id = i.object_id
                JOIN SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(),NULL,NULL,NULL,NULL) s ON t.object_id = s.object_id AND i.index_id = s.index_id
                LEFT JOIN (SELECT o.OBJECT_ID, ddps.row_count as RowCnt
                                FROM sys.indexes AS i
                                INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
                                INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
                                AND i.index_id = ddps.index_id
                                WHERE i.index_id < 2
                                AND o.is_ms_shipped = 0) TR ON TR.object_id = t.object_id
                WHERE t.type  = 'U'
                AND s.avg_fragmentation_in_percent > 5
                AND i.name is not null
                ORDER BY t.name, s.avg_fragmentation_in_percent

Yes I see that fragmentation all the time with GUID columns, due to the random inserts and page splits. In your case, this could be a result of a non-standard fill factor to account for GUID fields.

A clustering key should be on an ever-increasing, narrow data type, like INT or BIGINT, and the fill factor set as close to 100 (or 0) as possible.

That all said, rebuilding a clustered index due to fragmentation is a time-consuming process, and it generates a lot of transaction log, so it's generally more accepted to simply update the statistics fairly frequently.

You can worry about fragmentation less frequently, say once a month.


Short CIrcuit in WHERE clause How to make short circuit in where clause, e.g:

select 1 where 1=2 AND 1/0=0

In the above example 1/0=0 condition should not be evaluate as the first condition is false.

Just like ypercube commented, Sql Server doesn't adhere to similar a boolean evaluation as, say, Java or C#. There are lots of articles about this, but facts are a bit hard to find.

As per the documentation:

  The order of evaluation of logical operators can vary depending on
  choices made by the query optimizer.

This behavior is caused by the fact that SQL is a declarative language. In such a language one describes the desired output, not the steps how to build the output. It is up to the RDBMS how to build the output. This often is counter-intuitive to programmers used to procedural and OOP approach, in which one tells explicitly step-by-step what the application should do.

If you got queries that mis-behave with boolean logic evaluation order assumption, I'm afraid you got to re-write those.


Powershell SQL Server High Availability PrimaryReplicaServerName - inconsistent results I developed a simple PowerShell script to list which High Availability replica is the current Primary.  Seemed to work.  Did a manual failover to test it, but results become inconsistent, often describing both replicas as Secondary.
Simple illustration code:

Import-Module -Name SQLPS -DisableNameChecking
"Server1:"
cd \sql\Server1\WSSAPPS\AvailabilityGroups
gci | select Name, LocalReplicaRole, PrimaryReplicaServerName
""
"Server2:"
cd \sql\Server2\WSSAPPS\AvailabilityGroups
gci | select Name, LocalReplicaRole, PrimaryReplicaServerName

If Server2 is Primary AND I run the code on the Server2, the results seem fine:

Server1:

Name                 LocalReplicaRole      PrimaryReplicaServerName
----                 ----------------      ------------------------
SP-STAGE-APPS               Secondary      Server2\WSSAPPS

Server2:

SP-STAGE-APPS                 Primary      Server2\WSSAPPS

But if I run exactly the same code on Server1 (cut and paste) with Server2 still as Primary I get different results:

Server1:

Name                 LocalReplicaRole      PrimaryReplicaServerName
----                 ----------------      ------------------------
SP-STAGE-APPS              Secondary       Server2\WSSAPPS

Server2:
SP-STAGE-APPS              Secondary       Server2\WSSAPPS

This is now reporting BOTH replicas have the role Secondary!
Can anyone explain why these results should differ?
Please don't say 'Just always run it on the primary then' The whole point is to give people the information as to which instances are primary!  I've got 11 AGs on 12 replicas to monitor.
Once I understand what's happening I can mod my main script code accordingly.
Thanks

Whether you run that code while you are on the primary or secondary is irrelevant. When your path is SQLSERVER:\SQL\SERVERx\INSTANCE... you are technically running it against that server, it does not matter if you ran the command from the server or a random desktop in the domain.

The issue with it showing as secondary though is local. I do not get that on my servers. If, you had PowerShell and the provider opened on "Server1" when you failed it over then I would suggest opening a new PowerShell prompt and see if it shows the same thing. If it does then try from another computer all together.


designing server side database to store user-specific data I have a database with a number of quiz questions and answers. My basic design consists of these two tables

questions (question_id, question_string);

answers (answer_id, answer_string, correct_answer, question_id);

I would then like to keep track of user data, such as the number of times the user has seen a particular question. Assuming each user has their own unique ID, how could I go about designing a schema to store the data efficiently?

At the moment, I can only think of a table (user_id, question_id, number_of_times_seen). With thousands of users and thousands of questions, this table would grow rather quickly and might be a problem?

There are 2 scenarios to consider. The first is when you have the user take 1 or more questions without a meaningful context such an exam or formal quiz. In which case you solution works. However, the second scenario is about when the user takes a question as part of a formal session. In this case you have the following rules:

A user takes zero, one or more quizes
A quiz contains 1 or more questions
A user provides an answer for zero,1 or more specific question on a specific quiz

Note:

Table names may be different in the solution than from this diagram

A1 = Standard answer for question 1

UA1 = User provided answer for question 1


SQL Server uses only PRIMARY partition by default My database has 2 partitions files: PRIMARY, DATABASE_ARCHIVED. A lot of tables have a column "archived". When archived is 1 row is in the DATABASE_ARCHIVED partition. When archived is 0 row is in the PRIMARY partition.

To improve performance I started to modify requests in the application by adding archive=0 in WHERE and JOIN.

select *
from myTable1 t1
join myTable2 t2
on t1.id = t2.t1id
and t2.archive=0
where t1.archive=0

But there are a lot of requests, and I might make mistake when typing and won't be able to test everything.

Is there a way to force all requests on the PRIMARY partition and use the ARCHIVED partition on demand? In the application there is a checkbox to see archived data or not.

Not checked : look for data in PRIMARY ONLY, checked : look in both partition.

Can I do this?

No, if you are talking about partitioned tables, sql uses the where clause to filter rows. if your query doesn't have the where clause it will return all rows.
if you can't test your changes you probably shouldn't be making them.


Are these linked server queries susceptible to SQL injection? I'm using a stored procedure to generate a create table script on the fly based on parameters passed into it. I am validating that the server exists, and then validating if the table exists in the database on said server.

So, first I check if the server exists:

Exists(SELECT 1 FROM sys.servers WHERE name = @Server)

If it passes this check I go on to build a string that points to Information_schema:

set @InformationSchemaPath = @Server + '.' + @DataBaseName + '.' +  'information_schema'

and then use that string in a dynamic SQL statement that gets put into a string:

set @DynamicSQL = ('SELECT count(*)
             FROM ' +  @InformationSchemaPath + '.Tables
             WHERE  TABLE_NAME = ''' + @TableName + '''')

The results get inserted into a temp table:

INSERT INTO @receiver Exec (@DynamicSQL )

I am unable to use SP_EXECUTESQL. I am stuck using Exec.

Yes, this is unsafe. Try passing this:

@TableName = 'FakeTableName'';DROP TABLE Foo;PRINT''You have been Pwned'

One way to avoid this is to pass @TableName as a parameter to the dynamic SQL. Like so:

SET @DynamicSQL = '... WHERE TABLE_NAME = @TableName'

And then do this:

INSERT INTO @Receiver EXEC sp_executesql @DynamicSQL, @TableName

From the wording of your question, I am not sure if you are saying that you can't run sp_executesql or that you are not sure about the syntax. If you have to run the statement using only EXEC there is a method that works, but only on a Linked Server. You do do this:

SET @DynamicSQL = '... WHERE TABLE_NAME = ?'

Followed by this:

INSERT INTO @Receiver EXEC (@DynamicSQL, @TableName) AT YourLinkedServer


MySQL query is very slow (15 minutes) I was previously using SQLite for a personal project and due to a constraint of having it available online, I decided to make the switch to MySQL. I converted my database to the MySQL equivalent but I just noticed that performance is VERY poor. This is a 70 mb database with around 600k records total. The query I am running is an INNER JOIN that executes in less than 500 ms using SQLite but the same query using MySQL takes 15 minutes.
SELECT has.tag_id, has.image_id
FROM has
INNER JOIN image ON image.image_id = has.image_id
INNER JOIN person ON person.person_id = image.person_id
WHERE person.name="Random Person"

has table has 80k records
image table has 290k records
person table has 500 records

Here is the structure of the three tables:
create table media.person
(
    person_id int auto_increment
        primary key,
    name         text not null
)
    collate = utf8_unicode_ci;

create table media.image
(
    id           int auto_increment,
    image_id     int  not null,
    person_id int  not null,
    link         text not null,
    checksum     text null,
    constraint id
        unique (id)
)
    collate = utf8_unicode_ci;

alter table media.image
    add primary key (id);

create table media.has
(
    id       int auto_increment
        primary key,
    tag_id   int not null,
    image_id int not null
)
    collate = utf8_unicode_ci;

Note that I added a primary key to the has table because I suspected it might have been the source of the problem, but it isn't and SQLite was doing fine without that primary key.
The database uses the InnoDB engine. Here is the output of the mysql --version command:
mysql Ver 14.14 Distrib 5.7.30

Where could the problem come from? I can understand a small loss of performance because MySQL is heavier than SQLite but certainly not to the point of going from 500 ms to 15 minutes for such a simple query.
EDIT:
I just ran the query again, this time it took 7 minutes. I used slow_query_logs but it doesn't tell me much.
# Time: 2020-07-13T07:18:22.526915Z
# User@Host: media[media] @ localhost [127.0.0.1]  Id:   205
# Query_time: 429.434270  Lock_time: 0.000751 Rows_sent: 501  Rows_examined: 223451
use media;
SET timestamp=1594624702;
/* ApplicationName=DataGrip 2020.1.3 */ SELECT has.tag_id, has.image_id
                                FROM has
                                INNER JOIN image ON image.image_id = has.image_id
                                INNER JOIN person ON person.person_id = image.person_id
                                WHERE person.name="Random Person";

You need to add indexes:

image (person_id, image_id)
has (image_id, tag_id) - you can remove id column from this table and make this index a primary key
It is not the best idea to use "text" data type for person.name column. It would be better to use varchar or nvarchar. For example, varchar(100) should be enough. Then create index on this column too: person (name).


Secondary reporting SQL Server I have a SQL Server 2012 Std cluster that has a lot of writes going on. We'd like to offload some of the reporting to a separate (2012 Std) VM. It can be around 24 hours out of date.

I think log shipping is probably a good approach but I'm concerned I won't be able to compress the tlogs (they'll be big). My question is, can I manually set up log-shipping, essentially leaving the primary untouched and just restore 24 hours worth of transactions on the secondary each day - whilst still being able to read from the secondary during the day?

EDIT: After doing some testing and with the help from users here, it looks like I can just "manually" restore 24 hours' worth of backups each day by going SINGLE_USER then restoring all the tlogs WITH STANDBY, essentially log shipping but without touching the primary at all.

I think log shipping is probably a good approach but I'm concerned I won't be able to compress the tlogs

Since you are using logshipping on SQL Server 2012, you can compress your transaction log backups

  (they'll be big).

Take frequent log backup, so that the size of the T-logs is manageable and you are using backup compression (see above screenshot). The minimum is 1 min.

  My question is, can I manually set up log-shipping, essentially leaving the primary untouched and just restore 24 hours worth of transactions on the secondary each day - whilst still being able to read from the secondary during the day?

Yes you can do what you are saying. You can delay the restoration of log backups on the secondary server. So, just have to copy job to copy the log backups from primary to secondary and depending on your need and business requirement, you can delay the log restoration.

Note that when T-logs are being restored, users will be disconnected.

EDIT:

You can put inplace of allowing only COPY_ONLY log backups so that it does not break your log backup chain. I have written it in my answer here. Also, remember that a full backup will never break a log chain. It can mess up a differential backup though. So, its a good practice to have COPY_ONLY backups!


Full GROUP BY in MySQL 5.7 breaks de-dupe queries Prior to MySQL 5.7, I was able to dedupe queries by using GROUP BY as long as the query results were done in such a way that the duplicates had the same data and were already in the sort order I wanted.

I got really good at that.

Now, with 5.7 and only_full_group_by mode, those queries are invalid. I've already been through the 5 stages of grief over it, and am working to update / improve my queries to be more standard.

the problem is: when I do a LEFT JOIN (in some cases) it produces rows and rows of (essentially) duplicate information. I get that they are not technically duplicates because some column may be different. And, I see a lot of questions about this in a general way. One such use case is "find the highest value for X in the day." You used to just use ORDER BY x DESC with a GROUP BY  and poof you had it. Now, you have to do LEFT JOINs and look for NULLs. (Part of me still wonders "why is this better?" but that's off topic).

....but I want to know what best practice replaced using GROUP BY to remove these duplicates? SELECT DISTINCT?

SELECT DISTINCT dedups on all columns. Sounds like that is what you want. GROUP BY has other purposes.

But I smell another problem here...  A common pattern is

SELECT
    ... some columns, some aggregates
    FROM a
    JOIN b  ON ...
    WHERE ...
    GROUP BY some columns

This is rather inefficient (even when you don't run into the "only group by" restriction).  First the JOIN is done.  This inflates the number of rows, and builds a big tmp table.  Then the GROUP BY is used to deflate it, often back to exactly the number of rows that are in one of the tables.

There are a couple of workarounds.  But the depend on the details of the query (please show us the query).

If all the aggregates are on columns in, say, b, then use a derived table to first compute all the aggregates from b, then join the result of that to a to get the rest of the columns.

If there is only one aggregate, and even if the JOIN is actually LEFT JOIN, then

SELECT some columns,
       ( SELECT ... FROM b WHERE ... GROUP BY ...) -- correlated subquery
    FROM a
    -- no GROUP BY needed here, so no inflate-deflate inefficiency


Carry over long sequence of missing values with Postgres I have a table like this:

create table foo (foo_label text, foo_price int, foo_date date);

insert into foo (
          values
          ('aaa', 100,  '2017-01-01'),
          ('aaa', NULL, '2017-02-01'),
          ('aaa', NULL, '2017-03-01'),
          ('aaa', NULL, '2017-04-01'),
          ('aaa', 140,  '2017-05-01'),
          ('aaa', NULL, '2017-06-01'),
          ('aaa', 180,  '2017-07-01')
        );

As you can see a few values on the foo_price column are missing.

What I need is that missing values get filled up with the "previous" available value in this way:

 foo_label | fixed_foo_price | foo_date
-----------+-----------------+------------
 aaa       | 100             | 2017-01-01
 aaa       | 100             | 2017-02-01
 aaa       | 100             | 2017-03-01
 aaa       | 100             | 2017-04-01
 aaa       | 140             | 2017-05-01
 aaa       | 140             | 2017-06-01
 aaa       | 180             | 2017-07-01

My attempt:

select
    foo_label,
    (case when foo_price is null then previous_foo_price else foo_price end) as fixed_foo_price,
    foo_date
from (
  select
      foo_label,
      lag(foo_price) OVER (PARTITION BY foo_label order by foo_date::date) as previous_foo_price,
      foo_price,
      foo_date
      from foo
) T;

As you can see from here:

https://www.db-fiddle.com/#&togetherjs=s6giIonUxT

It doesn't fill completely the '100' series.

Any idea how can I get the wanted result?

I would form groups with the window function count() and then take the first value for each group:

SELECT foo_label
     , first_value(foo_price) OVER (PARTITION BY foo_label, grp ORDER BY foo_date) AS fixed_foo_price
     , foo_date
FROM  (
   SELECT foo_label
        , count(foo_price) OVER (PARTITION BY foo_label ORDER BY foo_date) AS grp
        , foo_price
        , foo_date
   FROM   foo
   ) sub;

This works because count() only counts non-null values. So all rows with NULL end up in the same group as the last row with an actual value. Exactly what you need.


Can an active Postgres serverlog be truncated? Running Postgres 9.1 on Ubuntu 14.10 LTS.

I've got an application running on a machine that's going to run out of diskspace soon.  However, the application is in the middle of a crucial run that has taken 6 days thus far, and canceling it would mean another 6-day run.

I've deleted everything I can for which I have permissions, but I'd like to truncate the server log to free up some space.

If I were to copy the server log off and then do a cp /dev/null /database/serverlog would I risk causing the server to hang?  Or since the file reference has not changed, will it keep chugging along?

I realize the server log I copy off will have incomplete entries at the end and the new servlog will have incomplete entries at the beginning, so it's obviously not an optimal situation if I had to try to restore data using those files as there would be transactions missing.  However, I think the likelihood of actually needing to use the serverlog files before I have a chance to reconfigure and restart the server.

By default PostgreSQL logs to stderr, from PostgreSQL Documentation 18.8. Error Reporting and Logging:

  PostgreSQL supports several methods for logging server messages, including stderr, csvlog and syslog. On Windows, eventlog is also supported. Set this parameter to a list of desired log destinations separated by commas. The default is to log to stderr only. This parameter can only be set in the postgresql.conf file or on the server command line.

If you just direct stderr to file according to the documentation this means you have to stop and restart the server to truncate, 23.3. Log File Maintenance:

  If you simply direct the stderr of postgres into a file, you will have log output, but the only way to truncate the log file is to stop and restart the server. This might be acceptable if you are using PostgreSQL in a development environment, but few production servers would find this behavior acceptable.

About manually deleting the file, take a look at this thread from PostgreSQL mailing lists (from year 2010):

  What platform?  If in Unix/Linux, just:
  cat /dev/null > postgres_logfile
  or
  postgres_logfile
  Most running programs don't like it when you delete a file with an open file handle but you it doesn't mind if you clear it on the fly.  Not sure how to do this in a Windows environment.

And finally here's a good blog post more generally about PostgreSQL logs.