JOIN ON syntax error in Microsoft Access 2016 I am querying a couple tables, and for some reason Microsoft Access 2016 continually informed me that my SQL query JOIN ON syntax is incorrect. Specifically, the JOIN syntax is highlighted, followed by the ON syntax when removing that (the latter for clearer reasons based on prior knowledge of SQL JOIN ON syntax).

Here is the code:

SELECT Column1, Column2, Table1.Column3
FROM Table1
JOIN Table2
    ON Table1.Column3 = Table2.Column3
   AND Column4 = '{NAME}'
ORDER BY Column4;

Is there something incorrect about this syntax? By the way, this is using ANSI-89 Syntax.

Access requires INNER JOIN. The simple JOIN (which should default to INNER JOIN according to the SQL standard) is not allowed in Access.

After this passage ON Table1.Column3 = Table2.Column3 you should put where instead of and.


Percona xtradb cluster node2 won't start I have configured Percona Xtradb cluster 5.6. The first node starts fine when bootstrapped, but the second node seems to have an issue with this variable setting:

wsrep_provider=/usr/lib/libgalera_smm.so

I am running the cluster nodes on Ubuntu 14.04. For the error log, this is what is throws at me:

150608 18:43:03 mysqld_safe Starting mysqld daemon with databases from
/var/lib/mysql

150608 18:43:03 mysqld_safe Skipping wsrep-recover for empty datadir:
/var/lib/mysql
150608 18:43:03 mysqld_safe Assigning 00000000-0000-0000-0000-000000000000:-1
to wsrep_start_position
2015-06-08 18:43:03 0 [Warning] Using unique option prefix key_buffer instead
of key_buffer_size is deprecated and will be removed in a future release.
Please use the full name instead.
2015-06-08 18:43:03 0 [Warning] TIMESTAMP with implicit DEFAULT value is
deprecated. Please use --explicit_defaults_for_timestamp server option (see
documentation for more details).
2015-06-08 18:43:03 0 [Note] /usr/sbin/mysqld (mysqld 5.6.24-72.2-56-log)
starting as process 5977 ...
2015-06-08 18:43:03 5977 [Note] WSREP: Read nil XID from storage engines,
skipping position init
2015-06-08 18:43:03 5977 [Note] WSREP: wsrep_load(): loading provider library
'/usr/lib/galera3/libgalera_ssm.so'
2015-06-08 18:43:03 5977 [ERROR] WSREP: wsrep_load(): dlopen():
/usr/lib/galera3/libgalera_ssm.so: cannot open shared object file: No such
file or directory
2015-06-08 18:43:03 5977 [ERROR] WSREP:
wsrep_load(/usr/lib/galera3/libgalera_ssm.so) failed: Invalid argument (22).
Reverting to no provider.
2015-06-08 18:43:03 5977 [Note] WSREP: Read nil XID from storage engines,
skipping position init
2015-06-08 18:43:03 5977 [Note] WSREP: wsrep_load(): loading provider library
'none'
2015-06-08 18:43:03 5977 [ERROR] Aborting

2015-06-08 18:43:03 5977 [Note] WSREP: Service disconnected.
2015-06-08 18:43:04 5977 [Note] WSREP: Some threads may fail to exit.
2015-06-08 18:43:04 5977 [Note] Binlog end
2015-06-08 18:43:04 5977 [Note] /usr/sbin/mysqld: Shutdown complete

I have tried commenting out #wsrep_provider and starting the node with the following command

$sudo /etc/init.d/mysql start --wsrep_provider="/usr/lib/libgalera_ssm.so"

But this does not seem to help. The node still fails to start.

The libgalera_ssm.so file is actually present on both servers, hence I am kinda confused why it works on one server and not the other. But it is worth pointing out that even node one gives the same error when I try to start it without using bootstrap-pxc and just use /etc/init.d/mysql start.

The log entries

2015-06-08 18:43:03 5977 [ERROR] WSREP: wsrep_load(): dlopen(): /usr/lib/galera3/libgalera_ssm.so: cannot open shared object file: No such file or directory
2015-06-08 18:43:03 5977 [ERROR] WSREP: wsrep_load(/usr/lib/galera3/libgalera_ssm.so) failed: Invalid argument (22). Reverting to no provider.

show that MySQL cannot find the file /usr/lib/galera3/libgalera_ssm.so so it cannot use it as a WSREP provider.  Check that this file exists and is accessible.  If your system is 64-bit, it might be in /usr/lib64/.

Also please post the error message you get when starting the node -- without reading the exact error message it's a shot in the dark.

Check the library name:

/usr/lib/libgalera_ssm.so (wrong)
/usr/lib/libgalera_smm.so (right)


SQL Server: index creation date In SQL Server 2005 and above, how can I find when an index was created?

My answer is a direct quote of this link:

http://www.sqlservercentral.com/Forums/Topic960549-391-1.aspx#bm960643

There is no direct way of finding the creation date of an index. But however if you look in sysobjects there is a creation date for Primary Key or Unique Constraints.Indexes associated with primary Primary Key or Unique Constraints creation date can be known.

check if there are any new DMVs (Dynamic Mgmt Views) in sql server 2008 which can help you in getting creationDate.

try this query

select crdate, i.name, object_name(o.id)
from sysindexes i
   join sysobjects o ON o.id = i.id
order by crdate desc

There is no way to know a single creation date of an index because it is not saved or collected by SQL Server.


Tools to migrate from SQLite to PostgreSQL I need to continuously migrate from SQLite to PostgreSQL. By continuously, I mean that I will reimport the SQLite database into PostgreSQL every day. It would be nice if I could make changes to the tables in SQLite as I please without having to manually make any changes to the PostgreSQL database. I will run the migration from Python, but it could be any external tool, which can be controlled from command line. The tool needs to be available for Linux, and it would be nice if it also ran on Windows.

Have you looked into using ETL tools? They are external and I believe pentaho runs on both windows and linux and its free.
pentaho's site

I have good experience with Full Convert.


using tnsnames.ora and sqlplus client without installing oracle 11g server on linux client I want to create a connection to remote Oracle server using SQLplus client and tnsnames.ora without installing Oracle 11g server on the Linux client. I used debian and bash script to generate reports from remote Oracle servers. Is this possible?

I tried installing SQLplus client but I can't find the tnsnames.ora files.

If possible, can you please give me an example of configuring SQLplus client to use tnsnames.ora files?

I have installed the "basic instant client" and the "sqlplus addition" package. I can connect normally with sqlplus user/password@host:port/servicenames. I want to create bash scripts to execute *.sql files on more than 10 instances. So, if I can just connect with sqlplus user/pw@instance It will look simple. it's is possible?

Update :
Solved by copying tnsnames.ora files to /etc.

refer to this page on post #3

Thanks to :
Wernfried Domscheit, EdStevens, Paul White

tnsnames.ora doesn't "come with" anything.  The key is not that file, as it is nothing but a names resolution file. And sqlplus is nothing but a CLI application. If you want to connect to an Oracle database (or ANY database) you must have, at a minimum, the client software for that particular database product. For oracle you need to download and install the Oracle client. The easiest would be the "instant client", which you can download from here: http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html.  If you want the full "Admin" or "Runtime" client, download from edelivery.com and select just the client software. Pay attention to the license terms.

Hope this helps...  (wrapped for readability)

sqlplus username/password@'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
    (HOST=hostname)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=service_name)
    (INSTANCE_NAME=instance_name)))'


Single or multiple non-clustered indexes when pairing with a composite primary key I have a database where I am utilizing a TenantId throughout all tables that need to be uniquely identified to a particular tenant, and due to the ordering requirements within composite keys, I have TenantId as the first in the index list. Now comes into question an Authentication piece where the User table contains the TenantId, UserId (the IDENTITY column), and Email among other login-specific items.

The login portal is not tenant-specific, so when logging in, the user will simply input their Email, thus seeking to the row that verifies their login information. In this scenario, we are unable to immediately leverage the composite primary key of TenantId and UserId until we have found the row that applies to Email.

The composite primary key on TenantId and UserId will always be utilized in all other conditional clauses. However, in order to leverage this key in the first place, we must first seek to that row based on a query of Email. Without an index on Email, a table scan will occur instead.

My question is, what type of index combination would be seen as most suitable in this scenario? A single non-clustered index on Email alone, another composite key on UserId and Email in conjunction with the single non-clustered index on Email with INCLUDES on other relevant data, or none of the above?

The schema is similar as such:

CREATE TABLE [User] (
     [TenantId] [int] NOT NULL
    ,[UserId] [int] IDENTITY(1,1) NOT NULL
    ,[Email] [varchar](64) NOT NULL
    ,[FirstName] [varchar](32) NOT NULL
    ,[MiddleName] [varchar](32) NULL
    ,[LastName] [varchar](32) NOT NULL
    ,[PasswordHash] [varbinary](64) NOT NULL
    ,[PasswordSalt] [varbinary](32) NOT NULL
    ,[HashMethodId] [tinyint] NOT NULL
    ,[IsActive] [bit] NOT NULL CONSTRAINT [DF_User_IsActive] DEFAULT 1
    ,[IsLocked] [bit] NOT NULL CONSTRAINT [DF_User_IsLocked] DEFAULT 0

    ,CONSTRAINT [PK_User_TenantId_UserId] PRIMARY KEY CLUSTERED ([TenantId] ASC, [UserId] ASC)
    ,INDEX [IX_User_UserId_Email] NONCLUSTERED ([UserId] ASC, [Email] ASC)
    ,CONSTRAINT [FK_Tenant_TenantId] FOREIGN KEY ([TenantId]) REFERENCES [Tenant]([TenantId])
    ,CONSTRAINT [FK_HashMethod_HashMethodId] FOREIGN KEY ([HashMethodId]) REFERENCES [HashMethod]([HashMethodId])
);
CREATE NONCLUSTERED INDEX [IX_User_Email] ON [User]([Email]) INCLUDE ([PasswordHash],[PasswordSalt],[HashMethodId],[IsActive],[IsLocked])

-- Note for research: Why can an index that has INCLUDE not be specified in CREATE TABLE?

My understanding is that [IX_User_UserId_Email] is useful in this scenario to quickly tie back to [PK_User_TenantId_UserId], thus seeking to the appropriate level of isolation more efficiently. Is this an incorrect assumption? Am I better served with using just [IX_User_Email]?

All tables will JOIN to User on TenantId and UserId.
No tables will strictly JOIN to User based on UserId.
A lookup will happen based strictly on a query of Email. TenantId and UserId will not be known until the row is fetched. Once the row is fetched, remaining queries will utilize TenantId and UserId.

Another option I have been tossing around is within the Tenant table, including a Domain column that specifies the tenant source e-mail domain (which will always be the same across a tenant). Once the user has entered their Email and tabs/selects the Password field on the login page, it will parse out the e-mail domain (@sample.com), allowing us to query the smaller Tenant table to find their TenantId, thus being able to leverage the composite key [PK_User_TenantId_UserId] and thus only having to utilize a non-clustered index on Email. This may be a needless approach, however.

This is a strange one as [UserId] [int] IDENTITY(1,1) NOT NULL is unique.

