tag:blogger.com,1999:blog-4752849182353473259.post2430200409164296926..comments2024-03-15T00:14:24.636-07:00Comments on James' SQL Footprint: is count(1) faster than count(*)?Anonymoushttp://www.blogger.com/profile/12598141235217155685noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-4752849182353473259.post-76960367369599456402013-10-28T21:28:45.461-07:002013-10-28T21:28:45.461-07:00COUNT(1) and COUNT(*) are both blocked if the user...COUNT(1) and COUNT(*) are both blocked if the user is denied SELECT on any column in the table.<br /><br />USE YourDatabaseName<br />GO<br />SET NOCOUNT ON;<br />GO<br />CREATE TABLE dbo.TestColSecurity (Id INT, Name VARCHAR(100));<br />GO<br />INSERT INTO dbo.TestColSecurity<br /> (Id, Name)<br />VALUES (1, 'Joe');<br />GO<br />CREATE LOGIN TestColSecurityUser WITH PASSWORD = 'hello', CHECK_POLICY = OFF;<br />GO<br />CREATE USER TestColSecurityUser FROM LOGIN TestColSecurityUser;<br />GO<br />GRANT SELECT ON dbo.TestColSecurity TO TestColSecurityUser;<br />DENY SELECT ON dbo.TestColSecurity (Name) TO TestColSecurityUser;<br />GO<br /><br />EXECUTE AS USER = 'TestColSecurityUser'<br />GO<br />PRINT 'COUNT(*) - error'<br />SELECT COUNT(*) FROM TestColSecurity<br />GO<br />PRINT 'SELECT Name - error'<br />SELECT Name FROM TestColSecurity<br />GO<br />PRINT 'COUNT(1) - error'<br />SELECT COUNT(1) FROM TestColSecurity<br />GO<br />PRINT 'SELECT Id - works, no error'<br />SELECT Id FROM TestColSecurity<br />GO<br />REVERT<br />GO<br /><br />DROP TABLE dbo.TestColSecurity;<br />DROP USER TestColSecurityUser;<br />DROP LOGIN TestColSecurityUser;<br />GO<br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4752849182353473259.post-85317035966558510972013-10-28T06:58:03.511-07:002013-10-28T06:58:03.511-07:00As far as I've heard, the only difference betw...As far as I've heard, the only difference between the two is when there is column-level security on the table. Meaning COUNT(1) does not check for any, while COUNT(*) does enforce security per column. Never tested it myself. I'd rerun the tests you did with additional security on the table and see what happens. Doubt you'd see any difference without a large number of columns. But I could see security stopping the query entirely if you don't have privileges.Anonymoushttps://www.blogger.com/profile/11385577960868955023noreply@blogger.comtag:blogger.com,1999:blog-4752849182353473259.post-49537705349232834672013-10-28T06:44:02.705-07:002013-10-28T06:44:02.705-07:00Using count(1) is faster than count(*), because it...Using count(1) is faster than count(*), because it saves having to press the Shift key. ;)<br /><br />But seriously, thanks for the demonstration. I've always suspected that to be the case, but it's nice to see the confirmation!Anonymoushttps://www.blogger.com/profile/06613996076623441951noreply@blogger.com