What is the difference between COUNT(*) and COUNT(*) OVER() Take the following code example:

SELECT MaritalStatus,
       COUNT(*) AS CountResult
       COUNT(*) OVER() AS CountOverResult
       FROM (schema).(table)
       GROUP BY Marital Status

COUNT(*) Returns all rows ignoring nulls right?

What does COUNT(*) OVER() do?

This question came in a practice exam so I didn't have the data to query.  I have been using Adventure Works and this site http://www.sqlishard.com/Exercise to practice.

If I enter a query like

SELECT ID, COUNT(*) AS 'COUNT(*)' , COUNT(*) OVER() AS 'COUNT(*) OVER()'
FROM Customers
GROUP BY ID

into the practice site I get 3794 rows returned with the Count(*) column full of ones and the Count(*) Over() column full of the total number of rows.  I didn't understand this pattern (sorry) so I came here.

COUNT(*) Returns all rows ignoring nulls right?

I'm not sure what you mean by "ignoring nulls" here. It returns the number of rows irrespective of any NULLs

SELECT COUNT(*)
FROM (VALUES (CAST(NULL AS INT)),
             (CAST(NULL AS INT))) V(C)

Returns 2.

Altering the above query to COUNT(C) would return 0 as when using COUNT with an expression other than * only NOT NULL values of that expression are counted.

Suppose the table in your question has the following source data

+---------+---------------+
|  Name   | MaritalStatus |
+---------+---------------+
| Albert  | Single        |
| Bob     | Single        |
| Charles | Single        |
| David   | Single        |
| Edward  | Married       |
| Fred    | Married       |
| George  | NULL          |
+---------+---------------+

The query

SELECT MaritalStatus,
       COUNT(*) AS CountResult
FROM   T
GROUP  BY MaritalStatus

Returns

+---------------+-------------+
| MaritalStatus | CountResult |
+---------------+-------------+
| Single        |           4 |
| Married       |           2 |
| NULL          |           1 |
+---------------+-------------+

Hopefully it is obvious how that result relates to the original data.

  What does COUNT(*) OVER() do?

Adding that into the SELECT list for the previous query produces

+---------------+-------------+-----------------+
| MaritalStatus | CountResult | CountOverResult |
+---------------+-------------+-----------------+
| Single        |           4 |               3 |
| Married       |           2 |               3 |
| NULL          |           1 |               3 |
+---------------+-------------+-----------------+

Notice that the result set has 3 rows and CountOverResult is 3. This is not a coincidence.

The reason for this is because it logically operates on the result set after the GROUP BY.

COUNT(*) OVER () is a windowed aggregate. The absence of any PARTITION BY or ORDER BY clause means that the window it operates on is the whole result set.

In the case of the query in your question the value of CountOverResult is the same as the number of distinct MaritalStatus values that exist in the base table because there is one row for each of these in the grouped result.

Count(*) over () returns the resultset number from your query whether you have a condition or not.


SQL Server LocalDB Instance; error restoring backup (MasterDBPath not available) I have SQL Server 2012 Local DB Installed, and am connecting to it via (localdb)\v11.0.

When trying to restore a backup file, I get:

  TITLE: Microsoft SQL Server Management Studio

  Property MasterDBPath is not available for Information
  'Microsoft.SqlServer.Management.Smo.Information'. This property may
  not exist for this object, or may not be retrievable due to
  insufficient access rights.  (Microsoft.SqlServer.Smo)

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1846+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=MasterDBPath&LinkId=20476