There could be a case to use [UserId] alone as the PK.  You will get less fragmentation compared to a composite clustered index TenantId, UserId.

I get you plan to use TenantId, UserId in all queries but you don't need to.  UserID will uniquely identify each user.

If you have reports by TenantId you can include that in the tables and put a non-clustered index on it.

As far as email I would just put a non-clustered unique index on it. You can include the other fields if you like but it is just picking up data from a single row so it is not really necessary.  Right now there is nothing to prevent duplicate email.

Could argue repeating TenantId in all tables is not 3NF as it can be derived from  UserId.

I personally would not repeat TenantId and have a view for each table that brings it in. I get you don't want to do it that way but it is still my answer.

select t.*, u.tenant
  from table t
  join [user] u
    on u.userid = t.userid

User is not a good name for a table as it is a keyword.

If you will be accessing this table by TenantID and UserID then primary key choice is good, although the UserID is unique by it self.
Why did you put UserId before Email in the nonclustered index. Put only Email in the index and you will be obviously be able to search by Email. If you know the UserId then you know the Email too.


Neo4J community edition DB size limit? The Neo4J site states that the community version of Neo4J has a limit in the graph size, but does not state the limit of the community version.

https://neo4j.com/editions/

What is the size limit of the community version?

I contacted uk@neo4j.com to ask for clarification and the answer they gave is that:

  There is a limit on node count and relationship count of 34Bn each

and

  The 34bn limit doesn't apply to Neo4j Enterprise

The release announcement for 3.0 mentions a 34bn limit that no longer applies:

  Dynamic pointer compression expands Neo4j’s available address space as needed, making it possible to store graphs of any size. That’s right: no more 34 billion node limits!

So my guess is that 'Dynamic pointer compression' is an Enterprise Edition feature.

From version 3.x.x there's not node limit for the graph.
For more reference you can go through this reference saying

  Dynamic pointer compression expands Neo4j’s available address space as needed, making it possible to store graphs of any size. That’s right: no more 34 billion node limits!

currently we have billions of nodes in our servers with size upto TBs. It has been tested.

Hope this helps!


Why SqlBulkCopy gets slower by table's row count? I'm using SqlBulkCopy to insert a lot of data (10000/second at peek) into a database. And when the table starts empty, each bulk insertion takes like 300 milliseconds. But after a day when the records count become more than 20 millions, the insertion time becomes 2.5 seconds per 10K records. And this time increases with the count of records.

I have only a simple table, with no indexes except an identity clustered primary key.

I thought that this behavior is an append-only behavior and won't be affected by the table size. Like throwing a pile of litter on yet a huge pile of litter. That shouldn't be affected by the size.

What do I miss here?

Update: Table's script created via SSMS is:

CREATE TABLE [pardis].[DeliveryReports](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [PardisId] [bigint] NOT NULL,
    [Status] [bigint] NOT NULL,
    [PartId] [bigint] NOT NULL,
    [ShortCode] [varchar](100) NOT NULL,
    [PhoneNumber] [varchar](100) NOT NULL,
    [DateTimeReceived] [datetime] NOT NULL,
    [PersianDateTimeReceived] [varchar](100) NOT NULL,
    [DateTimeSentToCp] [datetime] NULL,
    [PersianDateTimeSentToCp] [varchar](100) NULL,
    [DeliveringToCpResult] [nvarchar](max) NULL,
    [IsProcessing] [bit] NOT NULL CONSTRAINT [DF_DeliveryReports_IsProcessing_1]  DEFAULT ((0)),
    [IsSentToCp] [bit] NOT NULL CONSTRAINT [DF_DeliveryReports_IsSentToCp]  DEFAULT ((0)),
 CONSTRAINT [PK_DeliveryReports] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Update 2: This is the execution plan for inserting 1 record via SSMS. As you can see, no hidden cost exists in inserting records in the table. Please note that this execution plan is after deleting the primary key index to prevent any index from affecting the performance.

Why you write that you don't make any index ?
What is PK_DeliveryReports in your opinion ?
As for me - the clu of your problem is just rebuilding of PK.
Try to use partitions to your table - or drop this index (PK) and rebuild it after the load - should be quicker (of course if you don't need index during the load for other purposes).


SQL Server unpredictable select results (dbms error?) Below is simple example, which returns strange results, that are unpredictable and we cannot explain it in our team. Are we doing something wrong or is it SQL Server error?

After some investigation we reduced the search area to union clause in subquery, which selects one record from "men" table

It works as expected in SQL Server 2000 (returns 12 rows), but in 2008 and 2012 it returns only one row.

create table dual (dummy int)

insert into dual values (0)

create table men (
man_id int,
wife_id int )

-- there are 12 men, 6 married
insert into men values (1, 1)
insert into men values (2, 2)
insert into men values (3, null)
insert into men values (4, null)
insert into men values (5, null)
insert into men values (6, 3)
insert into men values (7, 5)
insert into men values (8, 7)
insert into men values (9, null)
insert into men values (10, null)
insert into men values (11, null)
insert into men values (12, 9)

This returns only one row:   1  1   2

select
man_id,
wife_id,
(select count( * ) from
    (select dummy from dual
     union select men.wife_id  ) family_members
) as family_size
from men
--where wife_id = 2 -- uncomment me and try again

Uncomment last line and it gives: 2 2   2

There is a lot of odd behaviors:

After series of drops, creates, truncates and inserts on "men" table it sometimes works (returns 12 rows)
When you change "union select men.wife_id" to "union all select men.wife_id" or "union select isnull(men.wife_id, null)" (!!!) it returns 12 rows (as expected).
The strange behavior seems to be unrelated to datatype of column "wife_id". We observed it on development system with much greater data sets.
"where wife_id > 0" returns 6 rows
we also observes strange behavior of views with this kind of statements. SELECT * returns subset of rows, SELECT TOP 1000 returns all

Are we doing something wrong or is it SQL Server error?

It is a wrong-results bug, which you should report via your usual support channel. If you do not have a support agreement, it may help to know that paid incidents are normally refunded if Microsoft confirms the behaviour as a bug.
The bug requires three ingredients:

Nested loops with an outer reference (an apply)
An inner-side lazy index spool that seeks on the outer reference
An inner-side Concatenation operator

For example, the query in the question produces a plan like the following:

There are many ways to remove one of these elements, so the bug no longer reproduces.
For example, one could create indexes or statistics that happen to mean the optimizer chooses not to utilize a Lazy Index Spool. Or, one could use hints to force a hash or merge union instead of using Concatenation. One could also rewrite the query to express the same semantics, but which results in a different plan shape where one or more of the required elements are missing.
More details
A Lazy Index Spool lazily caches inner side result rows, in a work table indexed by outer reference (correlated parameter) values. If a Lazy Index Spool is asked for an outer reference it has seen before, it fetches the cached result row from its work table (a "rewind"). If the spool is asked for an outer reference value it has not seen before, it runs its subtree with the current outer reference value and caches the result (a "rebind"). The seek predicate on the Lazy Index Spool indicates the key(s) for its work table.
The problem occurs in this specific plan shape when the spool checks to see if a new outer reference is the same as one it has seen before. The Nested Loops Join updates its outer references correctly, and notifies operators on its inner input via their PrepRecompute interface methods. At the start of this check, inner side operators read the CParamBounds:FNeedToReload property to see if the outer reference has changed from last time. An example stack trace is shown below:

When the subtree shown above exists, specifically where Concatenation is used, something goes wrong (perhaps a ByVal/ByRef/Copy problem) with the bindings such that CParamBounds:FNeedToReload always returns false, regardless of whether the outer reference actually changed or not.
When the same subtree exists, but a Merge Union or Hash Union is used, this essential property is set correctly on each iteration, and the Lazy Index Spool rewinds or rebinds each time as appropriate. The Distinct Sort and Stream Aggregate are blameless, by the way. My suspicion is that Merge and Hash Union make a copy of the previous value, whereas Concatenation uses a reference. It is just about impossible to verify this without access to the SQL Server source code, unfortunately.
The net result is that the Lazy Index Spool in the problematic plan shape always thinks it has already seen the current outer reference, rewinds by seeking into its work table, generally finds nothing, so no row is returned for that outer reference. Stepping through the execution in a debugger, the spool only ever executes its RewindHelper method, and never its ReloadHelper method (reload = rebind in this context). This is evident in the execution plan because operators under the spool all have 'Number of Executions = 1'.

The exception, of course, is for the first outer reference the Lazy Index Spool is given. This always executes the subtree and caches a result row in the work table. All subsequent iterations result in a rewind, which will only produce a row (the single cached row) when the current iteration has the same value for the outer reference as the first time around.
So, for any given input set on the outer side of the Nested Loops Join, the query will return as many rows as there are duplicates of the first row processed (plus one for the first row itself of course).
Demo
Table and sample data:
CREATE TABLE #T1
(
    pk integer IDENTITY NOT NULL,
    c1 integer NOT NULL,

    CONSTRAINT PK_T1
    PRIMARY KEY CLUSTERED (pk)
);
GO
INSERT #T1 (c1)
VALUES
    (1), (2), (3), (4), (5), (6),
    (1), (2), (3), (4), (5), (6),
    (1), (2), (3), (4), (5), (6);

The following (trivial) query produces a correct count of two for each row (18 in total) using a Merge Union:
SELECT T1.c1, C.c1
FROM #T1 AS T1
CROSS APPLY
(
    SELECT COUNT_BIG(*) AS c1
    FROM
    (
        SELECT T1.c1
        UNION
        SELECT NULL
    ) AS U
) AS C;

If we now add a query hint to force a Concatenation:
SELECT T1.c1, C.c1
FROM #T1 AS T1
CROSS APPLY
(
    SELECT COUNT_BIG(*) AS c1
    FROM
    (
        SELECT T1.c1
        UNION
        SELECT NULL
    ) AS U
) AS C
OPTION (CONCAT UNION);

The execution plan has the problematic shape:

And the result is now incorrect, just three rows:

Though this behaviour is not guaranteed, the first row from the Clustered Index Scan has a c1 value of 1. There are two other rows with this value, so three rows are produced in total.
Now truncate the data table and load it with more duplicates of the 'first' row:
TRUNCATE TABLE #T1;

INSERT #T1 (c1)
VALUES
    (1), (2), (3), (4), (5), (6),
    (1), (2), (3), (4), (5), (6),
    (1), (1), (1), (1), (1), (1);

Now the Concatenation plan is:

And, as indicated, 8 rows are produced, all with c1 = 1 of course:

I notice you have opened a Connect item for this bug but really that is not the place to report issues that are having a production impact. If that is the case, you really ought to contact Microsoft Support.

This wrong-results bug was fixed at some stage. It no longer reproduces for me on any version of SQL Server from 2012 onward. It does repro on SQL Server 2008 R2 SP3-GDR build 10.50.6560.0 (X64).

Why do you use a subquery without the from statement? I think that may cause the difference in 2005 and 2008 servers. Maybe you could go with explicit join?

select
m1.man_id,
m1.wife_id,
(select count( * ) from
    (select dummy from dual
     union
     select m2.wife_id
     from men m2
     where m2.man_id = m1.man_id) family_members
) as family_size
from men m1


Which one to consider for upgrade SQL Server 2008 vs SQL Server 2014 or Compatibility level 100 vs 110 I need to upgrade the SQL Server 2008 to SQL Server 2014 . SQL Server 2008 is currently running on compatibility level 100. I need to upgrade to SQL Server 2014 with compatibility level 100 this year and to compatibility level 110 next mid year.

If I deploy SQL Server 2014 with compatibility level 100 will I able to see the new features of SQL Server 2014 or I need to upgrade the compatibility level also in order to use new features of SQL 2012 and SQL 2014?

Compatibility levels are the way to tell SQL Server how to interpret the T-SQL and how the database will behave. Paul Randal wrote up a short comparison about the differences between version and compatibility levels about 9 years ago.

In a nutshell he says there is no relationship between compatibility versions and a database versions.  But you may have access to the new features in an older compatibility version of a database they just may not behave as expected.  One extra thing of note is that if you have multiple databases on the same server you can set each of them to their own compatibility level.

SQL Server 2014 has a great feature which can not be compared with any other version. It is the in-memory computing feature of SQL Server 2014. So if you want to get use of this in-memory feature of SQL Server 2014, upgrade to it with converting your codes for supporting native in-memory development features of Transact-SQL.
SAP is promoting the in-memory computing under the brand of HANA.
SQL Server 2014 and SQL Server 2016 or any other in-memory databases combines OLTP and OLAP properties providing fast analysis, reporting, etc besides managing transactional tasks at the same time.


SQL Server VARCHAR Column Width Searching around the web, I have found conflicting advice on whether there is a performance impact when specifying overly-wide VARCHAR columns, e.g. VARCHAR(255) when VARCHAR(30) will probably do.

I consistently see agreement that there's a performance hit if the entire row exceeds 8060 bytes.  Other than that, I see disagreement.

Is the claim true that The default is SET ANSI PADDING ON = potential for lots of trailing spaces?  As long as the total row width is less than 8060, are there any real performance concerns in over-sizing VARCHAR columns?

Evidence that column width matters

The same goes for CHAR and VARCHAR data types. Don’t specify more characters in character columns that you need.

http://www.sql-server-performance.com/2007/datatypes/

Length is a constraint on the data (like CHECK, FK, NULL etc)
Performance when the row exceeds 8060 bytes
Can not have unique constraint or index (key column width must be < 900)
The default is SET ANSI PADDING ON = potential for lots of trailing spaces

What are the consequences of setting varchar(8000)?

Evidence that column width DOES NOT matter

If you're talking about varchar and nvarchar then no, there is no penalty for allowing a higher field length.

https://stackoverflow.com/questions/7025996/overstating-field-size-in-database-design

The varchar datatype, by contrast, consumes only the amount of
actual space used plus 2 bytes for overhead

http://sqlfool.com/content/PerformanceConsiderationsOfDataTypes.pdf

The question might be better stated as:

  "What is the advantage of over-specifying the maximum length of a variable-length column?"

In general, there is little advantage, and several disadvantages as the various linked answers point out.  Aside from any other concerns, consider that SQL Server is not open-source: there are many 'magic numbers' and heuristics applied based on the information provided to the system.  Without source code access, we could never be entirely sure what the impact of this practice might be.

In some cases, where the average length of a column is significantly higher than the 50% assumed by SQL Server when calculating sort/hash memory grants, you may see a performance improvement by over-specifying the maximum length.  This is a dubious workaround, and should probably only be applied by an explicit CAST or CONVERT (with comments!) rather than changing the base column definition.  Sometimes, it will be preferable to rewrite the query to sort keys instead of whole rows anyway.

Where the maximum row size might exceed the in-row limit (even if no rows actually do), deleting rows can cause unexpected page-splitting if a trigger is present.  Updates may also lead to fragmentation via the same mechanism.

SQL Server does a pretty good job in most cases where it is provided with good, accurate information from metadata.  Compromising this principle for 'convenience' seems unwise to me.  A sensible approach is to choose a maximum length value that is reasonable according to the actual data to be stored, and any foreseeable changes.

AS you indicated, as long as the row size does not exceed 8060 (or whatever the maximum is set to), there is no performance difference in using VARCHAR(30) or VARCHAR(255) or larger. The CHAR to VARCHAR comparison from the linked article is not really pertinent. Although I agree you should not specify more space than you are sure you need, in many cases you do not know just how much space you will actually need. So be liberal with VARCHAR space - I'm pretty sure increasing the size of fields requires a table rebuild, while decreasing is a simple ALTER TABLE statement.


Increase speed altering column on large table to NON NULL I recently added a NULL-able bit column to a table which has close to 500 million rows.  There's not a default on the column, however all inserts are specifying a value of 0 or 1, and I ran a one-time routine to assign 0 or 1 to all existing rows (updating the rows in small batches).  Every row should now have a 0 or 1 in that column.

I want to make the bit column non-nullable however when I tried doing so via ALTER TABLE t1 ALTER COLUMN c1 bit not null, it started running for 3 minutes and I stopped it because it was blocking all reads to the table and I suspected it was going to take a long time to complete.  It's possible it wouldn't take too long but I couldn't risk too much unavailability.  The rollback itself took 6 minutes.

Do you have any suggestions on how I can make the column non-nullable without it taking potentially hours to complete?  Additionally is there any way to estimate how long the ALTER TABLE ALTER COLUMN statement I started and then cancelled would take to complete?

I am using SQL Server 2017 Web Edition.

Instead of changing the column definition you could add a CHECK CONSTRAINT that doesn't allow NULLs for that column. The table will still need to be scanned but it won't need to modify every single data page, so it should be a much faster operation. Regrettably, a Sch-M lock will still be held during the operation. One trick is to try to get as much of the table into the buffer pool as possible before attempting to add the constraint. That may reduce the amount of time the Sch-M lock is held.

You can then remove the constraint and change the column definition during your next maintenance window.

Try copying the data to a new table, then rename it. You have to take care of any constraints and indexes. That's what SSMS table designer is doing when you want to reorder the columns (for example), but you should check the script to see if there is anything that doesn't look right.

During the copying, read access to the source table is not a problem, but if there are any writes they may be blocked or not copied, depending on the isolation level.


What is the Query to display the failed sql jobs What is the Query to display the failed sql jobs,so that I can get all the failed job information with one query

I don't think you will really get "all" the job information with one query since jobs can be configured to go to output files. Output files can at times get more information than is reported or written to the msdb tables.

However, the view that is found by looking at the job history via SSMS can be pulled with this query to return only failed jobs (e.g. if a job has 2 steps and the second failed this query will return both steps):

select j.name
    ,js.step_name
    ,jh.sql_severity
    ,jh.message
    ,jh.run_date
    ,jh.run_time
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobsteps AS js
   ON js.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobhistory AS jh
   ON jh.job_id = j.job_id AND jh.step_id = js.step_id
WHERE jh.run_status = 0

SELECT  [JobName] = [jobs].[name]
 ,[Category] = [categories].[name]
 ,[Owner] = SUSER_SNAME([jobs].[owner_sid])
 ,[Enabled] = CASE [jobs].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END
 ,[Scheduled] = CASE [schedule].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END
 ,[Description] = [jobs].[description]
 ,[Occurs] =
 CASE [schedule].[freq_type]
 WHEN   1 THEN 'Once'
 WHEN   4 THEN 'Daily'
 WHEN   8 THEN 'Weekly'
 WHEN  16 THEN 'Monthly'
 WHEN  32 THEN 'Monthly relative'
 WHEN  64 THEN 'When SQL Server Agent starts'
 WHEN 128 THEN 'Start whenever the CPU(s) become idle'
 ELSE ''
 END
 ,[Occurs_detail] =
 CASE [schedule].[freq_type]
 WHEN   1 THEN 'O'
 WHEN   4 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' day(s)'
 WHEN   8 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' weeks(s) on ' +
 LEFT(
 CASE WHEN [schedule].[freq_interval] &  1 =  1 THEN 'Sunday, '    ELSE '' END +
 CASE WHEN [schedule].[freq_interval] &  2 =  2 THEN 'Monday, '    ELSE '' END +
 CASE WHEN [schedule].[freq_interval] &  4 =  4 THEN 'Tuesday, '   ELSE '' END +
 CASE WHEN [schedule].[freq_interval] &  8 =  8 THEN 'Wednesday, ' ELSE '' END +
 CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, '  ELSE '' END +
 CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, '    ELSE '' END +
 CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, '  ELSE '' END ,
 LEN(
 CASE WHEN [schedule].[freq_interval] &  1 =  1 THEN 'Sunday, '    ELSE '' END +
 CASE WHEN [schedule].[freq_interval] &  2 =  2 THEN 'Monday, '    ELSE '' END +
 CASE WHEN [schedule].[freq_interval] &  4 =  4 THEN 'Tuesday, '   ELSE '' END +
 CASE WHEN [schedule].[freq_interval] &  8 =  8 THEN 'Wednesday, ' ELSE '' END +
 CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, '  ELSE '' END +
 CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, '    ELSE '' END +
 CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, '  ELSE '' END
 ) - 1
 )
 WHEN  16 THEN 'Day ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)'
 WHEN  32 THEN 'The ' +
 CASE [schedule].[freq_relative_interval]
 WHEN  1 THEN 'First'
 WHEN  2 THEN 'Second'
 WHEN  4 THEN 'Third'
 WHEN  8 THEN 'Fourth'
 WHEN 16 THEN 'Last'
 END +
 CASE [schedule].[freq_interval]
 WHEN  1 THEN ' Sunday'
 WHEN  2 THEN ' Monday'
 WHEN  3 THEN ' Tuesday'
 WHEN  4 THEN ' Wednesday'
 WHEN  5 THEN ' Thursday'
 WHEN  6 THEN ' Friday'
 WHEN  7 THEN ' Saturday'
 WHEN  8 THEN ' Day'
 WHEN  9 THEN ' Weekday'
 WHEN 10 THEN ' Weekend Day'
 END + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)'
 ELSE ''
 END
 ,[Frequency] =
 CASE [schedule].[freq_subday_type]
 WHEN 1 THEN 'Occurs once at ' +
 STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':')
 WHEN 2 THEN 'Occurs every ' +
 CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Seconds(s) between ' +
 STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' +
 STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
 WHEN 4 THEN 'Occurs every ' +
 CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Minute(s) between ' +
 STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' +
 STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
 WHEN 8 THEN 'Occurs every ' +
 CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Hour(s) between ' +
 STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' +
 STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
 ELSE ''
 END
 ,[AvgDurationInSec] = CONVERT(DECIMAL(10, 2), [jobhistory].[AvgDuration])
 ,[Next_Run_Date] =
 CASE [jobschedule].[next_run_date]
 WHEN 0 THEN CONVERT(DATETIME, '1900/1/1')
 ELSE CONVERT(DATETIME, CONVERT(CHAR(8), [jobschedule].[next_run_date], 112) + ' ' +
  STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [jobschedule].[next_run_time]), 6), 5, 0, ':'), 3, 0, ':'))
 END
FROM  [msdb].[dbo].[sysjobs] AS [jobs] WITh(NOLOCK)
  LEFT OUTER JOIN [msdb].[dbo].[sysjobschedules] AS [jobschedule] WITh(NOLOCK)
  ON [jobs].[job_id] = [jobschedule].[job_id]
  LEFT OUTER JOIN [msdb].[dbo].[sysschedules] AS [schedule] WITh(NOLOCK)
  ON [jobschedule].[schedule_id] = [schedule].[schedule_id]
  INNER JOIN [msdb].[dbo].[syscategories] [categories] WITh(NOLOCK)
  ON [jobs].[category_id] = [categories].[category_id]
  LEFT OUTER JOIN
 ( SELECT  [job_id], [AvgDuration] = (SUM((([run_duration] / 10000 * 3600) +
 (([run_duration] % 10000) / 100 * 60) +
  ([run_duration] % 10000) % 100)) * 1.0) / COUNT([job_id])
 FROM  [msdb].[dbo].[sysjobhistory] WITh(NOLOCK)
 WHERE  [step_id] = 0
 GROUP BY [job_id]
  ) AS [jobhistory]
  ON [jobhistory].[job_id] = [jobs].[job_id];
GO


Why my query does not return correct data (NORTHWIND)? select *
from orders o
WHERE EXISTS
(
  select 1
  from orders o2
  where o2.EmployeeID = o.EmployeeID
    AND YEAR(o2.OrderDate) = 1997
    and MONTH(o2.OrderDate) = 9
    AND DAY(o2.OrderDate) = 5
)

I expect to receive only two records in result instead of 228.
As you can see this is EXISTS on the same table.
Thank you in advance.

There are two orders with an orderdate=1997-09-05.  One for employee=4 and one for employee=7.  Now, for employee=4, there are 156 rows on the order table and for employee=7, there are 72 rows on the order table.  That totals to 228.  Your query is returning all orders where the employeeid matches those two id's.

Alter your query to include a check for o2.OrderDate=o.OrderDate

SELECT *
FROM orders o
WHERE EXISTS (
        SELECT 1
        FROM orders o2
        WHERE o2.EmployeeID = o.EmployeeID
            AND YEAR(o2.OrderDate) = 1997
            AND MONTH(o2.OrderDate) = 9
            AND DAY(o2.OrderDate) = 5
            AND o2.OrderDate = o.OrderDate
        )

Based on your example, you would get the same results by simplifying the query to:

select * from orders where orderdate = '1997-09-05'

Seems like you are using two tables based on this code: o2.EmployeeID = o.EmployeeID.

you need to use JOIN for you to be able to get the correct data.


Data Validation before entry into Couchbase server? Is it possible to validate the data before it is entered into couchbase? I did not find any documentation confirming this. But I believe there must be some way to do it.

You can validate the input by adding another entity "validate_doc_update" to the _design/ document.The validate_doc_update function gets executed for each document you want to create or update. If the validation function raises an exception, the update is denied; when it doesn’t, the updates are accepted.
It is optional i.e. if you do not write one, no checking will be done.

More details can be found on http://guide.couchdb.org/draft/validation.html

Couchbase will look at the data to make sure it is in a format it can store, but I doubtt that is what you mean by validation. If by data validation you mean that Couchbase will look at the data coming in and check it for a valid schema or something of that nature than no.


How do I list all tables in all schemas owned by the current user in Postgresql? I can list all tables in all schemas using

> \dt *.*

but that also lists system tables that greatly outnumber my tables that I care about. I'd like all the tables (and possibly views) created by me in the public schema and any schemas I've defined.

I'm hoping to find a way to do this without having to explicitly add schemas to the search path as I create them as described here:

https://stackoverflow.com/a/12902069

EDIT:

Based on the accepted answer, I've created the following View:

create view my_tables as
select table_catalog, table_schema, table_name, table_type
from information_schema.tables
where table_schema not in ('pg_catalog', 'information_schema');

And now the following command gives me what I wanted:

select * from my_tables;

This will list all tables the current user has access to, not only those that are owned by the current user:

select *
from information_schema.tables
where table_schema not in ('pg_catalog', 'information_schema')
and table_schema not like 'pg_toast%'