I have tried setting MasterDBPath string value in:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11E.LOCALDB\MSSQLServer\CurrentVersion`

but this hasn't helped.

Anyone have any thoughts?

I had the same problem, and solved it.

You need to add 2 strings in the Windows Registry via regedit.exe at
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11E.LOCALDB\MSSQLServer:

DefaultData
DefaultLog

For me, their values are both C:\Users\neil\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\v11.0.

The problem was fixed after I installed the newest Microsoft SQL Server Management Studio 2017.


SQL Server 2005: Job scheduled every 4 hrs, but sp_help_jobactivity shows unexpected value for next_scheduled_run_date I have done a lot with sql and jobs, and this is throwing me off.

My job is scheduled as shown below.

I can run the job interactively from SSMS by starting the job, it runs, all is well.

It is currently Jan 27 at 11 AM local time.
But sp_help_jobactivity returns a next_scheduled_run_date of

next_scheduled_run_date
2012-01-27 12:01:00.000

I expect it to be four hours from the last time I ran it, or from when I created the job and schedule (which I created at the same time).

Observed Problem: While the SSMS GUI displays "12:01 AM" the data in next run shows "12:01:00" which is "12:01 PM"

What am I missing?
When will the job next run?

Thanks!

The job runs every 4 hours starting with the start time in the schedule. If you run it manually that has no effect on the next scheduled startstart time.

OK, the above comments are the story. Basically, the GUI is not very clear.

As stated, when you set a job to run every 4 hrs, with a start time of 12:01 AM, then SQL works out the sequence of 00:01, 4:01, 8:01, 12:01 PM, and so you see the 12:01 PM.

There does not seem to be a way to say "every four hours, starting now and every four hours thereafter"

The issue could be solved with a GUI improvement so that the GUI simply spells out what is going to happen.


Are certain environment variables required for mysql login-path? I created a bash script that backs up my database, db_backup.sh

It basically just runs these commands:

DB=/home/linuxuser/backups/backupfilename.sql.gz
mysqldump --login-path=local db_name | gzip > "$DB"

When I run it from the command line as linuxuser or as root, it works.

But when I add it to cron.d, I get a mysql error and the sql.gz file is empty.

mysqldump: Got error: 1045: Access denied for user 'linuxuser'@'localhost' (using password: NO) when trying to connect

(I get the same type of error when running the cron as root, too)

How can I fix this? Could it be a problem with environment variables?

I tried changing:

HOME=/

to

HOME=/linuxuser/

... but that did not help.

mysqldump needs -u and -p for some mysql "user".


What's the difference between POINT(X,Y) and GeomFromText("POINT(X Y)")? I'd like to store some geometric positions in my MySQL database. For this I use the POINT datatype. Almost everywhere I read that the function GeomFromText should be used to insert data in the table.

However, I found out that POINT(X,Y) also works. I didn't find any description why GeomFromText should be used instead of POINT.

For example I have the following simple relation:

CREATE TABLE Site (
    SiteID      BIGINT UNSIGNED,
    Position    POINT
);

And I can insert values using the following two variants:

INSERT INTO Site (
    1,
    GeomFromText( 'POINT(48.19976 16.45572)' )
);

INSERT INTO Site (
    2,
    POINT(48.19976, 16.45572)
);

When I view the table (SELECT * FROM Site) I see the same binary blob for the location, and when I view the coordinates (SELECT *, AsText(Position) FROM Site) I also see the same values.

So why should GeomFromText be used? Are there any (known) performance differences between these two variants? How is this solved in other database systems than MySQL?

There are two different binary formats related to the MySQL spatial extensions, the "well-known binary" (WKB) format from the standards, and the MySQL internal GEOMETRY data type.

Prior to MySQL 5.1.35, functions like POINT() didn't return the MySQL internal data type; they returned WKB... so prior to then, you had to do this:

INSERT INTO t1 (pt_col) VALUES (GeomFromWKB(Point(1,2)));

But now, as in your example, this works:

INSERT INTO t1 (pt_col) VALUES(Point(1,2));

To the developers' credit, when they changed Point() and similar functions to (more sanely) return GEOMETRY objects, they allowed GeomFromWKB() and similar functions to actually accept either WKB or MySQL Geometry data as input even though the functions are intended to accept WKB as input.

The fact that the 1st method works (in spite of being technically wrong) on newer servers and the 2nd method doesn't work at all prior to MySQL 5.1.35 might explain why examples were written using the approach you've seen -- to avoid the issue entirely.  Otherwise... I've got nothing, here.

Concatenating and then parsing text seems intuitively slower and more error-prone than functions that accept proper variables as input, so I can't think of any reason to craft concatenated strings and use the text-based functions.

http://dev.mysql.com/doc/refman/5.1/en/creating-spatial-values.html#gis-wkb-functions

http://dev.mysql.com/doc/relnotes/mysql/5.1/en/news-5-1-35.html

With GeomFromText or any other *FromText function you can specify the SRID. I don't think you can do it otherwise.

PointFromText('POINT(lat lng)', 4326)


Atrocious performance joining INSERTED and DELETED tables in a trigger I've got an UPDATE trigger on a table that watches for a specific column changing from one specific value to any other value. When this happens, it updates some related data in another table via a single UPDATE statement.

The first thing the trigger does is check to see if any updated rows had the value of this column changed from the value in question. It simply joins INSERTED to DELETED and compares the value in that column. If nothing qualifies, it bails out early so the UPDATE statement doesn't run.

IF NOT EXISTS (
    SELECT TOP 1 i.CUSTNMBR
    FROM INSERTED i
        INNER JOIN DELETED d
            ON i.CUSTNMBR = d.CUSTNMBR
    WHERE d.CUSTCLAS = 'Misc'
        AND i.CUSTCLAS != 'Misc'
)
    RETURN

In this case, CUSTNMBR is the primary key of the underlying table. If I do a large update on this table (say, 5000+ rows), this statement takes AGES, even if I haven't touched the CUSTCLAS column. I can watch it stall on this statement for several minutes in Profiler.

The execution plan is bizarre. It shows an Inserted Scan with 3,714 executions, and ~18.5 million output rows. That runs through a filter on the CUSTCLAS column. It joins this (via nested loop) to a Deleted Scan (also filtered on CUSTCLAS), which executes only once and has 5000 output rows.

What idiotic thing am I doing here to cause this? Note that the trigger absolutely must properly handle multi-row updates.

EDIT:

I also tried writing it like this (in case EXISTS was doing something unpleasant), but it's still just as terrible.

DECLARE @CUSTNMBR varchar(31)
SELECT TOP 1 @CUSTNMBR = i.CUSTNMBR
FROM INSERTED i
    INNER JOIN DELETED d
        ON i.CUSTNMBR = d.CUSTNMBR
WHERE d.CUSTCLAS = 'Misc'
    AND i.CUSTCLAS != 'Misc'

IF @CUSTNMBR IS NULL
    RETURN

You could evaluate using explicit INNER MERGE JOIN or INNER HASH JOIN hints but given that you are presumably using these tables again later in the trigger you are probably better off just inserting the contents of inserted and deleted tables into indexed #temp tables and being done with it.

They do not get useful indexes created for them automatically.

The following code might increase the performance of this trigger. I did not know the correct data type of the [custclass] column so you need to adjust it.

DECLARE @i AS TABLE (CUSTNMBR VARCHAR(31) NOT NULL PRIMARY KEY, custclass VARCHAR(10) NOT NULL)
DECLARE @d AS TABLE (CUSTNMBR VARCHAR(31) NOT NULL PRIMARY KEY, custclass VARCHAR(10) NOT NULL)
INSERT INTO @i SELECT CUSTNMBR, custclass FROM inserted
INSERT INTO @d SELECT CUSTNMBR, custclass FROM deleted
IF NOT EXISTS
  (SELECT * FROM @i AS i INNER JOIN @d AS d ON d.CUSTNMBR = i.CUSTNMBR
   WHERE i.custclass <> d.custclass) RETURN

Notice that you can include additional columns in these in memory copies of the inserted and deleted tables if you need them in your trigger code. The primary keys on these tables will greatly increase join performance when updating more than a few rows at once. Good luck!


I think this will get you what you want.

It creates a recursive CTE (common table expression) that generates all the dates between January 1 and March 1.

It then joins those dates to the tool rental data, checking to see if each date record falls between the rental dates.  This gives you one record per toolid for every day within the range of rental dates.

Lastly, it groups by the toolID, and counts the distinct dates that the tool was rented to get rid of duplicate date values.

;WITH Dates(Date_Day) AS
(
    SELECT Convert(DateTime, '2016-01-01') AS Date_Day
UNION ALL
    SELECT DateAdd(day, 1, Date_Day) FROM Dates
    WHERE Date_Day < '2016-03-01'
)
SELECT
    Rental_Dates.ToolId,
    Count(DISTINCT Calendar_Dates.Date_Day)
FROM
    Dates Calendar_Dates
Inner Join
    #tmpToolRentalDays Rental_Dates
        ON
            Calendar_Dates.Date_Day BETWEEN Rental_Dates.StartDate AND Rental_Dates.EndDate
GROUP BY
    Rental_Dates.ToolId;

try this

SELECT ToolId,SUM(RentalDays) As RentalDaysSum
FROM #tmpToolRentalDays
GROUP BY ToolId , Cast(StartDate as DATE)


MySQL: Keep the 10 most recent rows for each user I have a table saving scores for a game that have 3 columns (more in practice, but it is easier for everyone this way):

userid   : the id of the user that got the score
timestamp: the time the score was gotten
score    : the score itself

The goal of the query is to delete rows so we have at most 10 scores per user.We also want to keep the most recent rows. So we need to delete the older ones until we get to 10.Note that some players may have less than 10 scores.

How would I manage to do this?

I found figured out an answer that works pretty well, though if you have a lot of users it may take some time to execute. I have yet to do any time testing with it. It also assumes you have an id field that uniquely identifies each score.

The way it works is in the inner query it finds up to the 10 most recent scores. Then deletes everything not returned by the inner query.

DELETE FROM score
WHERE id NOT IN (
  SELECT id
  FROM (
    SELECT *
    FROM score s1
    WHERE (
        SELECT COUNT(*)
        FROM score s2
        WHERE s1.userid = s2.userid
            AND s1.timestamp <= s2.timestamp
    ) <= 10 --Keep this many records
  ) foo
);

Delete from table where id not in ( select top 10 Id from table)


MySQL optimizing - use ram instead of CPU? I am on a dedicated server with Intel Xeon D D-1520 4/8t 2,2 / 2,6 GHz, 32 GB of RAM DDR4 ECC 2133 MHz, and 2 x480GB of SSD SOFT. The problem I am encountering is that MySQL eats up all of my CPU power and slows down everything else. My server pilot says my total CPU usage is 92% right now, and these are the active processes.

As you can see, I have a lot of RAM left to be used, but nearly any CPU. This is my MySQL config my.cnf

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]

user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

bind-address        = 127.0.0.1

key_buffer      = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 2M

log_error = /var/log/mysql/error.log

expire_logs_days    = 10
max_binlog_size         = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer      = 16M

!includedir /etc/mysql/conf.d/

my mysql_innodb.cnf only has this entry

[mysqld]
innodb_file_per_table = 1

Now, is there anything I can do to optimize my situation?

I am completely new to managing my own server, but I figured I have to start sometime somewhere somehow. So I would sincerely appreciate any recommendation or advice.

Thanks!

Verify that you tables are using the InnoDB engine and not the MyISAM or ARIA engine. Concurrent writes on non InnoDB tables can cause huge CPU spikes from attempts to get full table locks.


Partition Function vs. Clustered Index We have a table that is clustered on identity/datetime2. It is partitioned on the same datetime2. Are there any reasons to cluster on datetime2/identity instead? I understand the reasoning behind clustering generally, but with partitioning included, do things change?

Partitioning a table only divides it into "chunks" based on the partition function. The clustered index will give order to the data within each partition.

If you're planning to run queries that involve parts of a partition (i.e., show me sales between Jan 5th and Jan 12th), then it can be advantageous to those queries to have the date as the leading column of the clustering key. This type of structure will result in clustered index seeks, instead of partition scans. (Assuming there are no other suitable indexes on the table.)

If the queries only touch entire partitions, it doesn't matter as much, as partition elimination is enough to isolate the needed data. That said, ordering by date first could eliminate the need for an expensive sort operation, depending on what you're doing.

But this also depends on which columns you need from the table. If you only need to do something like aggregate total sales amounts over a date range, it may be sufficient to use a covering nonclustered index with the date as the leading (or only) column of the key, instead of rebuilding the entire table just for that.

If you do change the clustered index, this will affect singleton lookups (probably by the identity column which I assume is the primary key) as they will now involve a nonclustered index seek + key lookup. If this type of activity isn't a major part of the workload, this will be fine, but you have to be really careful that not too many rows are selected by these queries, or the optimizer will revert to a partition scan on the assumption that it's cheaper. Again, depending on which columns you need, it may be advantageous to create a covering nonclustered index that includes only the columns you need.

From a partitioning perspective there's no need to change the clustering key to datetime2/identity.  It would be totally dependent on your queries.


create incremental number in oracle sql query how to create incremental number in oracle sql query without create any table ? I have tried using "with" clause, but I failed to get the expected result. I am using oracle 10g

here is the code that I try,it seems not working:

WITH
TABLE3 AS ( SELECT 2008 YEARS FROM dual WHERE 1=1
union all
select t3.YEARS+1 from TABLE3 t3
WHERE 1=1 AND t3.YEARS < 2011
)

select YEARS from TABLE3

expected result I want is :

2008
2009
2010
2011

I think this will work (based on  this page ( http://psoug.org/definition/LEVEL.htm ) as a starting point):

WITH counter
AS ( SELECT LEVEL seq
       FROM DUAL
     CONNECT BY LEVEL <= 4 )
SELECT (2008 + seq - 1) myYear
  FROM counter
 ORDER BY 1
;

This should return:

myYear
------
  2008
  2009
  2010
  2011

Adjust 2008 and 4 to get different results.

Here is an example of adding multiple flag and incrementing that based on case statement.

WITH T3(FLAG1,FLAG2,FLAG3,tt,OTHER_DATA)
AS (
SELECT '0' FLAG1, '0' FLAG2, '0' FLAG3 , current_timestamp  tt , 'dummy'  OTHER_DATA
FROM dual
UNION ALL
SELECT case when cast( FLAG2 as int) > 5 then
cast ((cast(FLAG1 as int) + 1) as varchar2(30)) else  FLAG1 end FLAG1,
cast((cast(FLAG2 as int) + 1) as varchar2(30)) FLAG2  ,case when (
(FLAG2 ='3') or (FLAG2 = '4')) then cast ((cast(  FLAG3 as int) + 1)
as varchar2(30)) else FLAG3 end FLAG3  ,current_timestamp  tt ,
'ACTUAL' OTHER_DATA FROM T3 WHERE FLAG2 < 10
)
SELECT * FROM T3
WHERE OTHER_DATA != 'dummy' ;

--
Result set is below

Flag1   Flag2   Flag3   TT                                              OTHER_DATA
0       1       0       21-DEC-15 08.31.05.229502000 PM ASIA/CALCUTTA   ACTUAL
0       2       0       21-DEC-15 08.31.05.229502000 PM ASIA/CALCUTTA   ACTUAL
0       3       0       21-DEC-15 08.31.05.229502000 PM ASIA/CALCUTTA   ACTUAL
0       4       1       21-DEC-15 08.31.05.229502000 PM ASIA/CALCUTTA   ACTUAL
0       5       2       21-DEC-15 08.31.05.229502000 PM ASIA/CALCUTTA   ACTUAL
0       6       2       21-DEC-15 08.31.05.229502000 PM ASIA/CALCUTTA   ACTUAL
1       7       2       21-DEC-15 08.31.05.229502000 PM ASIA/CALCUTTA   ACTUAL
2       8       2       21-DEC-15 08.31.05.229502000 PM ASIA/CALCUTTA   ACTUAL
3       9       2       21-DEC-15 08.31.05.229502000 PM ASIA/CALCUTTA   ACTUAL
4      10       2       21-DEC-15 08.31.05.229502000 PM ASIA/CALCUTTA   ACTUAL


From the DMVs, can you tell if a connection used ApplicationIntent=ReadOnly? I have an Always On Availability Group set up, and I want to make sure my users are using ApplicationIntent=ReadOnly in their connection strings.

From the SQL Server via DMVs (or Extended Events or whatever), can I tell if a user connected with ApplicationIntent=ReadOnly in their connection string?

Please don't answer with how to PREVENT connections - that's not what this question is about. I can't simply go stopping connections, because we have existing applications that are connecting without the right string, and I need to know which ones they are so I can work with the developers and users to get it fixed gradually over time.

Assume that users have multiple applications. For example, Bob connects with SQL Server Management Studio, and with Excel. He connects with SSMS when he needs to do updates, and Excel when he needs to do reads. I need to make sure he's using ApplicationIntent=ReadOnly when he connects with Excel. (That's not the exact scenario, but it's close enough to illustrate.)

Picking up on the sqlserver.read_only_route_complete Extended Event mentioned by Kin and Remus, it's a nice Debug event, but it doesn't carry a great deal of information with it - just route_port (eg 1433) and route_server_name (eg sqlserver-0.contoso.com) by default.  This would also only help determine when a read-only intent connection was successful.  There is a read_only_route_fail event but I couldn't get it to fire, maybe if there was a problem with the routing URL, it didn't seem to fire when the secondary instance was unavailable / shutdown as far as I could tell.

I have however had some success joining that up with the sqlserver.login event and causality tracking enabled, along with some actions (like sqlserver.username ) to make it useful.

Steps to Reproduce

Create an Extended Events session to track relevant events, plus useful actions and track causality:

CREATE EVENT SESSION [xe_watchLoginIntent] ON SERVER
ADD EVENT sqlserver.login
    ( ACTION ( sqlserver.username ) ),
ADD EVENT sqlserver.read_only_route_complete
    ( ACTION (
        sqlserver.client_app_name,
        sqlserver.client_connection_id,
        sqlserver.client_hostname,
        sqlserver.client_pid,
        sqlserver.context_info,
        sqlserver.database_id,
        sqlserver.database_name,
        sqlserver.username
        ) ),
ADD EVENT sqlserver.read_only_route_fail
    ( ACTION (
        sqlserver.client_app_name,
        sqlserver.client_connection_id,
        sqlserver.client_hostname,
        sqlserver.client_pid,
        sqlserver.context_info,
        sqlserver.database_id,
        sqlserver.database_name,
        sqlserver.username
        ) )
ADD TARGET package0.event_file( SET filename = N'xe_watchLoginIntent' )
WITH (
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0 KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = ON,   --<-- relate events
    STARTUP_STATE = ON      --<-- ensure sessions starts after failover
)

Run the XE session (consider sampling as this is a Debug event), and collect some logins:

Note here sqlserver-0 is my readable secondary and sqlserver-1 the primary.  Here I'm using the -K switch of sqlcmd to simulate read-only application intent logins and some SQL logins.  The readonly event fires on a successful read-only intent login.

On pausing or stopping the session I can query it and attempt to link up the two events, eg:

DROP TABLE IF EXISTS #tmp

SELECT IDENTITY( INT, 1, 1 ) rowId, file_offset, CAST( event_data AS XML ) AS event_data
INTO #tmp
FROM sys.fn_xe_file_target_read_file( 'xe_watchLoginIntent*.xel', NULL, NULL, NULL )

ALTER TABLE #tmp ADD PRIMARY KEY ( rowId );
CREATE PRIMARY XML INDEX _pxmlidx_tmp ON #tmp ( event_data );

-- Pair up the login and read_only_route_complete events via xxx
DROP TABLE IF EXISTS #users

SELECT
    rowId,
    event_data.value('(event/@timestamp)[1]', 'DATETIME2' ) AS [timestamp],
    event_data.value('(event/action[@name="username"]/value/text())[1]', 'VARCHAR(100)' ) AS username,
    event_data.value('(event/action[@name="attach_activity_id_xfer"]/value/text())[1]', 'VARCHAR(100)' ) AS attach_activity_id_xfer,
    event_data.value('(event/action[@name="attach_activity_id"]/value/text())[1]', 'VARCHAR(100)' ) AS attach_activity_id
INTO #users
FROM #tmp l
WHERE l.event_data.exist('event[@name="login"]') = 1
  AND l.event_data.exist('(event/action[@name="username"]/value/text())[. = "SqlUserShouldBeReadOnly"]') = 1

DROP TABLE IF EXISTS #readonly

SELECT *,
    event_data.value('(event/@timestamp)[1]', 'DATETIME2' ) AS [timestamp],
    event_data.value('(event/data[@name="route_port"]/value/text())[1]', 'INT' ) AS route_port,
    event_data.value('(event/data[@name="route_server_name"]/value/text())[1]', 'VARCHAR(100)' ) AS route_server_name,
    event_data.value('(event/action[@name="username"]/value/text())[1]', 'VARCHAR(100)' ) AS username,
    event_data.value('(event/action[@name="client_app_name"]/value/text())[1]', 'VARCHAR(100)' ) AS client_app_name,
    event_data.value('(event/action[@name="attach_activity_id_xfer"]/value/text())[1]', 'VARCHAR(100)' ) AS attach_activity_id_xfer,
    event_data.value('(event/action[@name="attach_activity_id"]/value/text())[1]', 'VARCHAR(100)' ) AS attach_activity_id
INTO #readonly
FROM #tmp
WHERE event_data.exist('event[@name="read_only_route_complete"]') = 1

SELECT *
FROM #users u
    LEFT JOIN #readonly r ON u.attach_activity_id_xfer = r.attach_activity_id_xfer

SELECT u.username, COUNT(*) AS logins, COUNT( DISTINCT r.rowId ) AS records
FROM #users u
    LEFT JOIN #readonly r ON u.attach_activity_id_xfer = r.attach_activity_id_xfer
GROUP BY u.username

The query should show the logins with and without application read-only intent:

read_only_route_complete is a Debug event so use sparingly.  Consider sampling for example.
the two events together with track causality offer the potential to fulfil  your requirement - further testing necessary on this simple rig
I did notice if the database name was not specified in the connection, things didn't seem to work
I tried to get pair_matching target to work but ran out of time.  There is some potential for development here, something like:

ALTER EVENT SESSION [xe_watchLoginIntent] ON SERVER
ADD TARGET package0.pair_matching (
    SET begin_event = N'sqlserver.login',
        begin_matching_actions = N'sqlserver.username',
        end_event = N'sqlserver.read_only_route_complete',
        end_matching_actions = N'sqlserver.username'
    )

Does your application use a service account or maybe multiple service accounts?  If so, use Extended Event to monitor your login traffic but exclude your service accounts on your primary always-on server.  You should now be able to see who is logging into the primary always-on server and not using the readonly secondary connection string.  I am getting ready to install Always-On and this is what I am going to do unless you tell me this will not work.


Why are the system tables in MySQL and MariaDB utf8 and not utf8mb4? I know the general advice is never to use utf8, but instead to use utf8mb4. If that's the advice, then why do the system tables themselves use utf8?

You can see this in the MariaDB test suite, and and the MySQL test suite; or, by using SHOW CREATE TABLE mysql.db;

By Definitions as per MySQL Reference Manual:

  utf8mb4: A UTF-8 encoding of the Unicode character set using one to
  four bytes per character.

  utf8mb3 : A UTF-8 encoding of the Unicode character set using one to
  three bytes per character.

Since, utf8mb3 (also known as UTF8) can not store all languages like Chinese or Emoticons, it is recommended to use utf8mb4 when it comes to store application data.

For simple example, You want to extend your existing business into China.  if you have used utf8mb4, your application is always ready to adopt your business extension while in case of utf8mb3(UTF-8), you need an character set upgrade to your application.

While storing system's data MySQL does not store information (supplementary character) which requires utf8mb4, this could be the reason, why MySQL itself is not using utf8mb4.

Pretty much every column in that table is a Y/N field, using the ENUM datatype. Since it has such a consistent and simple dataset there was no reason to use a more complex collation.


Proc runinng slow once in a while I am trying to troubleshoot the performance issue on one of the SP which executes throughout the day daily every 20 mins and on avg completes under a min.

For past couple of weeks it happens once in a week that it will stuck and keep running for hours. Stats maintenance is done daily for modified stats and automatic update stats on is ON

There is no change to SP neither too much of load being increased in that run time.

I recompiled that SP to see if the plan was bad due to parameter sniffing issue, but does not help.

Due to security constraints i am not able to share the plan or any table structure here.

I know without seeing plan and query its tough to comment, but need possible ideas on what else should i be checking here?

Is auto_update_stats_async on or off?

If this is off, it is possible that the query prepares, the stats are being discovered to be stale, and updated before the query executes.

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-2017#auto_update_statistics_async


Is MSDTC required for SQL Server 2012 Fail Over Cluster? I am making a 2 node SQL Server 2012 Failover Cluster; do I need to install the MSDTC component?

If yes, can both be installed on single shared disk?

MSDTC is not required for a SQL Server 2012 fail over cluster.  However, if you plan to use Linked Servers, then you will need to create a clustered MSDTC resource.  The good news is that can be setup after the cluster is already built and after SQL Server has been installed.

Microsoft purposefully makes MSDTC an optional installation.

  SQL 2005 does require MSDTC for setup, since it uses a transactions to control setup on multiple nodes. However, SQL Server 2008/2008R2/2012 and SQL 2014 setup does NOT require MSDTC to install SQL.

One would need MSDTC "only" if they are planning to use distributed transactions.  For SQL Server in simple plain vanilla usage (most SQL Server databases I have seen) don't bother with the MSDTC.

See http://blogs.msdn.com/b/alwaysonpro/archive/2014/01/15/msdtc-recommendations-on-sql-failover-cluster.aspx for further details.


Oracle XE 11.2 Export / Import Charset I created a database in oracle XE 11.2 that uses charset AL32UTF8 that I now want to export.

When I perform the export, the resulting data appears in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set.

The server uses AL32UTF8 character set (possible charset conversion).

Why is the export from the database in a different charset than the database itself is using?

The export utility will use the NLS_LANG environment variable specified for the client session.  If all your data can be represented in the Windows-1252 character set, that shouldn't be an issue.  If you want to do the export using the AL32UTF8 character set, you'd need to set the NLS_LANG.  In Windows, that would be something like

c:\> set nls_lang = american_america.al32utf8

Steps to migrate the characterset of database

  Changing character to WE8MSWIN1252:

  Take Full export of database before starting this activity...

#sqlplus system/admin as sysdba

SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;

select * from nls_database_parameters ;

set linesize 2000

SHUTDOWN IMMEDIATE
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;

ALTER DATABASE CHARACTER SET WE8MSWIN1252;

-- if the above fails:
ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252;

SHUTDOWN IMMEDIATE; — or SHUTDOWN NORMAL;
STARTUP;


Duplicate row with Primary Key in PostgreSQL Assume I have a table as follows named people, where id is a Primary Key:

+-----------+---------+---------+
|  id       |  fname  |  lname  |
| (integer) | (text)  | (text)  |
+===========+=========+=========+
|  1        | Daniel  | Edwards |
|  2        | Fred    | Holt    |
|  3        | Henry   | Smith   |
+-----------+---------+---------+

I'm trying to write a row duplication query which is robust enough to account for schema changes to the table. Any time I add a column to the table, I don't want to have to go back and modify the duplication query.

I know I can do this, which will duplicate record id 2 and give the duplicated record a new id:

INSERT INTO people (fname, lname) SELECT fname, lname FROM people WHERE id = 2;

However if I add an age column, I'll need to modify the query to also account for the age column.

Obviously I can't do the following, because it will also duplicate the primary key, resulting in a duplicate key value violates unique constraint -- And, I don't want them to share the same id anyway:

INSERT INTO people SELECT * FROM people WHERE id = 2

With that said, what would be a reasonable approach to solving this challenge? I would prefer to stay away from stored procedures, but I'm not 100% against them, I suppose ...

Simple with hstore

If you have the additional module hstore installed (instructions in link below), there is a surprisingly simple way to replace the value(s) of individual field(s) without knowing anything about other columns:

Basic example: duplicate the row with id = 2 but replace 2 with 3:

INSERT INTO people
SELECT (p #= hstore('id', '3')).* FROM people p WHERE id = 2;

Details:

How to set value of composite variable field using dynamic SQL
Assign to NEW by key in a Postgres trigger

Assuming (since it's not defined in the question) that people.id is a serial column with an attached sequence, you'll want the next value from the sequence. We can determine the sequence name with pg_get_serial_sequence(). Details:

PostgreSQL SELECT primary key as "serial" or "bigserial"

Or you can just hard-code the sequence name if it's not going to change.
We would have this query:

INSERT INTO people
SELECT (p #= hstore('id', nextval(pg_get_serial_sequence('people', 'id'))::text)).*
FROM people p WHERE id = 2;

Which works, but suffers from a weakness in the Postgres query planner: The expression is evaluated separately for every single column in the row, wasting sequence numbers and performance. To avoid this, move the expression into a subqery and decompose the row once only:

INSERT INTO people
SELECT (p1).*
FROM  (
   SELECT p #= hstore('id', nextval(pg_get_serial_sequence('people', 'id'))::text) AS p1
   FROM   people p WHERE id = 2
   ) sub;

Probably fastest for a single (or few) row(s) at once.

json / jsonb

If you don't have hstore installed and can't install additional modules, you can do a similar trick with json_populate_record() or jsonb_populate_record(), but that capability is undocumented and may be unreliable.

How to set value of composite variable field using dynamic SQL

Transient temporary table

Another simple solution would be to use a transient temporary like this:

BEGIN;
CREATE TEMP TABLE people_tmp ON COMMIT DROP AS
SELECT * FROM people WHERE id = 2;
UPDATE people_tmp SET id = nextval(pg_get_serial_sequence('people', 'id'));
INSERT INTO people TABLE people_tmp;
COMMIT;

I added ON COMMIT DROP to drop the table automatically at the end of the transaction. Consequently, I also wrapped the operation into a transaction of its own. Neither is strictly necessary.

This offers a wide range of additional options - you can do anything with the row before inserting, but it's going to be a bit slower due to the overhead of creating and dropping a temp table.

This solution works for a single row or for any number of rows at once. Each row gets a new default value from the sequence automatically.

Using the short (SQL standard) notation TABLE people.

Dynamic SQL

For many rows at once, dynamic SQL is going to be fastest. Concatenate the columns from the system table pg_attribute or from the information schema and execute it dynamically in a DO statement or write a function for repeated use:

CREATE OR REPLACE FUNCTION f_row_copy(_tbl regclass, _id int, OUT row_ct int) AS
$func$
BEGIN
   EXECUTE (
      SELECT format('INSERT INTO %1$s(%2$s) SELECT %2$s FROM %1$s WHERE id = $1',
                    _tbl, string_agg(quote_ident(attname), ', '))
      FROM   pg_attribute
      WHERE  attrelid = _tbl
      AND    NOT attisdropped  -- no dropped (dead) columns
      AND    attnum > 0        -- no system columns
      AND    attname <> 'id'   -- exclude id column
      )
   USING _id;

   GET DIAGNOSTICS row_ct = ROW_COUNT;  -- directly assign OUT parameter
END
$func$  LANGUAGE plpgsql;

Call:

SELECT f_row_copy('people', 9);

Works for any table with an integer column named id. You could easily make the column name dynamic, too ...

Maybe not your first choice since you wanted to stay away from stored procedures, but then again, it's not a "stored procedure" anyway ...

Related:

psql: SELECT * ... except one column
How do I list all columns for a specified table

Advanced solution

A serial column is a special case. If you want to fill more or all columns with their respective default values, it gets more sophisticated. Consider this related answer:

Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3

Dynamic SQL Work great, I am looking for this since few years,

if you have more than one excluded column, try simply,

AND    attname <> 'id'   -- exclude id column
AND    attname <> 'second_col_name'   -- exclude second_col_name


Cannot connect to database remotely (not even locally when using ip address) I can login using this command:
sqlplus test/test@127.0.0.1:1521/local
But not this one: (where 192.168.0.240) is my IP
sqlplus test/test@192.168.0.240:1521/local

I am absolutely sure that my ip address (server's adress) is 192.168.0.240. So why am I getting the following error? ORA-12541: TNS:no listener

I get a connection timeout when trying to connect from other computers.

lsnrctl statusgives me the following:

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Produ
ction
Start Date                23-OCT-2013 15:27:35
Uptime                    0 days 21 hr. 37 min. 20 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\pc2\product\11.2.0\dbhome_2\network\admin\liste
ner.ora
Listener Log File         c:\app\pc2\diag\tnslsnr\pc2-PC\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "local" has 1 instance(s).
  Instance "local", status READY, has 1 handler(s) for this service...
Service "localXDB" has 1 instance(s).
  Instance "local", status READY, has 1 handler(s) for this service...
The command completed successfully

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

You have the localhost ip in the listener.ora (127.0.0.1), change this or add another line with the NIC ip.

Usually, its an issue with the firewall. Please make sure that your firewall is set to Off and then try to reconnect from another computer. I had the same problem and resolved it by doing the same.


advantages of single-leader (transactions) over multi-leader replication I am reading the excellent book "Designing Data-Intensive Applications" which I wholeheartedly recommend, but I'm confused by a section comparing multi-leader (i.e. multi-writer) replication to single-leader replication. I understand the basic difference: In multi-leader, multiple leader nodes can accept writes, each leader sends its writes to the other leaders, and you have conflict-resolution rules to decide how to merge them. Single leader solves concurrency using transactions.

The following two paragraphs describe how multi-writer can be more challenging because conflicts aren't resolved right away. My question is afterward.

  [This paragraph and the diagram are describing multi-leader.] For example, consider a wiki page that is simultaneously being edited
  by two users as shown in Figure 5-7 [copied below]. User 1 changes the title of the
  page from A to B, and user 2 changes the title from A to C at the same
  time. Each user’s change is successfully applied to their local
  leader. However, when the changes are asynchronously replicated, a
  conflict is detected. This problem does not occur in a
  single-leader database.

  In a single-leader database, the second writer will either block and
  wait for the first write to complete, or abort the second write
  transaction, forcing the user to retry the write. On the other hand,
  in a multi-leader setup, both writes are successful, and the conflict
  is only detected asynchronously at some later point in time. At that
  time, it may be too late to ask the user to resolve the conflict.

I see the difficulty with multi-writer here, but I'm skeptical that single-writer would be much better.

Consider the most likely chain of events when two people edit a Wikipedia page at roughly the same time: 1) Person 1 loads the edit page, takes 3-5 seconds to edit the title and submits. 2) Person 2 loads the edit page, takes 3-5 seconds to edit the title and submits. Each database transaction to apply the edit is only a few milliseconds, so it is far more likely that that the updates will happen one after the other, than that they will happen at the same time. Therefore if your concern is that one of these 2 people's updates will be lost, you need to address the potential for conflicts at the application level somehow; transactions won't really help you.

Furthermore in the case where the two transactions do overlap, it doesn't help the users to simply delay one of the transactions until the other is done. Once it resumes it will still overwrite the first user's data.

So my question is, is there some helpful transaction technique I'm missing that would actually be useful here? It's been a while since I tried to use transactions so my technique is rusty.

The best improvement I can think of: add AND title='A' to the end of both UPDATE statements, and add a second statement to the transaction that checks the number of affected rows, and rollback if that is equal to 0. The rollback would have no effect but it would indicate a failure to the client. But this is a bit hackish.

I don't think it would help to begin the transaction with a check (i.e. SELECT * FROM pages WHERE title='A' and make sure you get something back). Both transactions could possibly see 'A' at the beginning even though only one transaction would win out.

I came across your question while reading the same book.

I think the issue to consider is that in the single leader scenario, there is a natural total ordering as it can apply a sequence number per transaction.  It always knows "which happened first". It's hard or impossible to do this correctly in multi leader due to issues like clock skew.

So I think it boils down to, does the system even know which happened first.

Your "write if" suggestion is mentioned/used many times and I think is agnostic to single/multi leader. Write if is application level (the user probably has to enter what they think the old value is) whereas single/multi leader is probably not exposed to the user. In single leader, the write if would unambiguously avoid conflicts; the total ordering determines which operation happens first, and whichever gets there second will safely fail due to the write if.

I think the advantage of multi is performance based; the advantage of single is "correctness/complexity" based.

Verify Data Hasn't Changed

Stateless Applications have a similar problem.  (eg Web)

Data can change between "select data to display for editing" and "end-user clicks the UPDATE button".

To ensure the data hasn't changed between the SELECT and UPDATE requests, the UPDATE process should:

serialize access to the row using some sort of lock.

Oracle developers prefer SELECT ... FOR UPDATE

verify data has not changed.

calculate a hash value and compare before/after values.
or look at "last update timestamp" column and compare before/after values.

do the DML accordingly.
COMMIT/ROLLBACK & release the lock.

Oracle row locks are released via COMMIT/ROLLBACK

raise an error as necessary.

The implementation of this is very formulaic.

The logic can be implemented within the framework used to create the application (eg the Oracle APEX process "Automatic DML" does this by default).

A template based code generator can generate the procedures/functions (Table APIs [TAPI]) that implement this logic by using the appropriate template.


How to get a list/result of best selling items I'm trying to get the results of my best selling products using SQL.

My table is order_items which has the following columns.

id|product_id|qty

As there is a QTY field I have to somehow count or group each by product ID then sum the qty, then only return the top 5 selling items.

So the data looks kind of like below.

id|product_id|qty
-------------------------
 1|   1      |   1
 2|   1      |   3
 3|   2      |   3
 4|   2      |   1
 5|   1      |   7

... and so on

Not to sure about how to go about this, any help is much appreciated.

Database system used is MySQL

Assuming SQL Server, I might use:

SELECT TOP(5) ProductID, SUM(Quantity) AS TotalQuantity
FROM order_items
GROUP BY ProductID
ORDER BY SUM(Quantity) DESC;

This returns the top 5 best-selling Products.

Since SUM(Quantity) is also included in the SELECT clause, you can reference its alias in ORDER BY instead of repeating the expression:

...
ORDER BY TotalQuantity DESC;

SELECT product_id, SUM(qty) AS TotalQuantity
FROM order_items
GROUP BY product_id
HAVING SUM(qty) DESC
LIMIT 10


mssql-server crashing: Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION 8 We have an Azure Linux VM with a local MSSQL server. I recently had to reboot the VM, and since then I haven't been able to start the MSSQL server. The error logs would say Recovery of database 'MyDB' (6) is 1% complete ..., then hang the VM to the point that I could not access it over SSH, and eventually the mssql-server service would just stop. The last time this happened there was finally an error message in the logs:

2017-07-30 10:20:35.19 spid24s      Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION 8
2017-07-30 10:20:38.85 spid24s
Process/System Counts                         Value
---------------------------------------- ----------
Available Physical Memory                5837422592
Available Virtual Memory                 140733193256960
Available Paging File                    5837422592
Working Set                              4294967296
Percent of Committed Memory in WS                82
Page Faults                                       0
System physical memory high                       1
System physical memory low                        0
Process physical memory low                       0
Process virtual memory low                        1
2017-07-30 10:21:22.83 spid24s
Memory Manager                                   KB
---------------------------------------- ----------
VM Reserved                                 5883372
VM Committed                                5091232
Locked Pages Allocated                            0
Large Pages Allocated                             0
Emergency Memory                               1024
Emergency Memory In Use                           8
Target Committed                            5700608
Current Committed                           5091232
Pages Allocated                             2923720
Pages Reserved                                    0
Pages Free                                  2024712
Pages In Use                                 217880
Page Alloc Potential                        5197704
NUMA Growth Phase                                 0
Last OOM Factor                                   9
Last OS Error                                     0
2017-07-30 10:21:23.78 Server      Long Sync IO: Scheduler 0 had 1 Sync IOs in nonpreemptive mode longer than 1000 ms
2017-07-30 10:22:40.64 spid24s
Memory node Id = 0                               KB
---------------------------------------- ----------
VM Reserved                                 5883244
VM Committed                                5091196
Locked Pages Allocated                            0
Pages Allocated                             2923752
Pages Free                                  2024680
Target Committed                            5700568
Current Committed                           5091200
Foreign Committed                                 0
Away Committed                                    0
Taken Away Committed                              0
2017-07-30 10:23:21.93 spid24s
Memory node Id = 64                              KB
---------------------------------------- ----------
VM Reserved                                       0
VM Committed                                     36
Locked Pages Allocated                            0

The VM is running Ubuntu with 8GB ram.
Do I need more memory, or are there other solutions?

The error message is indicating internal memory pressure; could you please share the complete errorlog output when the OOM condition had occurred. We would like to validate memory consumption by each clerk.

How large is your database which is still in recovery; It looks like there some long outstanding i/o operations occurring. Are there any latencies at the storage layer?

-Pradeep


How to prevent duplicate VARCHAR without a key limit? I'd like to store URLs in a database column, and enforce a constraint that values must be unique. Unfortunately, MySQL has a limit on the length of index keys which means that only the first X characters of the URL gets checked for uniqueness. Thus, I've run into false positives where two different URLs triggered a constraint integration violation because the first X characters just-so-happened to be identical.

Is there a way to enforce uniqueness on a VARCHAR column without any limit on its length?

Is it possible to, say, create a non-UNIQUE index over the first X characters and then have a trigger block INSERTs if the remaining characters are identical?

We keep giving you answers that do not directly answer the question, because that is how we solve this problem.  An index of unlimited length is impractical and inefficient, but a unique hash provides a solution that sufficient to the task because of the astronomically low likelihood of a meaningful collision.

Similar to the other offered solutions, my standard approach does not check for duplicates up front -- it is optimistic in that sense: it relies on constraint checking by the database, with the assumption that most inserts are not duplicates, so there's no point in wasting time trying to determine if they are.

Working, tested example (5.7.16, backwards compatible to 5.6; previous versions do not have a built-in TO_BASE64() function):

CREATE TABLE web_page (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  url LONGTEXT NOT NULL,
  url_hash CHAR(24) COLLATE ascii_bin,
  PRIMARY KEY(id),
  UNIQUE KEY(url_hash),
  KEY(url(16))
)ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;

Note that I am storing the base64 version of the hash.  This is a 4:3 size tradeoff compared to storing it in binary form because it makes the table contents and the error message human readable, and the inefficiency is partially offset by the table compression.  The hash column has a unique constraint.  The data type is CHAR, not VARCHAR, since this eliminates the byte needed to store the size -- the hash is always a fixed size.  The column uses the ascii character set with ascii_bin (case-sensitive) collation, keeping the column and the unique index as small as practical.

The url_hash is set by a trigger, below, but the trigger does not check for a collision -- there is no need to check, because of the unique constraint on url_hash.  The database will block a duplicate insert.

Note that url_hash should have been declared NOT NULL but MySQL incorrectly enforces this before the BEFORE INSERT trigger fires, instead of after, so we are limited by that.  The trigger does prevent it from being null.

The url column has a prefix index length of 16, which was chosen arbitrarily.  This isn't a unique constraint, just an index for lookups, and it is probably shorter than you might want it to be, but its length has no operational impact on the problem we are solving, here.

Here's the trigger to set the url_hash.  We don't need to include this value in an INSERT statement when we insert rows.

DELIMITER $$
DROP TRIGGER IF EXISTS web_page_bi $$
CREATE TRIGGER web_page_bi BEFORE INSERT ON web_page FOR EACH ROW
BEGIN
  SET NEW.url_hash = TO_BASE64(UNHEX(MD5(NEW.url)));
END $$
DELIMITER ;

You need a trigger on update also, either to block updates if the table is supposed to be immutable, or to update the hash if the URL changes.  We also need this trigger to ensure that the url_hash column can't be inappropriately set to NULL since the limitation in MySQL doesn't allow us to actually declare it that way, as we should.

Now, to test.

mysql> INSERT INTO web_page (url) VALUES ('http://example.com/');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM web_page;
+----+---------------------+--------------------------+
| id | url                 | url_hash                 |
+----+---------------------+--------------------------+
|  1 | http://example.com/ | pr8XV//wV/JmtpffnPF2/Q== |
+----+---------------------+--------------------------+
1 row in set (0.00 sec)

So far, so good.  Now, a different URL:

mysql> INSERT INTO web_page (url) VALUES ('http://example.net/');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM web_page;
+----+---------------------+--------------------------+
| id | url                 | url_hash                 |
+----+---------------------+--------------------------+
|  1 | http://example.com/ | pr8XV//wV/JmtpffnPF2/Q== |
|  2 | http://example.net/ | ZVk/eLfvBI6tHN0Luj3NnQ== |
+----+---------------------+--------------------------+
2 rows in set (0.00 sec)

Still works.  Now, a duplicate.

mysql> INSERT INTO web_page (url) VALUES ('http://example.com/');
ERROR 1062 (23000): Duplicate entry 'pr8XV//wV/JmtpffnPF2/Q==' for key 'url_hash'

Perfect.  If you want an even lower risk of hash collisions than MD5 provides, use a SHA variant, increasing the length of data_hash to CHAR_LENGTH(TO_BASE64(UNHEX( /* your hash function */ ))) to accommodate the values generated by the hash algorithm in use.

If 3072 bytes will be enough, you can enable innodb_large_prefix, or upgrade to a recent version of 5.7 to have it by default:

http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_large_prefix

For a URL, it will help to use ASCII as the character set if the characters will be truly limited to that set. One byte per char.


Efficiently Filter Large Set With Disjunctions Let's say I have a single table

CREATE TABLE Ticket (
    TicketId int NOT NULL,
    InsertDateTime datetime NOT NULL,
    SiteId int NOT NULL,
    StatusId tinyint NOT NULL,
    AssignedId int NULL,
    ReportedById int NOT NULL,
    CategoryId int NULL
);

In this example TicketId is the Primary Key.

I want users to be able to create "partially ad-hoc" queries against this table. I say partially because a few parts of the query will always fixed:

The query will always perform a range filter on an
InsertDateTime
The query will always ORDER BY InsertDateTime DESC
The query will page results

The user can optionally filter on any of the other columns. They can filter on none, one, or many. And for each column the user may select from a set of values which will be applied as a disjunction. For example:

SELECT
    TicketId
FROM (
    SELECT
        TicketId,
        ROW_NUMBER() OVER(ORDER BY InsertDateTime DESC) as RowNum
    FROM Ticket
    WHERE InsertDateTime >= '2013-01-01' AND InsertDateTime < '2013-02-01'
      AND StatusId IN (1,2,3)
      AND (CategoryId IN (10,11) OR CategoryId IS NULL)
    ) _
WHERE RowNum BETWEEN 1 AND 100;

Now assume the table has 100,000,000 rows.

The best I can come up with is a covering index that includes each of the "optional" columns:

CREATE NONCLUSTERED INDEX IX_Ticket_Covering ON Ticket (
    InsertDateTime DESC
) INCLUDE (
    SiteId, StatusId, AssignedId, ReportedById, CategoryId
);

This gives me a query plan as follows:

SELECT

Filter

Top

Sequence Project (Compute Scalar)

Segment

Index Seek

It seems pretty good. About 80%-90% of the cost comes from the Index Seek operation, which is ideal.

Are there better strategies for implementing this kind of searching?

I don't necessarily want to offload the optional filtering to the client because in some cases the result set from the "fixed" part could be 100s or 1000s. The client would then also be responsible for sorting and paging which might too much work for the client.

If this particular work load is the majority of queries against the table you might consider:

ALTER TABLE Ticket ADD CONSTRAINT PK_Ticket PRIMARY KEY NONCLUSTERED (TicketId);

CREATE UNIQUE CLUSTERED INDEX IX_Ticket_Covering ON Ticket (
    InsertDateTime ASC
);

Considerations:

can you use datetime2 (SQL 2008+; flexible precision)
will InsertDateTime be unique within your precision
if the times are not constrained unique sql will add a hidden uniquifier column of type int. This is added to all nonclutred index so they can reference the correct clustered record

Advantages:

Adds new rows to the end of the table
prevent writing the optional filter columns twice (once in the clustered, and once on the index leaf for the include)
majority of you time will still be on a cluster index seek with more or less filers.
then add other nonclustered index for most popular column pairs

If the clients are filtering in almost the same way over and over again you can create an index for those queries.

E.g. the client is filtering on SiteId and StatusId you can create an additional index:

CREATE NONCLUSTERED INDEX IX_Ticket_InsertDateTime_SiteId_StatusId ON Ticket
(InsertDateTime DESC,
 SiteId [ASC/DESC],
 StatusId [ASC/DESC] )
 INCLUDE ( ... );

This way, most of the 'more common' queries could run fast.


sys.dm_db_stats_properties seems to be misbehaving for a small table - how to get the number of records of a table in a different way? I have this table in one of my databases:

IF OBJECT_ID('[dbo].[repl_Gender_Type]') IS NOT NULL
DROP TABLE [dbo].[repl_Gender_Type]
GO
CREATE TABLE [dbo].[repl_Gender_Type] (
[Gender_TypeID]  CHAR(1)                          NOT NULL,
[Gender_Desc]    VARCHAR(20)                      NOT NULL,
[Create_Date]    DATETIME                         NOT NULL,
[Create_Userid]  VARCHAR(20)                      NOT NULL,
CONSTRAINT   [PK__Gender__46DD686B]  PRIMARY KEY NONCLUSTERED ([Gender_TypeID] asc))

there are only 3 records on it.

even after running the following command:

update statistics dbo.repl_Gender_Type with fullscan

I get no results when running the following query:

select SP.*
from  dbo.sysarticles A
OUTER APPLY [sys].[dm_db_stats_properties](a.objid,1) sp
where a.objid = OBJECT_ID('dbo.repl_Gender_Type')

so dm_db_stats_properties does not keep track of small tables?
what is the work around to find the number of records in the table?

  sys.dm_db_stats_properties returns an empty rowset under any of the
  following conditions:

  object_id or stats_id is NULL.
  The specified object is not found or
  does not correspond to a table or indexed view. The specified

  statistics ID does not correspond to existing statistics for the
  specified object ID.

  The current user does not have permissions to
  view the statistics object. This behavior allows for the safe usage of
  sys.dm_db_stats_properties when cross applied to rows in views such as
  sys.objects and sys.stats.

none of the above is right, me thinks.

I like to use this script below, that comes from this question:

sp_updatestats vs Update statistics

SELECT [sch].[name] + '.' + [so].[name] AS [TableName] ,
[ss].[name] AS [Statistic],
[sp].[last_updated] AS [StatsLastUpdated] ,
[sp].[rows] AS [RowsInTable] ,
[sp].[rows_sampled] AS [RowsSampled] ,
[sp].[modification_counter] AS [RowModifications]
FROM [sys].[stats] [ss]
JOIN [sys].[objects] [so] ON [ss].[object_id] = [so].[object_id]
JOIN [sys].[schemas] [sch] ON [so].[schema_id] = [sch].[schema_id]
OUTER APPLY [sys].[dm_db_stats_properties]([so].[object_id],
[ss].[stats_id]) sp
WHERE [so].[type] = 'U'
AND [sp].[modification_counter] > 0--change accordingly
ORDER BY [sp].[last_updated] DESC;

Looking for row counts in stats objects is problematic.  Especially since stats are not updated automatically every time a row is inserted, deleted, or modified.

Use sys.dm_db_partition_stats instead.  It returns row count information for every partition in the database.

SELECT Name = QUOTENAME(sch.name) + N'.' + QUOTENAME(o.name)
    , dbps.partition_number
    , dbps.row_count
    , dbps.*
FROM sys.schemas sch
    INNER JOIN sys.objects o ON sch.schema_id = o.schema_id
    INNER JOIN sys.dm_db_partition_stats dbps ON o.object_id = dbps.object_id
WHERE o.is_ms_shipped = 0
    AND (dbps.index_id = 0 OR dbps.index_id = 1); --heap or clustered indexes only

I wrote a blog post on SQLServerScience.com that shows some of the problems with the stats properties DMV in relation to row counts.  That post uses this query to obtain row counts, and shows when the stats object was last updated:

SELECT Name = QUOTENAME(sch.name) + N'.' + QUOTENAME(o.name) + N'.' + QUOTENAME(s.name)
    , ddsp.stats_id
    , last_updated = CONVERT(datetime2(1), ddsp.last_updated)
    , ddsp.rows
    , mod_count = ddsp.modification_counter
    , ddsp.unfiltered_rows
FROM sys.schemas sch
    INNER JOIN sys.objects o ON sch.schema_id = o.schema_id
    INNER JOIN sys.stats s ON o.object_id = s.object_id
    OUTER APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) ddsp
WHERE o.is_ms_shipped = 0;

this is only a partial answer as I could not find out why the dm_db_stats_properties did not hold anything for that particular table.

what I really wanted though, was to have a good view on the replicated table on my publisher databases, and following in line with the comments and some other scripts I got a good enough view for my analyses using the script below:

use MY_PUBLICATION_DB
GO

DECLARE @SHOW_ONLY_SUBSCRIBED_PUBLICATION BIT = 0

SELECT
         Publication=P.name
        ,p.immediate_sync
        ,p.allow_anonymous
        ,p.replicate_ddl
        ,PublicationDB          = db_name()
        ,the_TableName          = OBJECT_SCHEMA_NAME(a.objid) + '.' + a.name
        ,total_number_of_rows   = REPLACE(CONVERT(VARCHAR(50),CAST(SUM (ddps.row_count) OVER (PARTITION BY ddps.OBJECT_ID)  AS MONEY),1), '.00','')
        ,[Statistics_Updated]   = STATS_DATE(a.objid,1)
        ,dius.last_user_update
        ,DestinationServer      = s.srvname
        ,DestinationDB          = s.dest_db
        ,the_DestinationTable   = a.dest_owner + '.' + a.dest_table
        ,user_seeks             = REPLACE(CONVERT(VARCHAR(50),CAST(dius.user_seeks AS MONEY),1), '.00','')
        ,user_scans             = REPLACE(CONVERT(VARCHAR(50),CAST(dius.user_scans AS MONEY),1), '.00','')
        ,user_lookups           = REPLACE(CONVERT(VARCHAR(50),CAST(dius.user_lookups AS MONEY),1), '.00','')
        ,user_updates           = REPLACE(CONVERT(VARCHAR(50),CAST(dius.user_updates AS MONEY),1), '.00','')
        ,dius.last_user_seek
        ,dius.last_user_scan
        ,dius.last_user_lookup

FROM dbo.syspublications P

INNER JOIN dbo.sysarticles A
        ON P.pubid = A.pubid

LEFT OUTER JOIN dbo.syssubscriptions s
             ON a.artid = s.artid

LEFT OUTER JOIN sys.dm_db_partition_stats ddps
             ON a.objid = ddps.OBJECT_ID
            AND ddps.index_id < 2

LEFT OUTER JOIN sys.dm_db_index_usage_stats dius
             ON 1=1
            AND (dius.index_id=1 OR dius.index_id=0)
            AND dius.object_id = a.objid
            AND dius.database_id = DB_ID()

WHERE 1=1
  AND 1=CASE WHEN @SHOW_ONLY_SUBSCRIBED_PUBLICATION  = 1 THEN
                CASE WHEN s.srvid > 0 THEN 1
                     ELSE 0
                END
             ELSE
                CASE WHEN EXISTS(SELECT 'RADHE' FROM dbo.syssubscriptions s  WHERE s.artid = a.artid AND s.srvid > 0) THEN
                          CASE WHEN (s.srvid < 0)  THEN 0
                               ELSE 1
                          END
                     ELSE 1
                END
        END

ORDER BY ddps.row_count DESC

and this gives me:

EDIT:

When looking at the output table from the script above I get:

IF OBJECT_ID('[dbo].[publications_20190423_]') IS NOT NULL
DROP TABLE [dbo].[publications_20190423_]
GO
CREATE TABLE [dbo].[publications_20190423_] (
[Publication]           SYSNAME                          NOT NULL,
[immediate_sync]        BIT                              NOT NULL,
[allow_anonymous]       BIT                              NOT NULL,
[replicate_ddl]         INT                                  NULL,
[PublicationDB]         NVARCHAR(128)                        NULL,
[the_TableName]         NVARCHAR(257)                        NULL,
[total_number_of_rows]  VARCHAR(8000)                        NULL,
[Statistics_Updated]    DATETIME                             NULL,
[last_user_update]      DATETIME                             NULL,
[DestinationServer]     SYSNAME                              NULL,
[DestinationDB]         SYSNAME                              NULL,
[the_DestinationTable]  NVARCHAR(257)                        NULL,
[user_seeks]            VARCHAR(8000)                        NULL,
[user_scans]            VARCHAR(8000)                        NULL,
[user_lookups]          VARCHAR(8000)                        NULL,
[user_updates]          VARCHAR(8000)                        NULL,
[last_user_seek]        DATETIME                             NULL,
[last_user_scan]        DATETIME                             NULL,
[last_user_lookup]      DATETIME                             NULL)

To reduce the varchar(8000) to varchar(50) I had to use this little trick.


Execute permission denied on object sp_start_job I need to allow a user to kick of a specific agent job without having any ability to start other ones. To accomplish this, I've created the following procedure (simplified):

ALTER PROCEDURE [dbo].[RunJob]
    @job_name nvarchar(200)
WITH EXECUTE AS 'sysadminaccount'
AS
BEGIN
    --SET NOCOUNT ON;
    BEGIN TRY
        EXEC msdb.dbo.sp_start_job @job_name = @job_name

        -- Wait for job to finish
        DECLARE @job_history_id AS INT = NULL
        DECLARE @job_result AS INT = NULL

        WHILE 1=1
        BEGIN
            SELECT TOP 1 @job_history_id = activity.job_history_id
            FROM msdb.dbo.sysjobs jobs
            INNER JOIN msdb.dbo.sysjobactivity activity ON activity.job_id = jobs.job_id
            WHERE jobs.name = @job_name
            ORDER BY activity.start_execution_date DESC

            IF @job_history_id IS NULL
            BEGIN
                WAITFOR DELAY '00:00:01'
                CONTINUE
            END
            ELSE
                BREAK
        END

        -- Check exit code
        SET @job_result = (SELECT history.run_status
        FROM msdb.dbo.sysjobhistory history
        WHERE history.instance_id = @job_history_id)

        RETURN @job_result;

    END TRY
    BEGIN CATCH
        THROW;
        RETURN;
    END CATCH
END

However, when I call this procedure (having verified it is running via "sysadminaccount"), I get the following error message:

  Msg 229, Level 14, State 5, Procedure sp_start_job, Line 1
  The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.

The account is a member of the sysadmin role, so I as understand it there shouldn't be any problems kicking off jobs. I've verified that it is a member of the three sqlagent roles in msdb, and those roles all have execute permission on sp_start_job.

How can I give this account the appropriate permissions? Is there something else that needs to be done because of the impersonation?

I don't like the TRUSTWORTHY option because it significantly increases your exposure to a variety of things. As Remus explains in this answer, it essentially elevates any db_owner to sysadmin. Some other things worth reading are a series on TRUSTWORTHY by Sebastian Meine, the BOL topic, and a KB article (even though the assembly portions may not be relevant to you in this scenario):

The TRUSTWORTHY Database Property Explained – Part 1
The TRUSTWORTHY Database Property Explained – Part 2
The TRUSTWORTHY Database Property Explained – Part 3
TRUSTWORTHY Database Property
KB #2183687 : Guidelines for using the TRUSTWORTHY database setting in SQL Server

(And there are tons of other posts out there cautioning against the blind use of this property - just because it works and it is easy doesn't mean it's the right thing to do - in fact that should make you question it even more.)
So I would suggest a different approach (and there are still others, such as signing with a certificate, but this has always worked for me):

Create the procedure in msdb.
Create a user for this user's login in msdb:

USE msdb;
GO
CREATE USER floobarama FROM LOGIN floobarama;

Grant the user execute privileges on the stored procedure:

GRANT EXECUTE ON [dbo].[RunJob] TO floobarama;

Test it - either by calling the procedure from another database:

USE tempdb;
GO
EXECUTE AS LOGIN = N'floobarama';
GO
EXEC msdb.dbo.RunJob @job_name = N'whatever';
GO
REVERT;

Or an easier test, in case you don't want to run any job now and don't want to wait until this user executes it to find out whether they have sufficient access to msdb:

USE msdb;
GO
CREATE PROCEDURE dbo.whatever
WITH EXECUTE AS N'sysadminaccount'
AS
BEGIN
  SET NOCOUNT ON;
  SELECT [I am really...] = SUSER_SNAME();
END
GO
GRANT EXECUTE ON dbo.whatever TO floobarama;

USE tempdb;
GO
EXECUTE AS LOGIN = N'floobarama';
GO
EXEC msdb.dbo.whatever;
GO
REVERT;

Result should be:

I am really...
---------------
sysadminaccount

Validate that this doesn't expose anything else in msdb to this user:

USE tempdb;
GO
EXECUTE AS LOGIN = N'floobarama';
GO
SELECT job_id FROM msdb.dbo.sysjobs;
GO
REVERT;

Result should be...

  Msg 229, Level 14, State 5, Line 21
  The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'.

...since creating a user in a database doesn't give them automatic rights to anything in that database; you need to explicitly do so either for that user, or for a role or group they are in (including public).

In Sql Server:

just go to security->schema->dbo.. Double click dbo... then click on permission tab->(blue font)view database permission and feel free to scroll for required fields like "execute"....help yourself with choosing....and grant,with grant or deny controls....hope this will help


How can I disable database lock in sqlite with python? I made a application in python and it should read and write simultaneously in multiple instances(multi CMD widows)(multi-threading). each thread is configured independently so it will not change the data that written with other instances. so in action app instance 1 will work with John's data and app instance 2 will work with Jane's data and so On.

the application concept is simple enough but in action when running multiple instances at once, there may be a situation that I get database locked error and the app will stop working.

I want to disable this locking system, because its not necessary in my application, I designed it Independent just for this reason.

can anybody tell me to do it in python? is it possible? where should I place a code or sql command or anything?

Using embedded sqlite is good for developing and testing, and I'm assuming you are using db like this:

db = sqlite3.connect('data/mydb')

In this case you could use several db like mydb1, mydb2 and so on. It would prevent the lockdown but overall it's not the solution.

I would suggest using several tables in single database instance via any RDS service you find appropriate. For example this one

Another solution could be moving from relational database to NoSQL solution. It would completely prevent the lockdown situation and it's highly scalable and affordable when data exchange volumes are relatively low.


Show all stored procedure definitions (content) for editing Apologies if this has been asked before, the suggested 'related' questions weren't relevant and my own search turned up very little.

I need to list the definitions (code) of all my stored procedures, so that I can start working through all 450 of them and adding in semi-colons to work toward v2014 standards.

I found the following code here and adapted it slightly:

SELECT
    obj.Name AS SPName,
    REPLACE(modu.definition, 'CREATE PROC', 'ALTER PROC') + 'GO' AS SPDefinition
FROM
    sys.sql_modules modu INNER JOIN
    sys.objects obj ON modu.object_id = obj.object_id
WHERE
    (obj.type = 'P')
ORDER BY
    obj.name;

The code above is run using the SA account.

I assumed that this provided exactly what I needed, but, I've just noticed that somebody on MSDN is claiming it doesn't show the full text as expected.

When pasting the results into the SSMS editor, I have 46,000 lines of code to work through, so its not apparent to me if anything is missing.  I'd like to be sure I've started this process correctly before wasting a day in the editor.

Therefore, I am reaching out to you pro's to ask if this is a genuine shortcoming of this approach, or is there an alternative method rather than one-by-one?

The problem mentioned on MSDN has nothing to do with sys.sql_modules or OBJECT_DEFINITION(); they've misinterpreted the problem. What actually happened is they were thwarted by an output limitation in Management Studio, which by default will only show 255 characters and at most 8192 in any output tuple in Results to Text.

So first, make sure you change this to 8192:

  Tools > Options > Query Results > SQL Server > Results to Text >
  Maximum number of characters displayed in each column

Now you can make one script for all the procedures that are less than 4K:

SELECT REPLACE(m.definition, N'CREATE PROC', N'ALTER PROC')
  + CHAR(13) + CHAR(10) + N'GO'
FROM sys.sql_modules AS m
INNER JOIN sys.procedures AS p
ON m.[object_id] = p.[object_id]
WHERE LEN(m.definition) <= 4000
ORDER BY p.name;

(Of course, your replacement is naïve - what if they have create    procedure (more than one space) or you are on a case sensitive collation? You may have to fix some of those manually.)

Now, you only have to worry about manually deriving the script for the larger procedures. First, see if you have any:

SELECT name
  FROM sys.procedures
  WHERE LEN(OBJECT_DEFINITION([object_id])) > 4000;

I talk a little about some workarounds here (in the context of dynamic SQL, but the same would be true if you assigned OBJECT_DEFINITION() to a variable).

Mr. Magoo is right though, you will get the full definition without having to worry about truncation if you use the scripting functionality within Management Studio:

right-click a procedure, modify or script as > alter to >
in Object Explorer Details, do the same as above but for multiple
right-click a database, tasks > generate scripts > (but this does CREATE, not ALTER, so you'll need to perform replacements still)

As an aside, while you are adding semi-colons everywhere, I also suggest ensuring that all of your procedures are created with schema prefixes (e.g. dbo.procedurename, not just procedurename), and also for all object references inside the procedure. Those are likely to cause problems long before a lack of semi-colons ever will. Also a few general things to keep in mind while performing your changes.

In this scenario system table has always been useful.

Please use this query.

Select * From SYS.Comments
Where Text = 'Stored Procedure Name'

Although this table as multiple rows for a single object_id but putting a concat function on the table helps to get a single ROW for a single object_id.


Handling case-insensitive queries with case-sensitive data What are some practices for designing a table, or queries, when it comes to case-sensitive data and case-insensitive queries?

For example, I have a Products table that holds household goods, and presumably because I intend to display this data on a client, I would like to have the original names.

If one of the products is called "Vacuum Cleaner" and someone's doing a search through the database for vacuums, I wouldn't want a term like "vacuum" to not find this particular vacuum.

Currently I am using Postgres so I could probably get away with the ILIKE operator, but if a database system didn't support this kind of operator, what would be some alternatives?

SQL text and string data has a concept called "collation". Collations define how character sets compare and sort. Almost every database out there has the concept of "case-sensitive collation" vs. "case-insensitive collation". The collation used for comparison, search and sort can be changed on-the-fly (at a significant runtime cost usually) on a per-query basis. More often the collation is chose during development and defined at the database/table/column level and then the engine will use the chosen collation to do search/comparison/sorting of strings/text.

The specifics of how you define the collation to use will depend on your platform of choice.

I'm not sure if I 100% understand your question. This is how I understand your question:

  How can I match "vacuum" to "Vacuum Cleaner" inside my database?

To match something like that you can use the % wildcard within the LIKE function. Basically bottom line you can do that by sending the following query.

SELECT * FROM `products` WHERE LOWER(`name`) LIKE LOWER('%vacuums%');

The above query should would then of course return Vacuum Cleaner as one of the results.

Extra

Here are a few links where you can read more into detail about the wildcards.

http://www.sqlexamples.info/PHP/mysql_like_wildcards.htm
http://www.sqlexamples.info/PHP/mysql_rlike.htm


How to get the greatest element of a GROUP BY? As you can see I would like to get the latest purchase order for each SKU.

Sample Data

I have this data set,

CREATE TABLE PurchaseOrders (
        id            int  PRIMARY KEY AUTO_INCREMENT,
        sku           varchar(6),
        purchase_date date
);

INSERT INTO PurchaseOrders VALUES
    ( 1, 'ABC123', '2017-12-23' ),
    ( 2, 'ABC123', '2016-11-11' ),
    ( 3, 'DEF456', '2011-01-03' ),
    ( 4, 'DEF456', '2011-10-21' ),
    ( 5, 'GHI789', '2017-01-23' ),
    ( 6, 'GHI789', '2017-11-21' );

Desired Result

1 | ABC123 | 2017-12-23
4 | DEF456 | 2011-10-21
6 | GHI789 | 2017-11-21

Probably easiest to do with a join against a derived table:

select a.*
from PurchaseOrders as a
join (
    select sku, max(date) as date
    from PurchaseOrders
    group by sku
) as b
   on a.sku = b.sku
   and a.date = b.date;

The derived table b contains the max dt for each sku. To get the id we join that against the original table.

As Evan Carroll points out you can shorten this a bit since the name of the join attributes is the same for both left and right operand:

select a.*
from PurchaseOrders as a
join (
    select sku, max(date) as date
    from PurchaseOrders
    group by sku
) as b
   using (sku, date);

I hope it will help you.

SELECT max(date) AS date, sku
FROM PurchaseOrders
GROUP BY sku;


oracle oraenv script bash I want to do same commande shell CLi into a script

Command shell without script (CLI)

su - oracle
export ORAENV_ASK=NO
export ORACLE_SID=HRPRD
. oraenv
env | grep ora

output:

USER=oracle
**LD_LIBRARY_PATH**=/u01/pe/oracle/u/25/bas/lib
ORACLE_BASE=/u01/pe/oracle
MAIL=/var/spool/mail/oracle
PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/u01/pe/oracle/u/25/bas
PWD=/oracle
HOME=/oracle
LOGNAME=oracle
**ORACLE_HOME**=/u01/pe/oracle/u/25/bas/

My script

rr=`su - oracle`
export ORAENV_ASK=NO
export ORACLE_SID=MySID
uu=`. oraenv`
searchOraHome= `env | grep ora`
echo $searchOraHome

output:

USER=oracle ORACLE_BASE=/u01/app/oracle
PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/oracle/bin
MAIL=/var/spool/mail/oracle PWD=/oracle HOME=/oracle LOGNAME=oracle

Why are the variables ORACLE_HOME and LD_LIBRARY_PATH not present with the script shell?

Is the line:

uu=`. oraenv`

... not correct?

If you us a shell to execute a command in backticks (`), it is executed in a subshell, so all the environment variables it sets are only set in the subshell and are gone when the subshell exits.

You have to "source" the script with

. oraenv

without the backticks.

I never liked the default oraenv script or the coraenv scripts. so I wrote my own. I use grep and cut to pull out the sid that I am looking for as well as the Oracle home from the /etc/oratab file. I can then set any other environment variables that I want. Things such as email addresses for shell scripts, backup directory, any aliases that I want to use etc. The Oracle provided scripts work ok, I just like being able to control how setting the environment variables works.


Cannot view SQL Server Agent in SSMS v17 I'm logged into two accounts. The SA account and my personal account. On my SA account, I can see the SQL Server Agent node, and on the other I cannot. They are both connecting to the exact same server and instance.

In my personal user properties I have all roles including SQLAgentOperatorRole, SQLAgentReaderRole and SQLAgentUserRole checked. I'm also a sysadmin.

I ended up deleting my profile in MSSQL and recreating it to fix it.

You should run the SSMS as admin. Then Agent will appear.


Where are SQL Server catalog views persisted? It seems that sys catalog views can be found under every database, either built in or user created. Are they all actual view definitions created under each of the database or just "links" to somewhere?
Thanks

Catalog views do not provide access to replication, SQL Server Agent, or backup metadata.  Those are available in MSDB only.

Reference:

https://docs.microsoft.com/en-us/sql/relational-databases/databases/resource-database