(I'm not entirely sure the not like 'pg_toast%' is actually needed though.)

I you really need the owner information, you probably need to use pg_class and related tables.

Edit: this is the query that includes the owner information:

select nsp.nspname as object_schema,
       cls.relname as object_name,
       rol.rolname as owner,
       case cls.relkind
         when 'r' then 'TABLE'
         when 'm' then 'MATERIALIZED_VIEW'
         when 'i' then 'INDEX'
         when 'S' then 'SEQUENCE'
         when 'v' then 'VIEW'
         when 'c' then 'TYPE'
         else cls.relkind::text
       end as object_type
from pg_class cls
  join pg_roles rol on rol.oid = cls.relowner
  join pg_namespace nsp on nsp.oid = cls.relnamespace
where nsp.nspname not in ('information_schema', 'pg_catalog')
  and nsp.nspname not like 'pg_toast%'
  and rol.rolname = current_user  --- remove this if you want to see all objects
order by nsp.nspname, cls.relname;

See this. All tables:

SELECT relname FROM pg_class WHERE relname !~ '^(pg_|sql_)' AND relkind = 'r';


The mysql Syntax for insert data is :

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

In your query you just missed values and you must pass where condition to select a specific row, in your case the last insert id:

 INSERT INTO report_backup(mid, account, dev, start, end, logged_user, added_time) SELECT mid, acc, device, start, end, login_user, date_added from main_tbl;

Correct one is :

INSERT INTO report_backup(mid, account, dev, start, end, logged_user, added_time) VALUES (SELECT mid, acc, device, start, end, login_user, date_added from main_tbl WHERE mid = 'Your Last Inserted Value');

Hope my answer is clear enough to fix your issue.

I used the same insert query 2 times on both tables.


Rebuild indexes on replication server I have 1 live server and 1 replication server. For reporting purpose we use the replication server and on the replication server we have created an index. So I want to know if I rebuild my index on replication server or do I have to remove the replication server first and then rebuild the index or can I directly rebuild the indexes on replication server?

According to official advice from Microsoft there is no need to remove the subscriber.

How do I rebuild indexes or tables in replicated databases?
There are a variety of mechanisms for rebuilding indexes. They can all be used with no special considerations for replication, with the following exception: primary keys are required on tables in transactional publications, so you cannot drop and recreate primary keys on these tables.
How do I add or change indexes on publication and subscription databases?
Indexes can be added at the Publisher or Subscribers with no special considerations for replication (be aware that indexes can affect performance). CREATE INDEX and ALTER INDEX are not replicated, so if you add or change an index at, for example, the Publisher, you must make the same addition or change at the Subscriber if you want it reflected there.

There is a potential, AFAIK, if you're using transactional replication that you will get a big queue of transactions that can't be applied to your subscriber (assuming that your publisher is quite busy otherwise you wouldn't be using replication). This may need to be managed, possibly by doing a REORGANIZE instead of a REBUILD or some such, but you can gauge that best for your situation.

Assuming that the data within the tables are changing, yes, you'll need to maintain the indexes on  each DB. You'll also need to set up maintenance for the statistics as well.


Shared Memory with Microsoft Hekaton Does the Microsoft SQL Server 2014 In-Memory Engine supports Shared Memory access like Oracle TimesTen? I only found that there was a protocol with this name in SqlServer2008  but i found nothing for SqlServer2014

thanks

Hekaton does not support direct memory access from client applications. In theory an application could access the same memory space that SQL Server uses using OS memory sections. That is not implemented, though.

The shared memory transport has nothing to do with this. Indeed, it uses a shared memory region to transfer data. But it still runs the normal TDS protocol (including the data conversion and serialization that this involves).

Hekaton is Microsoft's copy of the VoltDB architecture from Michael Stonebraker. It is a more radical concept than Oracle's In-Memory seems to be. It involves a sharded, shared-nothing architecture where stored prodcedures are isolated and run to completion without any of the buffering and other overhead associated with transaction management - or what Stonebraker calls OldSQL.

He argues that NewSQL is the way to go with the VoltDB framework for OLTP and columnar stores for OLAP - no need to get rid of SQL and no need to get rid of ACID. From what I can read here, Oracle appear to be taking a hybrid approach. I personally believe that they are wrong in this choice, but who am I to argue with Larry Ellison?


Good explanation of cascade (ON DELETE/UPDATE) behavior I don't design schemas everyday, but when I do, I try to setup cascade updates/deletes correctly to make administration easier. I understand how cascades work, but I can never remember which table is which.

For example, if I have two tables - Parent and Child - with a foreign key on Child that references Parent and has ON DELETE CASCADE, which records trigger a cascade and which records get deleted by the cascade? My first guess would be the Child records get deleted when Parent records are deleted, since Child records depend on Parent records, but the ON DELETE is ambiguous; it could mean delete the Parent record when the Child record is deleted, or it could mean delete the Child record when the Parent is deleted. So which is it?

I wish the syntax was ON PARENT DELETE, CASCADE, ON FOREIGN DELETE, CASCADE or something similar to remove the ambiguity. Does anyone have any mnemonics for remembering this?

If you like the Parent and Child terms and you feel they are easy to be remembered, you may like the translation of ON DELETE CASCADE to Leave No Orphans!

Which means that when a Parent row is deleted (killed), no orphan row should stay alive in the Child table. All children of the parent row are killed (deleted), too. If any of these children has grandchildren (in another table through another foreign key) and there is ON DELETE CASCADE defined, these should be killed, too (and all descendants, as long as there is a cascade effect defined.)

The FOREIGN KEY constraint itself could also be described as Allow No Orphans! (in the first place). No Child should ever be allowed (written) in the child table if it hasn't a Parent (a row in the parent table).

For consistency, the ON DELETE RESTRICT can be translated to the (less aggresive) You Can't Kill Parents! Only childless rows can be killed (deleted.)

well, perhaps we can rationalize the syntax. Let's take a Python example:

class Parent(self):
    # define parent's fields

class Child(self):
    # define child's fields
    parent_pk_is_childs_foreign_key = models.ForeignKey(Parent, on_delete=models.CASCADE)

what this line says is on_delete of the Parent (which is accidentally mentioned in the statement), please cascade the deletion onto the child. That is why the CASCADE statement is defined at the child level, it marks those children that need to be deleted

For example if you had another class

class GrownUpChild(self):
        # define grown up child's fields
        parent_pk_is_childs_foreign_key = models.ForeignKey(Parent, on_delete=models.DO_NOTHING)

this structure would clearly show which of the children need to be removed (Child) and which are to stay (GrownUpChild) albeit orphaned

[Edit: Given the context of the discussion, specifically in cases of on_delete=models.CASCADE etc, ] in fact it is often a desired behavior to leave children of a deleted parent, due to auditing and reporting reasons, as well as recovering accidental deletions. [ of course enterprise level software will be built around such behavior and will flag deleted records as deleted=1 instead of actually deleting them and also will not include them in any queries for the front end, minus some specially designed reports. In addition it will have a function of purging the deleted==1 records from the database, which will usually be executed by the UI administrator, often avoiding any involvement from the database administrator's side.]


Stopping N number of nodes of a Cassandra cluster running on a single machine using bash script I have set up a Multi-Node cassandra (3.11) cluster (with N nodes) in a single Ubuntu machine. I have written a bash script which

Creates N copies of conf folder
Configures the cassandra yaml file according to the need of N nodes (Such as separating the data and log directories)
Creates N copies of cassandra script inside the bin folder

It works correctly so far. But I am a little stuck after that.

I want to stop all the nodes effectively at the end of the script. Any help will be really appreciated at this point.

Thanks in advance!

I figured it out:

kill $(ps auwx | grep '[c]assandra' | awk '{print $2}')


Contact system database design I'm designing a database for contacts and I'm looking for some feedback on the current design.

What I want:

A contact can be a person or an organization.
A contact can have many groups.
A contact can have many emails and addresses
A contact can have one organization (employed)
An organization can have many contacts (employees)
A group can have many contacts.

What I have:

Contact(Id, PersonId (FK), OrganizationId (FK), EmployerId (FK, self reference))
Person(Id, FirstName, LastName, Birthday)
Organization(Id, Name, Founded)
Group(Id, Name)
ContactGroup(Id, ContactId (FK), GroupId (FK))
Email(Id, ContactId (FK), Name)
Address(Id, ContactId (FK), Street, City, ZipCode, Country)

What bothers me the most is that the contact entity has one organization or one person, and also a self reference (EmployerId) if an organization has employees (which are also contact entities) and vice versa.

Is this a problem or see you a better way?

Check the "Data Model Resource Book, Vol. 1". It covers standard schemata for - among other things - contact management. Pretty much the whole CRM and accounting etc. side.

And yes, there are a lot of problems with your approach - but I wont replicate 30 pages or so from that book describing all the issues.


How can I set up a read only odbc connection? I have a user that needs to do queries on an SQL Server 2008 database that I administrate. They have rights to edit the data, but I'd rather they weren't able to do it directly against the table (i.e., I'd prefer they'd use the application that they were given).

What I'd like to do is give them a Read Only ODBC connection, and hang an MS Access MDB/ACCDB off of it, and let them query to their heart's content without accidentally "updating" data.

Is there a way to do this, or am I stuck having to create a RO user for them to use, and then threaten them with much pain if they connect using their regular RW account? In which case why not threaten them with much pain for editing data outside of the application?

Create a user in SQL Server with only the perms you want them to have.
Set up the user ODBC string to use this credential
Profit :)

Create a user and give only read-permissions to him


xp_cmdshell only sends first character of string I am trying to use xp_cmdshell to launch a python script with a list as its input but for some reason its only passing the first character of the string to command line. The result I get from the code below is "'D' is not recognized as an internal or external command, operable program or batch file.". If I add the START command before the path it just replaces 'D' with 'S'. This particular row I'm trying to test with concatenates to 241 characters so I'm not bumping up against the varchar limits for xp_cmdshell. Using MSSQL 2008R2 on Windows Server 2008R2.

DECLARE
    @inspection varchar

SELECT
    @inspection = 'D:/Python27/ArcGISx6410.1/python.exe D:/GIS-DATA/Scripts/whydrant_update_01.py [''' + COALESCE(FACILITYID, '') + ''',''' + COALESCE(MAKER, '') + ''',''' + COALESCE(HYDRCOND, '') + ''',''' + COALESCE(BASECOL, '') + ''',''' + COALESCE(CAPCOL, '') + ''',''' + COALESCE(STEAMSIZE, '') + ''',''' + COALESCE(DISSIZE, '') + ''',''' + COALESCE(CAST(FLOW AS varchar), '') + ''',''' + COALESCE(CAST(PRESSURE AS varchar), '') + ''',''' + COALESCE(VALVELOC, '') + ''',''' + COALESCE(ACTIVE, '') + ''',''' + COALESCE(OPERABLE, '') + ''',''' + COALESCE(OPERHARD, '') + ''',''' + COALESCE(CORRODED, '') + ''',''' + COALESCE(CLEARING, '') + ''',''' + COALESCE(PAINTING, '') + ''',''' + COALESCE(MARKER, '') + ''',''' + COALESCE(NOTE, '') + ''',''' + COALESCE(CAST(created_date AS varchar), '') + ''',''' + COALESCE(USER_, '') + ''']'
FROM sde.sde.a1288
WHERE FACILITYID = '2294'

EXEC xp_cmdshell @inspection

The problem is with your variable declaration varchar.

The command_string is   varchar(8000) or nvarchar(4000)

so define your variable along with proper length  to avoid string truncation

  DECLARE
      @inspection varchar

should be

  DECLARE
      @inspection varchar(8000)

couple of things as well (since I dont know Python).

Make sure you print or select @inspection and see that you can run the produced command successfully.
Use a variable for success or failure and raise an error message when the command fails.

e.g.

declare @result int
DECLARE @inspection varchar(8000)
exec @result = master..xp_cmdshell @inspection
if @result <> 0
begin
    print 'Python script errored out !!'
    RAISERROR ('Python script errored out', 16, 1)
end

I don't see the correct declaration of the variable type.

DECLARE @inspection VARCHAR(8000);


Can I move rows between partitions by updating the partition key? I would think that this would be a fairly simply question, but I've actually had a difficult time finding an answer for this.

The question:
Can you move rows of data within a partitioned table from one partition to another by simply updating the partition column so that it crosses the partition boundary?

For example, if I have a table that has a partition key:

CREATE TABLE SampleTable
(
    SampleID INT PRIMARY KEY,
    SampleResults VARCHAR(100) NOT NULL,
)

With the partition function that maps to the primary key:

CREATE PARTITION FUNCTION MyPartitionFunc (INT) AS
RANGE LEFT FOR VALUES (10000, 20000);

Can I move a row from the first partition to the third partition by changing the SampleID from 1 to (say) 500,000?

Note: I'm tagging this as both sql server 2005 and 2008, since they both support partitioning.  Do they handle it differently?

I do not have a 2005 server to test with. 2008 however, appears to handle this as expected:

USE [Test]
GO
CREATE TABLE [IDRanges](
    [ID] [int] NOT NULL
)
GO

CREATE PARTITION FUNCTION IDRange1 (int)
AS RANGE LEFT FOR VALUES (10) ;
GO
--Add one record to each partition
INSERT INTO IDRanges ([ID]) VALUES (17)
INSERT INTO IDRanges ([ID]) VALUES (7)
GO
--Verify records in partition
SELECT $PARTITION.IDRange1([ID]) AS Partition, COUNT(*) AS [COUNT]
FROM IDRanges
GROUP BY $PARTITION.IDRange1([ID])
ORDER BY Partition ;
GO
--Move row between partitions
UPDATE IDRanges
SET [ID] = 8 WHERE [ID] = 17
GO
--Verify records in partition
SELECT $PARTITION.IDRange1([ID]) AS Partition, COUNT(*) AS [COUNT]
FROM IDRanges
GROUP BY $PARTITION.IDRange1([ID])
ORDER BY Partition ;

You should see one record in each partition before the update, and both records in the first partition afterwards.

I don't think that answer is correct. When you use the value

 $PARTITION.IDRange1([ID]) AS Partition

you are simply recalculating what the partition should be, not where the record currently is.

You should use:

select * from sys.partitions where object_id = object_id('IDRanges')

In my tests on sql 2005 the value changes but the record stays in the same partition. This will probably mess with stats and the optimizer as it will run in a multi threaded mode expecting a partition to be in a specific range. It will also be completely wrong when it tries to use partition elimination to only query the relevant partition. I think you need to delete and re-insert each record to get them to move.


Connect to MySQL (WAMP) via SSH Hi I installed the latest WAMP on Windows 7. I would like connect via SSH to MySQL database and create dump.

As SSH client I use PUTTY.

I tried using the root account and also other accounts, but I get this error:

  server unexpectedly closed network connection

Then I tried using another SSH client Private Shell http://www.privateshell.com/

I get this error:

  other side does not support SSH protocol on this port

For connection I use these settings:

IP: localhost
PORT: 3306

MySQL version is 5.5.16 I am not sure if support SSH connection.

Is it needed to allow SSH in phpAdmin or what is the root of problem?

Is there a SSH server on your machine?? If not you may install copssh - OpenSSH for Windows or similar and then try to connect.

Much simpler way to go about this.

Copy cmd.exe from your system32 folder, copy to: C:\wamp\bin\mysql\MYSQL_VERSION\bin

Open cmd, run the following command:

mysqldump.exe -u USERNAME -pPASSWORD DB_NAME > "DIRECTORY_TO_DUMP.sql" --lock-tables=false

Replace MYSQL_VERSION, USERNAME, PASSWORD, DB_NAME, DIRECTORY_TO_DUMP with appropriate values.

Using the various executables, you can do pretty much anything.


mongodb Date strings and other issues I asked a question on Stackoverflow, and was told that my schema was way off. I am using mongodb to store whether or not a log file exists. The log file contains info for an hour. I'm not actually storing the file, just the file's name and the hour it represents. I'm also determining the existence of data for a given hour if that file exists in my db. I don't deny my document structure is bad, but:

What is a better way to do the schema I have (below)? Each file represents an hour. The DB just has to know that a file exists for a specific day and hour. My webapp pulls this info to display available days and hours for the user.
Why is my schema so bad?

Current document structure:

{
    _id   : "2014-02-13",
    hours : [
              { hour: "00", file: [
                     {name:"something.csv", date_added : ISODate("2014-04-04T08:59:34.268Z")},
                     {...},
                     {...}]
              },
              { hour: "01", file: [{},{}]},
              { hour: "04", file: [{},{}]} // some hours can be missing
            ]
}

Is very bad for lots of registers... if you need accessing a file, need typing db.collection.find({"hours.hour":"05", "hours.file.name":"name"}). And you hours arrays will grow  very fast...

You can having a struct like this,

{
    _id   : {date:"2014-02-13",hour: "00"}, files: [
                     {name:"something.csv", date_added : ISODate("2014-04-04T08:59:34.268Z")},
                     {...},
                     {...}]
 }

Having a documents with a array having little data and where you find very fast...


Is Order part of a result set? This question got me thinking:
ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) doesn't use index on (A,B,C)
In it the questioner states two functions give the same result set, however they don't in that they have identical rows but they are always in different (and consistent) orders.

Is the order of a result set part of the result set? Wikipedia implies no (but doesn't strongly determine an answer, nor does it speak from a position of authority):

https://en.wikipedia.org/wiki/Result_set

Is

Sector  Name
A   Alpha
B   Beta

the same result set as

Sector  Name
B   Beta
A   Alpha

I personally only work with SQL Server, does the answer change by platform? Is the answer a matter of opinion only? (If so please give answers of fact only, or of consensus by professional bodies at any rate- not your personal opinion please)

Possibly the ANSI 92 definition held at

http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

gives the answer. Section 3.1 Definitions seems to draw a distinction between the idea of a result set and a result sequence. The distinction was not in my awareness when I wrote the question using the former phrase

  v) sequence: An ordered collection of objects that are not necessarily
  distinct.

  w) set: An unordered collection of distinct objects. The collection
  may be empty.

The term "result set" [sic] does not appear in the 1992 SQL standard. It's typically used to mean the result of a select statement. Which is a partially ordered sequence of rows from a table:

  20.2
  Format
  < direct select statement: multiple rows >
  General Rules
  5) If an < order by clause > is specified, then the ordering of rows of the result is effectively determined by the < order by clause > as follows:

From the 2003 SQL standard:

  3.1.6.30 result set: A sequence of rows specified by a < cursor specification >, brought into existence by opening a cursor and ranged over by that cursor, and having operational properties of sensitivity, scrollability, holdability, and returnability.

  3.1.6.32 returned result set: A result set created during execution of an SQL-invoked procedure and not destroyed when that execution terminates. Such a result set can be accessed by using a cursor other than the one that brought it into existence (a received cursor).

  14.3
  Format
  < cursor specification > ::= < query expression > [ < order by clause > ] [ < updatability clause > ]

  22.2
  Function
  Specify a statement to retrieve multiple rows from a specified table.
  Format
  < direct select statement: multiple rows > ::= < cursor specification >
  Syntax Rules
  2) The < cursor specification > shall not contain an < updatability clause >.
  General Rules
  1) Let Q be the result of the < cursor specification >.
  2) Case:
  a) If Q is empty, then a completion condition is raised: no data.
  b) Otherwise, Q is not empty and Q is returned. The method of returning Q is implementation-defined.

(Clearly the result of a SELECT statement is ordered because that is what its ORDER BY does.)

Tables are unordered. But a result set [sic] is not a table.


Is there a way to include an assertion about the expected number of rows? I have a function that abstracts updating event data:

async (
  connection: DatabaseConnectionType,
  id: number,
  venueId: number,
  movieId: number,
  cinemaMovieNameId: number,
  date: string,
  time: string,
  url: string | null,
  seatingIsAllocated: boolean | null,
  reservationFee: number | null,
  reservationPerTicketFee: number | null,
  guide: $PropertyType<EventResultType, 'guide'> | null
): Promise<number> => {
  const timezone = await connection.maybeOneFirst(sql`
    SELECT
      locality.timezone
    FROM venue
    INNER JOIN locality
    ON locality.id = venue.locality_id
    WHERE venue.id = ${venueId}
  `);

  if (!timezone) {
    log.error({
      venueId
    }, 'timezone not found');

    throw new Error('Timezone not found.');
  }

  // @todo Prevent unncessary updates. See updateVenue and updateTicket.
  await connection
    .query(sql`
      UPDATE
        event
      SET
        venue_id = ${venueId},
        movie_id = ${movieId},
        cinema_movie_name_id = ${cinemaMovieNameId},
        starts_at = ${date + ' ' + time}::timestamp AT TIME ZONE (${timezone}),
        url = ${url},
        seating_is_allocated = ${seatingIsAllocated},
        reservation_fee = ${reservationFee},
        reservation_per_ticket_fee = ${reservationPerTicketFee},
        guide = ${guide ? JSON.stringify(guide) : null}
      WHERE
        id = ${id}
    `);

  return id;
};

It seems redundant separate locality.timezone lookup query from the UPDATE query. However, combining these two queries requires asserting within a query that timezone exists.

Is there a syntax to assert how many results a query returns?

Something that would allow me to rewrite the above into ~:

await connection
  .query(sql`
    WITH timezone AS (
      SELECT
        locality.timezone
      FROM venue
      INNER JOIN locality
      ON locality.id = venue.locality_id
      WHERE venue.id = ${venueId}
      -- Made up syntax
      ASSERT ROW COUNT 1
    )
    UPDATE
      event
    SET
      venue_id = ${venueId},
      movie_id = ${movieId},
      cinema_movie_name_id = ${cinemaMovieNameId},
      starts_at = ${date + ' ' + time}::timestamp AT TIME ZONE ((SELECT timezone FROM timezone)),
      url = ${url},
      seating_is_allocated = ${seatingIsAllocated},
      reservation_fee = ${reservationFee},
      reservation_per_ticket_fee = ${reservationPerTicketFee},
      guide = ${guide ? JSON.stringify(guide) : null}
    WHERE
      id = ${id}
  `);

It is not an assert, but you can just have the query return the empty string (rather than NULL) when there is no match:

... AT TIME ZONE ((SELECT timezone FROM timezone union select '' limit 1))

when invoked with empty timezone query gives:

ERROR:  time zone "" not recognized

starts_at = ${date + ' ' + time}::timestamp AT TIME ZONE (${timezone}),

Oh my, that's not safe nor optimal. That's a really bad idea. First you don't know that date + ' ' + time isn't a SQL injection attack. You're simply not safely quoting those queries.

Not to mention the AT TIME ZONE is super sketch

starts_at = ${date + ' ' + time}::timestamp AT TIME ZONE (${timezone}),

This is setting starts_at with a timestamp without time zone as that's what AT TIME ZONE is returning. This is not what you want!

What you want is for all of your timestamps to be stored as timestamp with time zone. Your library should set the client_timestamp to UTC and pull them out in that format returning a JavaScript Date object. That can be used as is, or you can use a Moment.js to manipulate it from the client.

tldr: retrieve and set time stamps with UTC when possible. Don't rely on PostgreSQL to convert things to an arbitrary ${timezone} because the logic will be ugly. Instead use Moment.js.


How do I deal with items that I need to repeat monthly? I have a table where users will enter transactions on a weekly basis, these transactions will either be single items or will be recurring i.e. rent would be recurring and should be the same for every month. How should I store this recurring item?

We thought just add in 12 new entries so it atleast covers the year (The users will want to look a few months into the future) but then the problem is that if the cost changes, we have to update all following entries

Our other option was to create another table which would store the key of the original with the dates or day of the month that it needs to repeat on?

Is there a better solution than these?

You shouldn't try to do too many things with one data store.

If you have a table for transactions, then just keep transactions in that table.  Don't mix in scheduled future transactions with current transactions.  Actual (historical) transactions will have different attributes (columns) than future, scheduled transactions.

Keep the recurring/scheduled transactions (like rent) in a separate table.  When the rent is paid, create a new record in the (actual) transaction table.

The scheduled transaction table could have a totally different structure.  Instead of keeping a record for each expected transaction, you could keep one record with a date range and recurrence fields.  This would save having to update multiple records when the rent price changes, for example.  Consider something like this for your scheduled/recurring transaction table:

Scheduled_Transaction:=

CustomerID
StartDate
EndDate
Recurrence (monthly, weekly, every two weeks, - whatever you need)
Amount

According to the Concept of Normalisation there should be no Redundant set of records


How To Export-Import Database in Oracle 11g(Application Express Edition)? I am beginner in Oracle 11g. I have created a database in oracle 11g(Application Express).

I want to export this database with all data(whole workspace) and import this to another computer where I already have installed Oracle.Is there any way to export-import the whole workspace in oracle 11g application express?

You have lots of different options for doing this:

http://docs.oracle.com/cd/B25329_01/doc/admin.102/b25107/impexp.htm

I would recommend using Oracle Datapump as this is the most powerful solution, and would represent the best opportunity for instruction on how this is done in an enterprise environment.

exp username/password@database name file=path (where you want to store the dump file)
exp username/password@database name file=path (where you want to store the dump file )statics=none


A Remote GUI for PostgreSQL I've installed PostgreSQL 12 server onto an Ubuntu 20.04 server, but I want to access it using a GUI from my Kubuntu 20.04 laptop. I'd be interested to know any options for GUI administration.

I have an old Access database that I used mdbtools to export all tables to csv files. I'm wanting to import those table into PostgreSQL, but I'd really like something nicer than the command line.

One option I know about is pgadmin, however, it isn't clear to me where I should install that. Do I install it on the server or my laptop? I see that it is a web interface, I don't want to run a web server on my laptop just to have a GUI for Postgres located on a server. I'd like some direction from someone who uses a GUI to administer PostgreSQL remotely.

I've used Access, DB2, SQL Server, MySQL, MarinaDB, and Pervasive, but this is my first time messing with Postgres. Hopefully, I've saved the best for last. Please advise.

Use pgadmin (Open source)
https://www.pgadmin.org/

It has the same comofort as phpmyadmin, mysql  workbench ssms, and with some kings.

Workbench can connect to Postgres Server
Ref :
https://dev.mysql.com/doc/workbench/en/wb-migration-database-postgresql-drivers-linux.html
https://phoenixnap.com/kb/postgresql-workbench
https://data36.com/install-sql-workbench-postgresql/
Table Data Export and Import Wizard is available in Workbench using csv
https://dev.mysql.com/doc/workbench/en/wb-admin-export-import-table.html


Need SQL outer join help with 3 tables See below example. ID_1 and ID_A are primary keys for table_1 and table_3 respectively. Table_2, the primary key is ID_1 and ID_A. ID_A is joined to one and only one ID_1 via table_2 due to business rules.

I need a query that will return all rows from table_1 where the value_1 = 11 and joined to that, I need only the rows from table_3 that have a value_a = 'a' if it joins to a row in table_1 that has a value_1 = 11.

Please see results required. Note there is no row

|1 | a |

in the result set because value_a for id_a = 'b';

I've tried a left outer join but all ID_1 are returned. I've also tried CTE, to no avail. Here's one SQL, I tried.

Select ID_1, Value_1, ID_A, VALUE_A
   From TABLE_1 t1
   Join TABLE_2 t2 on t1.ID_1 = t2.ID_1
   Left Outer Join TABLE_3 t3 on t2.ID_A = t3.ID_A;

I've also tried using a temporary table which include only the results from TABLE_1 that I need, but the left outer join using the temp table still returns the above mentioned row.

The DB is Oracle, but I prefer a standard solution. Also, performance is a consideration, given that TABLE_1 has over 200 million rows and TABLE_3 has about 4 times that many.

TABLE_1

| ID_1 | Value_1  |
-------------------
| 1    | 11       |
| 2    | 12       |
| 3    | 11       |
| 4    | 13       |

TABLE_2

| ID_1 | ID_A  |
----------------
| 1    | a     |
| 1    | b     |
| 3    | c     |
| 4    | d     |

TABLE_3

| ID_A | Value_A |
------------------
| a    | b       |
| b    | a       |
| c    | e       |
| d    | a       |

Results Required

| ID_1 | ID_A    |  Value_1  | Value_A |
----------------------------------------
| 1    | b       |    11     |    a    |
| 3    | <NULL>  |    11     | <NULL>  |

First, join TABLE_2 and TABLE_3 using an inner join and additionally filtering on Value_A = 'a':

SELECT
  t2.ID_1,
  t3.ID_A,
  t3.Value_A
FROM
  TABLE_2 t2
  INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A
WHERE
  t3.Value_A = 'a'

This will give you the following result set:

ID_1  ID_A  Value_A
----  ----  -------
1     b     a
4     d     a

Now use the above as a derived table and join it, using an outer join this time, to TABLE_1, additionally filtering the results on Value_1 = 11:

SELECT
  t1 .ID_1,
  t23.ID_A,
  t1 .Value_1,
  t23.Value_A
FROM
  TABLE_1 t1
  LEFT JOIN
  (
    SELECT
      t2.ID_1,
      t3.ID_A,
      t3.Value_A
    FROM
      TABLE_2 t2
      INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A
    WHERE
      t3.Value_A = 'a'
  ) t23 ON t1.ID_1 = t23.ID_1
WHERE
  t1.Value_1 = 11
;

That will give you the output you want:

ID_1  ID_A  Value_1  Value_A
----  ----  -------  -------
1     b     11       a
3     NULL  11       NULL

However, nesting a query is not the only way to solve your problem – you can also use a nested join, which is much more concise:

SELECT
  t1.ID_1,
  t3.ID_A,
  t1.Value_1,
  t3.Value_A
FROM
  TABLE_1 t1
  LEFT JOIN
    TABLE_2 t2
    INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A AND t3.Value_A = 'a'
  ON t1.ID_1 = t2.ID_1
WHERE
  t1.Value_1 = 11
;

The last query implements exactly the same logic as the previous query: first, tables TABLE_2 and TABLE_3 are joined and the result is filtered, then it is joined to TABLE_1 and the final set is filtered again. Some people also add brackets around a nested join:

FROM
  TABLE_1 t1
  LEFT JOIN
  (
    TABLE_2 t2
    INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A AND t3.Value_A = 'a'
  )
  ON t1.ID_1 = t2.ID_1

to make it clearer (perhaps both for themselves and for future maintainers) that the nested join takes place before the outer-level one, logically, although the syntax is unambiguous enough without them.

Nevertheless, many people find it confusing even with brackets, and if you find yourself struggling with it as well, there is another option – right outer join:

SELECT
  t1.ID_1,
  t3.ID_A,
  t1.Value_1,
  t3.Value_A
FROM
  TABLE_2 t2
  INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A AND t3.Value_A = 'a'
  RIGHT JOIN TABLE_1 t1 ON t1.ID_1 = t2.ID_1
WHERE
  t1.Value_1 = 11
;

Again, the logical sequence of events specified by the FROM clause repeats that of the derived table solution: a join between TABLE_2 and TABLE_3, filtered, is followed by a join with TABLE_1. The different syntax does not alter the outcome and the results produced still match your requirements.

Your SQL is not referencing Alias correctly. If you're doing a JOIN you should reference the source table in your query for OUR sake.

SELECT ID_1, Value_1, ID_A, VALUE_A
   FROM TABLE_1 t1
   INNER JOIN TABLE_2 t2 ON t1.ID_1 = t2.ID_1
   INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A AND t3.Value_A = 'a'
   WHERE t1.Value_1 = 11 ;


How to remove identity specification for multiple tables I have 109 tables in my database. Now I am migrating to Amazon server with identity insert and I want to remove auto increment column specification for all tables. How set auto increment column i.e. Identity specification to "no" in SQL Server 2008 for all tables?

For Example, I have

CREATE TABLE dbo.tbl_acc
    (
    a1     NUMERIC (18) IDENTITY NOT NULL,
    a2     NUMERIC (18) NULL,
    a3     VARCHAR (4000) NULL,
    a4     VARCHAR (3999) NULL,

    CONSTRAINT PK_tbl_acc PRIMARY KEY (a1)
    )
GO

And now I would like to remove IDENTITY, not only for this but for all remaining tables.

There is no built-in command to remove the identity property from a column in SQL Server. You may have to create a new table, copy all the data across, and then rename the table. This can be time-consuming and awkward, especially if you have things like foreign key constraints, schema-bound functions, indexed views and so on.

That said, there is a way to avoid copying the data at least, but again this will not work in all scenarios, particularly if you have foreign keys. Example follows:

Example table and data

CREATE TABLE dbo.tbl_acc
    (
    a1     NUMERIC (18) IDENTITY NOT NULL,
    a2     NUMERIC (18) NULL,
    a3     VARCHAR (4000) NULL,
    a4     VARCHAR (3999) NULL,

    CONSTRAINT PK_tbl_acc PRIMARY KEY (a1)
    )
GO
INSERT dbo.tbl_acc
    (a2, a3, a4)
VALUES
    (1, 'a', 'b'),
    (2, 'c', 'd');

Meta data transfer and rename

-- Copy of the source table specification,
-- but without the IDENTITY property
-- Note the PRIMARY KEY constraint name
-- also has to be different
CREATE TABLE dbo.tbl_acc2
    (
    a1     NUMERIC (18) NOT NULL,
    a2     NUMERIC (18) NULL,
    a3     VARCHAR (4000) NULL,
    a4     VARCHAR (3999) NULL,

    CONSTRAINT PK_tbl_acc2 PRIMARY KEY (a1)
    )
GO
BEGIN TRANSACTION
    -- Transfer the data as a metadata operation
    -- TODO: Add error handling
    ALTER TABLE dbo.tbl_acc
    SWITCH TO dbo.tbl_acc2;

    -- Drop the source table (now empty)
    DROP TABLE dbo.tbl_acc;

    -- Rename the table
    EXECUTE sys.sp_rename
        @objname = N'dbo.tbl_acc2',
        @newname = N'tbl_acc',
        @objtype = 'OBJECT';

    -- Rename the primary key
    EXECUTE sys.sp_rename
        @objname = N'dbo.tbl_acc.PK_tbl_acc2',
        @newname = N'PK_tbl_acc',
        @objtype = 'INDEX';
COMMIT TRANSACTION;

Set identity_insert ABC ON

Insert into table ABC ( ... )
Values (....)

The code above will make sure data can be force fed in to identity column even if the identity insert is set ON.


Select repeating number x times and then increment I need a select query that can generate z amount of rows starting from 1, repeating x times and then increment to the next number.

For example 10 rows repeating 4 times would result in the following:

| # |
|---|
| 1 |
| 1 |
| 1 |
| 1 |
| 2 |
| 2 |
| 2 |
| 2 |
| 3 |
| 3 |

One solution using simple integer arithmetic on a table of numbers. This example uses an efficient way to generate a sequence on the fly, in practice you would probably have a permanent Numbers table.

DECLARE
    @z bigint = 10,
    @x integer = 4;

-- Itzik Ben-Gan's row generator
WITH
  L0   AS (SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS n FROM L5)
-- Main logic
SELECT TOP (@z)
    # = ((N.n - 1) / @x) + 1
FROM Nums AS N
ORDER BY N.n;

Output:

╔═══╗
║ # ║
╠═══╣
║ 1 ║
║ 1 ║
║ 1 ║
║ 1 ║
║ 2 ║
║ 2 ║
║ 2 ║
║ 2 ║
║ 3 ║
║ 3 ║
╚═══╝

Execution plan:

DECLARE @z INT=10, @x INT=4;
WITH q AS (SELECT 0 AS n UNION ALL SELECT n+1 FROM q)
SELECT TOP(@z) n/@x+1 FROM q OPTION(MAXRECURSION 0);


SQL Server unusual behavior on LOB insertion We are facing unusual behavior on a SQL Server 2008 R2 instance. We have a small .NET application, which we use to insert images and pdf files inside database as VARBINARY. Sometimes, we are facing query timeouts when the file size is bit large, sometimes larger than 3 MB, and sometimes this behavior is observed with 4 to 5 MB files.

IIS is installed on a separate machine.

Through trace we have found that no such insertion query is received on the SQL Server end during save image process from application.

This could be an application server issue, but it's the fifth time that we have resolved this issue by restarting SQL Server services. Why so?

During this problem rest of discrete data operations and small LOB insertion queries work fine.

Version: SQL Server 2008 R2 Service Pack 3
Edition: Standard
Windows Server 2008 R2 Enterprise (SP1)
Intel(R) Xeon(R) CPU E5-2630 v2 @2.60Ghz (2 Processors)
Memory 36GB to SQL Server (out of total 64GB)

(We have requested IT to assign at least 20GB more to SQL Server as it's a dedicated DB instance.)

Optimize for adhoc option is set to 1. Adhoc query plans size is about 18% of total cached plans. We are going to clean these adhoc plans periodically using Kimberly's script. I have verified that things are normal on wait stats and performance counters side, and nothing received on Trace when query is submitted from app. LOB ndf file is on local drives.

Current workaround
Thanks Kin for this idea. This time, when we faced the same issue, we just refreshed the system cache by executing the following DBCC statement, and issue is resolved:

DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL

We are still in search of permanent solution as periodically clearing system cache is not possible for some of workloads (specially 24x7 cases).

If your SQL server trace is not showing anything, it is most likely on the application. Even if it is resolved by restarting the SQL service. I have faced a very similar issue in the past and the connection for the upload process was the culprit. Restarting the SQL service basically gives you another attempt at reconnecting. Since it works some of the time, this second attempt may be all that you need to get it going.

Look into how you build your connection for large files. Limit the time it takes to send the query to the server, and when you get past it, mimic the service restarting by trying it again.

Where there are WAN links between application server and SQL Server, possible packet-filtering operation of some net security device/layer.

Where periodic large file packets (image: streaming burst, relative to average insert activity/frequency, and some net-sec action triggered), might trigger resets (like DOS attack counteraction); might get connection timeouts for all retries, and stop on retries; connection pooling config's / contention; rare orphaned tcp connection or "half_opened" tcp, from the initial SQL Server connection event and SQL Server still "sees it" idle, but the connection on the far side is already gone.

Somewhere, somehow if these critters are preventing a proper connection back to the database engine, a SQL Server service restart will clear out all of the goofiness, and then connection pools resume making complete tcp, etc. and happiness returns.

If case same domain, still possible per network, configs, etc. and enterprise antivirus loving to do deep-dives (heuristics?) with streams of "non-ascii" data packets, latency loads the transmission, and 'smart' devices may be doing the offload stuff, and one side of tcp is faked out, and left half open.

Bounce SQL Server and same request goes through as expected - suspect rats on the wire!

select * from sys.dm_exec_connections
where net_transport ='tcp'
and local_net_address !='127.0.0.1'
and parent_connection_id is not null;

Above DMV misses orphans (rare). With SQL trace, no connection trapped since never fully established. Maybe see related extended events, diagnostic logs, if you have time and are allowed. Easy to miss since default trace trap missed in between poll interval.

Drop into DOS on the SQL side and web side, and try netstat -p tcp etc. and put into a cmd file loop with now()s at each start, >> file, and you might catch incomplete tcp builds from your web app side.

If heavy activity, large scale ops, you might get lucky, but more often can be wasting time. Only helps if you get a catch, and present to LAN/WAN group as a 'concern'.

PIX firewalls w/ packet-filtering enabled? Question to ask WAN person, even if you just add the fact to your server book. Always start off by saying "Just curious..."


mongodb Query per sec troubleshooting I manage a MongoDB replica set for one of my customer and sometimes he send me a ticket (email) with only one argument :

~ WARNING Date/Time: ~Tue Jul 4 02:39:33 GMT 2017
Summary: ~WARNING - Queries / Sec: 9.674419

How understand this kind of message? I could guess that we have some slow query running. So the first thing I do it's to check the log. What i found in it's that we have some aggregate command that take 500-600ms to execute. Here an example :

2017-07-04T04:08:52.205Z I COMMAND  [conn2104435]
command databaseName.cards_PWCD900004
command: aggregate { aggregate: "cards_PWCD900004",
pipeline: [ { $unwind: "$virtualCards" },
{ $project: { cardProfileId: 1, virtualCards.state: 1,
virtualCards.device: 1,
virtualCards.primaryDpanId: { $ifNull: [ "$virtualCards.primaryDpanId", null ] } } },
{ $group: { _id: { cardProductId: "$cardProfileId",
virtualCardsState: "$virtualCards.state",
deviceId: "$virtualCards.device" }, tokensNumber: { $sum: 1 },
virtualCardsNumber: { $sum: { $cond: { if: { $eq: [ "$virtualCards.primaryDpanId", null ] },
then: 1, else: 0 } } } } }, { $group: { _id: { issuerName: "APLUS",
cardProductId: "$_id.cardProductId",
virtualCardsState: "$_id.virtualCardsState" },
devicesNumber: { $sum: 1 }, virtualCardsNumber: { $sum: "$virtualCardsNumber" },
tokensNumber: { $sum: "$tokensNumber" } } } ] }
keyUpdates:0 writeConflicts:0 numYields:219 reslen:30763
locks:{ Global: { acquireCount: { r: 460 } },
Database: { acquireCount: { r: 230 } },
Collection: { acquireCount: { r: 230 } } } protocol:op_query 523ms

What I would like to know is how troubleshoot such thing? I could launch a mongotop or mongostat but if the issue just finished I will see nothing. I also could use db.serverStatus() but I don't really know which variables is the most significant.

database size : 2GB
SSL : true

About mongo.conf all it's by default. Here more information about hardware:

RAM : 8GB
2 proc : 2,3 GHz each

What are best practices an administrator should have to check if all works on his database ?

If you have any question don't hesitate please.

Lets first look at few of the output terms to understand better.

numYields

The number of times the operation yielded to allow other operations to complete. Typically, operations yield when they need access to data that MongoDB has not yet fully read into memory. This allows other operations that have data in memory to complete while MongoDB reads in data for the yielding operation.
Does a read or write operation ever yield the lock?

reslen

The length in bytes of the operation’s result document. A large responseLength can affect performance. To limit the size of the result document for a query operation, you can use any of the following:

Projections
The limit() method
The batchSize() method

acquireCount

Number of times the operation acquired the lock in the specified mode.

How do you troubleshoot this thing?

I am taking a guess here as I cannot see your query plan. I suggest using explain method and looking at the plan. Especially focusing on index usage, scan/seek.

See this answer about high number of yields.

Monitor your instance for some key matrix which will help you tune your long running queries.  In your case I would especially focus on index misses.

mongotop - shows how much time was spend reading or writing each
collection over the last second
mongostat - brilliant live debug tool, gives a view on all your
connected MongoDB instances

What I did was to run db.serverStatus() and find the most significant variables, in your words.

> db.serverStatus( { metrics: 0, locks: 0, tcmalloc: 0, opcounters: 1 } )

I found opcounters to be the clarifying key.

My server was getting overloaded with a fresh started instance and got this result: (this results are 2 minutes after boot in a dev env)

"opcounters" : {
  "insert" : 20,
  "query" : 14761124,
  "update" : 1727,
  "delete" : 2,
  "getmore" : 0,
  "command" : 24528
}

This is an app with low traffic and virtually no use, it seems odd that there are over 14 mill. queries in little time.

That let me diagnose that some module had a while(true) loop querying the database and deal with it.

The reason I got to this question, was because I was monitoring my server (htop) and some module had 60% of the processor busy and mongod had another 30%, constantly. With that data I searched among the code and found that a config file was incorrect, making the default value for the delay between queries to fallback to 0. That 0 is virtually instantly querying every data in the database, infinitely. Hence overloading the db and server.

The thing is that you can troubleshoot mongo by reading into the variables and find the one that fits best.

For your req/s problem, you could write a script that queries the server every second, summing up the opcounters insert, query, update and delete and just counting. Check this answer on how to do that.

Reading through the variables I couldn't find one that debugs slow requests.


Hibernate Query Running Slow I am currently facing a problem with a slow running Hibernate query that is being kicked off from a Java program. It is making a call to Oracle 11g at the back end.

This query is taking anything from 40-90 seconds the first time it is run. On subsequent executions however the query is returned in a fraction of the time (and I don't even see the database being hit, so assume that hibernate is caching it).

If I copy and paste the query from Enterprise Manager into a SQL client and run the very same query directly (even changing a few parameters) the query returns in a fraction of a second.

If I look at the performance tuning tab in EM I see that the time taken is primarily taken up with User I/O Waits (97.5%), and CPU (2.5%). Could this mean that the fetch size I am using in hibernate is configured at too small a value?

If there is any other information you might need to help me get to the bottom of this issue then please let me know.

=====

Additional information:

We do have an index on the table and I can see that it is being used as part of the execution of the query, unfortunately it isn't very readable but I am not sure how else to include it:

Id  Operation   Name    Rows
(Estim)     Cost    Time
Active(s)   Start
Active  Execs   Rows
(Actual)    Read
Reqs    Read
Bytes   Mem
(Max)   Activity
(%)     Activity Detail
(# samples)
0   SELECT STATEMENT                        1
1   . FILTER                        1
2   .. HASH JOIN RIGHT OUTER        2674    7223    1   +4  1   0           1M
3   ... TABLE ACCESS FULL   TOTEM_EQ_EXPIRYCODES    475     4   1   +4  1   481
4   ... HASH JOIN RIGHT OUTER       2674    7219    1   +4  1   0           399K
5   .... TABLE ACCESS BY INDEX ROWID    TOTEM_EQ_UNDERLYINGS    1   2   1   +4  1   1
6   ..... INDEX UNIQUE SCAN     TOTEM_EQ_UND_PK     1   1   1   +4  1   1
7   .... NESTED LOOPS                       1
8   ..... NESTED LOOPS      2674    7216    42  +4  1   0
9   ...... TABLE ACCESS BY GLOBAL INDEX ROWID   EQUITIES_MONTHLY_INSTRUMENTS    2671    1871    45  +1  1   8438    3517    27MB        24.44   db file sequential read (11)
10  ....... INDEX RANGE SCAN    EQ_MON_INS_UNDERLYING_INDX  2671    12  42  +4  1   8438    27  216KB
11  ...... PARTITION RANGE ITERATOR         1   2           8438
12  ....... INDEX RANGE SCAN    EQ_MON_RESULT_INSPT_UNQ     1   2   44  +2  8438    0   5403    42MB        75.56   Cpu (1)
db file sequential read (33)
13  ..... TABLE ACCESS BY LOCAL INDEX ROWID     EQUITIES_MONTHLY_RESULTS    1   3

Here are the Global stats:

Elapsed
Time(s)     Cpu
Time(s)     IO
Waits(s)    Fetch
Calls   Buffer
Gets    Read
Reqs    Read
Bytes
45  0.73    45  1   22980   9740    76MB

It turns out that the problem was to do with the index that was being selected as part of the query plan.

The query was accepting a range as part of its set of parameters. For reasons I won't go into now we were passing the range start and end date to be the same value.

When we were making the call directly we were hard coding these parameters and Oracle was deducing that they represented a single date, whereas when passing them via code the range was passed as bind variables.

This meant that Oracle selected to scan across all partitions in one case, and against a specific partition in the other.

We ultimately fixed the query by passing in just a single date parameter, since we realised we didn't need a range!

A rather peculiar situation admittedly!

Have you try to create indexes on which you are creating query.
I had a similar issue with my project, in which i have to fetch a list of users as per search criteria of firstname , lastname and email_address,
To fetch the specific set of users from total 1,000,000 users, hibernate is taking aroung 300 seconds .
But after  creating indexes of firstname , lastname and email_address ,it takes only 2-3 seconds for same number of users as previous.

Even one more strange thing i have notice with hibernate, take example of Class User

Class User
{
    ...
    String firstName;
    String lastName;
    String email;
    ...
}

if i create a search criteria for User and adding restriction of email containing "gmail.com" , its gave me result in around 10 secs, result contains approximate 10,000 users.
But if i write a HQL query for same conditions which i have added in hibernate criteria, its gave me result in around 2-3 seconds.

So what i have notice is HQL are much faster then criteria.
This can be wrong , but i am sharing my personal experience.

I hope this will help you to solve performance issue.


Normalised database with one-to-one relationship Is it bad practice to use a 1-1 relationship between two logically distinct tables: the user details and the user configuration settings? Each configuration record would have a single unique user ID, but they seemed separate enough to warrant two tables rather than one big one. I ask because the WikiBook for my school course said that I should only use 1-Many relationships.

In the User table I will be storing details such as their username, id and authorisation token for an external system. In the Config table I will be storing settings such as whether to show a welcome message, which are changeable through the settings page.

The attributes should be split in 2 tables because the configuration details are not depending on the user but on the configuration of an external system. If a user can have only 1 configuration then you must use an 1-1 relation. If a user can have multiple configurations then you must use the 1-M relation.

This is the theory. If you want (in case of a 1-1 relation) put all in 1 table than this is also 'allowed'. Remember well that if one day the relation becomes 1-M that you are in problems. This is also why it is preferred to split the information into 2 tables.


Size of Binary log file in Mysql Replication I have a master - server replication setup in Linux servers.

Master Configuration details :

 MySQL version is 5.1 and replication mode is MIXED type.

Slave Configuration details :

 MySQL version is 5.6

If i insert single record , then it is getting replicated to slave.

When i am running a script to insert thousand of records at master , it is not getting replicated.

When i see the size of binary log file , it is of 908 MB
and when i see the size of relay log file , it is 560 KB.

Does , size of a binary log file matters ?
or
what is the probable cause of this.

Below is master cnf details :

  [mysqld]

  datadir=/var/lib/mysql
  socket=/var/lib/mysql/mysql.sock
  user=mysql
  # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    bind-address = #master IP# ( not showing here )
    port=3306
    wait_timeout=90
    net_read_timeout=90
    connect_timeout=90
    max_connections=100
    lower_case_table_names=1
    log-bin=mysql-bin
    binlog_format=MIXED
    server-id=1

[mysqld_safe]
     log-error=/var/log/mysqld.log
     pid-file=/var/run/mysqld/mysqld.pid

I have resolved the issue myself only using following process :

Change the replication mode to ROW.
Restrict the size of max_binlog_size=30M

In this way binary log file cant be of huge size and easy to replicate.


Oracle Compatible Parameter to set Is there any difference in setting the Compatible Parameter to 11.2.0.0 vs 11.2.0.4 ? Are there any advantages / dis-advantages ?

The parameter is described in the Upgrade Guide. You cannot downgrade a database to a version that is below the version of the COMPATIBLE parameter. The COMPATIBLE parameter controls how data is written to disk. But it does not control the usage of optimizer features. There are a lot of parameter to control the oprimizer features. The OPTIMIZER_FEATURES_ENABLED is a parameter that you have to set appropriately to enable the optimizer features of the Oracle version. Tom Kytes discusses the difference of these two parameter in this article.

Generally only the first 3 digits are taken into account for the compatible parameter, so either one is fine.