https://technet.microsoft.com/en-us/library/ms179503(v=sql.105).aspx

  The Resource database is a read-only database that contains all the
  system objects that are included with SQL Server. SQL Server system
  objects, such as sys.objects, are physically persisted in the Resource
  database, but they logically appear in the sys schema of every
  database. The Resource database does not contain user data or user
  metadata.

  The Resource database makes upgrading to a new version of SQL Server
  an easier and faster procedure. In earlier versions of SQL Server,
  upgrading required dropping and creating system objects. Because the
  Resource database file contains all system objects, an upgrade is now
  accomplished simply by copying the single Resource database file to
  the local server.

  The physical file names of the Resource database are
  mssqlsystemresource.mdf and mssqlsystemresource.ldf. These files are
  located in :\Program Files\Microsoft SQL
  Server\MSSQL.\MSSQL\Binn\ and should not be
  moved. Each instance of SQL Server has one and only one associated
  mssqlsystemresource.mdf file, and instances do not share this file.

  System base tables are the underlying tables that actually store the
  metadata for a specific database. The master database is special in
  this respect because it contains some additional tables that are not
  found in any of the other databases. These tables contain persisted
  metadata that has server-wide scope. The Resource database contains
  the same base table schema as any other non-master database.

I think of sys "views" as mysterious objects that can produce metadata on demand without telling you how.  It seems to me that some of that data can't be coming from any "table" of the recognised kind.  But that's my ignorance.  Maybe it is tables.  And yet a lot of the metadata is -about- tables, so how can it be -in- a table?  It seems like this kind of paradox
 https://en.wikipedia.org/wiki/Bootstrapping#Etymology

And yet that page explains that computers can do this thing  ;-)


Alternative to WHERE clause Is there a way to only SELECT rows with certain data in a column, without using WHERE?

e.g. If I had this:

SELECT * FROM Users
WHERE town = 'Townsville'

is there a way to implement the WHERE clause into the SELECT statement?

something like

SELECT *, town('Townsville') FROM Users

It's a bizarre question but it's one I've been asked by my peers

Not sure if this is the kind of crazy thing you were looking for....

Disclaimer: I have absolutely no idea why you would want to use this.

SELECT *
FROM Users AS u
INNER JOIN (SELECT 'Townsville' town) towns
  ON towns.town = u.Town;

SELECT *,
    case when town='Townsville' then 'Townsville'
         else null
    end as Town
FROM Users

All towns besides Townsville would be null. Problem solved.


Manipulate duplicated records In a table ( in Microsoft Access), I have a large number of records that have duplicated field, for example field-1. I have to change the table and set field-1 as primary key. But as you know, it is not possible. So I want to change the value of field-1 for duplicated data. For example, now I have a table like this:

field-1  | field-2
1        | f1
1        | f2
2        | f3
2        | f4

And want to change to this:

field-1  | field-2
1        | f1
201      | f2
2        | f3
202      | f4

Note:

Maximum number of duplicated record is 2.

How I can do that?

This is kind of complicated, but you could try this.

Create a duplicate table to store the duplicates. Use this query in your insert statement:

SELECT field-1,COUNT(*) AS COUNT
FROM yourtable
GROUP BY field-1
HAVING COUNT(*) = 2

Create a nonDuplicate table to store the non duplicates. Use this query in your insert statement:

SELECT field-1,COUNT(*) AS COUNT
FROM yourtable
GROUP BY field-1
HAVING COUNT(*) = 1

Create a duplicateValue table to store the modified values of the duplicates. You will need to modify the case statement below to work with Access, before using this query in an insert statement. Here are some links that should help you.

How to add row number in Access

Access case statements

--Modify the 2nd instance of the duplicate field
--If the row number is Odd, then the value is the same
--If the row number is Even, then 200 is added to the value
SELECT
  --This is how you would do it with SQL Server
  --You will need to figure out how to modify this code to work with Access
  CASE WHEN ROW_NUMBER() OVER (ORDER BY field-1 ASC) % 2 <> 0
  THEN field-1 ELSE field-1 + 200 END AS 'field-1'
  --THE ABOVE CASE STATEMENT NEEDS TO BE MODIFIED FOR ACCESS
  ,field-2
FROM yourtable
    JOIN duplicateTable ON yourtable.field-1 = duplicateTable.field-1

Create a nonDuplicateValue table to store the non duplicate values. Use this query in your insert statement:

--Get the values of field 1 and field 2
--from non duplicate rows
SELECT field-1,field-2
FROM yourtable
    JOIN nonDuplicateTable ON yourtable.field-1 = nonDuplicateTable.field-1

Query to union the results together and to use for the insert into the final table

SELECT field-1,field-2
FROM duplicateValues

UNION

SELECT field-1,field-2
FROM nonDuplicateValues


Change database login default schema I have a problem. I have a Windows user [domain\user] on my instance that has access to some database. I want to change the default schema for that login. I can do this when I create some test login and user in the database but not for my Windows user:

ALTER USER [MFG\giorgi.nakeuri] WITH DEFAULT_SCHEMA=[TBBS]

...results in:

  Cannot alter the user 'MFG\giorgi.nakeuri', because it does not exist
  or you do not have permission.

But when I am trying to add it:

CREATE USER [MFG\giorgi.nakeuri] FOR LOGIN [MFG\giorgi.nakeuri]

I get:

  The login already has an account under a different user name.

When I observe login in properties it has a check on the database and both user and default schema are dbo. But I can not change any of those from that window. I am connected to instance with this windows user and login server role is all: public, securityadmin, serveradmin, setupadmin, sysadmin.

It looks like based on your error that the user you are trying to create a login for already has a login in that database with another name, so you are effectively "double mapping" them.

If you have access to SSMS you can see what their mapping is by using the Object Explorer and navigating the following path:
Security -> Logins -> Double click on the username

This should pop up a GUI, you can see the "User Mapping" in the top left hand corner, this should show you to whom the user is mapped.


AlwaysON - Force users connection to use Readable Secondary Nodes I'm implementing SQL Server 2015 Enterprise on my actual client, using Always On Availability Groups. Processes like Reporting, ETL (extraction of data), and read workload are being redirected to the secondary replica using the Listener and the "initial catalog" and "applicationintent=readonly" parameters.

The problem is: My client wants to force all non-admin users to connect always to the secondary-readable replica. BUT! make this automatically. The client does not want that the users have to add parameters to the connection in SQL Server Management Studio.

I'm testing logon trigger with no results. I can't find a way out to this.

Is this possible?

There is no way to complete this with just SQL Server and the interfaces it exposes. In order to do this you'll either need an appliance between the clients and SQL Server (the users connect to the appliance endpoint and the appliance does the redirection) or you can implement your own through a cname and some type of configurable load balancer like an F5.

There is nothing in 2016 to change this behavior AFAIK.

It's possible but the client won't like it.

Disable automatic failover of the AG.
Keep applications using the listener.
Remove all user logins from the primary.
Setup user logins only on the secondary. Provide the secondary node name directly to the users instead of the listener.
Failover manually when required (if it stays that way then you'll then need to switch all the logins over again and possibly have people start connecting directly to the old primary).

It sounds stupid but some people do use AGs in this scenario because they want the read only replica and don't care about the HA. Problem "solved".


Load Balancing with SQL Server What exists today to do Microsoft SQL Server Load Balancing on Windows Server and being transparent to any programmatic access. Is this built in or does a solution have to be purchased?

I'm trying to find out if there is a solution today that makes this very easy from the view point of the web servers or other database access to SQL Server.

There is no "standard" load balancing set up for MS SQL Server that you can run via a wizard.

This would be a database architecture decision and implemented at the database level not the server level. Techniques would be:

Scaling out/federating the database servers
Partitioning
Offload your reporting requirements
Perhaps replication

If anyone disagrees, then I'd like to see an article by a respected known MS SQL figure saying clustering is load balancing. The articles quoted above do not mention load balancing. For example, A Microsoftie (Chas Boyd) says it is not here.

My question to the OP would be what kind of load do you expect?

Database servers are usually IO and memory bound, so proper disk configuration (with appropriate filegroups) and as much RAM as possible will go a lot further than any solution above.

Don't forget: SQL Server 2005/Windows 2003 Enterprise 32-bit goes to 32GB RAM (of which you'd have 26-28GB data cache) and you are not limited by drive letters because of NTFS mount points. As for x64...

New technologies for database load balancing, separate from SQL Server, are available. These software solutions integrate with Always On in 2012 or 2014 and support automatic read/write split and other load balancing techniques. Look for NetScaler DataStream or ScaleArc for SQL Server as two examples of this transparent SQL load balancing software.


What alternatives exist when a table requires too many foreign keys? We have a base table that defines parts and holds information like part number, description, price, weight, etc.  We also have approximately 400 tables that reference the base table and provide additional information about the parts based on their type / category.

We started by using foreign key constraints so that a part cannot be deleted from the base table if it is being referenced in one of the 400 part specific tables but we quickly reached the maximum 253 recommended foreign keys for SQL Server 2005.

Are there any alternatives to foreign keys in this situation that will ensure data integrity?  We haven't seen performance issues when accessing the data but updating an existing part in the base table will fail as the query plan is too complex.

If there's any way to group parts, you might be able to introduce intermediate tables as a workaround.  This won't work.

Parts
+ Table 1
+ Table 2
+ ...
+ Table 400

But something along these lines might.

Parts
+ RedOrangeYellow parts
  + Table 1
  + Table 2
  + ...
  + Table 200

+ GreenBlueIndigoViolet parts
  + Table 201
  + Table 202
  + ...
  + Table 400

I'd want to take a hard look at your DDL before I recommended doing this, though.  And if you do this, don't start throwing ID numbers all over the place. You ought to be able to join "Table 400" directly to "Parts" without including "GreenBlueIndigoViolet parts".

Replace the 400+ tables with one.  Only need 3 fields (+1 autonumber or whatever primary key if you want it, don't have to have it you can form it out of the other fields)

Item ID
Attribute
Value

So where in your other tables each field represents an attribute, in this table your attributes are all in one field.  You would have something like this

ItemID  Attribute  Value

Sock    Material   Wool

Sock    Color      Red

Sock    Weight     20 pounds

Alien   Planet     Alpha Centauri

Alien   Color Purple

Alien   Friendly   No

The ItemIDs should probably be a number/alphanumeric ofc.  Then, you just crosstab w/ Atrributes as column headers when necessary to generate the tables like you like.  This also allows for better queries for things like "Show me all items that are from Alpha Centauri" which could give you back the Alien and also the Meteorite fragment that contained the plague that wipes out mankind (it's coming.....)

Optimization may be tricky depending on how many records there are but it's a much better way to design this.  I did the same for a database that contained a bunch of recipes (10k+) that had few overlaps.  Worked well in that case.  Didn't have any speed problems really.  Yours may be harder depending on how many you are dealing with.